mysql db compare_使用mysqldbcompare 对比数据库间差异

使用mysqldbcompare 对比数据库间差异

mysqldbcompare –server1=root:root@localhost –server2=root:root@localhost db1:db2 –changes-for=server1 -a –difftype=sql

mysqldbcompare –server1=admin:admin@172.16.9.83:3355 –server2=admin:admin@172.16.9.83:3356  –changes-for=server1  -a –difftype=sql

使用-a 就不能指定数据库，是所有库的对比

mysqldbcompare –server1=admin:admin@172.16.9.83:3355 –server2=admin:admin@172.16.9.83:3356  –changes-for=server1  -a –difftype=sql

[root@sqltest02 ~]# mysqldbcompare –server1=admin:admin@172.16.9.83:3355 –server2=admin:admin@172.16.9.83:3356 –changes-for=server1 budget:budget –difftype=sql

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

# server1 on 172.16.9.83: … connected.

# server2 on 172.16.9.83: … connected.

# Checking databases budget on server1 and budget on server2

#

#                                                   Defn    Row     Data

# Type      Object Name                             Diff    Count   Check

# ————————————————————————-

# PROCEDURE head_audit_procedure                    pass    -       -

# TABLE     base_data_budget                        pass    pass    -

#           – Compare table checksum                                pass

# TABLE     budget_body                             pass    pass    -

#           – Compare table checksum                                FAIL

—————参数说明相关文章

http://www.ttlsa.com/mysql/mysqldbcompare-compare-two-databases/

https://blog.csdn.net/zxb4221v/article/details/61782128

[root@sqltest02 ~]# find / -name mysqldbcompare^C

[root@sqltest02 ~]# mysqldbcompare –server1=admin:admin@172.16.9.83:3355 –server2=admin:admin@172.16.9.83:3356 –changes-for=server1 budget:budget

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

# server1 on 172.16.9.83: … connected.

# server2 on 172.16.9.83: … connected.

# Checking databases budget on server1 and budget on server2

[root@sqltest02 ~]# rpm -ivh https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el6.noarch.rpm

Retrieving https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el6.noarch.rpm

warning: /var/tmp/rpm-tmp.2Ob7hD: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: Failed dependencies:

mysql-connector-python >= 2.0.0 is needed by mysql-utilities-1.6.5-1.el6.noarch

[root@sqltest02 ~]# yum update mysql-connector-python

v[root@sqltest02 ~]# rpm -e  yum install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm^C

[root@sqltest02 ~]# rpm -e mysql-connector-python-1.1.6-1.el6.noarch

[root@sqltest02 ~]#

[root@sqltest02 ~]#

[root@sqltest02 ~]#

[root@sqltest02 ~]#

[root@sqltest02 ~]# rpm -ivh https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm

Retrieving https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm

warning: /var/tmp/rpm-tmp.CcFjfP: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing…                ########################################### [100%]

1:mysql-connector-python ########################################### [100%]

[root@sqltest02 ~]#

示例一：检查server1,server2上的某个表结构是否一致

mysqldiff –server1=user:pwd@host:port –server2=user:pwd@host:port –changes-for=server2  dbname.tablename:dbname.tablename

示例二：检查server1,server2上的两个数据库中的表结构是否一致

mysqldiff –server1=user:pwd@host:port –server2=user:pwd@host:port dbname:dbname –changes-for=server2

3、mysqldbcompare

mysqldiff该工具是官方mysql-utilities工具集的一个脚本，可以用来检查不同数据库之间的数据一致性，检查内容包括数据库字符集、表结构、数据内容，只要有一个不一样，则检查不通过。

示例：

mysqldbcompare –server1=user:pwd@192.168.56.110:3306 –server2=user:pwd@192.168.56.111:3306 –changes-for=server1 dbname:dbname

mysqldbcompare –server1=admin:admin@172.16.9.83:3355 –server2=admin:admin@172.16.9.83:3356 –changes-for=server1 budget:budget