, , ,

Note: this applies to build 2442 of the Maatkit tools the syntax has changed slightly in newer versions.

If your MySQL master slave set up has got out of sync you’ll need to match up the data again. One way could be to re-copy your master database to your slave but this would be very inefficient with a large database. Mk-table-sync from the maatkit package allows you to do an efficient re-sync of databases.

Running the command:

mk-table-sync --execute --replicate test.checksum --askpass u=root,h=,D=exampledb --synctomaster

Will sync up a slave database with data held on the master.

–execute will execute the commands to re-sync the databases. Another option is to use –print, which will  print out the commands that mk-table-sync will use to sync without making any changes.

–replicate specifies the table containing the output from mk-table-checksum which it uses to find the areas of the database that are out of sync

–askpass will prompt for the users password before running

u=root,h=,D=exampledb specifies the (u) username, (h) host to log into and the (D) database to sync. The host specified here is the slave server.

–synctomaster will sync up the slave node with data from the master. The synctomaster option specifies that the connection parameters (above) are those of the slave and that the host information will be gathered from the show slave status output.

The above command should sync up an out of sync master slave replication pair and leave replication intact. Master-Master replication should be treated with caution and you should consult the documentation further. As ever read up on what this command does and don’t use it in production until you are confident in what it will do as it has the potential to cause some major damage.