linux中部署mysql主从同步示例详解（绝对经典，看了必会）

准备两台服务器并安装MySQL5.7

master服务器

192.168.254.132

slave服务器

192.168.254.136

确保两台服务器安装的mysql版本一致，本测试使用的是mysql5.7，登录mysql数据库，使用如下命令查看mysql数据库版本

[root@localhost ~]# mysql -u root -p
Enter password: 
mysql> show variables like '%version%';

 

master服务器配置流程

1、登录mysql数据库

[root@localhost ~]# mysql -u root -p
Enter password: 

 

2、创建数据库，数据库名称为：TONGBU

mysql> create database TONGBU;

3、进入TONGBU数据库并创建表TB1

mysql> use TONGBU;
mysql> create table TB1(id varchar(60),name varchar(100));

4、修改配置/etc/my.cnf文件

 

修改的具体参数如下：

server-id=1
log-bin=mysql-bin
binlog-do-db=TONGBU
binlog-ignore-db=mysql
sync_binlog=1
binlog_checksum=none
binlog_format=mixed

提示

[mysqld] server-id=1 #数据库唯一ID，主从的标识号绝对不能重复。

log-bin=mysql-bin #开启bin-log，并指定文件目录和文件名前缀

binlog-do-db=liting #需要同步liting数据库。如果是多个同步库，就以此格式另写几行即可。如果不指明对某个具体库同步，就去掉此行，表示同步所有库（除了ignore忽略的库）。

binlog-ignore-db=mysql #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。

sync_binlog = 1 ＃确保binlog日志写入后与硬盘同步

binlog_checksum = none ＃跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none

binlog_format = mixed ＃bin-log日志文件格式，设置为MIXED可以防止主键重复。

在主服务器上最重要的二进制日志设置是sync_binlog，这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上，即使服务器崩溃也会把事件写入日志中。

sync_binlog这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

5、重置mysql数据库

[root@hadoop4 ~]# systemctl restart mysqld.service

6、授权给slave查同步权限

mysql> grant replication slave on *.* to slave@192.168.254.136 identified by "123456";

注意，注意，注意：：：

授权的用户：slave，不是从服务器的用户名，也不是从服务器中Mysql登录用的用户名，也是主服务器为从服务器自定义开户的一个可供从服务读（或者说是同步数据）数据用的一个用户名，可以自定义，但是要求从服务器在my.cnf配置文件中配置的master-user参数的名称一致，

如：master-user=slave #该名称为主服务器上可以进行同步的用户名，也是在此处授权的用户；

另外这一步容易出现一个异常，异常如下

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因是不满足密码设置的策略，这里需要两个命令来设置一下slave服务器mysql密码设置的策略

mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;

 

执行刷新数据库：

flush privileges;

7、授权成功后，查看主从状态

 mysql> show master status;

 

8、查看二进制日志文件

[root@hadoop4 ~]# find / -name mysql-bin.000001

 

可以看到二进制日志文件在路径：/var/lib/mysql/mysql-bin.000001下

[root@hadoop4 ~]# ll /var/lib/mysql

 

9、备份要同步的数据库

1）、数据库备份语法

表结构+数据

mysqldump -u用户名 -p 数据库名 > 数据库名.sql

只导出表结构

mysqldump -u用户名 -p -d 数据库名 > 数据库名.sql如
示例：
#/usr/local/mysql/bin/mysqldump -uroot -p -d abc > abc.sql

执行备份命令前，一定要先到mysql的运行路径下，再执行一下命令，如果不知道可以通过find命令查询:

[root@hadoop4 ~]# find / -name mysqldump

本系统中mysql数据库导出目录如下：

/usr/bin/mysqldump

 

2）、执行数据库导出命令

[root@hadoop4 ~]# /usr/bin/mysqldump -uroot -p TONGBU > /usr/mytest/TONGBU.sql

 

10、把导出的数据库文件拷贝到slave（192.168.254.136）服务器上

[root@hadoop4 mytest]# src TONGBU.sql root@192.168.254.136:/usr/mytest/

 

slave（192.168.254.136）服务器上拷贝成功：

 

11、slave（192.168.254.136）数据库导入备份脚本

1）、数据库导入语法

使用 mysql 命令导入数据库格式如下：

mysql -u用户名 -p 数据库名 < 数据库名.sql
 示例：
# mysql -uroot -p123456 < abc.sql

使用 source 命令导入

先登录到 mysql 中，并创建一个空的数据库：

mysql> create database abc; # 创建数据库
mysql> use abc; # 使用已创建的数据库 
mysql> set names utf8; # 设置编码
mysql> source /home/abc/abc.sql # 导入备份数据库

2）、slave（192.168.254.136）数据库具体导入步骤

登录数据库，并创建数据库

[root@localhost mytest]# mysql -u root -p
mysql> create database TONGBU;
Query OK, 1 row affected (0.03 sec)
mysql> use TONGBU;
Database changed
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

 

执行导入命令如下：

[root@localhost mytest]# mysql -uroot -p TONGBU < /usr/mytest/TONGBU.sql 

 

slave服务器配置流程

1、设置slave数据库的my.cnf配置文件

[root@localhost mytest]# vi /etc/my.cnf

 

具体配置：

[mysqld]
server-id=2
log-bin=mysql-bin-slave #从主机中可要可不要
replicate-do-db=TONGBU
replicate-ignore-db=mysql
slave-skip-errors = all
master-host=192.168.254.132 #指定主服务器IP地址
master-user=slave #指定定在主服务器上可以进行同步的用户名
master-password=123456 #密码
#master-port=3306
master-connect-retry=60 #断点重新连接时间

提示：

[mysqld]

server-id=2 #设置从服务器id，必须于主服务器不同

log-bin=mysql-bin #启动MySQ二进制日志系统

replicate-do-db=liting #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。

replicate-ignore-db=mysql #不同步test数据库

slave-skip-errors = all #跳过所有的错误，继续执行复制操作

当只针对某些库的某张表进行同步时，如下，只同步liting库的haha表和test库的heihei表：

replicate-do-db = liting

replicate-wild-do-table = liting.haha //当只同步几个或少数表时，可以这样设置。注意这要跟上面的库指定配合使用；

replicate-do-db = test

replicate-wild-do-table = test.heihei //如果同步的库的表比较多时，就不能这样一一指定了，就把这个选项配置去掉，直接根据指定的库进行同步。

重点

master-user=slave #指定定在主服务器上可以进行同步的用户名

master-password=123456 #密码

这两个配置要与主服务器上授权的用户名和密码一致，不到主从无法同步数据，一定要清楚这一点，也是主服务器和从服务器是否能同步的核心要点

mysql> grant replication slave on *.* to slave@192.168.254.136 identified by "123456";

2、设置启用授权用户

mysql>stop slave; #停止slave
mysql> change master to master_host='192.168.254.132',master_user='slave',master_password='123456';
mysql> start slave; #启动slave

3、查看配置状态是否成功

1）、从服务器配置状态查看如下

mysql> show slave statusG

 

2）、主服务器配置状态查看命令如下：

mysql> show processlist G

 

目前主服务器和从服务器的配置已经全部结束，下面我们进行测试

1、进入主服务器，登录mysql数据库

[root@localhost ~]# mysql -u root -p
Enter password: 

2、进入TONGBU数据库，并在TB1表中添加一条数据

mysql> use TONGBU;
 
mysql> insert into TB1 values('10001','testname1');
 

 

3、查看主服务器中，TONGBU数据，TB1表中是否添加成功

mysql> select * from TB1;

 

4、进入从服务器，登录mysql数据库

[root@localhost ~]# mysql -u root -p
Enter password: 

5、进入TONGBU数据库，并查询TB1表

mysql> select * from TB1;

 

可以看到，从服务器已经同步了主服务器的数据库信息，linux下部署mysql主从同步-成功！！！！