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