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 '测试编码';"