使用pt-online-schema-change 修复主从数据表数据不一致

dsfwqd

使用pt-online-schema-change 修复主从数据表数据不一致


step 1: 设置master的binlog_format为row    set global binlog_format=row;
step 2： 对比一下主从的test11 的某一条数据，并在slave上删除该条数据
--master
mysql> select * from test11 where comment_id=20;
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
| comment_id | report_id | uid   | content              | dateline   | status | client_ip       | reply_uid | score |
+------------+-----------+-------+----------   -----+------------+--------+-----------------+-----------+-------+
|         20 |       134 | 48**7 | 感谢一下你的推荐哦     | 1301567128 |      0 | 113.234.154.150 |         0 | 0.000 |
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql>
--slave
mysql> select * from test11 where comment_id=20;
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
| comment_id | report_id | uid   | content              | dateline   | status | client_ip       | reply_uid | score |
+------------+-----------+-------+----------   -----+------------+--------+-----------------+-----------+-------+
|         20 |       134 | 48**7 | 感谢一下你的推荐哦     | 1301567128 |      0 | 113.234.154.150 |         0 | 0.000 |
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql>
mysql> delete from test11 where comment_id=20;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test11 where comment_id=20;
Empty set (0.00 sec)
mysql>
step 3: 使用pt工具在master上执行 alter table test.test11 ENGINE=InnoDB
--master
pt-online-schema-change -S /home/jm/data/my6007/socket/mysqld.sock --alter='ENGINE=InnoDB' D=test,t=test11 --execute
...
Successfully altered `test`.`test11`.
step 4: 查看主从数据
--slave
mysql> select * from test11 where comment_id=20;
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
| comment_id | report_id | uid   | content              | dateline   | status | client_ip       | reply_uid | score |
+------------+-----------+-------+----------   -----+------------+--------+-----------------+-----------+-------+
|         20 |       134 | 48**7 | 感谢一下你的推荐哦     | 1301567128 |      0 | 113.234.154.150 |         0 | 0.000 |
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql>
--master
mysql> select * from test11 where comment_id=20;
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
| comment_id | report_id | uid   | content              | dateline   | status | client_ip       | reply_uid | score |
+------------+-----------+-------+----------   -----+------------+--------+-----------------+-----------+-------+
|         20 |       134 | 48**7 | 感谢一下你的推荐哦     | 1301567128 |      0 | 113.234.154.150 |         0 | 0.000 |
+------------+-----------+-------+-----------------+------------+--------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql>