使用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>