MySQL mysqldbcompare utility

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 --help
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