MYSQLDBCOMPARE(1)						  MySQL Utilities						 MYSQLDBCOMPARE(1)

NAME

       mysqldbcompare - check two databases and identify any 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.

       Those  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.
	  To skip this 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 monitor.

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

       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:

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

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

       o --changes-for=server2: Produce output that shows how to make the definitions of objects on server2 like the  definitions  of  the  corre-
	 sponding 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

       mysqldbcompare accepts the following command-line options:

       --help Display a help message 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.

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

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

       --quiet, -q
	      Do not print anything. Return only an exit code of success or failure.

       --run-all-tests, -a
	      Do not halt at the first difference found. Process all objects.

       --server1=<source>
	      Connection information for the first server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format.

       --server2=<source>
	      Connection information for the second server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format.

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

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

       --width=<number>
	      Change the display width of the test report.  The default is 75 characters.

NOTES

       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.

EXAMPLES

       Use the following command to compare the emp1 and emp2 databases on the local server, and run all tests even if earlier tests fail:

       $ 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    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    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:

       $ 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    FAIL
       # # Data transformations for direction = 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:

       $ 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    FAIL
       # # Data transformations for direction = 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');

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

       $ 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    FAIL
       # # Data transformations for direction = 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');

       # Data transformations for direction = 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

COPYRIGHT

       Copyright (c) 2011, 2012, Oracle and/or its affiliates. All rights reserved.

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

       This  program  is  distributed  in  the	hope  that  it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MER-
       CHANTABILITY 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 this program; if not, write to the Free  Software  Foundation,
       Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

AUTHOR

       MySQL Utilities Team

COPYRIGHT

       2010, Oracle and/or its affiliates. All rights reserved.

1.0.3								   May 09, 2012 						 MYSQLDBCOMPARE(1)