（5.5）mysql高可用系列——MySQL半同步复制（实践）
关键词，mysql半同步复制
【0】实验环境
操作系统：CentOS linux 7.5
数据库版本：5.7.24
数据库架构：主从复制，主库用于生产，从库用于数据容灾和主库备机，采用默认传统的异步复制。
主库IP：192.168.1.201 端口：3306
从库IP：192.168.1.202 端口：3306
正文：
【1】配置my.cnf
【1.1】通用复制配置
#replication_new log_bin=/mysql/log/3306/mysql-bin #开启binlog log_bin_index=/mysql/log/3306/mysql-bin.index binlog_format=row binlog_rows_query_log_events=on max_binlog_size=2048 bind-address=0.0.0.0 server_id=2013306 expire_logs_days=7 #超过7天的binlog清理 innodb_support_xa=1 binlog_cache_size=1M log_bin_trust_function_creators=1 #同步存储过程、函数、触发器 innodb_flush_log_at_trx_commit=1 sync_binlog=1 transaction-isolation=read-committed relay_log=/mysql/log/3306/relaylog/mysql-relay.log log-slave-updates=1 read_only=1 slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=4 master_info_repository=table #master_info 会记录到 mysql.slave_master_info relay_log_info_repository=table #relay_log 会记录到，mysql.slave_relay_log_info relay_log_recovery=1 slave_skip_errors=ddl_exist_errors slave_preserve_commit_order=1
【2】半同步复制配置的2种办法（5.7）
两种办法 【2.1】手动安装半同步复制插件（在mysql环境下，主从都安装） 【step 1】安装半同步插件 install plugin rpl_semi_sync_master soname 'semisync_master.so'; install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; show plugins; -- 查看插件安装情况 or select * from mysql.plugin;
【step 2】设置开启半同步参数 建议都开启，以免主备切换时需要重新加；如果分开，如下图； 在主库上：set global rpl_semi_sync_master_enabled=1; 在从库上：set global rpl_semi_sync_slave_enabled=1; 如果异步复制正在运行，需要在从库运行一下命令 才会使用半同步； stop slave io_thread;start slave io_thread; 【step 3】检查状态 在主库查看半同步状态：show status like '%rpl_semi%';
圈出第1行表示，连接主库的客户端个数。 圈出第2行表示，该实例做为半同步的主库状态，是on，是开启的。 最后1行，Rpl_semi_sync_slave_status，表示该实例，作为半同步的从库状态，是off，不是从库。 （但是从库上进行查看时，图中的 master_status 与 slave_status 应该正好相反，作为主库是关闭的，作为从库是开启的） 其他重要参数， Repl_semi_sync_master_yes_tx：表示已经同步的事务数，0表示还没有任何一个事务以半同步的方式复制到从库 Repl_semi_sync_master_no_tx：假如为3，表示当前有3事务不是半同步模式下从库及时响应的（比如可能有网络延迟，导致半同步超时切换成异步） 【2.2】写入配置文件（推荐使用，my.cnf） show variables like 'plugin%'; -- 查看插件路径 #修改my.cnf 复制代码 #如果已经有异步复制了，也不需要改其他的，只需要把下面参数加入到my.cnf，重启mysql服务即可。 #如果业务不允许宕机重启实例，那么可以参考方法1，并且把该段代码加上，就可以直接用了。 plugin_dir=/mysql/app/mysql/lib/plugin/ plugin_load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so #如果是5.7，参数前面加上loose_，如下列，如果是5.6 则直接使用 rpl_semi_sync_master_enabled=1 之类的就好了。 #我这里是5.7就直接做增强半同步了（loseless Semisynchronous ） loose_rpl_semi_sync_master_enabled=1 #MySQL开启主的半同步复制（rpl_semi_sync_master_enabled） loose_rpl_semi_sync_slave_enabled=1 #MySQL5.6开启从的半同步复制 loose_rpl_semi_sync_master_timeout=5000 #超时5秒，切回异步 rpl_semi_sync_master_wait_for_slave_count=1 #至少收到1个slave发会的ack rpl_semi_sync_master_wait_point=AFTER_SYNC #MySQL 5.7的方法，AFTER_SYNC（default，增强半同步） & AFTER_COMMIT（传统半同步）
rpl_semi_sync_master_wait_no_slave=on # 默认为on
#1.为OFF时，只要master发现【Rpl_semi_sync_master_clients】小于【rpl_semi_sync_master_wait_for_slave_count】，则 master 立即转为异步模式。
#2.为ON时，空闲时间（无事务提交）里，即使master发现【Rpl_semi_sync_master_clients】小于【rpl_semi_sync_master_wait_for_slave_count】，也不会做任何调整。
# 只要保证在事务超时之前，master收到大于等于【rpl_semi_sync_master_wait_for_slave_count】值的ACK应答数量，master就一直保持在半同步模式；
# 如果在事务提交阶段（master等待ACK）超时，master才会转为异步模式。
#3.无论【rpl_semi_sync_master_wait_no_slave】为ON还是OFF，当slave上线到【rpl_semi_sync_master_wait_for_slave_count】值时，master都会自动由异步模式转为半同步模式。
【3】进行初始化操作
【3.1】完全同步情况
#【1】在主库和从库上 准备复制账户
create user 'rpl'@'192.168.1.%' identified by '123456';
grant replication slave on *.* to 'rpl'@'192.168.1.%';
flush privileges;
select user,host from mysql.user;
#【2】在主库上，设置读锁定有效。以便获取一个一致性的快照
mysql下操作：（锁表，获取一致性）
flush tables with read lock;
#【3】在主库上 show master status;获取主库当前的二进制名和偏移量pos位置。
show master status;
-- 查看到的日志名记录下来，比如：mysql-bin.000002 ， postion： 881
#【4】备份主库还原到从库
逻辑方式：mysqldump。然后scp拷贝过去。然后登录上从库的mysql，直接把dump的文件加载进去即可。
物理方式：直接拷贝，备份删除从库原有data目录，然后把主库的data目录复制过去。
复制到从库后记得删除拷贝过来data/下的auto.cnf，否则uuid会一样，导致无法复制。
不管是物理还是逻辑方式，CP或者mysqldump完后，就都可以解锁主库了
在主库的mysql登录下，使用命令，unlock tables;
【3.2】如果业务正在跑，利用备份还原恢复同步
#【1】在主库和从库上 准备复制账户 create user 'rpl'@'192.168.1.%' identified by '123456'; grant replication slave on *.* to 'rpl'@'192.168.1.%'; flush privileges; select user,host from mysql.user; #【2】利用备份 #mysqldump，如果备份时加了--master-data=2参数，是有记录binlog文件和位置的。
#XtraBackup，也会复制Binlog文件和位置信息
#【3】开始还原 利用备份去进行还原，这个时候多半是不一致的，因为业务在跑。 这个时候利用备份中的binlog 来做 change master to
#【4】change master to 示例
change master to master_host='192.168.1.201', master_port=3306, master_user='rep', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=154;
【4】参数解析
【4.1】show status like '%rpl%'; -- 查看半同步状态
主库：
Variable_name
--------------------------------------------
Rpl_semi_sync_master_clients --已连接到本机主库的客户端输了
Rpl_semi_sync_master_net_avg_wait_time --平均等待时间
Rpl_semi_sync_master_net_wait_time --总等待时间
Rpl_semi_sync_master_net_waits --等待次数
Rpl_semi_sync_master_no_times --关闭半同步的次数
Rpl_semi_sync_master_no_tx --没有成功同步提交的事务个数
Rpl_semi_sync_master_status --主库半同步状态（on为打开半同步，off为异步）
Rpl_semi_sync_master_timefunc_failures --调用函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time --事务平均等待时间、平均传输时间
Rpl_semi_sync_master_tx_wait_time --事务总等待时间 、总传输时间
Rpl_semi_sync_master_tx_waits --事务的总等待次数
Rpl_semi_sync_master_wait_pos_backtraverse -- 后来的事务先来了，先来的事务还没有到的次数
Rpl_semi_sync_master_wait_sessions -- 当前有多少Session 因为slave 而造成的等待
Rpl_semi_sync_master_yes_tx -- 成功被slave恢复同步的事务个数
Rpl_semi_sync_slave_status -- 表示当前本词库是异步还是半同步（主库应该为off，从库因为on）
【4.2】show variables like '%rpl%'; -- 查看半同步配置参数
【5】复制的日常管理
#复制的日常管理与维护
（1）show slave status\G ：在从库查看从库线程状态
（2）flush tables with read lock; ：主从不一致后锁表。
然后 show master status\G
然后 show slave status\G 来查看从库同步状态 或者重新 change master to....
然后 select master_pos_wait('mysql-bin.00002','389'); （即刚刚show master status找到的文件及位置），如果为1 标识超时退出 ，如果为1 则标识主从同步。
最后再主库 unlock tables; 解锁
（3）跳过错误
跳过错误有两种方式： 1.跳过指定数量的事务：（建议如果已经出现了错误，使用这种办法） mysql>slave stop; mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务 mysql>slave start 2.修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误（建议配置时使用这种办法） vi /etc/my.cnf [mysqld] #slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误，DDL错误类型包含 1007,1008,1050,1051,1054,1060,1061,1068,1091,1146（5.6可以用这个） #slave-skip-errors=ddl_exist_errors #跳过DDL错误，all：跳过所有错误(mysql5.7才有ddl_exist_errors)
（4）大对象blog ,text 传输
【6】从库宕机如何重新连入主库？
1. 此2个参数rpl_semi_sync_master_enabled 和rpl_semi_sync_slave_enabled 不要直接写入到my.cnf配置文件开启。
2.在slave库上先 stop slave io_thread ;set global rpl_semi_sync_slave_enabled=0 关闭此参数。
然后start slave io_thread 或者start slave 开启异步复制，让slave库追赶上master库。
3.然后在slave库 set global rpl_semi_sync_slave_enabled=1 ；stop slave io_thread;start slave io_thread;
【7】参数探索
（7.1）rpl_semi_sync_master_wait_for_slave_count 与 rpl_semi_sync_master_wait_no_slave 参数的影响
本部分参考转自：https://www.cnblogs.com/konggg/p/12205505.html
【rpl_semi_sync_master_wait_for_slave_count】 ，
1.master提交后所需的应答数量！如果slave clients数量大于等于这个值，那么master会一路畅行无阻；如果低于这个值，master可能会在事务提交阶段发生一次超时等待，当等待超过参数（rpl_semi_sync_master_timeout）设定时，master就转为异步模式（原理见下一个参数）。
2.master将这个参数值作为标杆，用来和【Rpl_semi_sync_master_clients】参数做比较。
【rpl_semi_sync_master_wait_no_slave】
1.为OFF时，只要master发现【Rpl_semi_sync_master_clients】小于【rpl_semi_sync_master_wait_for_slave_count】，则master立即转为异步模式。
2.为ON时，空闲时间（无事务提交）里，即使master发现【Rpl_semi_sync_master_clients】小于【rpl_semi_sync_master_wait_for_slave_count】，也不会做任何调整。只要保证在事务超时之前，master收到大于等于
rpl_semi_sync_master_wait_for_slave_count】值的ACK应答数量，master就一直保持在半同步模式；如果在事务提交阶段（master等待ACK）超时，master才会转为异步模式。
无论【rpl_semi_sync_master_wait_no_slave】为ON还是OFF，当slave上线到【rpl_semi_sync_master_wait_for_slave_count】值时，master都会自动由异步模式转为半同步模式。
粗略看来，设置为OFF时，master不会因为slave的离线而造成事务等待，这似乎是一个更合适的选择，但是为什么5.7中默认参数为ON呢？目前我还不得而知。
另外，本次实验建立在空闲场景（或者说极微小负载场景）下，在高并发的场景下，这个参数又会导致怎样的结果，这块也尚需后续探索。
【参考文档】
参考文章：
mysql半同步复制 rpl_semi_sync_master_wait_no_slave 的探索：https://www.cnblogs.com/konggg/p/12205505.html
mysql复制实践：https://www.cnblogs.com/gered/p/11239898.html
mysql复制日常维护，故障处理：https://www.cnblogs.com/gered/p/11388986.html
