Syncs two mysql databases by only inserting/deleting/updating the necessary records.
Switch branches/tags
Nothing to show
Clone or download
ixnewton
Latest commit 6656da2 Aug 6, 2014
Permalink
Failed to load latest commit information.
bin Fix UTF8 issue for json_encode() - update Aug 6, 2014
.gitignore first draft Nov 20, 2012
README.md Add json|verbose output - update Aug 5, 2014

README.md

mysql-sync

Syncs two mysql databases by only inserting/deleting/updating the necessary records.

Useful in cases where mysqldump generates gigabytes of data

##Usage:

mysql-sync [source-dsn] [target-dsn] [output-option]

dsn being username:password@hostname:port/database_name

output-option verbose outputs all output of INSERTs UPDATEs etc, json provides JSON encoded output for a return value to another script otherwise a minimal table update output is the resulting output

Example: mysql-sync root:password@localhost:port/source_db root:password@localhost:port/dest_db json

If the remote mysql port is not open, tunnel it via SSH i.e ssh -L 3308:localhost:3306 -o TCPKeepAlive=no -o ServerAliveInterval=15 user@host then mysql-sync root:password@localhost:3308/voeb root:password@localhost:3306/v verbose

What the script does

  • lists all tables on the remote and local host
  • compares the checksum of each mysql table
  • syncs the tables where checksum is invalid
  • verifies the sync was successful via another checksum

The databases must have the same schema. Therefore, the script will stop if

  • the number of tables is not similar
  • the structure of a table is not similar i.e describe my_table