'\" t
.\" Title: \fBmysqldbcompare\fR
.\" Author: [FIXME: author] [see http://docbook.sf.net/el/author]
.\" Generator: DocBook XSL Stylesheets v1.79.1
.\" Date: 08/01/2016
.\" Manual: MySQL Utilities
.\" Source: MySQL 1.6.3
.\" Language: English
.\"
.TH "\FBMYSQLDBCOMPARE\FR" "1" "08/01/2016" "MySQL 1\&.6\&.3" "MySQL Utilities"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
mysqldbcompare \- Compare Two Databases and Identify Differences
.SH "SYNOPSIS"
.HP \w'\fBmysqldbcompare\ [\fR\fB\fIoptions\fR\fR\fB]\ \fR\fB\fIdb1\fR\fR\fB\ [\fR\fB\fI:db2\fR\fR\fB]\ \&.\&.\&.\fR\ 'u
\fBmysqldbcompare [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb1\fR\fR\fB [\fR\fB\fI:db2\fR\fR\fB] \&.\&.\&.\fR
.SH "DESCRIPTION"
.PP
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\&.
.PP
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\&.
.PP
The comparison may be run against two databases of different names on a single server by specifying only the
\fB\-\-server1\fR
option\&. The user can also connect to another server by specifying the
\fB\-\-server2\fR
option\&. In this case, db1 is taken from server1 and db2 from server2\&.
.PP
All databases between two servers can also be compared using the
\fB\-\-all\fR
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
\fB\-\-server1\fR
and
\fB\-\-server2\fR
options are required\&. Users can skip the comparison of some of the databases using the
\fB\-\-exclude\fR
option\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
.PP
The data must not be changed during the comparison\&. Unexpected errors may occur if data is changed during the comparison\&.
.sp .5v
.RE
.PP
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
\fB\-vv\fR
option\&.
.PP
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
\fB\-\-run\-all\-tests\fR
option to cause the utility to run all tests regardless of their end state\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
.PP
Using
\fB\-\-run\-all\-tests\fR
may produce expected cascade failures\&. For example, if the row counts differ among two tables being compared, the data consistency will also fail\&.
.sp .5v
.RE
.PP
The tests include the following:
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP " 1." 4.2
.\}
Check database definitions
.sp
A database existence precondition check ensures that both databases exist\&. If they do not, no further processing is possible and the
\fB\-\-run\-all\-tests\fR
option is ignored\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP " 2." 4.2
.\}
Check existence of objects in both databases
.sp
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
\fB\-\-skip\-object\-compare\fR
option\&. That can be useful when there are known missing objects among the databases\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP " 3." 4.2
.\}
Compare object definitions
.sp
The definitions (the
\fBCREATE\fR
statements) are compared and differences are presented\&. To skip this test, use the
\fB\-\-skip\-diff\fR
option\&. That can be useful when there are object name differences only that you want to ignore\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 4.\h'+01'\c
.\}
.el \{\
.sp -1
.IP " 4." 4.2
.\}
Check table row counts
.sp
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
\fB\-\-skip\-row\-count\fR
option\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 5.\h'+01'\c
.\}
.el \{\
.sp -1
.IP " 5." 4.2
.\}
Check table data consistency
.sp
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 (\fBGRID\fR
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
\fB\-\-skip\-checksum\-table\fR
option\&. To skip this full test, use the
\fB\-\-skip\-data\-check\fR
option\&.
.RE
.PP
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\&.
.PP
Each test completes with one of the following states:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBpass\fR
.sp
The test succeeded\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBFAIL\fR
.sp
The test failed\&. Errors are displayed following the test state line\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBSKIP\fR
.sp
The test was skipped due to a missing prerequisite or a skip option\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBWARN\fR
.sp
The test encountered an unusual but not fatal error\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fB\-\fR
.sp
The test is not applicable to this object\&.
.RE
.PP
To specify how to display diff\-style output, use one of the following values with the
\fB\-\-difftype\fR
option:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBunified\fR
(default)
.sp
Display unified format output\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBcontext\fR
.sp
Display context format output\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBdiffer\fR
.sp
Display differ\-style format output\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBsql\fR
.sp
Display SQL transformation statement output\&.
.RE
.PP
To specify how to display output for changed or missing rows, use one of the following values with the
\fB\-\-format\fR
option:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBgrid\fR
(default)
.sp
Display output in grid or table format like that of the
\fBmysql\fR
client command\-line tool\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBcsv\fR
.sp
Display output in comma\-separated values format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBtab\fR
.sp
Display output in tab\-separated format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBvertical\fR
.sp
Display output in single\-column format like that of the
\eG
command for the
\fBmysql\fR
client command\-line tool\&.
.RE
.PP
The
\fB\-\-changes\-for\fR
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
\fB\-\-difftype=sql\fR
option)\&. Consider the following command:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcompare \-\-server1=root@host1 \-\-server2=root@host2 \-\-difftype=sql db1:dbx\fR
.fi
.if n \{\
.RE
.\}
.PP
The leftmost database (db1) exists on the server designated by the
\fB\-\-server1\fR
option (host1)\&. The rightmost database (dbx) exists on the server designated by the
\fB\-\-server2\fR
option (host2)\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fB\-\-changes\-for=server1\fR: Produce output that shows how to make the definitions of objects on
server1
like the definitions of the corresponding objects on
server2\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fB\-\-changes\-for=server2\fR: Produce output that shows how to make the definitions of objects on
server2
like the definitions of the corresponding objects on
server1\&.
.RE
.PP
The default direction is
server1\&.
.PP
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\&.
.PP
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
\fB\-\-disable\-binary\-logging\fR
option\&.
OPTIONS.PP
\fBmysqldbcompare\fR
accepts the following command\-line options:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-all, \-a
.sp
Compare all database in common (with the same name) between two servers\&.
.sp
The
\fB\-\-all\fR
option ignores the following databases:
\fIINFORMATION_SCHEMA\fR,
\fIPERFORMANCE_SCHEMA\fR,
\fImysql\fR, and
\fIsys\fR\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
The
\fIsys\fR
database is ignored as of Utilities 1\&.6\&.2\&.
.sp .5v
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-help
.sp
Display a help message and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-license
.sp
Display license information and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-changes\-for=
.sp
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
\fB\-\-changes\-for=server1\fR\&. Permitted values are
\fBserver1\fR
and
\fBserver2\fR\&. The default is
\fBserver1\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-character\-set=
.sp
Sets the client character set\&. The default is retrieved from the server variable
character_set_client\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-difftype=, \-d
.sp
Specify the difference display format\&. Permitted format values are
\fBunified\fR,
\fBcontext\fR,
\fBdiffer\fR, and
\fBsql\fR\&. The default is
\fBunified\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-disable\-binary\-logging
.sp
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
\fBSUPER\fR
privilege\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-exclude=, \-x
.sp
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
\fBLIKE\fR\&. With the
\fB\-\-regexp\fR
option, patterns use regular expressions for matching names\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
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
\fB\-\-regexp\fR
option, a warning is presented to suggest the user check the pattern for possible use with the
\fB\-\-regexp\fR
option\&.
.sp .5v
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-format=, \-f
.sp
Specify the display format for changed or missing rows\&. Permitted format values are
\fBgrid\fR,
\fBcsv\fR,
\fBtab\fR, and
\fBvertical\fR\&. The default is
\fBgrid\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-compact
.sp
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\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-quiet, \-q
.sp
Do not print anything\&. Return only an exit code of success or failure\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-regexp, \-\-basic\-regexp, \-G
.sp
Perform pattern matches using the
\fBREGEXP\fR
operator\&. The default is to use
\fBLIKE\fR
for matching\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-run\-all\-tests, \-t
.sp
Do not halt at the first difference found\&. Process all objects\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-server1=
.sp
Connection information for the first server\&.
.sp
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\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example : <\fIlogin\-path\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example : <\fIconfiguration\-file\-path\fR>[:<\fIsection\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example : <\fIuser\fR>[:<\fIpasswd\fR>]@<\fIhost\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-server2=
.sp
Connection information for the second server\&.
.sp
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\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example : <\fIlogin\-path\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example : <\fIconfiguration\-file\-path\fR>[:<\fIsection\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example : <\fIuser\fR>[:<\fIpasswd\fR>]@<\fIhost\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-show\-reverse
.sp
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\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-checksum\-table
.sp
Skip the CHECKSUM TABLE step in the data consistency check\&. Added in release\-1\&.4\&.3\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-data\-check
.sp
Skip the data consistency check\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-diff
.sp
Skip the object definition difference check\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-object\-compare
.sp
Skip the object comparison check\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-row\-count
.sp
Skip the row count check\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-span\-key\-size=
.sp
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\&.
.sp
Default value is 8\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-ca
.sp
The path to a file that contains a list of trusted SSL CAs\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL certificate file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL key file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl
.sp
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)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-verbose, \-v
.sp
Specify how much information to display\&. Use this option multiple times to increase the amount of information\&. For example,
\fB\-v\fR
= verbose,
\fB\-vv\fR
= more verbose,
\fB\-vvv\fR
= debug\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-version
.sp
Display version information and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-use\-indexes
.sp
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="\&.[;\&.;]"
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-width=
.sp
Change the display width of the test report\&. The default is 75 characters\&.
.RE
NOTES.PP
The login user must have the appropriate permissions to read all databases and tables listed\&.
.PP
For the
\fB\-\-difftype\fR
option, the permitted values are not case sensitive\&. In addition, values may be specified as any unambiguous prefix of a valid value\&. For example,
\fB\-\-difftype=d\fR
specifies the differ type\&. An error occurs if a prefix matches more than one valid value\&.
.PP
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
\fBmy_print_defaults\fR
tools which is required to read the login\-path values from the login configuration file (\&.mylogin\&.cnf)\&.
.PP
If any database identifier specified as an argument contains special characters or is a reserved word, then it must be appropriately quoted with backticks (\fB`\fR)\&. In turn, names quoted with backticks must also be quoted with single or double quotes depending on the operating system, i\&.e\&. (\fB"\fR) in Windows or (\fB\*(Aq\fR) 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
\fBweird`db\&.name\fR
with
\fBother:weird`db\&.name\fR, the database pair must be specified using the following syntax (in non\-Windows):
\fB\*(Aq`weird``db\&.name`:`other:weird``db\&.name`\*(Aq\fR\&.
.PP
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:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcompare \-\-server1=root@localhost emp1:emp2 \-\-run\-all\-tests\fR
# 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
.fi
.if n \{\
.RE
.\}
.PP
Given: two databases with the same table layout\&. Data for each table contains:
.sp
.if n \{\
.RS 4
.\}
.nf
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)
.fi
.if n \{\
.RE
.\}
.PP
To generate the SQL statements for data transformations to make
db1\&.t1
the same as
db2\&.t1, use the
\fB\-\-changes\-for=server1\fR
option\&. We must also include the
\fB\-a\fR
option to ensure that the data consistency test is run\&. The following command illustrates the options used and an excerpt from the results generated:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcompare \-\-server1=root:root@localhost \e\fR
\fB\-\-server2=root:root@localhost db1:db2 \-\-changes\-for=server1 \-a \e/\fR
\fB\-\-difftype=sql\fR
[\&.\&.\&.]
# 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 = \*(AqTest 123\*(Aq WHERE a = \*(Aq1\*(Aq;
UPDATE db1\&.t1 SET b = \*(AqTest 789\*(Aq WHERE a = \*(Aq3\*(Aq;
DELETE FROM db1\&.t1 WHERE a = \*(Aq4\*(Aq;
INSERT INTO db1\&.t1 (a, b) VALUES(\*(Aq5\*(Aq, \*(AqNew row \- db2\*(Aq);
# Database consistency check failed\&.
#
# \&.\&.\&.done
.fi
.if n \{\
.RE
.\}
.PP
Similarly, when the same command is run with
\fB\-\-changes\-for=server2\fR
and
\fB\-\-difftype=sql\fR, the following report is generated:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcompare \-\-server1=root:root@localhost \e\fR
\fB\-\-server2=root:root@localhost db1:db2 \-\-changes\-for=server2 \-a \e\fR
\fB\-\-difftype=sql\fR
[\&.\&.\&.]
# 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 = \*(AqTest 789\*(Aq WHERE a = \*(Aq1\*(Aq;
UPDATE db2\&.t1 SET b = \*(AqTest 123\*(Aq WHERE a = \*(Aq3\*(Aq;
DELETE FROM db2\&.t1 WHERE a = \*(Aq5\*(Aq;
INSERT INTO db2\&.t1 (a, b) VALUES(\*(Aq4\*(Aq, \*(AqNew row \- db1\*(Aq);
# Database consistency check failed\&.
#
# \&.\&.\&.done
.fi
.if n \{\
.RE
.\}
.PP
With the
\fB\-\-difftype=sql\fR
SQL generation option set,
\fB\-\-show\-reverse\fR
shows the object transformations in both directions\&. Here is an excerpt of the results:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbcompare \-\-server1=root:root@localhost \e\fR
\fB\-\-server2=root:root@localhost db1:db2 \-\-changes\-for=server1 \e\fR
\fB\-\-show\-reverse \-a \-\-difftype=sql\fR
[\&.\&.\&.]
# 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 = \*(AqTest 123\*(Aq WHERE a = \*(Aq1\*(Aq;
UPDATE db1\&.t1 SET b = \*(AqTest 789\*(Aq WHERE a = \*(Aq3\*(Aq;
DELETE FROM db1\&.t1 WHERE a = \*(Aq4\*(Aq;
INSERT INTO db1\&.t1 (a, b) VALUES(\*(Aq5\*(Aq, \*(AqNew row \- db2\*(Aq);
#
# Transformation for reverse changes (\-\-changes\-for=server2):
#
# # Data differences found among rows:
# UPDATE db2\&.t1 SET b = \*(AqTest 789\*(Aq WHERE a = \*(Aq1\*(Aq;
# UPDATE db2\&.t1 SET b = \*(AqTest 123\*(Aq WHERE a = \*(Aq3\*(Aq;
# DELETE FROM db2\&.t1 WHERE a = \*(Aq5\*(Aq;
# INSERT INTO db2\&.t1 (a, b) VALUES(\*(Aq4\*(Aq, \*(AqNew row \- db1\*(Aq);
# Database consistency check failed\&.
#
# \&.\&.\&.done
.fi
.if n \{\
.RE
.\}
.sp
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
\fB\-\-disable\-binary\-logging\fR
option is used, the user must also have the SUPER privilege\&.
.SH "COPYRIGHT"
.br
.PP
Copyright \(co 2006, 2016, Oracle and/or its affiliates. All rights reserved.
.PP
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.
.PP
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.
.PP
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/.
.sp
.SH "SEE ALSO"
For more information, please refer to the MySQL Utilities and Fabric
documentation, which is available online at
http://dev.mysql.com/doc/index-utils-fabric.html
.SH AUTHOR
Oracle Corporation (http://dev.mysql.com/).