[Mysql] 使用mysqldiff和mysqldbcompare检查数据一致性


本文总阅读量次

1、官网下载mysql-utilities工具

1
wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz

2、解压mysql-utilities工具：

1
2
3
4
5
6
7
tar -zxvf mysql-utilities-1.6.5.tar.gz
cd mysql-utilities-1.6.5/
cd scripts/
[root@dax-mysql-slave scripts]# ls
mysqlauditadmin.py  mysqlbinlogpurge.py   mysqldbcopy.py    mysqldiff.py       mysqlfrm.py         mysqlmetagrep.py   mysqlrpladmin.py  mysqlrplshow.py      mysqlserverinfo.py  mysqluserclone.py
mysqlauditgrep.py   mysqlbinlogrotate.py  mysqldbexport.py  mysqldiskusage.py  mysqlgrants.py      mysqlprocgrep.py   mysqlrplcheck.py  mysqlrplsync.py      mysqlslavetrx.py
mysqlbinlogmove.py  mysqldbcompare.py     mysqldbimport.py  mysqlfailover.py   mysqlindexcheck.py  mysqlreplicate.py  mysqlrplms.py     mysqlserverclone.py  mysqluc.py

在scripts目录下存在mysqldiff和mysqldbcompare用于比对数据的脚本

3、使用mysqldbcompare需要安装connector-python依赖关系：

官网下载

1
wget https://cdn.mysql.com/archives/mysql-connector-python-2.2/mysql-connector-python-2.2.3-0.1.el7.x86_64.rpm

安装

1
rpm -ivh mysql-connector-python-2.2.3-0.1.el7.x86_64.rpm

4、安装完成之后测试mysqldbcompare能否正常使用：

./mysqldbcompare.py

1
2
3
4
Traceback (most recent call last):
  File "./mysqldbcompare.py", line 28, in <module>
    from mysql.utilities.common.tools import check_python_version
ImportError: No module named utilities.common.tools

将python2.7下的模块，软连接到lib64目录下：

1
ln -s /usr/lib/python2.7/site-packages/mysql/utilities /usr/lib64/python2.7/site-packages/mysql/utilities

重新测试mysqldbcompare能否正常使用：
./mysqldbcompare.py 

1
2
3
Usage: mysqldbcompare --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1:db2

mysqldbcompare: error: You must specify at least one database to compare or use the --all option to compare all databases.

提示需要指定对表的库：
./mysqldbcompare.py –server1=root:12345678@10.0.7.50:3306:/data/mysql/tmp/mysql.sock –server2=root:12345678@10.0.7.51:3306:/data/mysql/tmp/mysql.sock test:test
或者
./mysqldbcompare.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test:test
执行结果：

1
2
3
4
5
6
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Checking databases test on server1 and test on server2
#
ERROR: The list of objects differs among database test and test.

5、准备测试数据：

server1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
root@db 03:40:  [test1]> select * from test;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

root@db 03:40:  [test1]> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

server2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
root@db 03:37:  [test1]> select * from test;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

root@db 06:20:  [test1]> show create table test
    -> ;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6、mysqldiff用于检测表结构的差异，如果某个对比表表结构相同，数据不同，mysqldiff并不会检测出来：

./mysqldiff.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test1:test1

1
2
3
4
5
6
7
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Comparing `test1` to `test1`                                     [PASS]
# Comparing `test1`.`aa` to `test1`.`aa`                           [PASS]
# Comparing `test1`.`test` to `test1`.`test`                       [PASS]
# Success. All objects are the same.

单独对比某一张表：
./mysqldiff.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test1.test:test1.test

1
2
3
4
5
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Comparing test1.test to test1.test                               [PASS]
# Success. All objects are the same.

7、结构一致，数据内容不一致，使用mysqldbcompare（用于检测数据库字符集，表结构，表数据等）检测：

./mysqldbcompare.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test1 –changes-for=server1 –difftype=sql

1
2
3
4
5
6
7
8
9
10
11
12
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Checking databases test1 on server1 and test1 on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     aa                                      pass    pass    -       
#           - Compare table checksum                                pass    
# TABLE     test                                    pass    FAIL    ERROR: Row counts are not the same among `test1`.`test` and `test1`.`test`.
#

提示test1库下面的test表数据不一致。

8、创建测试数据，表结构不同，数据内容相同：

server1：

1
2
3
4
5
6
7
8
9
10
11
12
create table test1 (id int(10) primary key);
insert into test1 values (1),(2),(3),(4),(5);
select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

server2:

1
2
3
4
5
6
7
8
9
10
11
12
create table test1 (id bigint(20));
insert into test1 values (1),(2),(3),(4),(5);
root@db 06:28:  [test1]> select * from test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

9、使用mysqldiff检测：

./mysqldiff.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test1.test1:test1.test1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Comparing test1.test1 to test1.test1                             [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- test1.test1
+++ test1.test1
@@ -1,4 +1,3 @@
 CREATE TABLE `test1` (
-  `id` int(10) NOT NULL,
-  PRIMARY KEY (`id`)
+  `id` bigint(20) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
# Compare failed. One or more differences found.

提示表结构存在差异。

10、使用mysqldbcompare检测：

./mysqldbcompare.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test1 –changes-for=server1 –difftype=sql

1
2
3
4
5
6
7
8
9
10
11
12
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Checking databases test1 on server1 and test1 on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     aa                                      pass    pass    -       
#           - Compare table checksum                                pass    
# TABLE     test                                    pass    FAIL    ERROR: Row counts are not the same among `test1`.`test` and `test1`.`test`.
#

当检测到异常之后会直接退出，不进行下面的比较，因此未检测到test1表的内容，加上-t（-t, –run-all-tests do not abort when a diff test fails）参数，运行测试模式，遇到异常之后仍然执行下面的比较：
./mysqldbcompare.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test1 –changes-for=server1 –difftype=sql -t

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Checking databases test1 on server1 and test1 on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     aa                                      pass    pass    -       
#           - Compare table checksum                                pass    
# TABLE     test                                    pass    FAIL    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  FAIL    
#
# Row counts are not the same among `test1`.`test` and `test1`.`test`.
#
# Transformation for --changes-for=server1:
#

DELETE FROM `test1`.`test` WHERE `ID` = '6';


# TABLE     test1                                   FAIL    pass    -       
#           - Compare table checksum                                FAIL    
#           - Find row differences                                  SKIP    
#
# Transformation for --changes-for=server1:
#

ALTER TABLE `test1`.`test1` 
  DROP PRIMARY KEY, 
  CHANGE COLUMN id id bigint(20) NULL;

# The table test1 does not have an usable Index or primary key.


# Database consistency check failed.
#
# ...done

11、创建两个test库，server1比server2实例多一个gtid_test14的表：

server1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
show tables;
+----------------+
| Tables_in_test |
+----------------+
| articles       |
| gtid_test10    |
| gtid_test11    |
| gtid_test12    |
| gtid_test13    |
| gtid_test14    |
| gtid_test15    |
| gtid_test2     |
| gtid_test3     |
| gtid_test4     |
| gtid_test5     |
| gtid_test6     |
| gtid_test7     |
| gtid_test8     |
| gtid_test9     |
| ratings        |
+----------------+
16 rows in set (0.00 sec)

server2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
show tables;
+----------------+
| Tables_in_test |
+----------------+
| articles       |
| gtid_test10    |
| gtid_test11    |
| gtid_test12    |
| gtid_test13    |
| gtid_test15    |
| gtid_test2     |
| gtid_test3     |
| gtid_test4     |
| gtid_test5     |
| gtid_test6     |
| gtid_test7     |
| gtid_test8     |
| gtid_test9     |
| ratings        |
+----------------+
15 rows in set (0.00 sec)

12、使用mysqldiff测试：

./mysqldiff.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test:test

1
2
3
4
5
6
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# WARNING: Objects in server1.test but not in server2.test:
#        TABLE: gtid_test14
# Compare failed. One or more differences found.

提示server2.test库下不存在gtid_test14表。

13、使用mysqldbcompare测试：

./mysqldbcompare.py –server1=root:12345678@10.0.7.50:3306 –server2=root:12345678@10.0.7.51:3306 test

1
2
3
4
5
6
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 10.0.7.50: ... connected.
# server2 on 10.0.7.51: ... connected.
# Checking databases test on server1 and test on server2
#
ERROR: The list of objects differs among database test and test.

只会提示两个实例的test库有差异，并没有列出详细信息。在对比数据时可先使用mysqldiff工具检测两个server端的表结构是否一致，如果没有差异，再使用mysqldbcompare工具去检测表数据是否一致。

mysql
目录
  1. 1. 1、官网下载mysql-utilities工具
  2. 2. 2、解压mysql-utilities工具：
  3. 3. 3、使用mysqldbcompare需要安装connector-python依赖关系：
  4. 4. 4、安装完成之后测试mysqldbcompare能否正常使用：
  5. 5. 5、准备测试数据：
  6. 6. 6、mysqldiff用于检测表结构的差异，如果某个对比表表结构相同，数据不同，mysqldiff并不会检测出来：
  7. 7. 7、结构一致，数据内容不一致，使用mysqldbcompare（用于检测数据库字符集，表结构，表数据等）检测：
  8. 8. 8、创建测试数据，表结构不同，数据内容相同：
  9. 9. 9、使用mysqldiff检测：
  10. 10. 10、使用mysqldbcompare检测：
  11. 11. 11、创建两个test库，server1比server2实例多一个gtid_test14的表：
  12. 12. 12、使用mysqldiff测试：
  13. 13. 13、使用mysqldbcompare测试：

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog