欢迎访问我的博客,你的支持,是我最大的动力!

MySQL王者晋级之路(五)主从复制

Mysql-笔记 小马奔腾 6589℃ 评论
目录:
[显示]

《MySQL王者晋级之路》张甦,电子工业出版社,2018.3

基于MySQL5.6和MySQL5.7

主从复制

MySQL支持单向/双向/链式级联/异步复制
5.5加入半同步复制
5.6加入GTID复制
5.7加入多源复制/并行复制/loss-less复制

主从架构模式

单向主从
双向主从
级联主从
一主多从
多主一从(5.7)

主从复制原理
线程:
主服务器:一个工作线程I/O dump thread
从服务器:两个工作线程I/O thread,SQL thread主库记录binlog日志,从库I/O thread请求主库binlog日志,并将得到的binlog日志写到自己的relay log(中继日志)文件中,然后在从库上应用中继日志中的SQL语句,主库通过I/O dump thread给从库I/O thread传送binlog日志

复制中的重点参数
log-bin,开启二进制日志
server-id,MySQL服务唯一标识
server-uuid,数据库启动过程中自动生成,存放在数据目录auto.cnf文件下
read only,设置从库为只读状态,避免从库上进行写操作。注:对超管权限super帐号没有效果,5.7后新增super_read_only参数,开启后超管也没有权限写入
binlog_format,二进制日志格式,建议row
log_slave_updates,将从master上获取的数据变更再记录到从服务器的二进制日志文件中
binlog_error_action,控制当不能写binlog时,server将会怎样。5.7后新增,有ABORT_SERVER(默认)和IGNORE_ERROR两个值
#ABORT_SERVER表示写binlog无法写入时服务退出;IGNORE_ERROR代表在错误日志中记录错误并强制关闭binlog功能
binlog-do-db,选择启用binlog的数据库(在主库上使用)。应尽量保证复制的过滤规则不在主库上添加
binlog-ignore-db,忽略某个库的复制
gtid_mode,决定gtid模式是否开启,使用gtid,设置gtid_mode=on
enforce-gtid-consistency,使用gtid复制模式时,要开启该参数,用来保证gtid的一致性,设置enforce-gtid-consistency=on
gtid_next,session级别的变量,下一个gtid,默认AUTOMATIC
gtid_purged,丢弃掉的gtid
relay log,记录从库的I/O thread从主库读取来的binlog内容
replicate_do_table,只复制指定的表,从库上使用
replicate_ignore_table,不复制指定的表,从库上使用
replicate_do_db,只复制指定的库,从库上使用
replicate_ignore_db,不复制指定的库,在从库上使用
replicate-wild-do-table,使用通配符复制指定的表,通配符为%,如test.aa%
replicate-wild-ignore-table,使用通配符不复制指定的表
master_info_repository,把master.info主从状态和配置信息记录下来,默认记录到文件里,建议使用表记录:master_info_repository=table
relay_log_info_repository,应用二进制日志的内容,将binlog应用到的位置记录到relay.info,默认记录到file,建议用表记录
relay_log_recovery,为了让从库是crash safe的,必须设置relay_log_recovery=1
#当从库崩溃或重启时,会把未执行完的中继日志删除,并会向主库重新获取binlog,再次生成relay log来完成中继日志的恢复。建议开启
relay_log_purge,消除已执行过的relay log,建议从库开启
slave_net_timeout,在多少秒没收到主库传来的binlog后,从库认为网络超时,从库的I/O thread会重新连接主库,默认60s
slave_parallel_type,5.7引入,两个值:DATABASE和LOGICAL_CLOCK。
#基于组提交的并行复制,通过设置slave_parallel_workers>0且slave_parallel_type='LOGICAL_CLOCK'实现
slave_parallel_workers,设置多个线程来并发执行relay log中主库提交的事务,最大值1024
master_info_repository=TABLE  默认为 FILE
relay_log_info_repository=TABLE 默认为 FILE
将主从复制信息存储到innodb表中 默认存于文件中 若从服务器宕机 易出现文件记录和实际同步信息不一致情况 存储到innodb中则可借助innodb的崩溃恢复机制保证记录的一致性

复制原理及实战演练

异步复制

在主库写入binlog日志后即可成功返回客户端,无须等待binlog日志传递给从库的过程。一旦主库发生宕机,就有可能出现丢失数据的情况

非gtid模式,基于binlog和position方式

环境
主192.168.10.110 server-id = 3306100 gtid_mode = off
备192.168.10.120 server-id = 3306200 gtid_mode = off
必要条件
1)server-id有且唯一
2)主库开启binlog功能 #建议从库也开启binlog,且开启log_slave_updates让从库与写binlog方便后期扩展
3)binlog_format = row
步骤
主库上的操作
#创建一个主从复制帐号
create user 'bak'@'192.168.10.%' identified by 'bak123';
grant replication slave on *.* to 'bak'@'192.168.10.%';
flush privileges;
#初始化数据
mysqldump --single-transaction -uroot -proot123 --master-data=2 -A > /root/all.sql
##--master-data=2必须,让备份出来的文件中记录备份这一时刻的binlog文件与position号,为搭建主从环境做准备
##CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000003', MASTER_LOG_POS=194;
#将备份文件复制到从服务器192.168.10.120
scp all.sql root@192.168.10.120:/root/
从库上的操作
#恢复从主库传递过来的数据
mysql -uroot -proot123 < /root/all.sql
#配置主从命令
mysql -uroot -proot123
change master to
master_host='192.168.10.110',
master_user='bak',
master_password='bak123',
master_port=3306,
master_log_file='mysql-binlog.000003',
master_log_pos=194;
#启动主从复制
start slave;
#查看主从复制状态
show slave status\G
##Slave_IO_Running和Slave_SQL_Running均为Yes即主从复制正常
主从复制管理命令
show slave status\G
show master status\G 查看主库binlog文件和位置及开启gtid模式下记录的gtid
change master to 在从库上配置主从过程
start slave 开启主从同步
stop slave 关闭主从同步
reset slave all 清空从库的所有配置信息
主从复制故障处理

1、主键冲突,错误代码1062

原因:由于误操作,在人库上执行了写入操作。生产环境中建议在从库开启read only

解决办法:可直接通过percona-toolkit工具集中的pt-slave-restart命令在从库跳过错误

./pt-slave-restart -uroot -proot123

2、主库更新数据,从库找不到而报错,错误代码1032

从库少数据,需要找到缺少的数据,在从库上重新执行一遍

原因:由于误操作,在从库上执行delete操作,导致主从数据不一致,这时再在主库执行同条数据的更新操作时,由于从库已经没有该数据,SQL无法在从库实现

解决办法:

根据报错信息所知道的binlog文件和position号,在主库上,通过mysqlbinlog找到在主库上执行的SQL语句
Last_Error: Could not execute Update_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-binlog.000003, end_log_pos 1408
mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-binlog.000003 |grep -A 10 1408
#180706 22:36:45 server id 3306100 end_log_pos 1408 CRC32 0xc37d8c70 Update_rows: table id 139 flags: STMT_END_F
### UPDATE test.t
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tzy' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### @3='sh' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='zyn' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### @3='sh' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
# at 1408
接下来只需把从库上丢失的这条数据补上,然后再执行跳过错误,主从复制功能就恢复正常了
注:在生产环境上,若从库缺失多条数据,建议重新搭建主从环境来确保数据一致性
insert into t (id,name,city) values ('2','zyn','sh');
./pt-slave-restart -uroot -proot123

3、主从server-id一致

原因:两台服务器配置了相同的server-id

解决方法:不同机器设置不同的server-id

4、跨库操作,丢失数据

原因:主库设置了binlog-do-db参数,使用的binlog记录格式为statement模式,导致在主库上执行跨库操作时,从库执行失败

解决办法:主库上尽量避免使用过滤规则,可以在从库上使用replicate-do-db或replicate-ignore-db等参数,最重要的是让binlog格式为row模式

半同步复制

异步复制的不足在于,当主库把event写入二进制日志后,并不知道从库是否已经接收并应用了。若主库崩溃,很有可能在主库中已经提交的事务,并没有传到任何一台从库机器上,在高可用集群架构下做主备切换,会造成新的主库丢失数据的现象

5.5版本引用半同步复制,主从服务器必须同时安装半同步复制插件,才能开启该复制功能。在从库接收完主库传递过来的binlog内容写入到自己的relay log里面后,才通知主库上面的等待线程,该操作完毕。若等待超时,超过rpl_semi_sync_master_timeout后,关闭半同步,转为异步,直到至少有一台从库通知主库已收到binlog信息为止

半同步复制可以提升主从间数据一致性,让复制更加安全可靠

5.7中增加rpl_semi_sync_master_wait_point参数,控制半同步模式下主库在返回给session事务成功之前的事务提交方式,值有:AFTER_COMMITAFTER_SYNC
AFTER_COMMIT,5.6默认值,主库将每个事务写入binlog,并传递给从库,刷新到中继日志中,同时主库提交事务,之后主库等待从库反馈,在收到从库回复后,主库才将"commit ok"结果反馈客户端
AFTER_SYNC,5.7默认值,主库将每个事务写入binlog,并传递给从库,刷新到中继日志中,主库等待从库反馈,在收到从库回复后,主库再提交事务并返回"commit ok"结果给客户端
rpl_semi_sync_master_wait_for_slave_count 控制主库接收多少个从库写事务成功反馈,才返回成功给客户端
在after_sync模式下,即使主库宕机,所有在主库上已经提交的事务都能保证已经同步到从库中继日志中,不会丢失数据

半同步复制搭建
1)在异步复制的基础上
2)在主库中安装半同步复制插件并开启半同步复制功能
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=on;
show variables like "%semi%";
show plugins;
#rpl_semi_sync_master_timeout 主库等待从库回复消息的时间超时,单位毫秒,超过该值就切换为异步复制,默认10s。该值可以设置得很大,禁止向异步复制切换保保证数据复制的安全性
3)在从库中安装半同步复制插件并开启半同步复制功能
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=on;
show variables like "%semi%";
show plugins;
为了以后开机自启半同步复制功能,可以把rpl_semi_sync_slave_enabled=on、rpl_semi_sync_master_enabled=on写到配置文件my.cnf中
4)重启从库I/O线程激活半同步复制
stop slave io_thread;
start slave io_thread;
5)在主库上检查半同步复制是否正常运行
show global status like "%semi%";
Rpl_semi_sync_master_clients 1 #已经有一个从库连接到了主库,且是半同步复制方式
Rpl_semi_sync_master_status ON #表示已经是半同步复制模式
Rpl_semi_sync_master_no_tx 0 #没有成功接收slave提交的次数
Rpl_semi_sync_master_yes_tx 0 #成功接收slave事务回复的次数
6)在从库上查看半同步复制状态
show global status like "%semi%";
Rpl_semi_sync_slave_status ON #从库开启了半同步复制
半同步复制和异步复制模式的切换

半同步复制的原理是从库I/O thread接收完主库binlog后,写入relay中,然后会给主库一个回馈。但若主库等待从库回复时间超过rpl_semi_sync_master_timeout时间后,会自动切换为异步复制方式

主库:show variables like "%rpl_semi_sync_master_timeout%";
从库:stop slave io_thread;
从库:show global status like "%semi%"; #OFF状态
主库:show global status like "%semi%"; #ON状态
主库:insert into t (name,city) values ('ff','99'); #插入要等待10秒
主库:show global status like "%semi%"; #OFF状态
生产环境中不建议半同步复制切换到异步复制模式,因为这样对安全性没有保证,所以部分公司将rpl_sync_master_timeout设置得很大
GTID复制

gtid,全局事务ID,是一个已提交事务的编号,且是全局唯一的编号,5.6版本后新增

gtid由server_uuid和事务id组成,GTID=server_uuid:transaction_id。server_uuid在数据库启动过程中自动生成,唯一,存放于数据目录auto.cnf中;transaction_id是事务提交时由系统顺序分配的不重复的序列号

gtid的优势:
1)使用master_auto_position=1代替基于binlog和position号的主从复制方式,更便于主从复制的搭建
2)gtid可以知道事务在最开始是在哪个实例上提交的
3)gtid方便实现主从间failover,再也不用不断地找position和binlog了

主从复制中gtid的管理与维护

主从复制的搭建过程,不再依赖binlog文件和position号,在从库change master to 时使用master_auto_position=1的方式进行搭建

主库配置
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
清除原有主从复制关系
从库:
stop slave;
reset slave all;
show slave status\G    #结果应为空
server-id=
主库:
reset master;    #删除所有binlog日志文件,并清空日志索引文件,重新开始所有新的日志文件
建立主从复制
主库:
show master status\G  #查看Executed_Gtid_Set
从库:
change master to
master_host='192.168.10.110',
master_user='bak',
master_password='bak123',
master_port=3306,
master_auto_position=1;
start slave;
show slave status\G
5.7之后,gtid_executed这个值持久化了,在mysql库下新增了一张表:gtid_executed
该表会记录已经执行的gtid集合的信息,有了这张表,就不用再像5.6版本时,必须开启log_slave_updates参数,从库才可以进行复制。gtid信息会保存在gtid_executed表中,可以关闭从库binlog,节约binlog记录开销。执行reset master时,会清空表内所有数据
5.7还有gtid_executed_compression_period参数,控制gtid_executed表压缩,默认值为1000,表示执行完1000个事务后开始压缩
5.7.6开始,gtid_mode支持动态修改,值有:OFF/OFF_PERMISSIVE/ON_PERMISSIVE/ON
OFF,关闭gtid事务
OFF_PERMISSIVE,新事务是匿名的,同时允许复制的事务可以是gtid,也可以是匿名的
ON_PERMISSIVE,新事务使用gtid,同时允许复制的事务可以是gtid,也可以是匿名的
ON,支持gtid事务
生产环境中,可能有把传统复制改为gtid复制的需求,gtid_mode不支持跳跃式修改
从库上可以通过show slave status获取接收的gtid(retrieve_gtid_set)和执行的gtid(execute_gtid_set)
gtid复制与传统复制的切换
当前为gtid复制,配置文件中gtid_mode=on,调整为传统复制
show slave status\G
#停止主从复制,调整为传统复制,让master_auto_position=0
stop slave;
show slave status\G #获取主库当前binlog和pos
change master to
master_auto_position=0,
master_host='192.168.10.110',
master_user='bak',
master_password='bak123',
master_port=3306,
master_log_file='mysql-binlog.000002',
master_log_pos=680;
start slave;
#传统复制 -> gtid复制
主从服务器上同时调整gtid_mode为on_permissive
show variables like "%gtid_mode%";   #ON状态
set global gtid_mode=on_permissive;
主从服务器上同时调整gtid_mode为off_permissive
set global gtid_mode=off_permissive;
主从服务器上同时关闭gtid功能
set global gtid_mode=off;
将gtid_mode=off和enforce_gtid_consistency=off写入配置文件my.cnf中,下次重启会直接生效
检查是否成功
标志:show slave status\G,gtid值不再增加
#gtid复制 -> 传统复制
主从上同时修改enforce_gtid_consistency=warn,确保在error log中不会出现警告信息
set global enforce_gtid_consistency=warn;
show variables like "%gtid_consistency%";
主从上同时把enforce_gtid_consistency改为on,保证gtid一致性
set global enforce_gtid_consistency=on;
主从服务器上同时调整gtid_mode为off_permissive
set global gtid_mode=off_permissive;
主从服务器上同时调整gtid_mode为on_permissive
set global gtid_mode=on_permissive;
show variables like "%gtid_mode%";
确认从库ongoing_anonymous_transaction_count参数是否为0,若为0,代表没有等待的事务,可以进行下一步了
show global status like "%ongoing_anonymous_transaction_count%";
主从服务器上同时设置gtid_mode=on
set global gtid_mode=on;
把传统复制改为gtid,需要把原有传统复制停止,再执行change master to master_auto_position=1
stop slave;
change master to master_auto_position=1;
start slave;
show slave status\G
检查:若gtid值增加,则说明gtid复制成功

gtid使用中的限制
gtid是针对事务来说的,一个事务对应一个gtid
1)不能使用create table table_name select * from table_name
2)在一个事务中既包含事务表的操作又包含非事务表的操作
3)不支持create temporary table、drop temporary table语句
4)使用gtid复制从库跳过错误时,不支持执行sql_slave_skip_counter参数

多源复制

把多台主库数据同步到一台从库上,从库会创建通往每个主库的管道。多源复制在5.7版本上新增,它的搭建模式支持gtid和binlog+position方式

多源复制优势:
1)可以集中备份,在从库上备份,不影响线上数据库正常运行
2)节约从库成本,只需一个服务器即可
3)数据都汇总在一起,方便后期做数据统计

搭建中注意事项:
MasterA和MasterB不能拥有相同的数据库名,否则会在从库出现数据覆盖现象
MasterA->slave与MasterB->slave要拥有不同的复制帐号
三台机器的数据库参数跟gtid复制一样,保证开启gtid,server-id不一致,binlog格式为row
从库需要配置参数,主从间复制信息要记录到表中
master_info_repository=table
relay_log_info_repository=table

基于gtid的多源复制搭建过程

1)分别在MasterA和MasterB上创建复制帐号
A:
create user 'bak'@'192.168.10.%' identified by 'bak123';
grant replication slave on *.* to 'bak'@'192.168.10.%';
flush privileges;
B:
create user 'repl'@'192.168.10.%' identified by 'repl123';
grant replication slave on *.* to 'repl'@'192.168.10.%';
flush privileges;
2)分别在A/B上使用mysqldump导出需要备份的tzy库和zs库
A:
mysqldump -uroot -proot123 --master-data=2 --single-transaction --set-gtid-purged=OFF tzy > /root/tzy.sql
B:
mysqldump -uroot -proot123 --master-data=2 --single-transaction --set-gtid-purged=OFF zs > /root/zs.sql
3)从库开启参数
master_info_repository=table
relay_log_info_repository=table
4)在从库上进行数据库的恢复操作
scp tzy.sql root@192.168.10.120:/root
scp zs.sql root@192.168.10.120:/root
mysql -uroot -proot123 tzy <tzy.sql
mysql -uroot -proot123 zs <zs.sql
5)在从库上分别配置MasterA->slave MasterB->slave的同步过程
忽略部分库
##建议配置文件中添加replicate_ignore_db=
stop slave sql_thread;
change replication filter replicate_do_db=(db_list) replication_ignore_db=(db_list)  #不需要重启
start slave sql_thread;
change replication filter需要super权限
##
change master to
master_host='192.168.10.110',
master_user='bak',
master_password='bak123',
master_auto_position=1 for channel 'm1';
change master to
master_host='192.168.10.130',
master_user='repl',
master_password='repl123',
master_auto_position=1 for channel 'm2';
这里定义两个从库通往主库的通道,m1和m2
6)开启主从复制,可以通过start slave开启所有复制,也可以通过start slave for channel来分别开启
start slave for channel 'm1';
start slave for channel 'm2';
查看状态
show slave status\G
show slave status for channel 'm1'\G
show slave status for channel 'm2'\G
performance_schema库下replication_connection_configuration记录复制配置信息;replication_connection_status表记录主从复制状态
start/stop slave for channel 'name'; 启动/关闭某个通道的复制
reset slave all for channel 'name'; 重置某个通道的复制

跳过一步

传统复制
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
show slave status;
gtid复制
首先找到gtid点
show slave status\G
Retrieved_Gtid_Set: cfc0499d-8109-11e8-89cc-000c29014dc0:1-9  #将要执行的事务
Executed_Gtid_Set: 99af9b61-8109-11e8-9b9e-000c297d2b33:1-7:9 #已经执行的事务
#将要执行事务1-9,已经执行1-7:9,已经从事务1执行到了事务7,报错了,说明是在执行事务8的时候,所以这里把事务8设置成空事务
#也可以到相应主库在查看事务8内容,确认是不是和报错的语句对应
#show binary logs;  查看binlog文件名
#show binlog events; 显示二进制日志内事件,默认为第一个日志文件
#show binlog events in 'mysql-binlog.000001';  查看指定文件
#语法:show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count]
stop slave;
set gtid_next='cfc0499d-8109-11e8-89cc-000c29014dc0:8';
begin;commit;
set gtid_next='automatic';
主从延迟的解决方案及并行复制

主库可以并发写入,但从库只有单个SQL thread,这是出现主从延迟的最核心原因

监控主从延迟,可以使用percona-toolkit工具集中的pt-heartbeat。原理是在主库创建heartbeat表,表中有时间戳字段,主库上pt-heartbeat的update线程会在指定时间间隔更新时间戳,从库的pt-hearbeat的monitor线程会检查复制的心跳记录,然后和当前系统时间进行对比,得出时间差。所以前提是主从的时间需要同步

pt-heartbeat参数:
--daemonize,执行时,在后台执行
--create-table,在主库上创建心跳监控的表
--user,-u;--host,-h;--password,-p;--port,-P,连接参数
--database,-D,连接的数据库
--monitor,持续监控从库的延迟情况
--master-server-id,指定主库server_id,若不指定,该工具会连接到主库上查找其server_id
--update,更新主库上的心跳表

1)在主库上创建heartbeat心跳表,通过update执行更新时间戳
./pt-heartbeat -uroot -proot123 --database tzy --update --create-table --daemonize
2)对从库进行监控操作
./pt-heartbeat --master-server-id=3306100 --monitor --database tzy -uroot -proot123 -h 192.168.10.120
0.00s [ 0.00s, 0.00s, 0.00s ]   #当前[1m,5m,15m]

其他延迟原因
1)主从本身就是异步的同步
2)在主库上对没有索引的大表的列进行delete或者update操作
3)从库硬件配置没有主库的好
4)网络抖动
解决办法
1)启用5.7的并行复制功能,基于组提交的并行复制,从库通过多个workers线程并发执行relay log中主库提交的事务
开启方法:
设置slave_parallel_workers>0
设置slave_parallel_type=LOGICAL_CLOCK
show variables like "%parallel%";
set global slave_parallel_workers=4;
stop sql_thread;
set global slave_parallel_type=LOGICAL_CLOCK;
start slave sql_thread;
2)采用percona公司的percona-xtradb-cluster(简称PXC架构),该架构可实现多节点写入,达到实时同步
3)业务规划初期选择合适的分库、分表过大
4)避免无用的IO消耗,使用高速磁盘、SSD或PCIE-SSD设备
5)IO调度选择deadline
6)阵列级别选择RAID10,raid cache策略使用WB,坚决不要使用WT
7)适当调整buffer pool大小
8)避免让数据库进行各种大量运算

主从复制的数据校验

通过pt-table-checksum工具检查主从数据一致性,通过pt-table-sync工具修复不一致的数据信息

pt-table-checksum用于校验主从数据一致性,该命令在主库上执行校验查询,然后对复制的一致性进行检查,来对比主从间校验值,并输出对比结果

perldoc ./pt-table-checksum #查看帮助文档

重要参数:
--[no]check-replication-filters,是否检查复制的过滤器,默认yes,建议不检查
--databases,需要检查的数据库,多个库逗号分隔
--[no]check-binlog-format,是否检查binlog文件格式,默认yes,建议不检查,因为在默认的row格式下会出错
--replicate,把checksum写入到指定表中
--replicate-check-only,只显示不同步信息
--tables,需要检查的表,多个表用逗号分隔
--where,大表校验中,使用where过滤一些条件
在主库上执行:
./pt-table-checksum --no-check-binlog-format --replicate=tzy.checksums --databases=tzy --tables=tt -uroot -proot123 -h 192.168.10.110
./pt-table-checksum --no-check-binlog-format --replicate=tzy.checksums --databases=tzy --tables=tt --channel=m1 --chunk-size=1 --replicate-check-only -uroot -proot123 -h 192.168.10.110
#注,在多源复制的过程中会报错,需添加参数 --channel=m1
--chunk-size-limit限制每次校验多少行 --chunk-size指定每次检验多少行
结果解读
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
TS,完成检查的时间
ERRORS,检查时发生错误和警告的数量
DIFFS,差异数,0表示没有不一致
ROWS,表的行数
原理是针对某张表的所有字段进行hash运算,在主库上运算,把得到值记录为master_crc、master_cnt,在从库上运算,把得到值记录为this_crc、this_cnt,最后对比master的值和this的值判断主从之间的数据一致性
在从库中找出不一致的地方
select db,tbl,sum(this_cnt) as total_rows,count(*) as chunks from tzy.checksums where ( master_cnt <> this_cnt or master_crc <> this_crc or isnull(master_crc) <> isnull(this_crc)) group by db,tbl;

使用pt-table-sync修复主从数据不一致的位置

核心参数:
--replicate,指定pt-table-checksum得到的表
--print,打印但不执行命令
--execute,执行命令
在主库上通过pt-table-sync修复不一致
修复按照主为准
#一致性检查,得到checksums表,发现有不一致
./pt-table-checksum --no-check-binlog-format --replicate=tzy.checksums --databases=tzy --tables=tt --channel=m1 -uroot -proot123 -h 192.168.10.110
#打印修复用的SQL语句
./pt-table-sync --replicate=tzy.checksums h=192.168.10.110,u=root,p=root123 --channel=m1 --print
#执行修复
./pt-table-sync --replicate=tzy.checksums h=192.168.10.110,u=root,p=root123 --channel=m1 --execute
#再次检查,发现已经修复
./pt-table-checksum --no-check-binlog-format --replicate=tzy.checksums --databases=tzy --tables=tt --channel=m1 -uroot -proot123 -h 192.168.10.110

生产中建议使用GTID+rows复制模式,根据gtid找同步位置,不用再查找binlog和pos

转载请注明:轻风博客 » MySQL王者晋级之路(五)主从复制

喜欢 (0)or分享 (0)