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