10.mysql主从复制

1.mysql异步复制
基于binlog和position搭建一主一从架构
192.168.1.100 主
192.168.1.101 从
主要条件：
（1）主库的server-id，从库的server-id保证两个不一致；
（2）主库开启binlog功能，建议重库也开启binlog，并且开启log_slave_updates参数，让从库也写binlog，方便扩张；
（3）保证binlog格式为row模式；
主库上操作：
（1）创建一个主从账号；
create user 'bak'@'192.168.1.%' identified by 'bak123';
grant replication slave on *.* to 'bak'@'192.168.1.%';
flush privilegs;
（2）初始化数据，让从库与主库在某一position位置时达到同步，把主库数据导出，并传到从库上；
/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot123 --master-data=2 -A >all.sql
注：--master-data=2参数让备份出来的文件中记录备份这一时刻的binlog文件与position号，查看all.sql备份文件即可看到；
scp all.sql 192.168.1.101:/root/
从库操作：
（1）恢复数据：
mysql -uroot  -proot123 <all.sql
（2）在命令行执行配置：
CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='bak',
MASTER_PASSWORD='bak123',
MASETE_PORT=3306,
MASTER_LOG_FILE='all.sql导出数据中的master_log_file值',
MASTER_LOG_POS='all.sql导出数据中的master_log_pos值';
（3）执行主从复制命令
start slave;
（4）查看主从复制状态
show slave status \G;
注：从库上I/O thread和SQL thread都呈现yes状态，表示主从复制开始工作。
验证：主上执行数据插入：create table t(id int(11),name varchar(20),city varchar(20));
insert into t (id,name,city) values(1,'zz',zz');
从上检查结果：select * from t ;

主从复制管理命令：
show slave status\G：从库上查看主从复制状态
show master status:查看主库上binlog和position位置，以及开启GTID模式下记录的pid
change master to :在从库上配置主从过程
start slave开启主从同步
stop slave关闭主从同步
reset slave all:清空从库的所有配置信息。

2.主从复制故障处理
主从复制故障处理1：从库上误执行了写入操作，再到主库上执行相同操作（1062），故建议从库上开启read only。
模拟故障：
从库上：insert into t(name,city) values('tzz','sh');
主库上：insert into t(name,city) values('tzz','sh');
从库上：show slave status; 错误：1062
解决办法：通过percona-toolkit工具集中pt-slave-restart命令在从库跳过错误
./pt-slave-restart -uroot -proot123
show slave status\G;

主从故障2，主库更新数据，从库找不到而报错（1032），即从库上少数据：
模拟故障：
在从库上执行：delete from t where name='tzz';
update t set name='zyn' where name='zz';
解决方法：利用binlog和position号，在主库上通过mysqlbinlog命令，找到主库上执行的语句：
/usr/local/mysql/bin/mysqlbnlog  --no-defaults -v -v --base64-output=decode-rows /datamysql/master_log值 |grep -A 10 end_log_pos值
在从库上执行相应导出来的insert语句，并执行./pt_slave-restart -uroot -proot123; show slave status\G;

主从故障3：主从server-id一致：
不同机器设置不同的server-id

3.半同步复制
搭建方式：在异步复制基础上，安装半同步复制插件即可：
首先在主库上安装插件和开启半同步复制功能：
mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql>set global rpl_semi_sync_master_enabled=on;
mysql>show variables like '%semi%';
mysql>show plugins确认插件安装成功
然后在从库上也安装相应软件：
mysql>install plugin rpl_semi_sync_slave soname 'semisync_master.so';
mysql>set global rpl_semi_sync_slave_enabled=on;
mysql>show variables like '%semi%';
启动从库I/O线程，激活半同步复制：
stop slave io_thread;
start slave io_thread;
在主库上查看是否半同步复制正常运行：show global status like '%semi%';
注：Rpl_semi_sync_master_clients参数代表已经有一个从库连接到了主库，且是半同步方式
Rpl_semi_sync_mater_status:参数为on表示已经是半同步复制模式。
Rpl_semi_sync_master_no_tx：代表没有成功接收slave提交的次数
Rpl_semi_sync_master_yes_tx:代表成功接收slave事务回复的次数
Rpl_semi_sync_slave_status:on表示已经成功开启半同步复制模式

4.半同步复制和异步复制模式切换
半同步复制原理是从库从I/O thread接收完主库的binlog，并把它写入relay中后，给主库一个反馈，但是主库等待从库的回复时间超过rpl_semi_snc_master_timeout参数设置的时间，会自动切换为异步复制（目前为10秒）
show variable like '%rpl_semi_sync_master_timeout%';
例把从库i/O thread关掉：stop slave io_thread;
查看从库的半同步状态为off:show global status like '%semi%';
查看主库产同步复制状态：show global status like '%semi%'; 还是为on的
在主库：insert into  tt(name,score) values('ff','99');
在查看主库半同步状态：show global status like '%semi%';
再切换为半同步状态,开启从库：start slave io_thread;
show global status like '%semi%';

5.gtid复制：双叫全局事务ID,mysql5.6后新增，由server_uuid和事务id组成

gtid价值：
（1）gtid使用master_auto_position=1代替了基于binlog和position号的主从复制方式，更方便主从复制建立
（2）gtid可以知道事务在最开始是在哪个实例上提交的
（3）gtid方便实现主从之间的failover，不用去找position和binlog

主从复制中gtid管理和维护：
搭建过程中注意事项：
主库配置：
gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
server-id不能与从库一样
binlog_format=row
从库配置：
gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
主库也创建了复制账号，就可以直接在从库上执行change master to 语句

gtid与传统复制切换
192.168.1.100/101
当前主状态：
过程：
（1）从库执行stop slave,调整为传统复制模式，让master_auto_position=0
执行命令：change master to master_auto_position=0, master_host='192.168.1.100',master_user='bak', master_password='bak123', 'master_log_file='mysql-binlog.0000002', master_log_pos=1110;
执行完成后开启复制功能：start slave
（2）需要在主从服务器上同时调整gtid模式为on_permissive
set global gtid_mode=on_permissive;
（3）需要在主从服务器上同时调整gtid模式为off_permissive
set global gtid_mode=off_permissive;
（4）需要在主从服务器上同时关闭gtid功能
set global enforce_gtid_consistency=off
set global gtid_mode=off
（5）关闭gtid_mode=off和enforce_gtid_consistency=off写入配置文件my.cnf中，下次重启生效
（6）测试切换成功
insert into zz(name,score) values('gg',99);
从库查询select * from tt;
从库执行：show slave status发瑞gtid值没有增加，切换成功。

传统模式切换为gtid复制模式过程：
（1）主从库上同时修改参数enforce_gtid_consistency=warn,确保error log中不会出现警告信息
set global enforce_gtid_consistency=warn
（2）主从服务器上把enforce_gtid_consistency改为on，保证gtid一致
set global enforce_gtid_consistency=on;
（3）主从服务器上调整gtid模式为off_permissive;
set global gtid_mode=off_permissive;
（4）主从服务器上调整gtid模式为on_permissive
set global gtid_mode=on_permissive;
（5）确认从库的onging_anonymous_transaction_count=0,为0意味着没有等待事务，可以直接进行下一步操作
show global status like 'ongoing_anonymous_%';
（6）在主从库上同时设置gtid_mode=on
set global gtid_mode=on;
show ariables like '%gtid%';
（7）把传统模式改为gtid模式，执行stop slave，再执行change master to master_auto_position=1; start slave;

gtid使用中的限制条件：
（1）不能使用create table table_name select * from table_name;
（2）在一个事务中即包含事务表的操作又包含非事务表
（3）不支持create temporary table or drop temporary table操作。
（4）使用gtid复制从库跳过错误时，不支持执行sql_slave_skip_counter参数语法

6.多源复制
多主一从，只能使用mariadb，mysql5.7可以实现
主:192.168.1.100
主:192.168.1.101
从:192.168.1.102

注：
（1）.master 不能拥有相同的数据库名字，且要拥有不同的复制账号
（2）.三台机器之间的数据库参数开启GTID功能，binlog模式为row模式
（3）.从库的参数配置：master_info_repository=table,relay_log_info_repository=table

步骤：
（1）分别在masterA和B上创建复制账号
create user 'bak'@192.168.1.%' identified by 'bak';
grant replication slave on *.* to 'bak'@'192.168.1.%';
flush privileges;
create user 'repl'@192.168.1.%' identified by 'repl';
grant replication slave on *.* to 'repl'@'192.168.1.%';
（2）分别在A和B主机上导出数据并传送到从主机上
/usr/local/mysql/bin/mysqldump -uroot -proot123 --master-data=2 --single-transaction tzy>tzy.sql
scp tzy.sql  192.168.1.102:/root/

/usr/local/mysql/bin/mysqldump -uroot -proot123 --master-data=2 --single-transaction zz >zz.sql
scp zz.sql  192.168.1.102:/root/
（3）在从库上进行数据库恢复
mysql  -uroot  -proot123 <tzy.sql
mysql  -uroot  -proot123 <zz.sql
（4）在从库上分别配置master A-salve和B->slave同步过程
change master to master_host='192.168.1.100',master_user='bak',master_password='bak',master_auto_position=1 for channel 'c1';
change master to master_host='192.168.1.101',master_user='repl',master_password='repl',master_auto_position=1 for channel 'c2';
（5）开启主从复制
start slave for channel 'c1';
start slave for channel 'c2';
查看状态：
show slave status for channel 'c1'\G
show slave status for channel 'c2'\G
也可以通过performance_schema库下replication_connection_status表内容查看
（6）验证数据是否同步
在主上库插入数据 ，在从库上检查数据

7.主从延迟解决方法及监控主从延迟

推荐percona=toolkit工具其中的一个工具pt-heartbeat
确保主从的时间同步一致
192.168.1.100 master server-id=3306100
192.168.1.101 slave server-id=3306101

7.1步骤：
在主库上创建heartbeat心跳表，通过update执行更新时间戳操作，指定建立在zz库下：
./pt-heartbeat -uroot -proot123 --database zz --update --create-table -daemonie
select * from heartbeat;
然后就可以对从库进行监控工作：
./pt-heartbeat --master-server-id=3306100 --monitor --database zz -uroot  -proot123;
显示表示1m，5m，15m的平均值

其他延迟原因：
主从之间同步不是实时同步，是异步的，即主库提交事务后，从库才再执行；
在主库上对没有索引的大表的列进行delete或update操作；
从库硬件没有主库好；
网络抖动导致io线程复制延迟；

延迟解决办法：
开启并行复制功能，设置并行参数，在从库设置参数slave_parallel_workers >0
slave_parallel_type=logical_clock

7.2主从复制的数据校验

主库挂了，从库提升为主库时，数据一致性不能保证，可用percona-toolkit工具其中的pt-table-checksum检查主从数据的一致性，再通过pt-table-sync工具来修复不一致的数据信息。

校验zz库下tt表的数据信息：

pt-table-checksum --no-check-binlog-format --replicate=zz.checksums  --databases=zz --tables=tt -uzz -p123456 -h 102.168.1.101

在主库执行完成命令后，会在主从服务器的zz库下生成表checksums.

在主库上查看zz.checksums;select * from checksums \G;

在从库上查看zz.checksums:select * from checksums \G;

通过在从库执行如下语句查看不一致数据：

select db,tb1,sum(this_cnt) as total_rows,count(*) as chunks from zz.checksums where (master_cnt <> this_cnt or master_crc <> this_crc OR isnull(master_crc) <> isnull(this_crc)) group by db,tb1;

再用pt_table-sync工具来修复：

pt-table-sync --replicate=zz.checksums h=192.168.1.101,u=zz,p=123456 -print

再到主库上执行pt-table-sync命令，使用--execute参数真正修复主从不一致数据。

./pt-table-sync --replicate=zz.checksums h=192.168.1.101,u=zz,p=123456 --execute

最后再校验主从数据是否一致：

./pt-table-checksum --no-check-binlog-format --replicate=zz.checksums --databases=zz --tables=tt -uzz -p123456 -h 192.168.1.101