MySQL Utilities

3.2.5 How do you synchronize a table on two servers where neither is up-to-date?

When working with servers that are used in different networks or are compartmentalized, or simply intentionally manually redundant (they do not use replication), or perhaps through some crisis, you may encounter a situation where a table (or an entire database) diverge.

We don't simply want to know which rows differ, rather, we need to know the SQL statements needed to bring the tables into synchronization. Furthermore, we aren't sure which table is most out of date so we'd like to see the transformation statements for both directions.

In this case, it would be very helpful to know exactly how the tables differ. For this, we use the mysqldbcompare utility.


The goal is to generate the SQL transformation statements to synchronize the tables.

Example Execution

shell> mysqldbcompare --server1=root:root@localhost:13001 --server2=root:root@localhost:13002 \
          menagerie -a --difftype=SQL --show-reverse --quiet
# Checking databases menagerie on server1 and menagerie on server2

# Row counts are not the same among `menagerie`.`pet` and `menagerie`.`pet`.
# Transformation for --changes-for=server1:

DELETE FROM `menagerie`.`pet` WHERE `pet_num` = '10';
DELETE FROM `menagerie`.`pet` WHERE `pet_num` = '12';
INSERT INTO `menagerie`.`pet` (`pet_num`, `name`, `owner`, `species`, `sex`, `birth`, `death`)
  VALUES('11', 'Violet', 'Annette', 'dog', 'f', '2010-10-20', NULL);

# Transformation for reverse changes (--changes-for=server2):
# DELETE FROM `menagerie`.`pet` WHERE `pet_num` = '11';
# INSERT INTO `menagerie`.`pet` (`pet_num`, `name`, `owner`, `species`, `sex`, `birth`, `death`)
#   VALUES('10', 'JonJon', 'Annette', 'dog', 'm', '2010-10-20', '2012-07-01');
# INSERT INTO `menagerie`.`pet` (`pet_num`, `name`, `owner`, `species`, `sex`, `birth`, `death`)
#   VALUES('12', 'Charlie', 'Annette', 'dog', 'f', '2010-10-20', NULL);


In the example above, we connected to two servers and compare the database named menagerie. We enabled the transformation statements using a combination of options as follows.

The --difftype=SQL option instructs the utility to generate the SQL statements.

The --show-reverse option instructs the utility to generate the differences in both direction. That is, from the perspective of server1 as compared to server2 and server2 as compared to server1. By convention, the second set is commented out should you wish to pipe the output to a consumer.

Lastly, the --quiet option simply turns off the verbosity of print statements that normally occur for communicating progress.

Permissions Required

The user must have the SELECT privilege for the databases on both servers.

Tips and Tricks

You can change the direction using the --changes-for option. For example, '--changes-for=server1' is the default direction and '--changes-for=server2' is the reverse. In the second case, the --show-reverse displays the perspective of server1 commented out for convenience and to make it easier to determine which is the alternative direction.