MySQL 表结构对比

一、安装 mysql-schema-diff

Ubuntu 20.04 系统使用如下命令安装:

1
apt install libmysql-diff-perl -y

安装完成后使用 --help 应该能看到相关提示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
➜ ~ mysql-schema-diff --help
Usage: mysql-schema-diff [ options ] <database1> <database2>

Options:
-?, --help show this help
-A, --apply interactively patch database1 to match database2
-B, --batch-apply non-interactively patch database1 to match database2
-d, --debug[=N] enable debugging [level N, default 1]
-l, --list-tables output the list off all used tables
-o, --only-both only output changes for tables in both databases
-k, --keep-old-tables don't output DROP TABLE commands
-c, --keep-old-columns don't output DROP COLUMN commands
-n, --no-old-defs suppress comments describing old definitions
-t, --table-re=REGEXP restrict comparisons to tables matching REGEXP
-i, --tolerant ignore DEFAULT, AUTO_INCREMENT, COLLATE, and formatting changes
-S, --single-transaction perform DB dump in transaction

-h, --host=... connect to host
-P, --port=... use this port for connection
-u, --user=... user for login if not current user
-p, --password[=...] password to use when connecting to server
-s, --socket=... socket to use when connecting to server

for <databaseN> only, where N == 1 or 2,
--hostN=... connect to host
--portN=... use this port for connection
--userN=... user for login if not current user
--passwordN[=...] password to use when connecting to server
--socketN=... socket to use when connecting to server

Databases can be either files or database names.
If there is an ambiguity, the file will be preferred;
to prevent this prefix the database argument with `db:'.

二、生成差异 SQL

安装完成后可直接使用该工具生成差异 SQL 文件mysql-schema-diff 工具使用如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Usage: mysql-schema-diff [ options ] <database1> <database2>

Options:
-?, --help show this help
-A, --apply interactively patch database1 to match database2
-B, --batch-apply non-interactively patch database1 to match database2
-d, --debug[=N] enable debugging [level N, default 1]
-l, --list-tables output the list off all used tables
-o, --only-both only output changes for tables in both databases
-k, --keep-old-tables don't output DROP TABLE commands
-c, --keep-old-columns don't output DROP COLUMN commands
-n, --no-old-defs suppress comments describing old definitions
-t, --table-re=REGEXP restrict comparisons to tables matching REGEXP
-i, --tolerant ignore DEFAULT, AUTO_INCREMENT, COLLATE, and formatting changes
-S, --single-transaction perform DB dump in transaction

-h, --host=... connect to host
-P, --port=... use this port for connection
-u, --user=... user for login if not current user
-p, --password[=...] password to use when connecting to server
-s, --socket=... socket to use when connecting to server

for <databaseN> only, where N == 1 or 2,
--hostN=... connect to host
--portN=... use this port for connection
--userN=... user for login if not current user
--passwordN[=...] password to use when connecting to server
--socketN=... socket to use when connecting to server

通俗的说,通过 --hostN 等参数指定两个数据库地址,例如 --password1 指定第一个数据库密码,--password2 指定第二个数据库密码;然后最后仅跟 数据库1[.表名] 数据库2[.表名],表名如果不写则默认对比两个数据库。以下为样例命令:

1
2
3
4
5
6
mysql-schema-diff \
--host1 127.0.0.1 --port1 3300 \
--user1 bleem --password1=Bleem77965badf \
--host2 127.0.0.1 --port2 3306 \
--user2 bleem --password2=asnfskdf667asd8 \
testdb testdb

注意,首次运行后请根据生成的 SQL 判断对比是否正确,比如说想把比较新的测试库更改同步到生产库,那么 SQL 里全是DROP 字样的删除动作,这说明 --hostN 等参数指定反了(变成了生产库同步测试库),此时只需要将 --hostN 参数调换一下即可(1改成2,2改成1),这样生成的 SQL 就会变为 ADD 字样的添加动作。

三、网络问题

mysql-schema-diff 工具需要在运行时能同时连接两个数据库,常规情况下可以通过 SSH 打洞来临时解决访问问题;如果实在无法打通网络环境,mysql-schema-diff 还支持文件对比,以下为一些文件对比的示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# compare table definitions in two files
mysql-schema-diff db1.mysql db2.mysql

# compare table definitions in a file 'db1.mysql' with a database 'db2'
mysql-schema-diff db1.mysql db2

# interactively upgrade schema of database 'db1' to be like the
# schema described in the file 'db2.mysql'
mysql-schema-diff -A db1 db2.mysql

# compare table definitions in two databases on a remote machine
mysql-schema-diff --host=remote.host.com --user=myaccount db1 db2

# compare table definitions in a local database 'foo' with a
# database 'bar' on a remote machine, when a file foo already
# exists in the current directory
mysql-schema-diff --host2=remote.host.com --password=secret db:foo bar

本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 国际许可协议进行许可,转载请注明出处。