首页 > 代码库 > MySQL Utilities-mysqldbcompare
MySQL Utilities-mysqldbcompare
mysqldbcompare从两个数据库比较对象和数据的不同。数据库中的对象包括：表、视图、触发器、存储过程、函数和事件。每一个对象类型计数可以使用-vv选项显示。通过一系列步骤检查进行测试，默认情况下，一旦测试失败就终止检测。可以指定--run-all-tests选项来进行所有的测试。
环境：CentOS6.5源码安装多个MySQL实例及复制搭建，之前做复制时Master实例的test库中的数据表没有复制到Slave实例
#使用Master中的test.test1的建表语句创建Slave中的test.test1，修改列c的说明，并插入数据use test;create table test1(id int not null primary key,a varchar(10) not null,b varchar(10),c varchar(10) comment ‘cc‘,d int)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘test1‘;insert into test1 values(1,‘a‘,‘b‘,‘c‘,1);
此时两表的定义和数据是不一致的。
比较检测的步骤
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 on 192.168.85.129: ... connected.# server2 on 192.168.85.129: ... connected.ERROR: The database test1 does not exist.
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 on 192.168.85.129: ... connected.# server2 on 192.168.85.129: ... connected.# Checking databases test on server1 and test on server2#ERROR: The list of objects differs among database test and test.
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 on 192.168.85.129: ... connected.# server2 on 192.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: The object definitions do not match.
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 on 192.168.85.129: ... connected.# server2 on 192.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`.
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 on 192.168.85.129: ... connected.# server2 on 192.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 ## Transformation for --changes-for=server2:#DELETE FROM `test`.`test1` WHERE `id` = ‘1‘;## Transformation for 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 on 192.168.85.129: ... connected.# server2 on 192.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 ## Transformation for --changes-for=server2:#DELETE FROM `test`.`test1` WHERE `id` = ‘1‘;## Transformation for 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 on 192.168.85.129: ... connected.# server2 on 192.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
在做Find row differences操作时，会将下面语句写到二进制日志
指定--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 on 192.168.85.129: ... connected.# server2 on 192.168.85.129: ... connected.# Checking databases test on server1 and test on server2## WARNING: Objects in server1.test but not in server2.test:# TABLE: test2## Defn Row Data # Type Object Name Diff Count Check # ------------------------------------------------------------------------- # TABLE test1 FAIL FAIL - # - Compare table checksum FAIL # - Find row differences FAIL ## Transformation for --changes-for=server2:#ALTER TABLE `test`.`test1` CHANGE COLUMN c c varchar(10) NULL COMMENT ‘c‘;## Transformation for 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`.## Transformation for --changes-for=server2:#DELETE FROM `test`.`test1` WHERE `id` = ‘1‘;## Transformation for 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
检测做复制的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 on 192.168.85.129: ... connected.# server2 on 192.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
感觉mysqldbcompare的--run-all-tests选项包括了mysqldiff。mysqldiff如果指定数据库(db1:db2)，将对比数据库下的对象(不会检查对象的定义)；如果指定具体对象(db1.obj1:db2.obj2)，将对比其定义。
MySQL Utilities-mysqldbcompare
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。