mysql compare 破解_MySQL-Utilities：mysqldbcompare及跳过复制错误

mysqldbcompare也是MySQL-Utilities工具集的一个脚本。mysqldbcompare从两个数据库比较对象和数据的不同。数据库中的对象包括：表、视图、触发器、存储过程、函数和事件。每一个对象类型计数可以使用-vv选项显示。通过一系列步骤检查进行测试，默认情况下，一旦测试失败就终止检测。可以指定--run-all-tests选项来进行所有的测试。

环境：CentOS6.5源码安装多个MySQL实例及复制搭建，之前做复制时Master实例的test库中的数据表没有复制到Slave实例

#使用Master中的test.test1的建表语句创建Slave中的test.test1，修改列c的说明，并插入数据usetest;create tabletest1

(idint not null primary key,

avarchar(10) not null,

bvarchar(10),

cvarchar(10) comment 'cc',

dint)

ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test1';insert into test1 values(1,'a','b','c',1);

View Code

此时两表的定义和数据是不一致的。

比较检测的步骤

1、数据库定义的检查 检查对比的数据库是否存在

[root@VMUest ~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test1

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

ERROR: The database test1 does not exist.

View Code

2、检测数据库的对象 检查两者数据库中的对象是否丢失。可以使用--skip-object-compare选项跳过这步。

[root@VMUest ~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases test on server1 and test on server2

#

ERROR: The list of objects differs among database test and test.

View Code

3、比较对象的定义 对对象的定义(CREATE语句)进行比较和显示不同。可以使用--skip-diff选项跳过这步。

[root@VMUest ~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test --skip-object-compare

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases test on server1 and test on server2

#

# Defn Row Data

# Type Object Name Diff Count Check

#-------------------------------------------------------------------------# TABLE test1 FAIL ERROR: Theobject definitions do not match.

View Code

4、检测表的行数 检查表是否有相同的行数，但这并不确保表的数据是一致性的。可以使用--skip-row-count选项跳过这步。

[root@VMUest ~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test --skip-object-compare --skip-diff# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases test on server1 and test on server2

#

# Defn Row Data

# Type Object Name Diff Count Check

#-------------------------------------------------------------------------# TABLE test1 SKIP FAIL ERROR: Row counts are not the same among `test`.`test1` and `test`.`test1`.

View Code

5、检测表数据的一致性 检查行数同时检查数据是否一致，两表需要有主键或唯一索引。可以使用--skip-checksum-table选项跳过表校验，使用--skip-data-check选项跳过数据检查。

[root@VMUest ~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test --skip-object-compare --skip-diff --skip-row-count

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases test on server1 and test on server2

#

# Defn Row Data

# Type Object Name Diff Count Check

#-------------------------------------------------------------------------# TABLE test1 SKIP SKIP-#-Compare table checksum FAIL

#-Find row differences FAIL

#

# Transformationfor --changes-for=server2:

#

DELETE FROM `test`.`test1` WHERE `id` = '1';

#

# Transformationfor reverse changes (--changes-for=server1):

#

# INSERT INTO `test`.`test1` (`id`, `a`, `b`, `c`, `d`) VALUES('1', 'a', 'b', 'c', '1');

#

# Database consistency check failed.

#

# ...done#使用--skip-checksum-table选项跳过表校验

[root@VMUest~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test --skip-object-compare --skip-diff --skip-row-count --skip-checksum-table

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases test on server1 and test on server2

#

# Defn Row Data

# Type Object Name Diff Count Check

#-------------------------------------------------------------------------# TABLE test1 SKIP SKIP-#-Compare table checksum SKIP

#-Find row differences FAIL

#

# Transformationfor --changes-for=server2:

#

DELETE FROM `test`.`test1` WHERE `id` = '1';

#

# Transformationfor reverse changes (--changes-for=server1):

#

# INSERT INTO `test`.`test1` (`id`, `a`, `b`, `c`, `d`) VALUES('1', 'a', 'b', 'c', '1');

#

# Database consistency check failed.

#

# ...done#使用--skip-data-check选项跳过数据检查

[root@VMUest~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test --skip-object-compare --skip-diff --skip-row-count --skip-checksum-table --skip-data-check

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases test on server1 and test on server2

#

# Defn Row Data

# Type Object Name Diff Count Check

#-------------------------------------------------------------------------# TABLE test1 SKIP SKIP SKIP

# Databases are consistent given skip options specified.

#

# ...done

View Code

指定--run-all-tests选项来进行所有的检测

[root@VMUest ~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql test:test --run-all-tests

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases test on server1 and test on server2

#

# WARNING: Objectsin server1.test but not inserver2.test:

# TABLE: test2

#

# Defn Row Data

# Type Object Name Diff Count Check

#-------------------------------------------------------------------------# TABLE test1 FAIL FAIL-#-Compare table checksum FAIL

#-Find row differences FAIL

#

# Transformationfor --changes-for=server2:

#

ALTER TABLE `test`.`test1`

CHANGE COLUMN c c varchar(10) NULL COMMENT 'c';

#

# Transformationfor reverse changes (--changes-for=server1):

#

# ALTER TABLE `test`.`test1`

# CHANGE COLUMN c c varchar(10) NULL COMMENT 'cc';

#

# Row counts are not the same among `test`.`test1` and `test`.`test1`.

#

# Transformationfor --changes-for=server2:

#

DELETE FROM `test`.`test1` WHERE `id` = '1';

#

# Transformationfor reverse changes (--changes-for=server1):

#

# INSERT INTO `test`.`test1` (`id`, `a`, `b`, `c`, `d`) VALUES('1', 'a', 'b', 'c', '1');

#

# Database consistency check failed.

#

# ...done

View Code

检测做复制的sakila库

[root@VMUest ~]# mysqldbcompare --server1=mydba:mysql5635@192.168.85.129:3306 --server2=mydba:mysql5635@192.168.85.129:3307 --changes-for=server2 --show-reverse --difftype=sql sakila:sakila

# WARNING: Using a password on the command line interface can be insecure.

# server1 on192.168.85.129: ... connected.

# server2 on192.168.85.129: ... connected.

# Checking databases sakila on server1 and sakila on server2

#

# Defn Row Data

# Type Object Name Diff Count Check

#-------------------------------------------------------------------------# FUNCTION get_customer_balance pass- -# FUNCTION inventory_held_by_customer pass- -# FUNCTION inventory_in_stock pass- -# PROCEDURE film_in_stock pass- -# PROCEDURE film_not_in_stock pass- -# PROCEDURE rewards_report pass- -# TABLE actor pass pass-#-Compare table checksum pass

# TABLE address pass pass-#-Compare table checksum pass

# TABLE category pass pass-#-Compare table checksum pass

# TABLE city pass pass-#-Compare table checksum pass

# TABLE country pass pass-#-Compare table checksum pass

# TABLE customer pass pass-#-Compare table checksum pass

# TABLE film pass pass-#-Compare table checksum pass

# TABLE film_actor pass pass-#-Compare table checksum pass

# TABLE film_category pass pass-#-Compare table checksum pass

# TABLE film_text pass pass-#-Compare table checksum pass

# TABLE inventory pass pass-#-Compare table checksum pass

# TABLE language pass pass-#-Compare table checksum pass

# TABLE payment pass pass-#-Compare table checksum pass

# TABLE rental pass pass-#-Compare table checksum pass

# TABLE staff pass pass-#-Compare table checksum pass

# TABLE store pass pass-#-Compare table checksum pass

# TRIGGER customer_create_date pass- -# TRIGGER del_film pass- -# TRIGGER ins_film pass- -# TRIGGER payment_date pass- -# TRIGGER rental_date pass- -# TRIGGER upd_film pass- -# VIEW actor_info pass- -# VIEW customer_list pass- -# VIEW film_list pass- -# VIEW nicer_but_slower_film_list pass- -# VIEW sales_by_film_category pass- -# VIEW sales_by_store pass- -# VIEW staff_list pass- -# Databases are consistent.

#

# ...done

View Code

感觉mysqldbcompare的--run-all-tests选项包括了mysqldiff。mysqldiff如果指定数据库对(db1:db2)，将对比数据库下的对象(不会检查对象的定义)；如果指定具体对象(db1.obj1:db2.obj2)，将对比其定义。

跳过复制错误

在前面对比数据的第5步做Find row differences操作时，会将下面语句写到二进制日志(实际是对比的双方都会有这样的操作，可开启log_bin查看)

现在已经知道主从的test库不一致，下面测试如何跳过复制错误。在主库删除表test2

mysql> drop tabletest2;

Query OK,0rows affected

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000002' from 16061;+------------------+-------+------------+-----------+-------------+----------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-------+------------+-----------+-------------+----------------------------------------------------------+

| mysql-bin.000002 | 16061 | Query | 6 | 16179 | use `test`; DROP TABLE `test2` /*generated by server*/ |

+------------------+-------+------------+-----------+-------------+----------------------------------------------------------+

1 row in set

View Code

在从库查看复制状态

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:127.0.0.1Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos:16179Relay_Log_File: mysql-relay-bin.000005Relay_Log_Pos:16224Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:1051Last_Error: Error'Unknown table'test.test2'' on query. Default database: 'test'. Query: 'DROP TABLE `test2` /* generated by server */'Skip_Counter:0Exec_Master_Log_Pos:16061Relay_Log_Space:16678Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:1051Last_SQL_Error: Error'Unknown table'test.test2'' on query. Default database: 'test'. Query: 'DROP TABLE `test2` /* generated by server */'Replicate_Ignore_Server_Ids:

Master_Server_Id:6Master_UUID: 02a05b2c-0557-11e7-bb02-000c29493a20

Master_Info_File:/usr/local/mysql3307/log/master.info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:170315 14:21:55Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0

1 row in set (0.00 sec)

View Code

报错Unknown table，使用下面语句跳过一个事务

mysql>stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

mysql> start slave;

View Code

重新查看复制状态

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:127.0.0.1Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos:16179Relay_Log_File: mysql-relay-bin.000006Relay_Log_Pos:283Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:16179Relay_Log_Space:16678Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:6Master_UUID: 02a05b2c-0557-11e7-bb02-000c29493a20

Master_Info_File:/usr/local/mysql3307/log/master.info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State: Slave hasread all relay log; waiting for the slave I/O thread to updateit

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position:0

1 row in set (0.00 sec)

View Code

对于这种需求明确的通过跳过出错事务就能将主从保持一致。