问题概述
客户数据库主从关系不一致，需要使用pt-table-sync 进行数据修复。在修复过程遇见2个问题，记录下，防止后面在此遇见。
一、 同步数据失败01
- 1、报错信息如下
[root@mysql-slave scripts]# pt-table-checksum h='172.16.2.216',u='ptck',p='xxxxxx',P=3306 --chunk-size-limit=16.0 -d db --nocheck-replication-filters --replicate=test.ck3 --no-check-binlog-format --recursion-method dsn=h=172.16.2.218,u=ptck,p=xxxxxx,P=3306,D=test,t=dsns
pt-table-sync --replicate=test.ck2 --sync-to-master h=172.16.2.218,u=ptck,p=xxxxxx -d dbname -t opt_log --print
Failed to prepare TableSyncChunk plugin: Cannot chunk table `dbname`.`opt_log` using the character column opt_id, most likely because all values start with the same character. This table must be synced separately by specifying a list of --algorithms without the Chunk algorithm at /usr/bin/pt-table-sync line 4128. while doing dbname.opt_log on 172.16.2.218
Failed to prepare TableSyncChunk plugin: Cannot chunk table `dbname`.`opt_log` using the character column opt_id, most likely because all values start with the same character. This table must be synced separately by specifying a list of --algorithms without the Chunk algorithm at /usr/bin/pt-table-sync line 4128. while doing dbname.opt_log on 172.16.2.218
Failed to prepare TableSyncChunk plugin: Cannot chunk table `dbname`.`opt_log` using the character column opt_id, most likely because all values start with the same character. This table must be synced separately by specifying a list of --algorithms without the Chunk algorithm at /usr/bin/pt-table-sync line 4128. while doing dbname.opt_log on 172.16.2.218
原因： "Failed to prepare TableSyncChunk plugin: Cannot chunk table"
代码语言：javascript复制在默认的算法中，要保证主键字段的数据前一位有不一样字符出现，而该表的主键数据第一个字符是一样的
使用--algorithms=参数指定算法，当然这种应该最好分库分表进行恢复。
--algorithms=s Algorithm to use when comparing the tables, in
order of preference (default Chunk,Nibble,GroupBy,
Stream)
- 2、解决方法(打印语句,并且输出语句,delete命令最好手动删除,我操作过程中-execute并没有执行成功)
[root@mysql-slave scripts]# pt-table-sync --replicate=test.ck2 --sync-to-master h=172.16.2.218,u=ptck,p=xxxxxx --algorithms=Nibble -d dbname -t opt_log --print
DELETE FROM `dbname`.`opt_log` WHERE `opt_id`='112b2abd-c743-45e8-bee2-f1c4b303f03b' LIMIT 1 /*percona-toolkit src_db:dbname src_tbl:opt_log src_dsn:P=3306,h=172.16.2.216,p=...,u=ptck dst_db:dbname dst_tbl:opt_log dst_dsn:h=172.16.2.218,p=...,u=ptck lock:1 transaction:1 changing_src:test.ck2 replicate:test.ck2 bidirectional:0 pid:27755 user:root host:mysql-slave*/;
DELETE FROM `dbname`.`opt_log` WHERE `opt_id`='2f9bb875-d62e-4951-851a-91b082e584f0' LIMIT 1 /*percona-toolkit src_db:dbname src_tbl:opt_log src_dsn:P=3306,h=172.16.2.216,p=...,u=ptck dst_db:dbname dst_tbl:opt_log dst_dsn:h=172.16.2.218,p=...,u=ptck lock:1 transaction:1 changing_src:test.ck2 replicate:test.ck2 bidirectional:0 pid:27755 user:root host:mysql-slave*/;
DELETE FROM `dbname`.`opt_log` WHERE `opt_id`='cf4275cc-119c-44a0-94cc-b0aa21d45f0f' LIMIT 1 /*percona-toolkit src_db:dbname src_tbl:opt_log src_dsn:P=3306,h=172.16.2.216,p=...,u=ptck dst_db:dbname dst_tbl:opt_log dst_dsn:h=172.16.2.218,p=...,u=ptck lock:1 transaction:1 changing_src:test.ck2 replicate:test.ck2 bidirectional:0 pid:27755 user:root host:mysql-slave*/;
[root@mysql-slave scripts]# pt-table-sync --replicate=test.ck2 --sync-to-master h=172.16.2.218,u=ptck,p=xxxxxx --algorithms=Nibble -d dbname -t opt_log --print -execute
DELETE FROM `dbname`.`opt_log` WHERE `opt_id`='112b2abd-c743-45e8-bee2-f1c4b303f03b' LIMIT 1 /*percona-toolkit src_db:dbname src_tbl:opt_log src_dsn:P=3306,h=172.16.2.216,p=...,u=ptck dst_db:dbname dst_tbl:opt_log dst_dsn:h=172.16.2.218,p=...,u=ptck lock:1 transaction:1 changing_src:test.ck2 replicate:test.ck2 bidirectional:0 pid:29751 user:root host:mysql-slave*/;
DELETE FROM `dbname`.`opt_log` WHERE `opt_id`='2f9bb875-d62e-4951-851a-91b082e584f0' LIMIT 1 /*percona-toolkit src_db:dbname src_tbl:opt_log src_dsn:P=3306,h=172.16.2.216,p=...,u=ptck dst_db:dbname dst_tbl:opt_log dst_dsn:h=172.16.2.218,p=...,u=ptck lock:1 transaction:1 changing_src:test.ck2 replicate:test.ck2 bidirectional:0 pid:29751 user:root host:mysql-slave*/;
DELETE FROM `dbname`.`opt_log` WHERE `opt_id`='cf4275cc-119c-44a0-94cc-b0aa21d45f0f' LIMIT 1 /*percona-toolkit src_db:dbname src_tbl:opt_log src_dsn:P=3306,h=172.16.2.216,p=...,u=ptck dst_db:dbname dst_tbl:opt_log dst_dsn:h=172.16.2.218,p=...,u=ptck lock:1 transaction:1 changing_src:test.ck2 replicate:test.ck2 bidirectional:0 pid:29751 user:root host:mysql-slave*/;
二、 数据同步失败02
代码语言：javascript复制[root@mysql-slave scripts]# pt-table-checksum h='172.16.2.216',u='ptck',p='xxxxxx',P=3306 --chunk-size-limit=16.0 -d dbname -t Appointment --nocheck-replication-filters --replicate=test.ck2 --no-check-binlog-format --recursion-method dsn=h=172.16.2.218,u=ptck,p=xxxxxx,P=3306,D=test,t=dsns
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-07T23:24:06 0 2 85117 5 5 0 1.138 dbname.Appointment
[root@mysql-slave scripts]# pt-table-sync --replicate=test.ck2 --sync-to-master h=172.16.2.218,u=ptck,p=xxxxxx -d dbname -t Appointment --print
Illegal mix of collations for operation 'concat_ws' [for Statement "SELECT /*dbname.Appointment:1/45*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `surname`, `givenname`, `nickname`, `passportno`, `mobile`, `gotime`, `backtime`, `entrytime`, `goflight`, `backflight`, `number`, `userremark`, `createtime`, `status`, `remark`, `memberid`, `wxnumber`, `alipaynumber`, `type`, `pass`, `permit`, CRC32(`pic`), `shopname`, `hoteladdress`, `hotelname`, `luggagenumber`, `goaddressname`, `comeaddressname`, `mobile2`, `isrelay`, `sex`, `birthday`, `questionandanswer`, `email`, `age`, `reservenum`, `goodsorderediqos`, `goodsorderedmulti`, `alipayname`, `couponcode`, `amountconsumption`, `entryshoptime`, `returnforeignairport`, `returnflighttime`, `returndomesticairport`, `mailingaddress`, `idcard`, `bankcardnumber`, `modifynum`, `rebatestatus`, CONCAT(ISNULL(`nickname`), ISNULL(`passportno`), ISNULL(`mobile`), ISNULL(`gotime`), ISNULL(`backtime`), ISNULL(`entrytime`), ISNULL(`goflight`), ISNULL(`backflight`), ISNULL(`userremark`), ISNULL(`remark`), ISNULL(`memberid`), ISNULL(`wxnumber`), ISNULL(`alipaynumber`), ISNULL(`type`), ISNULL(`pass`), ISNULL(`permit`), ISNULL(`pic`), ISNULL(`shopname`), ISNULL(`hoteladdress`), ISNULL(`hotelname`), ISNULL(`luggagenumber`), ISNULL(`goaddressname`), ISNULL(`comeaddressname`), ISNULL(`mobile2`), ISNULL(`isrelay`), ISNULL(`sex`), ISNULL(`birthday`), ISNULL(`questionandanswer`), ISNULL(`email`), ISNULL(`age`), ISNULL(`reservenum`), ISNULL(`goodsorderediqos`), ISNULL(`goodsorderedmulti`), ISNULL(`alipayname`), ISNULL(`couponcode`), ISNULL(`amountconsumption`), ISNULL(`entryshoptime`), ISNULL(`returnforeignairport`), ISNULL(`returnflighttime`), ISNULL(`returndomesticairport`), ISNULL(`mailingaddress`), ISNULL(`idcard`), ISNULL(`bankcardnumber`), ISNULL(`rebatestatus`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dbname`.`Appointment` FORCE INDEX (`PRIMARY`) WHERE (`id` < '216') AND ((((`id` >= '03136ba772c84ef7b492545dce294fac')) AND ((`id` <= '7a5bb57c6c4b4cffabc7bd5bb5daf9c1')))) FOR UPDATE"] at line 6251 while doing dbname.Appointment on 172.16.2.218
Illegal mix of collations for operation 'concat_ws' [for Statement "SELECT /*dbname.Appointment:1/44*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `surname`, `givenname`, `nickname`, `passportno`, `mobile`, `gotime`, `backtime`, `entrytime`, `goflight`, `backflight`, `number`, `userremark`, `createtime`, `status`, `remark`, `memberid`, `wxnumber`, `alipaynumber`, `type`, `pass`, `permit`, CRC32(`pic`), `shopname`, `hoteladdress`, `hotelname`, `luggagenumber`, `goaddressname`, `comeaddressname`, `mobile2`, `isrelay`, `sex`, `birthday`, `questionandanswer`, `email`, `age`, `reservenum`, `goodsorderediqos`, `goodsorderedmulti`, `alipayname`, `couponcode`, `amountconsumption`, `entryshoptime`, `returnforeignairport`, `returnflighttime`, `returndomesticairport`, `mailingaddress`, `idcard`, `bankcardnumber`, `modifynum`, `rebatestatus`, CONCAT(ISNULL(`nickname`), ISNULL(`passportno`), ISNULL(`mobile`), ISNULL(`gotime`), ISNULL(`backtime`), ISNULL(`entrytime`), ISNULL(`goflight`), ISNULL(`backflight`), ISNULL(`userremark`), ISNULL(`remark`), ISNULL(`memberid`), ISNULL(`wxnumber`), ISNULL(`alipaynumber`), ISNULL(`type`), ISNULL(`pass`), ISNULL(`permit`), ISNULL(`pic`), ISNULL(`shopname`), ISNULL(`hoteladdress`), ISNULL(`hotelname`), ISNULL(`luggagenumber`), ISNULL(`goaddressname`), ISNULL(`comeaddressname`), ISNULL(`mobile2`), ISNULL(`isrelay`), ISNULL(`sex`), ISNULL(`birthday`), ISNULL(`questionandanswer`), ISNULL(`email`), ISNULL(`age`), ISNULL(`reservenum`), ISNULL(`goodsorderediqos`), ISNULL(`goodsorderedmulti`), ISNULL(`alipayname`), ISNULL(`couponcode`), ISNULL(`amountconsumption`), ISNULL(`entryshoptime`), ISNULL(`returnforeignairport`), ISNULL(`returnflighttime`), ISNULL(`returndomesticairport`), ISNULL(`mailingaddress`), ISNULL(`idcard`), ISNULL(`bankcardnumber`), ISNULL(`rebatestatus`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `dbname`.`Appointment` FORCE INDEX (`PRIMARY`) WHERE (`id` < 'a9') AND ((((`id` >= '7a5bcb5a4b0f4260815b5e03c91b5d79')) AND ((`id` <= 'ffffce8989024bda88e586a3b4aa42ae')))) FOR UPDATE"] at line 6251 while doing dbname.Appointment on 172.16.2.218
“Illegal mix of collations for operation 'concat_ws'" 这个错误是个bug。
- 解决方法：
1、临时暂停从库主从复制关系 stop slave
2、主库备份需要操作的表 mysqldump -uroot -pxxx db table >table.sql
在主从库查看下表的行数 mysql -uroot -p**** -e 'select count(*) from db.tables'
3、在从库执行主库备份的表,进行数据库恢复。
4、从库启动start slave;
此方法，建议在数据写入低的时候操作。
