mysql快速比对大表一致性_mysqldiff批量比对表结构差异

mysqldiff是mysql官方推荐的库对比工具，MySQL Utilities中的一个脚本。可以比对两个库中缺少的表，相同的表缺少的字段。

1.下载mysqldiff

下载地址：http://downloads.mysql.com/archives/utilities/

2.下载mysql-connector(python)，否则执行会报错。因为这个插件是通过python连接器连接的。

下载地址：https://dev.mysql.com/downloads/connector/python/

3.安装

通过tar.gz安装和yum安装mysql-utilities出现错误

export pythonpath=$pythonpath:/root/mysql-utilities-1.6.5/mysql/utilities/common/tools

试了也不行

安装mysql5.6 yum源以rpm形式安装的mysql-utilities不会报module找不到

解决

卸载原mysql-connector-python8.0版本

yum -y remove mysql-connector-python

并下载安装mysql-connector-python-2.1.7

rpm -uvh https://cdn.mysql.com//downloads/connector-python/mysql-connector-python-2.1.7-1.el7.x86_64.rpm

mysqldbcompare --version

mysql utilities mysqldbcompare version 1.6.5

license type: gplv2

如果CENTOS版本较低，Python是2.6的是可以解决的，

但因为我的安装环境是CentOS Linux release 7.5.1804 (Core) ，python版本2.7.5,尝试过各种办法安装msyqldiff都是各种报错，无法使用

最后考虑在docker中安装，使用了CentOS6.10版本，可以成功使用mysqldiff工具了

docker run -itd centos:6 /bin/bash

# docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

8e85597eb102 centos:6 "/bin/bash" 2 months ago Up 2 months mysqldiff2

docker cp mysql-utilities-1.6.5-1.el6.noarch.rpm mysqldiff:/opt/mysqltools/

# docker exec -it mysqldiff2 /bin/bash

# yum install mysql-utilities-1.6.5-1.el7.noarch.rpm

# cat /etc/redhat-release

CentOS release 6.10 (Final)

# mysqldiff --version

MySQL Utilities mysqldiff version 1.6.5

License type: GPLv2

4.命令模板

mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4

这里讲的是两种用法。可以直接对比库，db3:db4 ,也可以对比表 db1.table1:db2.table2

mysqldiff --server1=root:mysql5635@localhost:3306 --server2=root:mysql5635@localhost:3306 --changes-for=server2 \

--skip-table-options --show-reverse --difftype=sql test.test1:test.test2

--server1：配置server1的连接。

--server2：配置server2的连接。

--character-set：配置连接时用的字符集，如果不显示配置默认使用character_set_client。

--width：配置显示的宽度。

--skip-table-options：保持表的选项不变，即对比的差异里面不包括表名、AUTO_INCREMENT、ENGINE、CHARSET等差异。 这个一定要加，否则肯定对比失败。测试环境和正式环境自增字段的当前值肯定不一样。如果是主从对比，就不要加。

-d DIFFTYPE,--difftype=DIFFTYPE：差异的信息显示的方式，有 [unified|context|differ|sql]，默认是unified。如果使用sql，那么就直接生成差异的SQL，这样非常方便。

--changes-for=：修改对象。例如 –changes-for=server2，那么对比以sever1为主，生成的差异的修改也是针对server2的对象的修改。

--show-reverse：在生成的差异修改里面，同时会包含server2和server1的修改。

--force：完成所有的比较，不会在遇到一个差异之后退出

-vv：便于调试，输出许多信息

-q：quiet模式，关闭多余的信息输出

mysqldiff --server1=root:root456@10.20.30.122:3306 --server2=root:root456@10.40.50.122:3306 --changes-for=server2 --skip-table-options --show-reverse --difftype=sql elag:elag

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

# server1 on localhost: ... connected.

# server2 on localhost: ... connected.

# Comparing elag.test1 to elag.test1 [FAIL]

# Transformation for --changes-for=server2:

#

ALTER TABLE `elag`.`test1`

DROP COLUMN D,

ADD PRIMARY KEY(`id`),

CHANGE COLUMN b b varchar(10) NULL,

ADD COLUMN d int(11) NULL AFTER c,

CHANGE COLUMN a a varchar(10) NOT NULL,

CHANGE COLUMN c c varchar(10) NULL COMMENT 'c';

#

# Transformation for reverse changes (--changes-for=server1):

#

# ALTER TABLE `elag`.`test1`

# DROP PRIMARY KEY,

# DROP COLUMN d,

# CHANGE COLUMN b b varchar(5) NULL,

# ADD COLUMN D int(11) NULL AFTER c,

# CHANGE COLUMN a a varchar(10) NULL,

# CHANGE COLUMN c c varchar(10) NULL;

#

# Compare failed. One or more differences found.

注意事项

1.mysqldiff工具比对两台数据库的时候，只要发现有异常就会停止继续比对，如果要继续比对，可以加参数--force

mysqldiff --server1=diff:diff900TEST@10.20.30.122:3306 --server2=diff:diff900TEST@10.40.50.122:3306 \

--changes-for=server2 --skip-table-options --difftype=sql --force elag:elag eclipse:eclipse

2.mysqldiff提供的建议修改SQL命令，drop index的操作比较危险，可以改成rename索引名称

例如

ALTER TABLE `eclipse`.`device_scan_info`

DROP INDEX IDX_BILL_CODE,

DROP INDEX IDX_CREATE_TIME,

ADD INDEX IDX_DEVICE_SCAN_BILL_CODE (BILL_CODE),

ADD INDEX IDX_DEVICE_SCAN_CREATE_TIME (CREATE_TIME);

ALTER TABLE eclipse.device_scan_info rename INDEX IDX_BILL_CODE to IDX_DEVICE_SCAN_BILL_CODE,\

rename INDEX IDX_CREATE_TIME to IDX_DEVICE_SCAN_CREATE_TIME;

3.如果批量比较很多服务器，那么需要用到批量工具，建议用python fabric,或者SHELL脚本遍历

4.修改的表中涉及到中文字符的，注意加参数--default-character-set=utf8

mysql --default-character-set=utf8 -e "ALTER TABLE elag.data_sync_log CHANGE COLUMN SERVER_HOST_CODE \

SERVER_HOST_CODE varchar(255) NULL COMMENT '测试编码';"