MySQL主从复制配置 – 运维那点事

一、基于日志点的主从复制

在MySQL 5.5中，复制的方法使用的是基于日志点的复制，什么意思呢？当从服务器要从主服务器复制数据时，需要指定从主服务器的哪个二进制日志文件和二进制日志文件的位置来进行数据的复制操作。由于这种依赖日志点进行数据复制的特性，所有复制是单线程的（如果有多线程复制会出现数据不一致情况）。而主库是多线程写入，所有主从复制延迟就变成了很长见了。开启主从复制功能，需要分别在Master主机和Slave主机做以下几点设置。

Master需要做以下几点？

1）启用二进制日志（尽可能修改二进制文件位置）

log-bin = mysql-bin

1	log-bin = mysql-bin

2）选择一个唯一的server-id

server-id = {232-1}

1	server-id = {232-1}

3）创建具有复制权限的用户

mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
mysql> flush privileges;

1 2	mysql> grant replication slave on . to 'repl'@'%' identified by '123456'; mysql> flush privileges;

Slave需要做以下几点？

1）启用中继日志

relay-log = relay-log
relay-log-index = relay-log.index

1 2	relay-log = relay-log relay-log-index = relay-log.index

2）选择一个唯一的server-id，不能跟主的server-id一样，如果有多个从都需要不同的server-id

server-id = {232-1}

1	server-id = {232-1}

3）连接至主服务器（根据日志点）

mysql> change master to master_host='',master_port=,master_log_file='',master_log_pos=,master_user='',master_password='';

1	mysql> change master to master_host='',master_port=,master_log_file='',master_log_pos=,master_user='',master_password='';

常用参数说明：

master_host：主服务器地址;

master_port：数值类型，主服务器端口;

master_log_file：指定从主服务器哪个二进制日志文件开始复制;

master_log_pos：数值类型，指定从主服务器哪个二进制日志文件的位置开始复制;

master_user：连接到主服务器的用户;

master_password：连接到主服务器的用户密码;

master_connect_retry：连接重试时间;

master_SSL_Allowed：是否开启基于SSL的复制;

master_SSL_CA_File：指定CA文件;

master_SSL_CA_Path：指定CA文件路径;

master_SSL_Cert：指定证书;

master_SSL_Cipher：加密算法;

master_SSL_Key：私钥文件;

PS：更多选项查看帮助信息(help change master to)，另外值为数字类的参数不需要加”号，比如master_port和mater_log_pos参数，不然会报语法错误。

4）启动复制线程复制数据

mysql> start slave;

1	mysql> start slave;

或

mysql> start slave io_thread;
mysql> start slave sql_thread;

1 2	mysql> start slave io_thread; mysql> start slave sql_thread;

PS：除了start启动slave线程外，还可以使用reset SLAVE重置SLAVE，使用stop SLAVE关闭SLAVE。

二、基于日志点的主从复制实例

1. 主从复制配置前的环境设置

A. Master/Slave时间同步。

B. Master/Slave关闭防火墙和Selinux。

C. Master/Slave主机名设定。

D. Master/Slave安装MySQL5.7，参见MySQL 5.7多方式安装。

2. MySQL主从复制配置（默认异步协议）

Master配置（192.168.60.10）

1）配置主配置文件，追加以下选项

[root@localhost ~]# cat /etc/my.cnf
log-bin = /data/mysql/log/binlog/mysql-bin
server-id = 100
innodb-file-per-table = 1
datadir = /var/lib/mysql/
sync-binlog = 1

[root@localhost ~]# cat /etc/my.cnf

log-bin = /data/mysql/log/binlog/mysql-bin

server-id = 100

innodb-file-per-table = 1

datadir = /var/lib/mysql/

sync-binlog = 1

2）创建复制账号

mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
mysql> flush privileges;
mysql> show slave hosts;

mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';

mysql> flush privileges;

mysql> show slave hosts;

3）创建需要的目录

[root@localhost ~]# mkdir /data/mysql/log/binlog
[root@localhost ~]# chown mysql:mysql /data/mysql/log/binlog

1 2	[root@localhost ~]# mkdir /data/mysql/log/binlog [root@localhost ~]# chown mysql:mysql /data/mysql/log/binlog

然后启动MySQL服务即可。

Slave配置（192.168.60.40）

1）配置主配置文件，追加以下选项

[root@localhost ~]# cat /etc/my.cnf
datadir = /var/lib/mysql
server-id = 200
innode-file-per-table = 1
relay-log = /data/mysql/log/relaylog/relay-log
relay-log-index = /data/mysql/log/relaylog/relay-log.index
master-info-repository = table
relay-log-info-repository = table

###########Slave可选参数##########
log-bin = /data/mysql/log/binlog/mysql-bin
log-slave-updates = true
read-only = 1
skip-slave-start = true

[root@localhost ~]# cat /etc/my.cnf

datadir = /var/lib/mysql

server-id = 200

innode-file-per-table = 1

relay-log = /data/mysql/log/relaylog/relay-log

relay-log-index = /data/mysql/log/relaylog/relay-log.index

master-info-repository = table

relay-log-info-repository = table

###########Slave可选参数##########

log-bin = /data/mysql/log/binlog/mysql-bin

log-slave-updates = true

read-only = 1

skip-slave-start = true

2）连接Master服务器

mysql> change master to master_host='192.168.60.10',master_user='repl',master_password='123456';

1	mysql> change master to master_host='192.168.60.10',master_user='repl',master_password='123456';

这里要注意一点，我的从服务器连接主服务器时没有指定二进制文件和position，因为我这里的实验是在主从服务器都是第一次配置复制的情况下。如果你的主服务器已经有了很多数据了，而你这个时候想添加从服务器的话，就需要把主服务器的数据，可以使用mysqldump导出来（要指定记录导出时的二进制文件和position），然后把导出来的数据再次导入进从服务器。此时，再去连接主服务器就需要指定从主服务器的那个二进制文件和position开始复制数据了。而怎么知道从那个二进制文件和position开始复制数据呢？就是从mysqldump导出数据文件中记录了导出数据时的二进制文件和position。

另外change master的连接信息默认会保存到MySQL数据目录下，名为master.info的文件中，所以是永久性的。另外也可以把连接选项直接定义在my.cnf配置文件中，但需要重启服务器生效，格式如下：

master-host = 192.168.60.10
master-port = 3306
master-user = backup
master-password = 123456

master-host = 192.168.60.10

master-port = 3306

master-user = backup

master-password = 123456

3）启动复制线程

mysql> start slave;
mysql> show slave status\G

1 2	mysql> start slave; mysql> show slave status\G

查看复制状态。

I/O_thread：用于向主服务器请求二进制日志中的事件

SQL_thread：用于从中继日志读取事件并在本地执行

4）重启Slave的MySQL

当数据库重启之后数据库连接和复制线程会自动启动继续复制数据，因为从服务器的连接信息保存在数据目录下的master.info中（保存的就是change master命令），而复制记录保存在relay-log.info中。其中IO线程更新master.info文件，SQL线程更新relay-log.info文件。

但是如果Master出现故障复制停止，那么当Slave在次重新启动之后就会关闭slave，这就是参数skip-slave-start的作用。

Slave验证复制

可以直接在Master上插入数据，然后在Slave上查看数据是否同步过来了。还可以在Master上查看当前二进制文件以及Position，命令如下：

mysql> show master status;

1	mysql> show master status;

然后在Slave上查看当前二进制文件以及Position，然后跟Master的作对比。如果一致复制就没有问题。

mysql> show slave status;

1	mysql> show slave status;

三、主从操作命令

# 查看slave详细信息;
mysql> show slave status;

# 启动slave;
mysql> start slave;

# 关闭salve;
mysql> stop slave;
  
# 重置slave（会删除master.info和relay-log.info,但是同步信息会保留）;
mysql> reset slave;

# 重置slave（会彻底清除所有信息）;
mysql> reset slave all;

# 重置master（会删除master.info和relay-log.info,但是同步信息会保留）;
mysql> reset master;
 
# 重置master（会彻底清除所有信息）;
mysql> reset master all;

# 查看slave详细信息;

mysql> show slave status;

# 启动slave;

mysql> start slave;

# 关闭salve;

mysql> stop slave;

# 重置slave（会删除master.info和relay-log.info,但是同步信息会保留）;

mysql> reset slave;

# 重置slave（会彻底清除所有信息）;

mysql> reset slave all;

# 重置master（会删除master.info和relay-log.info,但是同步信息会保留）;

mysql> reset master;

# 重置master（会彻底清除所有信息）;

mysql> reset master all;

RESET MASTER

删除所有index file中记录的所有binlog文件，将日志索引文件清空，创建一个新的日志文件，这个命令通常仅仅用于第一次用于搭建主从关系的时的主库。

注意：reset master不同于purge binary log的两处地方

1）reset master将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件起始值从000001开始，然而purge binary log命令并不会修改记录binlog的顺序的数值。

2）reset master不能用于有任何slave正在运行的主从关系的主库，因为在slave运行时刻reset master命令不被支持，reset master将master的binlog从000001开始记录，slave记录的master log则是reset master时主库的最新的binlog，从库会报错无法找的指定的binlog文件。

In MySQL 5.6.5 and later, RESET MASTER also clears the values of the gtid_purged system variable (known as gtid_lost in MySQL 5.6.8 and earlier) as well as the global value of the gtid_executed (gtid_done, prior to MySQL 5.6.9) system variable (but not its session value); that is, executing this statement sets each of these values to an empty string (”)

RESET SLAVE

RESET SLAVE将使slave忘记主从复制关系的位置信息。该语句将被用于干净的启动，它删除master.info文件和relay-log.info文件以及所有的relay log文件并重新启用一个新的relaylog文件。

使用reset slave之前必须使用stop slave 命令将复制进程停止。

注：所有的relay log将被删除不管他们是否被SQL thread进程完全应用（这种情况发生于备库延迟以及在备库执行了stop slave 命令），存储复制链接信息的master.info文件将被立即清除，如果SQL thread 正在复制临时表的过程中，执行了stop slave ，并且执行了reset slave，这些被复制的临时表将被删除。

RESET SLAVE ALL

在5.6版本中reset slave并不会清理存储于内存中的复制信息，比如 master host，master port，master user，or master password，也就是说如果没有使用change master命令做重新定向，执行start slave还是会指向旧的master上面。

当从库执行reset slave之后,将mysqld shutdown 复制参数将被重置。

在5.6.3 版本以及以后使用使用RESET SLAVE ALL来完全的清理复制连接参数信息。(Bug #11809016)

RESET SLAVE ALL does not clear the IGNORE_SERVER_IDS list set by CHANGE MASTER TO. This issue is fixed in MySQL 5.7. (Bug #18816897)
In MySQL 5.6.7 and later, RESET SLAVE causes an implicit commit of an ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

四、从库复制相关参数

log-bin = mysql-bin

从服务器是否开启二进制日志，默认关闭。

log-slave-updates = 0 | 1

Slave更新操作是否记入二进制日志，如果开启则必须要开启log-bin，开启二进制日志可以做级联复制。

sync-binlog = 0 | 1 | N

设置二进制日志刷新到磁盘的时间点，值为1时表示每次或每组事务提交都会刷新二进制日志，值为0时表示跟随操作系统刷盘的时间来刷新二进制日志到磁盘，值为N时表示每N个事务或N组事务提交会刷新二进制日志到磁盘。设置为1是保证Master CrashSafe的关键，最安全的方式。

在MySQL 5.7.7之前，默认值为sync_binlog=0。MySQL 5.7.7及更高版本使用默认值为1，这是最安全的选择，但如上所述可能会影响性能。

binlog-format = row

启用基于行的二进制日志的记录，对于复制更容易校验，不容易出错。

server-id = 1

同一个复制拓扑中的每个服务器的id号必须唯一。

read-only = 0 | 1

锁定从服务器为只读，对于super用户不生效，对只有select、update、insert、delete权限的用户生效。

skip-slave-start

告诉从服务器当服务器启动时不启动从服务器线程，使用START SLAVE语句在以后启动线程。

这四个参数是启用binlog/relaylog的校验，防止日志出错。

binlog-rows-query-log-events = 1

启用二进制日志记录事件相关的详细信息，可降低故障排除的复杂度。

relay_log_purge = 0 | 1

开启relay log自动purge操作，默认就是开启的。

relay_log_recovery = 0 | 1

当Slave发生crash导致重连master时，其不根据relay-info的信息进行重连，而是根据master-info中执行到master的位置信息重新开始拉master上的二进制日志数据。MySQL 5.6提供了把master-info信息保存到表中，需开启master-info-repository参数，当Slave发生Crash时，就能保证Slave安全。

slave-paralles-workers = 0

默认是0，不开启从服务器的多线程复制，MySQL 5.6中从服务器设置多线程复制只能针对多个库才有效，如果没有多个库开启多线程只会增加系统开销。（Mariadb中此参数为slave-paralles-threads）。

binlog-checksum = CRC32

二进制日志的校验算法。

master-verify-checksum = 1

启用此选项后，从库将检查从中继日志读取的校验和，如果发生不匹配，则从库将停止并出现错误。默认禁用。

slave-sql-verify-checksum = 1

启动此选项后，从库线程使用从中继日志读取的校验和来验证数据。在不匹配的情况下，从库停止并出现错误。设置此变量将立即对所有复制通道生效，包括运行通道。

report-port = 3306

report-host = master_ip/slave_ip

提供复制报告的端口，和数据库端口一致。提供复制报告的主机，设置为当前主机的主机IP。当在从库设置了这两个参数时，在主库使用show slave hosts时才可以看到完整的从库信息。

master-info-repository = file | table

relay-log-info-repository = file | table

MySQL 5.6开始支持在SLAVE上把master信息和relay信息记录在事务表，用于解决从库宕机后的主从数据一致性问题。另外，如果你使用MySQL5.7的多源复制的话就必须的要求把master和relay信息存储到事务表中。具体当Slave发送Crash导致重连master时怎么会导致数据不一致性看这篇文章：http://www.ywnds.com/?p=7326。

sync_master_info = 10000

此变量对从库的影响取决于从库master_info_repository是否设置为FILE或TABLE，如以下段落所述。

master_info_repository = FILE。如果sync_master_info值大于0，则从库在每次事件之后将其master.info文件同步到磁盘（使用 fdatasync()）。sync_master_info如果为0，则MySQL服务器不执行master.info文件到磁盘的同步，相反，服务器依赖于操作系统与其他文件一样定期刷新其内容到磁盘。

master_info_repository = TABLE。如果sync_master_info值大于0，则在每个sync_master_info事件之后，从库更新其主信息存储到表。如果为0，则表不会更新。

默认值为sync_master_info=10000，设置此变量将立即对所有复制通道生效，包括正在运行的通道。

sync_relay_log_info = 10000

此变量对从库的影响取决于从服务器的relay_log_info_repository设置（FILE或 TABLE），如果是这样TABLE，还判断中继日志信息表使用的存储引擎是否是事务性的（如InnoDB）还是不非事务性的（如MyISAM）。这些因素使从库对sync_relay_log_info的值大于零的行为的影响如下表所示：

relay_log_info_repository = FILE。如果sync_relay_log_info值大于0，则从库在每次事件之后将其relay-log.info文件同步到磁盘（使用 fdatasync()）。sync_relay_log_info如果为0，则MySQL服务器不执行relay-log.info文件到磁盘的同步，相反，服务器依赖于操作系统与其他文件一样定期刷新其内容到磁盘。

relay_log_info_repository = TABLE。不管sync_relay_log_info值是大于0或等于0，则在每个sync_relay_log_info事件之后，从库都会更新信息存储到表，前提此表引擎是事务表。如果是非事务表（如Myisam），当sync_relay_log_info=0，则表不会更新。

默认值为sync_relay_log_info=10000，设置此变量将立即对所有复制通道生效，包括正在运行的通道。

sync_relay_log = 10000

如果此变量的值大于0，则在每个sync_relay_log事件写入中继日志后，MySQL服务器将其中继日志同步到磁盘（使用fdatasync() ）。设置此变量将立即对所有复制通道生效，包括正在运行的通道。

设置sync_relay_log为0会导致磁盘无法同步，在这种情况下，服务器依赖于操作系统刷新中继日志的内容，与其他文件一样。

值为1是最安全的选择，因为在发生崩溃的情况下，从中继日志中最多丢失一个事件。但是，它也是最慢的选择（除非磁盘具有电池备份的缓存，这使得同步非常快）。

slave_transaction_retries = 10

如果从库SQL线程失败，是因为执行事务碰到InnoDB死锁或事务执行时间超过InnoDB的innodb_lock_wait_timeout，它会自动重试。默认值为10，设置此变量将立即对所有复制通道生效，包括运行通道。建议值为128次。

从MySQL 5.7.5起，在从库上启用多线程时，支持重试事务。在以前的版本中，从库使用多线程时，slave_transaction_retries被视为等于0。

slave_skip_errors = off | ddl_exist_errors | all |

通常情况下，当从库发生错误时，复制停止，这样你就可以手动解决数据的不一致。此选项会导致从库SQL线程在slave_skip_errors返回选项值中列出的任何错误时继续复制，虽然不会发生错误了，但很大可能会导致主从数据不一致。所以非常不推荐使用all值来使从库忽略所有错误消息。

MySQL 5.7支持一个额外的速记值ddl_exist_errors，相当于错误代码列表1007,1008,1050,1051,1054,1060,1061,1068,1094,1146。附录B，错误，错误代码和常见问题列出了服务器错误代码。

五、MySQL使用半同步协议复制

1. 半同步机制

A. 当Master上开启半同步复制的功能时，至少应该有一个Slave开启其功能。此时，一个线程在Master上提交事务将受到阻塞，直到得知一个已开启半同步复制功能的Slave已收到此事务的所有事件，或等待超时。

B. 当Slave主机连接到Master时，能够查看其是否处于半同步复制的机制。

C. 当一个事务的事件都已写入其relay-log中且已刷新到磁盘上，Slave才会告知已收到；这取决于sync_relay_log参数。

D. 如果等待超时，也就是Master没被告知已收到，此时Master会自动转换为异步复制的机制。当至少一个半同步的Slave赶上了，Master与其Slave自动转换为半同步复制的机制。

E. 半同步复制的功能要在Master，Slave都开启，半同步复制才会起作用；否则，只开启一边，它依然为异步复制。

2. Master配置（接着上一个实验开始）

首先，安装半同步模块并启动，此模块根据MySQL安装方式不同位置也可能不太一样。如果是二进制安装，则会在MySQL安装目录的./lib/plugin/下存放。建议无论主从都安装master & slave模块，以免高可用模式下遗忘。

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

1 2	mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

查看半同步相关的状态信息，如下：

mysql> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
| rpl_semi_sync_slave_enabled        | OFF   |
| rpl_semi_sync_slave_trace_level    | 32    |
+------------------------------------+-------+
6 rows in set (0.00 sec)

mysql> show global variables like '%semi%';

+------------------------------------+-------+

| Variable_name | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_semi_sync_slave_enabled | OFF |

| rpl_semi_sync_slave_trace_level | 32 |

+------------------------------------+-------+

6 rows in set (0.00 sec)

其中rpl_semi_sync_master_enabled参数表示Master是否开启了半同步协议，参数rpl_semi_sync_master_timeout表示与Slave半同步协议通信超时时间，如果超过这个时间则自动降级为异步复制。

下面开启Master上的半同步复制协议，并且调小了超时时间为2s，一旦有一次超时自动降级为异步。

mysql> set global rpl_semi_sync_master_enabled = 1;
mysql> set global rpl_semi_sync_master_timeout = 2000;

1 2	mysql> set global rpl_semi_sync_master_enabled = 1; mysql> set global rpl_semi_sync_master_timeout = 2000;

以上内容要想永久有效需要写到配置文件中，生产需写入配置文件，避免重启后半同步失效。

rpl_semi_sync_master_enabled = 1;
rpl_semi_sync_master_timeout = 2000;

1 2	rpl_semi_sync_master_enabled = 1; rpl_semi_sync_master_timeout = 2000;

3. Slave配置（接着上一个实验开始）

首先，安装半同步模块并启动。

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> show global variables like '%semi_sync_slave%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

mysql> set global rpl_semi_sync_slave_enabled = 1;

mysql> show global variables like '%semi_sync_slave%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | ON |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

2 rows in set (0.00 sec)

从节点需要重新连接主服务器半同步才会生效。

mysql> stop slave io_thread;
mysql> start slave io_thread;

1 2	mysql> stop slave io_thread; mysql> start slave io_thread;

PS：如果想卸载异步模块就使用uninstall即可。

4. Master上查看是否启用了半同步

此时，半同步复制应该已经正常工作了。可以在Master上查看半同步相关状态变量。

mysql> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

mysql> show global status like '%semi%';

+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 0 |

| Rpl_semi_sync_master_tx_wait_time | 0 |

| Rpl_semi_sync_master_tx_waits | 0 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 0 |

| Rpl_semi_sync_slave_status | OFF |

+--------------------------------------------+-------+

15 rows in set (0.00 sec)

现在半同步已经正常工作了，主要看Rpl_semi_sync_master_clients是否不为0，Rpl_semi_sync_master_status是否为ON。其中Rpl_semi_sync_master_clients=1表示此时有一个Slave以半同步协议连接Master。如果Rpl_semi_sync_master_status为OFF，说明出现了网络延迟或Slave IO线程异常，导致自动切换为异步复制了。

那么可以验证一下半同步超时，是否会自动降为异步工作。可以在Slave上停掉半同步协议，然后在Master上创建数据库看一下能不能复制到Slave上。

Slave

# 关闭半同步;
mysql> set global rpl_semi_sync_slave_enabled = 0;
mysql> stop slave io_thread;
mysql> start slave io_thread;

# 关闭半同步;

mysql> set global rpl_semi_sync_slave_enabled = 0;

mysql> stop slave io_thread;

mysql> start slave io_thread;

Master

mysql> create database dbtest;
Query OK, 1 row affected (2.01 sec)

mysql> create database dbtest01;
Query OK, 1 row affected (0.01 sec)

mysql> create database dbtest;

Query OK, 1 row affected (2.01 sec)

mysql> create database dbtest01;

Query OK, 1 row affected (0.01 sec)

创建第一个数据库花了2.01秒，而我们前面设置的超时时间是2秒，而创建第二个数据库花了0.01秒，由此得出结论是超时转换为异步传送。可以在Master上查看半同步相关的参数值Rpl_semi_sync_master_clients和Rpl_semi_sync_master_status是否正常。

mysql> show global status like '%semi%';   
+--------------------------------------------+-------------+
| Variable_name                              | Value       |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients               | 0           |
| Rpl_semi_sync_master_net_avg_wait_time     | 294         |
| Rpl_semi_sync_master_net_wait_time         | 588         |
| Rpl_semi_sync_master_net_waits             | 2           |
| Rpl_semi_sync_master_no_times              | 1           |
| Rpl_semi_sync_master_no_tx                 | 2           |
| Rpl_semi_sync_master_status                | OFF         |
| Rpl_semi_sync_master_timefunc_failures     | 0           |
| Rpl_semi_sync_master_tx_avg_wait_time      | 317         |
| Rpl_semi_sync_master_tx_wait_time          | 635         |
| Rpl_semi_sync_master_tx_waits              | 2           |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0           |
| Rpl_semi_sync_master_wait_sessions         | 0           |
| Rpl_semi_sync_master_yes_tx                | 2           |
| Rpl_semi_sync_slave_status                 | OFF         |
| Rpl_status                                 | AUTH_MASTER |
+--------------------------------------------+-------------+
16 rows in set (0.00 sec)

mysql> show global status like '%semi%';

+--------------------------------------------+-------------+

| Variable_name | Value |

+--------------------------------------------+-------------+

| Rpl_semi_sync_master_clients | 0 |

| Rpl_semi_sync_master_net_avg_wait_time | 294 |

| Rpl_semi_sync_master_net_wait_time | 588 |

| Rpl_semi_sync_master_net_waits | 2 |

| Rpl_semi_sync_master_no_times | 1 |

| Rpl_semi_sync_master_no_tx | 2 |

| Rpl_semi_sync_master_status | OFF |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 317 |

| Rpl_semi_sync_master_tx_wait_time | 635 |

| Rpl_semi_sync_master_tx_waits | 2 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 2 |

| Rpl_semi_sync_slave_status | OFF |

| Rpl_status | AUTH_MASTER |

+--------------------------------------------+-------------+

16 rows in set (0.00 sec)

可以看到都自动关闭了，需要注意一点的是，当Slave开启半同步后，或者当主从之间网络延迟恢复正常的时候，半同步复制会自动从异步复制又转为半同步复制，还是相当智能的。

另外，参数Rpl_semi_sync_master_no_times表示主库启动切换为异步的次数，可以看到我们这里模拟了一次切换，值加1了。参数Rpl_semi_sync_master_no_tx表示降级为异步后产生的事务数，这里变为2了。需要注意的是，DDL一条语句执行成功就会加1，DML一个事务成功才会加1。这些参数都可以用来做监控使用。

另外个人在实际使用中还碰到一种情况从库IO线程有延迟时，主库会自动把半同步复制降为异步复制；当从库IO延迟没有时，主库又会把异步复制升级为半同步复制。可以进行压测模拟，但是此时查看Master的状态跟上面直接关闭Slave半同步有些不同，会发现Rpl_semi_sync_master_clients仍然等于1，而Rpl_semi_sync_master_status等于OFF。

六、MySQL双主复制

MySQL双主复制模型如果配置出现问题，很有可能会导致主从数据不一致。单双主模型能做什么呢？可以分摊读的压力，但写数据同一时刻只能有一台。另外可以在双主模型下做高可用。

1. 在两台服务器上各自建立一个具有复制权限的用户。

2. 修改配置文件。

# 主服务器上配置

[mysqld]
server-id = 10
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 1

[mysqld]

server-id = 10

log-bin = mysql-bin

relay-log = relay-mysql

relay-log-index = relay-mysql.index

auto-increment-increment = 2

auto-increment-offset = 1

# 从服务器上配置

[mysqld]
server-id = 20
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 2

[mysqld]

server-id = 20

log-bin = mysql-bin

relay-log = relay-mysql

relay-log-index = relay-mysql.index

auto-increment-increment = 2

auto-increment-offset = 2

两台服务器都需要开启二进制日志和中继日志。对于Master设置主键自动增长auto-increment-offset起始值从1开始，auto-increment-increment步长为2，也就是auto_increment为奇数。而对于Slava自动增长从2开始，偏移为2，也就是auto_increment为偶数。这样才能避免两台服务器数据同步时出现主键冲突问题。

3）主从各自连接对方服务器CHANGE MASTER

4）主从服务器各自自动复制对方的Slave