mysql 数据库 对比_MySQL数据库mysqldiff实现两个数据库差异对比

在团队项目的开发之中。常常会存在多个环境或者版本并行开发。例如本地的test环境，演示环境的dev环境，已经正式环境。代码的管理可以使用git、svn 或者发布系统walle等。

但是MySQL的管理就有些难以应付，对于大多数的规范化的团队，对数据库的每一次表结构的操作都需要记录。这样升级程序的时候直接执行变更的SQL语句即可。但是有时候也会出现记录不完整或者遗漏等造成本地和正式环境的不一致。

这个时候就需要去寻找两个数据库表结构的不同，人力的去寻找费时费力且很难准确。这个时候我们就需要使用到MySQL的相关工具，例如mysqldiff。

//使用方法

D:\soft\MySQL\MySQL Utilities 1.6>

mysqldiff

--server1=user:password@host:3306:socket

--server2=user:password@host:3306:socket

--force

--changes-for=server2

--difftype=sql

dbname_test:dbname_dev > D:\diff.sql

这样就会导出一个sql语句到D盘，你就可以执行相关sql实现两个数据库的一致性了。

参数可以调整，更多参数参考下面的官方文档。

也可以考虑mysqldbcompare

下面是mysql工具的下载和相关说明。详细的内容请参考官方文档。

工具官方下载地址

https://downloads.mysql.com/archives/utilities/

MySQL的官方使用说明如下

https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiff.html

OPTIONS

mysqldiff accepts the following command-line options:

--help

Display a help message and exit.

--license

Display license information and exit.

--changes-for=direction

Specify the server to show transformations to match the other server. For example, to see the transformation for transforming object definitions on server1 to match the corresponding definitions on server2, use --changes-for=server1. Permitted values are server1 and server2. The default is server1.

--character-set=charset

Sets the client character set. The default is retrieved from the server variable character_set_client.

--difftype=difftype, -ddifftype

Specify the difference display format. Permitted format values are unified (default), context, differ, and sql.

--compact

Compacts the output by reducing the control lines that are displayed in the diff results. This option should be used together with one of the following difference types: unified or context.

--force

Do not halt at the first difference found. Process all objects to find all differences.

--quiet, -q

Do not print anything. Return only an exit code of success or failure.

--server1=source

Connection information for the first server.

To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

--server2=source

Connection information for the second server.

To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : login-path[:port][:socket]

Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : configuration-file-path[:section]

Specify the data on the command-line (unencrypted, visible). Example : user[:passwd]@host[:port][:socket]

--show-reverse

Produce a transformation report containing the SQL statements to conform the object definitions specified in reverse. For example, if --changes-for is set to server1, also generate the transformation for server2.

Note

The reverse changes are annotated and marked as comments.

--skip-table-options

Ignore the differences between all table options, such as AUTO_INCREMENT, ENGINE, CHARSET, etc.). A warning is issued if the --skip-table-options option is used and table option differences are found.

--ssl-ca

The path to a file that contains a list of trusted SSL CAs.

--ssl-cert

The name of the SSL certificate file to use for establishing a secure connection.

--ssl-key

The name of the SSL key file to use for establishing a secure connection.

--ssl

Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

--verbose, -v

Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

--version

Display version information and exit.

--width=number

Change the display width of the test report. The default is 75 characters.

当生活在别处时，那是梦，是艺术，是诗，而当别处一旦变为此处，崇高感随即便变为生活的另一面：残酷。