主备数据不一致常见原因
1 备库写数据
2 执行non-deterministic query
3 回滚掺杂事务表和非事务表的事务
4 binlog或者relay log数据损坏
应对措施
1 禁止修改备库数据
2 采用row-based replication
3 避免同一个事务中同时引用innodb和myisam表
4 开启binlog checksum
其中binlog checksum是5.6引入的新功能,由参数binlog-checksum控制(默认关闭);
开启该功能后,master在写binlog event时同时记录checksum,slave读取relay log时对每个event执行checksum校验,如果失败则停止sql thread并报错。
master-verify-checksum:默认关闭,开启后主库会对每个binlog event进行checksum验证,如果失败则停止写入并报错;
slave_sql_verify_checksum:默认关闭,开启后备库读relay log时会对每个event进行checksum验证;
1 备库写数据
2 执行non-deterministic query
3 回滚掺杂事务表和非事务表的事务
4 binlog或者relay log数据损坏
应对措施
1 禁止修改备库数据
2 采用row-based replication
3 避免同一个事务中同时引用innodb和myisam表
4 开启binlog checksum
其中binlog checksum是5.6引入的新功能,由参数binlog-checksum控制(默认关闭);
开启该功能后,master在写binlog event时同时记录checksum,slave读取relay log时对每个event执行checksum校验,如果失败则停止sql thread并报错。
master-verify-checksum:默认关闭,开启后主库会对每个binlog event进行checksum验证,如果失败则停止写入并报错;
slave_sql_verify_checksum:默认关闭,开启后备库读relay log时会对每个event进行checksum验证;
详细可参考 http://mysqlmusings.blogspot.co.uk/2011/04/replication-event-checksum.html?_sm_au_=iSVMJNwQ5DRFsDtN
sql_slave_skip_counter 的误区
当DML在slave执行出错时sql thread会停止且报告错误,通过show slave status可查看;
通常会部署监控脚本,定时执行show slave status,如遭遇sql thread错误,最常见的方式是跳过该事件并记录信息 ;
binlog以事件组方式记录,每组包含一系列事件, 对于Innodb,每个事务一个事件组,对于Myisam,则是每条sql一个事件组;
sql_slave_skip_counter = N意为跳过N个事件, 但当N=1时,其效果为跳过一个事务;详细可参考http://dinglin.iteye.com/blog/1236330
如何检测数据不一致
可定期调用pt-table-checksum进行主备数据校验,发现数据不一致则pt-table-sync进行恢复;
可使用percona的pt-table-checksum,http://nettedfish.sinaapp.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/
原理
连接主库同时自动侦测并连接到所有备库,创建规则表
CREATE TABLE checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
chunk_time float NULL,
chunk_index varchar(200) NULL,
lower_boundary text NULL,
upper_boundary text NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;
一次只操作一个表,在主库执行基于statement的sql语句来生成主库数据块的checksum,把相同的sql语句传递到从库并计算相同数据块的checksum;
最后,比较主从库上相同数据块的checksum值,由此判断主从数据是否一致。也可手工查询
SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM checksums
WHERE (
master_cnt <> this_cnt
OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;
如何计算checksum
依据表的主键或唯一索引,将表划分为多个数据块,以数据块为单位进行计算;
将块内数据行拼接起来,计算crc32的值,即为其checksum;
每一次对chunk进行checksum后,pt工具都会对耗时进行统计分析,并智能调整下一个chunk的大小,避免chunk太大对造成影响,也要避免太小而效率低下。
一致性保证
当pt工具在计算主库上某chunk的checksum时,主库可能还在更新,为保证chunk内部数据一致性,每计算一个chunk时加for update锁;
并把计算结果保存到pt工具自建的结果表中(采用replace into select的方式),然后释放锁。该语句最终会传递到从库并执行相同的计算逻辑。
注意事项
1 —check-binlog-format是默认选项,建议不要关闭它。pt-table-checksum产生的sql语句要基于语句格式同步到从库,这是由它的实现原理决定的。
但是在A-B-C的级联复制结构中,如果B是行格式的复制,那么B与C的数据一致性校验就没法做了。
在A上设置该sql语句为语句级并不会把set这个动作记录到binlog中;
2 主从异构的情况下,checksum语句可能在从库上执行失败,即使是索引的不一致。
例如sql语句中有force index某个索引,但是从库的表上没有这个索引,就会导致卡库。
如何同步主备库表
可使用pt-table-sync http://nettedfish.sinaapp.com/blog/2013/06/05/synchronizes-data-efficiently-by-pt-table-sync/
原理
同pt-table-checksum一样,将表分成chunk并计算checksum,一旦发现主从上同样的chunk的checksum值不同,就深入到该chunk内部,
逐行比较并修复有问题的行。其计算逻辑描述如下:
1 对每一个从库,每一个表,循环进行如下校验和修复过程。
2 对每一个chunk,在校验时加上for update锁。一旦获得锁,就记录下当前主库的show master status值。
3 在从库上执行select master_pos_wait()函数,等待从库sql线程执行到show master status得到的位置。以保证主从上这个chunk的内容均不再改变。
4 对这个chunk执行checksum,然后与主库的checksum进行比较。
5 如果checksum相同,说明主从数据一致,就继续下一个chunk。
6 如果checksum不同,说明该chunk有不一致。深入chunk内部,逐行计算checksum并比较(单行的checksum的比较过程与chunk的比较过程一样)。
如果发现某行不一致,则标记下来。继续检测剩余行,直到这个chunk结束。
7 对找到的主从不一致的行,采用replace into语句,在主库执行一遍以生成该行全量的binlog,并同步到从库,这会以主库数据为基准来修复从库;
对于主库有的行而从库没有的行,采用replace在主库上插入(必须不能是insert);
对于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行,而不建议直接修改从库数据)。
直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。
8 直到这个从库上所有的表修复结束。开始修复下一个从库。
注意事项
1 pt-table-sync在修复过程中不能容忍从库延迟,这正好与pt-table-checksum相反。
如果从库延迟太多,pt-table-sync会长期持有对chunk的for update锁,然后等待从库的master_pos_wait执行完毕或超时。
从库延迟越大,等待过程就越长,主库加锁的时间就越长,对线上影响就越大。因此要严格设置max-lag。
2 对从库数据的修复通常是在主库执行sql来同步到从库。因此,在有多个从库时,修复某个从库的数据实际会把修复语句同步到所有从库。
数据修复的代价取决于从库与主库不一致的程度,如果从库只有表结构,那么需要把主库的所有数据重新灌一遍,然后通过binlog同步并传递到所有从库。
正确的做法是,先用pt-table-checksum校验一遍:如果不同步的很少,用pt-table-sync直接修复;否则,用备份先替换它,然后用pt-table-sync修复。
sql_slave_skip_counter 的误区
当DML在slave执行出错时sql thread会停止且报告错误,通过show slave status可查看;
通常会部署监控脚本,定时执行show slave status,如遭遇sql thread错误,最常见的方式是跳过该事件并记录信息 ;
binlog以事件组方式记录,每组包含一系列事件, 对于Innodb,每个事务一个事件组,对于Myisam,则是每条sql一个事件组;
sql_slave_skip_counter = N意为跳过N个事件, 但当N=1时,其效果为跳过一个事务;详细可参考http://dinglin.iteye.com/blog/1236330
如何检测数据不一致
可定期调用pt-table-checksum进行主备数据校验,发现数据不一致则pt-table-sync进行恢复;
可使用percona的pt-table-checksum,http://nettedfish.sinaapp.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/
原理
连接主库同时自动侦测并连接到所有备库,创建规则表
CREATE TABLE checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
chunk_time float NULL,
chunk_index varchar(200) NULL,
lower_boundary text NULL,
upper_boundary text NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;
一次只操作一个表,在主库执行基于statement的sql语句来生成主库数据块的checksum,把相同的sql语句传递到从库并计算相同数据块的checksum;
最后,比较主从库上相同数据块的checksum值,由此判断主从数据是否一致。也可手工查询
SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
FROM checksums
WHERE (
master_cnt <> this_cnt
OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc))
GROUP BY db, tbl;
如何计算checksum
依据表的主键或唯一索引,将表划分为多个数据块,以数据块为单位进行计算;
将块内数据行拼接起来,计算crc32的值,即为其checksum;
每一次对chunk进行checksum后,pt工具都会对耗时进行统计分析,并智能调整下一个chunk的大小,避免chunk太大对造成影响,也要避免太小而效率低下。
一致性保证
当pt工具在计算主库上某chunk的checksum时,主库可能还在更新,为保证chunk内部数据一致性,每计算一个chunk时加for update锁;
并把计算结果保存到pt工具自建的结果表中(采用replace into select的方式),然后释放锁。该语句最终会传递到从库并执行相同的计算逻辑。
注意事项
1 —check-binlog-format是默认选项,建议不要关闭它。pt-table-checksum产生的sql语句要基于语句格式同步到从库,这是由它的实现原理决定的。
但是在A-B-C的级联复制结构中,如果B是行格式的复制,那么B与C的数据一致性校验就没法做了。
在A上设置该sql语句为语句级并不会把set这个动作记录到binlog中;
2 主从异构的情况下,checksum语句可能在从库上执行失败,即使是索引的不一致。
例如sql语句中有force index某个索引,但是从库的表上没有这个索引,就会导致卡库。
如何同步主备库表
可使用pt-table-sync http://nettedfish.sinaapp.com/blog/2013/06/05/synchronizes-data-efficiently-by-pt-table-sync/
原理
同pt-table-checksum一样,将表分成chunk并计算checksum,一旦发现主从上同样的chunk的checksum值不同,就深入到该chunk内部,
逐行比较并修复有问题的行。其计算逻辑描述如下:
1 对每一个从库,每一个表,循环进行如下校验和修复过程。
2 对每一个chunk,在校验时加上for update锁。一旦获得锁,就记录下当前主库的show master status值。
3 在从库上执行select master_pos_wait()函数,等待从库sql线程执行到show master status得到的位置。以保证主从上这个chunk的内容均不再改变。
4 对这个chunk执行checksum,然后与主库的checksum进行比较。
5 如果checksum相同,说明主从数据一致,就继续下一个chunk。
6 如果checksum不同,说明该chunk有不一致。深入chunk内部,逐行计算checksum并比较(单行的checksum的比较过程与chunk的比较过程一样)。
如果发现某行不一致,则标记下来。继续检测剩余行,直到这个chunk结束。
7 对找到的主从不一致的行,采用replace into语句,在主库执行一遍以生成该行全量的binlog,并同步到从库,这会以主库数据为基准来修复从库;
对于主库有的行而从库没有的行,采用replace在主库上插入(必须不能是insert);
对于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行,而不建议直接修改从库数据)。
直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。
8 直到这个从库上所有的表修复结束。开始修复下一个从库。
注意事项
1 pt-table-sync在修复过程中不能容忍从库延迟,这正好与pt-table-checksum相反。
如果从库延迟太多,pt-table-sync会长期持有对chunk的for update锁,然后等待从库的master_pos_wait执行完毕或超时。
从库延迟越大,等待过程就越长,主库加锁的时间就越长,对线上影响就越大。因此要严格设置max-lag。
2 对从库数据的修复通常是在主库执行sql来同步到从库。因此,在有多个从库时,修复某个从库的数据实际会把修复语句同步到所有从库。
数据修复的代价取决于从库与主库不一致的程度,如果从库只有表结构,那么需要把主库的所有数据重新灌一遍,然后通过binlog同步并传递到所有从库。
正确的做法是,先用pt-table-checksum校验一遍:如果不同步的很少,用pt-table-sync直接修复;否则,用备份先替换它,然后用pt-table-sync修复。