使用mysqldbcompare 对比数据库间差异

来源:本站原创 mysql_mariadb 超过53 views围观 0条评论

 

使用mysqldbcompare 对比数据库间差异

————-安装
rpm -ivh https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm
rpm -ivh https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.4-1.el6.noarch.rpm
————对比
mysqldbcompare –server1=root:root@localhost \
    –server2=root:root@localhost db1:db2 –changes-for=server1 -a \/
    –difftype=sql

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

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

[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

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

 
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

文章出自:CCIE那点事 http://www.jdccie.com/ 版权所有。本站文章除注明出处外,皆为作者原创文章,可自由引用,但请注明来源。 禁止全文转载。
本文链接:http://www.jdccie.com/?p=3740转载请注明转自CCIE那点事
如果喜欢:点此订阅本站
  • 相关文章
  • 为您推荐
  • 各种观点

暂时还木有人评论,坐等沙发!
发表评论

快捷键:Ctrl+Enter