MYSQL双主同步复制配置

1 MySQL同步机制实现概述

  MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。而在实际项目中，两台分布于异地的主机上安装有MySQL数据库，两台服务器互为主备，客户要求当其中一台机器出现变化或故障时，另外一台能够实时接管服务器上的数据，这就需要两台数据库的数据要实时保持一致，在这里使用MySQL的同步功能实现双机的同步复制。

  MySQL同步机制基于master主服务器把所有对数据库的更新操作(更新、删除 等)都记录在二进制日志里，并维护日志文件的一个索引以跟踪日志循环。从服务器在日志中读取最后一次成功更新的位置，并接收从那时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。因此，想要启用同步机制，在master端就必须启用二进制日志。每个slave从服务器接受来自master上在二进制日志中记录的更新操作，因此在slave上执行了这个操作的一个拷贝。应该非常重要地意识到，二进制日志只是从启用二进制日志开始的时刻才记录更新操作的。所以slave必须在启用二进制日志时把master上已经存在的数据拷贝过来(利用快照或备份)。如果运行同步时slave上的数据和master上启用二进制日志时的数据不一致的话，那么slave同步就会失败。

  MySQL同步功能由3个线程(master上1个，slave上2个)来实现。执行START SLAVE语句后，slave就创建一个I/O线程。I/O线程连接到master上，并请求master发送二进制日志中的语句。master创建一个线程来把日志的内容发送到slave上。这个线程在master上执行SHOW PROCESSLIST语句后的结果中的 Binlog Dump 线程便是。slave上的I/O线程读取master的Binlog Dump线程发送的语句，并且把它们拷贝到其数据目录下的中继日志(relay logs)中。第三个是SQL线程，salve用它来读取中继日志，然后执行它们来更新数据。

  如上所述，每个mster/slave上都有3个线程。每个master上有多个线程，它为每个slave连接都创建一个线程，每个slave只有I/O和SQL线程。在MySQL中执行SHOW PROCESSLIST语句就会告诉我们所关心的master和slave上发生的情况。

2 MySql配置

操作系统为：linux

mysql：mysql-5.7.10

两台服务器，简称A服务器，B服务器，实现双机同步

A服务器ip: 192.168.2.103

B服务器ip: 192.168.2.206

注意：

1、 当两个服务器IP地址不在同一网段时，需要将主服务器和从服务器通过路由器联通。

2、 请确保主服务器和从服务器上的Windows防火墙处于关闭状态！

2.1 在主/备服务器上创建同步用户

在A服务器上增加一个B服务器可以登录的帐号，使用mysql终端输入：(注意tongbu是用于同步的帐号，111111是tongbu的密码，实际中可按需自行设置)

mysql>flush privileges;

mysql>GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'192.168.2.206' IDENTIFIED BY '111111';

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '111111';

mysql>flush privileges;

在B服务器上增加一个A服务器可以登录的帐号：

mysql >flush privileges;

mysql >GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'192.168.1.103' IDENTIFIED BY '111111';

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '111111';

mysql >flush privileges;

2.2主服务器数据库备份（主服务器为新安装时此步省略）

2.3 修改主/备服务器配置文件

1、打开A服务器的my.cnf文件，在文件的[mysqld]下添加如下内容：

skip-name-resolve

server-id=1 #必须与其他服务器不同，且必须为1到232–1之间的一个正整数值

#i am master

log-bin=mysql-bin #二进制文件

binlog-do-db=db_dbs_config #要同步的数据库

binlog-ignore-db=information_schema#不同步的数据库

binlog-ignore-db=db_Im

binlog-ignore-db=db_Mdr

binlog-ignore-db=db_mrs

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=test

slave-skip-errors=1032,1062

2、重启A服务器的mysql服务

3、查看A服务器mysql的mysql库中user表

MySQL>use mysql;

MySQL>select user,host from user;

此时出现了配置好的登陆账号

4、打开B服务器的my.cnf文件，在文件的[mysqld]下添加如下内容：

skip-name-resolve

server-id=2

#i am master

log-bin=mysql-bin

binlog-do-db = db_dbs_config

binlog-ignore-db=information_schema

binlog-ignore-db=db_Im

binlog-ignore-db=db_Mdr

binlog-ignore-db=db_mrs

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=test

slave-skip-errors=1032,1062

5、重启B服务器的mysql服务

6、查看B服务器mysql的mysql库中user表

MySQL>use mysql;

MySQL>select user,host from user;

此时出现了配置好的登陆账号

2.4指定主/备服务器同步信息

1、在A服务器上查看当前二进制日志名和偏移量值：

MySQL> show master status;

记录Mysql二进制文件的file和position值

2、在B服务器上指定更新点（根据上面样例修改MASTER_LOG_FILE值为样例结果中File的值；修改MASTER_LOG_POS值为样例结果中Position的值）

MySQL> stop slave;

MySQL> change master to master_host='192.168.2.103', master_port=3306, master_user='tongbu',master_password='111111',master_log_file='mysql-bin.000004',master_log_pos=1219;

MySQL> start slave;

3、在B服务器上查看当前二进制日志名和偏移量值：

MySQL> show master status;

记录Mysql二进制文件的file和position值

4、在A服务器上指定更新点（根据上面样例修改MASTER_LOG_FILE值为样例结果中File的值；修改MASTER_LOG_POS值为样例结果中Position的值）

MySQL> stop slave;

MySQL>change master to master_host='192.168.2.206',master_port=3306, master_user='tongbu',master_password='111111',master_log_file='mysql-bin.000003',master_log_pos=154;

MySQL> start slave;

5、重启A、B服务器的mysql服务

2.5检查数据库同步状态

1、查看A、B数据库同步状态， 输入以下命令，会出来很多信息，但是主要看Slave_IO_Running与Slave_SQL_Running这两个字段都显示yes就行了。

MySQL> show slave status\G;

2、查看A、B服务器的同步线程状态，输入以下命令，

MySQL > show processlist\G

若命令窗口有下图的三个线程，则证明配置成功，若缺少任何一个线程，请重启主、备两台机器的MySql服务，然后再在MySql命令窗口中输入上述命令查看是否有下图标注的三个线程。若没有这三个线程，请重新检查配置是否正确。

注意：主备服务器的MySql进程必须都有这三个线程才证明配置成功！

常见同步异常及恢复方法另见博文《MySQL同步机制异常及恢复方法》