A simple script to compare 2 databases on different servers.

Pre-Req – install mysql workbench to pull in all dependencies and the mysqldbcompare scripts:

[html]
apt-get install mysql-workbench
[/html]

Now run the following command to run the comparison of the 2 databases:

[html]
mysqldbcompare –server1=databaseuser:password@127.0.0.1 –server2=databaseuser:password@10.10.10.10 server1db:server2db –changes-for=server2 -a –difftype=sql >> results.log
[/html]

The above compares the database ‘server1db’ against ‘server2db’ and outputs the results to results.log. Below is a sample of the log file:

[html]
# server1 on 127.0.0.1: … connected.
# server2 on 10.10.10.10: … connected.
# Checking databases server1db on server1 and server2db on server2
#

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
— server2db
+++ server1db
@@ -1 +1 @@
-CREATE DATABASE `server2db` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `server1db` /*!40100 DEFAULT CHARACTER SET latin1 */

# WARNING: Cannot generate SQL statements for these objects.
# Check the difference output for other discrepencies.
— server2db
+++ server1db
@@ -1 +1 @@
-CREATE DATABASE `server2db` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `server1db` /*!40100 DEFAULT CHARACTER SET latin1 */

# Defn Row Data
# Type Object Name Diff Count Check
# ————————————————————————-
# TABLE table1 pass pass pass
# TABLE table2 pass pass SKIP
#
No primary key found.
# TABLE table3 pass pass FAIL
#
# Transformation for –changes-for=server2:
#

# Data differences found among rows:
UPDATE server2db.table3 SET testid = ‘500’ WHERE id = ‘5’;

# Database consistency check failed.
#
# …done

[/html]

The above found that one of the rows in ‘table3’ on server1 differed from ‘table3’ on server2. It then outputs the updae statement needed to correct ‘table3’ on server1. i.e.

[html]
# Data differences found among rows:
UPDATE server2db.table3 SET testid = ‘500’ WHERE id = ‘5’;
[/html]

TROUBLESHOOTING

ERROR: Query failed. 1142: SELECT command denied to user ‘user’@’10.10.10.10’ for table ‘proc’
Failed to connect to DB

Written by Matt Cooper
Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@matthewc424.sg-host.com.