mysql主主复制原理及配置方法

Mysql主主复制原理

主主复制的原理实际上是主从复制的原理，让两台服务器互为主从，就实现了主主复制。以下介绍主从复制原理：

主从复制

如图：

主从复制过程存在三个线程，Master端的I/O线程，Slave的I/O线程与SQL线程。Master端需要开启binlog日志，Slave端需要开启relaylog。

1、Slave端的I/O读取master.info文件，获取binlog文件名和位置点，然后向Master端的I/O线程请求，该binlog文件名和位置点的binlog信息。

（master.info文件在配置主从复制时使用change master命令来指定生成）

2、Master端的I/O线程会根据Slave端的I/O线程请求的信息来读取Master的binlog日志信息与及读取到最新的binlog文件名和位置点一同返回给Slave的I/O线程。

3、Slave端的I/O线程会把获取到的binlog日志写入relaylog（中继日志）文件中，并且更新master.info文件信息。（把读取到Master最新的binlog日志文件名和位置点更新到master.info文件中，下一次当前位置去读取Master的binlog日志）

4、Slave端的SQL线程会定期读取relaylog，把二进制的日志解析成SQL语句，并执行这些SQL语句，同步数据到从库中。

注意：mysql主从复制要求主从两个数据库版本相同，或者从机比主机版本高；要实现主主复制，则需要两个数据库版本相同。

使用mysqlbinlog命令查看logbin文件，结果如下：

复制模式

1.  异步模式（mysql async-mode）

主节点不会主动push bin

log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin

log同步到本地。

2.  半同步模式(mysql semi-sync)

主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长。

3.  全同步模式

主节点和从节点全部执行了commit并确认才会向客户端返回成功。

4.  GTID复制模式

在传统的复制里面，当发生故障，需要主从切换，需要找到binlog和pos点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找binlog和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步。

多线程复制（基于库），在MySQL 5.6以前的版本，slave的复制是单线程的。一个事件一个事件的读取应用。而master是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL 5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制。

基于GTID复制实现的工作原理

主节点更新数据时，会在事务前产生GTID，一起记录到binlog日志中。

从节点的I/O线程将变更的bin log，写入到本地的relay log中。

SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log）。

如果有记录，说明该GTID的事务已经执行，从节点会忽略。

如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log。

在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。

复制方式

1、基于语句的复制在Master上执行的SQL语句，在Slave上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制

2、基于行的复制把改变的内容复制到Slave，而不是把命令在Slave上执行一遍。从MySQL5.0开始支持

3、混合类型的复制默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制

相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

mysql主主复制配置

         本方案使用基于GTID的复制模式，配置过程如下：

1. 两台数据库服务器，IP分别为 192.168.216.128 和 192.168.216.129，在服务器上装MYSQL（我的配置版本为5.5.56）

2. 打开 192.168.216.128 服务器上的MYSQL的配置文件 /etc/my.cnf （路径根据自己服务器的情况来看），将其中的 server-id 设为1（默认为1，总之两台服务器要设置为不同的ID）,然后重启MYSQL服务

3. 打开 192.168.216.129 服务器上的MYSQL的配置文件 /etc/my.cnf （路径根据自己服务器的情况来看），将其中的 server-id 设为2（默认为1），然后重启MYSQL服务

4. 设 192.168.216.128 为主服务器，那么在主服务器上加一个从服务器可以登录的用户，语句如下：

GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.216.129'IDENTIFIED BY 'raisecom@123';

FLUSH PRIVILEGES;

建好后，在192.168.216.129 服务器上执行以下语句

mysql -h 192.168.216.128 -uroot

-praisecom@123

然后试一下可不可以连上，如果可以，则正确，如果连不上，看一下什么原因，是否是防火墙的原因，如果是则去配置防火墙的规则。

5. 以上完成后在主服务器上执行以下语句，查询master的状态

show masterstatus;

可以看到以上结果，这儿我们使用master_auto_position配置，因此不需要关注File 和 Position，其它的两个分别是白名单和黑名单，意思为同步哪几个数据库和不同步哪几个数据库，可自行根据需求进行设置。在从库上执行以下语句：

CHANGE MASTER TO

MASTER_HOST='192.168.216.128',

MASTER_USER='root',

MASTER_PASSWORD='raisecom@123',

MASTER_AUTO_POSITION=1;

6. 执行完毕后，在从库上继续执行如下语句：

slave start;

show slavestatus\G

这样，查看从服务器的状态，如果状态中的用红线标出来两个参数的值都为YES，那证明配置已经成功，否则可以检查一下具体问题出现在什么地方。

这样，就算配置完成了。在主库中新建数据库，新建一张表，插几条数据，到从库上查询一下看是否已经同步过来。

如果失败，可以从以下几个方面去排查问题：　　

1.首先试一下主从服务器相互之间是否PING 得通

2.试一下远程连接是否正确，如果连不上，则有可能是网卡不一致、防火墙没有放行3306 端口

3.server-id 是否配成一致，主从不能一致

4.bin-log 的信息是否正确

二、如何配置MYSQL的主主复制？

上面说了主从复制的配置方法，现在接着上面的配置继续，然后实现双主复制，让以上的两个服务器互为主从。

1. 在主服务器上配置 /etc/my.cnf 文件，配置如下：

binlog_format=mixed

auto_increment_increment=2         #步进值auto_imcrement。一般有n台主MySQL就填n

auto_increment_offset=1            #起始值。一般填第n台主MySQL。此时为第一台主MySQL

binlog-ignore=mysql                #忽略mysql库【我一般都不写】

binlog-ignore=information_schema   #忽略information_schema库【我一般都不写】

gtid-mode = on

配置之后重启MYSQL服务

2.在从服务器上配置 /etc/my.cnf 文件，配置如下

binlog_format=mixed

auto_increment_increment=2   #步进值auto_imcrement。一般有n台主MySQL就填n

auto_increment_offset=2   #起始值。一般填第n台主MySQL。此时为第二台主MySQL

binlog-ignore=mysql   #忽略mysql库【我一般都不写】

binlog-ignore=information_schema   #忽略information_schema库【我一般都不写】

gtid-mode = on

配置之后重启MYSQL服务

3. 在从服务器上添加一个主服务器可以访问的用户，命令如下：

GRANT REPLICATION

SLAVE ON *.* TO 'root'@'192.168.216.128'IDENTIFIED BY 'raisecom@123';

FLUSH PRIVILEGES

建好后，在192.168.216.128 服务器上执行以下语句

mysql -h 192.168.216.129-uroot -praisecom@123

如果可以连上，则进行下一步，连不上的话，参考上面进行问题排查。

4. 因为要互为主从，所以现在从服务器也是master ，所以也要查看一下状态

show masterstatus;

查到相应的信息后，在原来的主服务器上执行以下命令（因为现在它现在也是另一台的从服务器）

CHANGE MASTER TO

MASTER_HOST='192.168.216.129',

MASTER_USER='root',

MASTER_PASSWORD='raisecom@123',

MASTER_AUTO_POSITION=1;

5. 执行完毕后，在原主库上继续执行如下语句：

start slave;

show slavestatus\G;

同上，如果出现如下画面，则证明配置成功。

6. 在两台服务器的MYSQL中分别进行一些建库、建表、插入、更新等操作，看一下另一台会不会进行同步，如果可以则证明主主配置成功，否则还是上面的排错方法，进行错误排查。