mysqldbcompare utility commands in MySQL
mysqldbcompare compare databases for consistency
Usage: mysqldbcompare --server1=user:password@host:port:socket --server2=user:password@host:port:socket db1:db2
mysql db compare command-line utility, in MySQL, compares the objects and/or data from two MySQL databases to find differences, objects having different definitions and/or data in the two databases.
mysqldbcompare --version
mysqldbcompare --server1=host1 --server2=host2 proddb1:proddb2 --run-all-tests
mysqldbcompare --run-all-tests --server1=host1 --server2=host2 proddb:proddb --changes-for=server1
mysqldbcompare --run-all-tests --server1=host1 --server2=host2 proddb1:proddb2 --changes-for=server1 --disable-binary-logging
mysqldbcompare --run-all-tests --server1=root@host1 --server2=root@host2 proddb --changes-for=server1 --disable-binary-logging
mysqldbcompare --server1=host1 --server2=host2 proddb --run-all-tests --changes-for=server1 --disable-binary-logging -a --format=csv
mysqldbcompare --run-all-tests --server1=localhost --server2=remotehost $DATABASE:$DATABASE --changes-for=server2 --disable-binary-logging -a --difftype=context
mysqldbcompare --server1=root:password@localhost:3306 --server2=root:password@localhost:3306 testdb:devdb --run-all-tests --skip-data-check
mysqldbcompare --run-all-tests --server1=master_host --server2=slave_host testdb:testdb --changes-for=server1 --disable-binary-logging --skip-table-options --difftype=sql
mysqldbcompare --run-all-tests --server1=`hostname` --login-path=rhost --server2=dba_login_path --changes-for=server2 --disable-binary-logging --difftype=sql mysql_db_compare_log.log
mysqldbcompare --run-all-tests --skip-diff --server1=localhost --server2=rhost --changes-for=server2 --disable-binary-logging --difftype=differ devdb
mysqldbcompare --server1=${USER}:${PASS_WORD}@${HOST1}:3310 --server2=${USER}:${PASS_WORD}@${HOST2}:3310 ${DB}:${DB} --run-all-tests --changes-for=server2 --difftype=sql
mysqldbcompare -v --run-all-tests --server1=host1 --server2=host2 proddb:proddb --skip-object-compare -a --width=100
mysqldbcompare -vv --run-all-tests --server1=localhost --server2=remotehost proddb --skip-object-compare --changes-for=server2 -a -- skip-data-check
mysqldbcompare -v --run-all-tests --server1=host1 --server2=host2 proddb:devdb --skip-data-check --changes-for=server2 -a -- disable-binary-logging --skip-checksum-table
mysqldbcompare -vvv --run-all-tests --server1=master_host --server2=slave_host proddb:proddb --changes-for=server1 --disable-binary-logging -a --format=tab
mysqldbcompare -v --run-all-tests --server1=host1 --server2=host2 proddb:devdb --skip-data-check --changes-for=server2 -- disable-binary-logging --skip-row-count --skip-diff
mysqldbcompare --server1=root@host1 --server2=root@host2 --difftype=sql db1:db2 --width=120
mysqldbcompare --server1=root:root@localhost --server2=root:root@localhost db1:db2 --changes-for=server1 -a --difftype=sql
mysql db compare options:
--version show program's version number and exit
--help display a help message and exit
--license display program's license and exit
--server1=SERVER1 connection information for first server in the form:
user[:password]@host[:port][:socket] or login-path[:port][:socket].
--server2=SERVER2 connection information for second server in the form:
user[:password]@host[:port][:socket] or login-path[:port][:socket].
--character-set=CHARSET sets client character set. The default is retrieved from server variable 'character_set_client'.
-f FORMAT, --format=FORMAT display the output in either grid (default), tab, csv, or vertical format
--skip-checksum-table skip CHECKSUM TABLE step in data consistency check.
--skip-object-compare skip object comparison step.
--skip-row-count skip row count step.
--skip-diff skip the object diff step.
--skip-data-check skip data consistency check.
--skip-table-options skip check of all table options (e.g., AUTO_INCREMENT, ENGINE, CHARSET, etc.).
--width=WIDTH display width
-t, --run-all-tests do not abort when a diff test fails
-a, --all check all databases, -a option to ensure that the data consistency test is run.
-x EXCLUDE, --exclude=EXCLUDE exclude one or more databases from the operation using either a specific name (e.g. db1), a LIKE pattern (e.g. db%) or a REGEXP search pattern. To use a REGEXP search pattern for all exclusions, you must also specify the --regexp option. Repeat the --exclude option for multiple exclusions.
-c, --compact compact output from a diff.
--disable-binary-logging turn binary logging off during operation if enabled (SQL_LOG_BIN=1).
Note: may require SUPER privilege. Prevents compare operations from being written to the binary log.
--span-key-size=SPAN_KEY_SIZE changes the size of the key used for comparing table contents. A higher value can help to get more accurate results comparing large databases, but may slow the algorithm. Default value is 8.
--use-indexes=USE_INDEXES for each table, indicate which index to use as if were a primary key (each of his columns must not allow null values).
-v, --verbose control how much information is displayed. e.g., -v = verbose, -vv = more verbose, -vvv = debug
-q, --quiet turn off all messages for quiet execution.
-d DIFFTYPE, --difftype=DIFFTYPE display differences in context format in one of the following formats: [unified|context|differ|sql] (default: unified).
--changes-for=CHANGES_FOR specify the server to show transformations to match the other server.
For example, to see the transformation for transforming server1 to match server2, use --changes-for=server1. Valid values are 'server1' or 'server2'. The default is 'server1'.
--show-reverse produce a transformation report containing the SQL statements to transform 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.
-G, --basic-regexp, --regexp use 'REGEXP' operator to match the pattern. Default is to use 'LIKE'.
--ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL CAs.
--ssl-cert=SSL_CERT The name of the SSL certificate file to use for establishing a secure connection.
--ssl-key=SSL_KEY The name of the SSL key file to use for establishing a secure connection.
--ssl=SSL Specifies if the server connection requires the use of SSL. If an encrypted connection cannot be established, the connection attempt fails. By default 0 (SSL not required).
Related MySQL Articles: mysqlshow utility commands mysqlbinlog utility commands
