如何保证MySQL主从数据一致性

什么情况下会出现数据不一致▼

首先我们来谈一谈在什么情况下可能会引起主从数据的不一致性；在这一部分我会介绍几种常见的场景，可能会有没考虑到的场景，欢迎大家补充。

• 复制过程中实例宕机

• 复制过滤参数配置

• Binlog非row格式

• 主从延迟过高

• 人为操作

• 从库发生写入操作

• 双主双写

• MySQL主从版本不一致

 

 

如何避免数据不一致▼

在这一部分，我将对上面可能会出现主从不一致的场景进行描述，并介绍避免的方法。

 

复制过程中实例宕机

实例宕机当然会引起数据的不一致啦~

 

如果是一主一从的架构，从库发生宕机情况，没有办法在第一时间进行修复启动；在宕机期间主库的写入是没有备份的，所以此时的架构是单实例状态，如果主库机器发生严重故障无法修复，就会导致数据的丢失。

 

如果在从库在修复后，主库的Binlog被purge了，那么从库无法继续复制，只能重新使用全备来进行从库的恢复了。对于数据量比较大的数据库，那是一个庞大的工程；而且在备份过程中可能会引起主库的锁表。

 

所以在资源允许和数据重要的业务还是建议使用一主多从的架构；这样可以完美的解决上面的两种问题，一个从库宕机还有另一从库挺着；Binlog被purge了，可以使用另一个从库进行恢复。

 

关于主机宕机后可能遇到的情况，主要是跟复制的方式有关。详情见我的上一篇博客--[浅析半同步]

 

复制过滤参数配置

复制过滤参数，可以在主库或者从库上进行配置。允许指定的库进行复制或者不进行复制。有兴趣的同学可以自行百度。

 

引起数据不一致的场景主要有：

• 不同从库配置的过滤规则不一样，切主后，数据不一致。

• 主从配置规则不一样，切主后，引起复制规则的改变。

 

对于过滤参数的配置，建议根据业务的实际场景，考虑主从切换后造成的影响，合理的进行配置，留有一个从库专门保证高可用。

 

主从延迟过高

主从延迟过高，说明在从库上还有event保存在relay-log，并没有执行；更严重的情况是，还存在binlog日志没有写入relay-log。

 

此时主从的数据并没有保持实时一致，如果架构做了读写分离，在从库上可进行读操作的话，此时读取的数据可能会比主库少，业务认为数据不一致。

 

对于主从延迟的详细介绍请见：

主从延迟计算：[问世间情为何物，主从延迟难计算，直教人无从下手]

如何避免主从延迟：[只要努力就为时不晚，趁春光正好，一定要追到主库]

 

 

人为操作和从库写入

在一些情况下，需要人为的去进行实例维护操作，是人操作就可能存在问题。

• change master位点不正确

• 跳过或设置某个GTID

• 从库执行flush logs会导致多记一个空的GTID，导致主从GTID的不一致

• 从库发生写入，导致从库多数据或数据修改

 

 

双主双写

对于双主架构，两个MySQL实例互为主从。常见的数据不一致场景有：

• 两个实例同时对同一行数据进行修改，在两个库上执行顺序不一样。导致最终数据不一致。

• 两个实例同时插入数据，插入的数据在两个库上的顺序不一致。

 

这种场景下的解决方式就只有尽量不要在两个主库上同时进行写入操作。

第二种场景有人提出：设置自增主键，并为多个实例设置不同起始位置和偏移量。但是会造成序列资源的大量浪费。

实例一设置：auto_increment_offset = 1auto_increment_increment = 2

实例二设置：auto_increment_offset = 2auto_increment_increment = 2

 

怎么确认数据是否一致▼

MySQL 原生 Checksum 命令

CHECKSUM TABLE tbl_name [, tbl_name] ... [QUICK | EXTENDED]

mysql> checksum table t;+-------+------------+| Table | Checksum   |+-------+------------+| hoo.t | 3578619433 |+-------+------------+1 row in set (0.04 sec)

checksum是MySQL原生自带的表校验工具，这个命令会计算出整个表内容的校验和，执行这个命令的时候会对数据库表加一个全局读锁。所以常用于校验处于静态状态下的表校验；如：进行逻辑备份恢复后，想判断备份前后数据表内容是否一致。

 

执行这个命令只需要拥有SELECT权限即可。如果执行checksum的为视图或者是不存在的表，那么checksum返回的值为NULL。

 

默认情况下，会对数据表逐行读取并计算校验和，其计算逻辑如下：

 

ha_checksum crc= 0;  foreach(row in table)  {    row_crc= get_crc(row);    crc+= row_crc;  }  return crc; 

switch (f->type()) {        case MYSQL_TYPE_BLOB:        case MYSQL_TYPE_VARCHAR        case MYSQL_TYPE_GEOMETRY:        case MYSQL_TYPE_BIT:        {               String tmp;            f->val_str(&tmp);            row_crc= my_checksum(row_crc, (uchar*) tmp.ptr(),tmp.length());            break;        }           default:        row_crc= my_checksum(row_crc, f->ptr, f->pack_length());        break;}   

可以看到checksum结果值是根据每一行的校验和累加计算而来，所以并不受该表使用的引擎、索引、主键等因素影响。

 

另外，在计算每一行的校验值时，会对字段进行顺序读取计算，每列计算的结果受上一列计算结果的影响；并且受该字段的实际长度影响。

 

所以行校验值会受字段顺序、定长字段的影响；但是对于变长字段，如果是增长操作的话，并不会影响其校验值，它计算的是列的实际使用长度。

 

这个工具只是单纯的输出一个计算后的校验值，最终你只能根据校验值判断数据是否一致，并不知道哪些数据不一致，要怎么去修复。

 

pt-table-checksum

pt-table-checksum 是 Percona-Toolkit 的组件之一，用于检测MySQL主、从库的数据是否一致。

 

其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。

 

pt-table-checksum默认情况下可以应对绝大部分场景，即使上千个库、上亿的行，它依然可以很好的工作；这是因为它设计非常简单，一次检查一张表，不需要太多的内存和多余的操作；必要的时候，会根据服务器负载动态的改变chunk大小，减少从库的延迟。

 

为了减少对数据库的干预，pt-table-checksum还会自动侦测并连接到从库，如果失败，可以指定--recursion-method选项来告诉从库在哪里。复制如果有延迟，在从库checksum会暂停赶上主库的计算时间点。

 

 

使用:

pt-table-checksum通过在主上执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。

注意：第一次运行的时候需要加上--create-replicate-table参数，生成checksums表；如果不加这个参数，则需要手动在对应的库下创建这张表。

 

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,   PRIMARY KEY (db, tbl, chunk),   INDEX ts_db_tbl (ts, db, tbl)) ENGINE=InnoDB;

 

 

授权

要在主库上进行授权操作，能让主库IP访问。需要一个既能登录主库也能登录从库的账号。

执行的账号需要有以下权限。

SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE

 

执行

在执行前需要注意以下几点：

1. 账号已经授权

2. 只能指定一个host，必须为主机的IP

3. 在检查的时候会加S锁，所以需要注意并发量

4. 运行之前需要确认从库主从复制正常

 

[root@localhost bin]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format  --create-replicate-table --databases=hoo --tables=t h=127.0.0.1,u=hoo,p=hoo,P=3306Checking if all tables can be checksummed ...Starting checksum ...            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE04-26T16:47:01      0      1       25          0       1       0   0.510 hoo.t

 

结果解释

TS ：完成检查的时间。ERRORS ：检查时候发生错误和警告的数量。DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。ROWS ：表的行数。CHUNKS ：被划分到表中的块的数目。SKIPPED ：由于错误或警告或过大，则跳过块的数目。TIME ：执行的时间。TABLE ：被检查的表名。

 

常用参数介绍

--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。--no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。--replicate-check-only :只显示不同步的信息。--replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。--databases= ：指定需要被检查的数据库，多个则用逗号隔开。--tables= ：指定需要被检查的表，多个用逗号隔开h= ：Master的地址u= ：用户名p=：密码P= ：端口

 

执行过程

1. 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效。2. 查找主库或者从库是否有复制过滤规则：这是为了安全而默认检查的选项。你可以关闭这个检查，但是这可能导致checksum的sql语句要么不会同步到从库，要么到了从库发现从库没有要被checksum的表，这都会导致从库同步卡库。3. 开始获取表，一个个的计算。4. 如果是表的第一个chunk，那么chunk-size一般为1000；如果不是表的第一个chunk，那么采用11步中分析出的结果。5. 检查表结构，进行数据类型转换等，生成checksum的sql语句。6. 根据表上的索引和数据的分布，选择最合适的split表的方法。7. 开始checksum表。8. 默认在chunk一个表之前，先删除上次这个表相关的计算结果。除非–resume。9. 根据explain的结果，判断chunk的size是否超过了你定义的chunk-size的上限。如果超过了，为了不影响线上性能，这个chunk将被忽略。10. 把要checksum的行加上for update锁，并计算。11. 计算结果会写入到校验表this_crc和this_cnt字段中。这条检核和的SQL语句会传递到从库，从库会收到后计算自己的SQL语句；12. 从库读取主库的中校验表的this_crc和this_cnt字段数据，生成SQL语句更新checksums表中的master_crc和master_cnt字段。13. 调整下一个chunk的大小。14. 等待从库追上主库。如果没有延迟备份的从库在运行，最好检查所有的从库，如果发现延迟最大的从库延迟超过max-lag秒，pt工具在这里将暂停。15. 如果发现主库的max-load超过某个阈值，pt工具在这里将暂停。16. 继续下一个chunk，直到这个table被chunk完毕。17. 等待从库执行完checksum，便于生成汇总的统计结果。每个表汇总并统计一次。18. 循环每个表，直到结束。

 

 

数据不一致后，我们该怎么办▼

pt-table-sync

通过pt-table-checksum检查找到了不一致的数据表，那么怎么修复不一致的数据呢？这个时候我们可以使用另外一个工具：pt-table-sync

 

pt-table-sync：高效的同步MySQL表之间的数据，可以做单向或者双向同步的表数据。可以用来同步单个表，也可以同步整个库。他不可以用来同步表结构、索引、或者其他模式对象。所以在修复一致性之前需要保证这些数据存在。

 

参数解释

--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。--databases= : 指定执行同步的数据库。--tables= ：指定执行同步的表，多个用逗号隔开。--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。h= ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。u= ：帐号。p= ：密码。--print ：打印，但不执行命令。--execute ：执行命令。

 

下面我们对前面的表进行数据修复操作

//打印出修复语句root@localhost ~]# pt-table-sync --replicate=percona.checksums h=127.0.0.1,u=hoo,p=hoo h=100.73.*.*,u=hoo,p=hoo --printREPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('1', 'h', '13') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;REPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('2', 'xx', '22') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;REPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('3', 'xx', '22') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;REPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('4', 'xx', '22') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;REPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('14', 'xx', '24') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;REPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('15', 'xx', '24') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;REPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('16', 'xx', '24') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;REPLACE INTO `hoo`.`t`(`id`, `name`, `age`) VALUES ('17', 'xx', '24') /*percona-toolkit src_db:hoo src_tbl:t src_dsn:h=127.0.0.1,p=...,u=hoo dst_db:hoo dst_tbl:t dst_dsn:h=100.73.*.*,p=...,u=hoo lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:22005 user:root host:localhost*/;

//执行命令修复pt-table-sync --replicate=percona.checksums h=127.0.0.1,u=hoo,p=hoo h=100.73.*.*,u=hoo,p=hoo --execute

//再次进行检查，发现数据已经修复了[root@localhost ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format  --databases=hoo --tables=t h=127.0.0.1,u=hoo,p=hoo,P=3306Checking if all tables can be checksummed ...Starting checksum ...            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE04-26T20:01:51      0      0       25          0       1       0   0.355 hoo.t

 

修复数据的时候，建议先使用--print将修复语句给打印出来，这样能够知道哪些数据有问题，可以人为的进行干预。

另外就是在修复之前对数据进行备份，避免造成更大范围的影响。