'\" 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=