mysql_compare_dbs

Overview

Important function which the MySQL provides is the comparing of two databases. The utility which is used to achieve this is called: mysqldbcompare and can be found in the mysql utilities package:

The utility is rather simple and can be used as follows:

The utility can perform three checks:

  • Definition Check
  • Row Count
  • Data Check

And it is used as follows:

PS C:\Program Files\MySQL\MySQL Utilities 1.6> mysqldbcompare.exe --server1='<username>':'<password>'@<IP>:<port> database1:database2 --run-all-tests --skip-data-check --format=vertical

The output is also rather simple with comparing the: Definition, Row Count and/or Data content check:

#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    pass    pass    SKIP
# TABLE     TABLE_NAMES							    FAIL    FAIL    SKIP <- In case we have a fail, the utility will show us what is the difference

In case the utility spots a difference, it will display the difference.

# Object definitions differ. (--changes-for=server1)
#

--- `<database1>`.`<table_name1>`
+++ `<database2>`.`<table_name1>`
@@ -2,4 +2,4 @@
   `id` int(6) NOT NULL AUTO_INCREMENT,
   `report` mediumtext,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=1583 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=1627 DEFAULT CHARSET=latin1 <- In this case the AUTO_INCREMENT is different
#

  • mysql_compare_dbs.txt
  • Last modified: 2019/10/18 20:04
  • by 127.0.0.1