Ubuntu Manpage: mysqldbcompare - Compare Two Databases and Identify Differences

Provided by: mysql-utilities_1.6.4-1_all

NAME

       mysqldbcompare - Compare Two Databases and Identify Differences

SYNOPSIS

       mysqldbcompare [options] db1 [:db2] ...

DESCRIPTION

This utility compares the objects and data from two databases to find differences. It
identifies objects having different definitions in the two databases and presents them in
a diff-style format of choice. Differences in the data are shown using a similar
diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV,
TAB, or VERTICAL.

Use the notation db1:db2 to name two databases to compare, or, alternatively just db1 to
compare two databases with the same name. The latter case is a convenience notation for
comparing same-named databases on different servers.

The comparison may be run against two databases of different names on a single server by
specifying only the --server1 option. The user can also connect to another server by
specifying the --server2 option. In this case, db1 is taken from server1 and db2 from
server2.

All databases between two servers can also be compared using the --all option. In this
case, only the databases in common (with the same name) between the servers are
successively compared. Therefore, no databases need to be specified but the --server1 and
--server2 options are required. Users can skip the comparison of some of the databases
using the --exclude option.

Note
The data must not be changed during the comparison. Unexpected errors may occur if
data is changed during the comparison.

The objects considered in the database include tables, views, triggers, procedures,
functions, and events. A count for each object type can be shown with the -vv option.

The check is performed using a series of steps called tests. By default, the utility stops
on the first failed test, but you can specify the --run-all-tests option to cause the
utility to run all tests regardless of their end state.

Note
Using --run-all-tests may produce expected cascade failures. For example, if the row
counts differ among two tables being compared, the data consistency will also fail.

The tests include the following:

1. Check database definitions

A database existence precondition check ensures that both databases exist. If they do
not, no further processing is possible and the --run-all-tests option is ignored.

2. Check existence of objects in both databases

The test for objects in both databases identifies those objects missing from one or
another database. The remaining tests apply only to those objects that appear in both
databases. To skip this test, use the --skip-object-compare option. That can be useful
when there are known missing objects among the databases.

3. Compare object definitions

The definitions (the CREATE statements) are compared and differences are presented. To
skip this test, use the --skip-diff option. That can be useful when there are object
name differences only that you want to ignore.

4. Check table row counts

This check ensures that both tables have the same number of rows. This does not ensure
that the table data is consistent. It is merely a cursory check to indicate possible
missing rows in one table or the other. The data consistency check identifies the
missing rows. To skip this test, use the --skip-row-count option.

5. Check table data consistency

This check identifies both changed rows as well as missing rows from one or another of
the tables in the databases. Changed rows are displayed as a diff-style report with
the format chosen (GRID by default) and missing rows are also displayed using the
format chosen. This check is divided in two steps: first the full table checksum is
compared between the tables, then if this step fails (or is skipped) the algorithm to
find rows differences is executed. To skip the preliminary checksum table step in this
test, use the --skip-checksum-table option. To skip this full test, use the
--skip-data-check option.

You may want to use the --skip-xxx options to run only one of the tests. This might be
helpful when working to bring two databases into synchronization, to avoid running all of
the tests repeatedly during the process.

Each test completes with one of the following states:

• pass

The test succeeded.

• FAIL

The test failed. Errors are displayed following the test state line.

• SKIP

The test was skipped due to a missing prerequisite or a skip option.

• WARN

The test encountered an unusual but not fatal error.

• -

The test is not applicable to this object.

To specify how to display diff-style output, use one of the following values with the
--difftype option:

• unified (default)

Display unified format output.

• context

Display context format output.

• differ

Display differ-style format output.

• sql

Display SQL transformation statement output.

To specify how to display output for changed or missing rows, use one of the following
values with the --format option:

• grid (default)

Display output in grid or table format like that of the mysql client command-line
tool.

• csv

Display output in comma-separated values format.

• tab

Display output in tab-separated format.

• vertical

Display output in single-column format like that of the \G command for the mysql
client command-line tool.

The --changes-for option controls the direction of the difference (by specifying the
object to be transformed) in either the difference report (default) or the transformation
report (designated with the --difftype=sql option). Consider the following command:

shell> mysqldbcompare --server1=root@host1 --server2=root@host2 --difftype=sql db1:dbx

The leftmost database (db1) exists on the server designated by the --server1 option
(host1). The rightmost database (dbx) exists on the server designated by the --server2
option (host2).

• --changes-for=server1: Produce output that shows how to make the definitions of
objects on server1 like the definitions of the corresponding objects on server2.

• --changes-for=server2: Produce output that shows how to make the definitions of
objects on server2 like the definitions of the corresponding objects on server1.

The default direction is server1.

You must provide connection parameters (user, host, password, and so forth) for an account
that has the appropriate privileges to access all objects in the operation.

If the utility is to be run on a server that has binary logging enabled, and you do not
want the comparison steps logged, use the --disable-binary-logging option. OPTIONS.PP
mysqldbcompare accepts the following command-line options:

• --all, -a

Compare all database in common (with the same name) between two servers.

The --all option ignores the following databases: INFORMATION_SCHEMA,
PERFORMANCE_SCHEMA, mysql, and sys.

Note
The sys database is ignored as of Utilities 1.6.2.

• --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>, -d<difftype>

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

• --disable-binary-logging

If binary logging is enabled, disable it during the operation to prevent comparison
operations from being written to the binary log. Note: Disabling binary logging
requires the SUPER privilege.

• --exclude=<exclude>, -x<exclude>

Exclude one or more databases from the operation using either a specific name such as
db1 or a search pattern. Use this option multiple times to specify multiple
exclusions. By default, patterns use database patterns such as LIKE. With the --regexp
option, patterns use regular expressions for matching names.

Note
The utility will attempt to determine if the pattern supplied has any special
characters (such as an asterisks), which may indicate that the pattern could be a
REGEXP pattern. If there are special, non-SQL LIKE pattern characters and the user
has not specified the --regexp option, a warning is presented to suggest the user
check the pattern for possible use with the --regexp option.

• --format=<format>, -f<format>

Specify the display format for changed or missing rows. Permitted format values are
grid, csv, tab, and vertical. The default is grid.

• --compact

Compacts the output by reducing the number of 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. It is most effective when used with the unified difference
type and the grid format.

• --quiet, -q

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

• --regexp, --basic-regexp, -G

Perform pattern matches using the REGEXP operator. The default is to use LIKE for
matching.

• --run-all-tests, -t

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

• --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.

• 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-checksum-table

Skip the CHECKSUM TABLE step in the data consistency check. Added in release-1.4.3.

• --skip-data-check

Skip the data consistency check.

• --skip-diff

Skip the object definition difference check.

• --skip-object-compare

Skip the object comparison check.

• --skip-row-count

Skip the row count check.

• --span-key-size=<number of bytes to use for key>

Change the size of the key used for compare table contents. A higher value can help to
get more accurate results comparing large databases, but may slow the algorithm.

Default value is 8.

• --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-cert

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.

• --use-indexes

List the index to use. Use this option to select the index to use if the table has no
primary key or it has more than one unique index without null columns. Use this option
in the format: --use-indexes="<table1>.<indexA>[;<table2>.<indexB>;]"

• --width=<number>

Change the display width of the test report. The default is 75 characters.
NOTES.PP The login user must have the appropriate permissions to read all databases and
tables listed.

For the --difftype option, the permitted values are not case sensitive. In addition,
values may be specified as any unambiguous prefix of a valid value. For example,
--difftype=d specifies the differ type. An error occurs if a prefix matches more than one
valid value.

The path to the MySQL client tools should be included in the PATH environment variable in
order to use the authentication mechanism with login-paths. This will allow the utility to
use the my_print_defaults tools which is required to read the login-path values from the
login configuration file (.mylogin.cnf).

If any database identifier specified as an argument contains special characters or is a
reserved word, then it must be appropriately quoted with backticks (`). In turn, names
quoted with backticks must also be quoted with single or double quotes depending on the
operating system, i.e. (") in Windows or (') in non-Windows systems, in order for the
utilities to read backtick quoted identifiers as a single argument. For example, to
compare a database with the name weird`db.name with other:weird`db.name, the database pair
must be specified using the following syntax (in non-Windows):
'`weird``db.name`:`other:weird``db.name`'.

When comparing two databases of different names, the utility will suppress differences in
the CREATE DATABASE statements when only the names differ. If any of the decorators
differ, the differences in the statements will be shown. EXAMPLES.PP Use the following
command to compare the emp1 and emp2 databases on the local server, and run all tests even
if earlier tests fail:

shell> mysqldbcompare --server1=root@localhost emp1:emp2 --run-all-tests
# server1 on localhost: ... connected.
# Checking databases emp1 on server1 and emp2 on server2
#
# WARNING: Objects in server2:emp2 but not in server1:emp1:
# TRIGGER: trg
# PROCEDURE: p1
# TABLE: t1
# VIEW: v1
#
# Defn Row Data
# Type Object Name Diff Count Check
# ---------------------------------------------------------------------------
# FUNCTION f1 pass - -
# TABLE departments pass pass -
# - Compare table checksum FAIL
# - Find row differences FAIL
#
# Data differences found among rows:
--- emp1.departments
+++ emp2.departments
@@ -1,4 +1,4 @@
************************* 1. row *************************
dept_no: d002
- dept_name: dunno
+ dept_name: Finance
1 rows.
# Rows in emp1.departments not in emp2.departments
************************* 1. row *************************
dept_no: d008
dept_name: Research
1 rows.
# Rows in emp2.departments not in emp1.departments
************************* 1. row *************************
dept_no: d100
dept_name: stupid
1 rows.
# TABLE dept_manager pass pass -
# - Compare table checksum pass
# Database consistency check failed.
#
# ...done

Given: two databases with the same table layout. Data for each table contains:

mysql> select * from db1.t1;
+---+---------------+
| a | b |
+---+---------------+
| 1 | Test 789 |
| 2 | Test 456 |
| 3 | Test 123 |
| 4 | New row - db1 |
+---+---------------+
4 rows in set (0.00 sec)
mysql> select * from db2.t1;
+---+---------------+
| a | b |
+---+---------------+
| 1 | Test 123 |
| 2 | Test 456 |
| 3 | Test 789 |
| 5 | New row - db2 |
+---+---------------+
4 rows in set (0.00 sec)

To generate the SQL statements for data transformations to make db1.t1 the same as db2.t1,
use the --changes-for=server1 option. We must also include the -a option to ensure that
the data consistency test is run. The following command illustrates the options used and
an excerpt from the results generated:

shell> mysqldbcompare --server1=root:root@localhost \
--server2=root:root@localhost db1:db2 --changes-for=server1 -a \/
--difftype=sql
[...]
# Defn Row Data
# Type Object Name Diff Count Check
#-------------------------------------------------------------------------
# TABLE t1 pass pass -
# - Compare table checksum FAIL
# - Find row differences FAIL
#
# Transformation for --changes-for=server1:
#
# Data differences found among rows:
UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1';
UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3';
DELETE FROM db1.t1 WHERE a = '4';
INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2');
# Database consistency check failed.
#
# ...done

Similarly, when the same command is run with --changes-for=server2 and --difftype=sql, the
following report is generated:

shell> mysqldbcompare --server1=root:root@localhost \
--server2=root:root@localhost db1:db2 --changes-for=server2 -a \
--difftype=sql
[...]
# Defn Row Data
# Type Object Name Diff Count Check
#-------------------------------------------------------------------------
# TABLE t1 pass pass -
# - Compare table checksum FAIL
# - Find row differences FAIL
#
# Transformation for --changes-for=server2:
#
# Data differences found among rows:
UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1';
UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3';
DELETE FROM db2.t1 WHERE a = '5';
INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1');
# Database consistency check failed.
#
# ...done

With the --difftype=sql SQL generation option set, --show-reverse shows the object
transformations in both directions. Here is an excerpt of the results:

shell> mysqldbcompare --server1=root:root@localhost \
--server2=root:root@localhost db1:db2 --changes-for=server1 \
--show-reverse -a --difftype=sql
[...]
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE t1 pass pass -
# - Compare table checksum FAIL
# - Find row differences FAIL
#
# Transformation for --changes-for=server1:
#
# Data differences found among rows:
UPDATE db1.t1 SET b = 'Test 123' WHERE a = '1';
UPDATE db1.t1 SET b = 'Test 789' WHERE a = '3';
DELETE FROM db1.t1 WHERE a = '4';
INSERT INTO db1.t1 (a, b) VALUES('5', 'New row - db2');
#
# Transformation for reverse changes (--changes-for=server2):
#
# # Data differences found among rows:
# UPDATE db2.t1 SET b = 'Test 789' WHERE a = '1';
# UPDATE db2.t1 SET b = 'Test 123' WHERE a = '3';
# DELETE FROM db2.t1 WHERE a = '5';
# INSERT INTO db2.t1 (a, b) VALUES('4', 'New row - db1');
# Database consistency check failed.
#
# ...done

LIMITATIONS.PP The utility reads the primary key of each row into a data structure, which
is then used to generate checksums for each row. The primary key and checksum are then
sorted and compared to detect which rows differ. Due to this design, the utility may
exhibit slower performance for very large tables (many rows) especially for tables with
wide primary keys. Use of this utility with tables that have blob fields as part of the
primary key is not recommended. PERMISSIONS REQUIRED.PP The user must have the SELECT,
CREATE TEMPORARY TABLES and INSERT privileges for the databases being compared on both
connections. The user must also have SELECT privilege on the mysql database. If the binary
log is enabled and the --disable-binary-logging option is used, the user must also have
the SUPER privilege.

COPYRIGHT

       Copyright © 2006, 2016, Oracle and/or its affiliates. All rights reserved.

       This documentation is free software; you can redistribute it and/or modify it only under
       the terms of the GNU General Public License as published by the Free Software Foundation;
       version 2 of the License.

       This documentation is distributed in the hope that it will be useful, but WITHOUT ANY
       WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
       PURPOSE. See the GNU General Public License for more details.

       You should have received a copy of the GNU General Public License along with the program;
       if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
       Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.

AUTHOR

       Oracle Corporation (http://dev.mysql.com/).

NAME

SYNOPSIS

DESCRIPTION

COPYRIGHT

SEE ALSO

AUTHOR