Copy Sync MySQL database with a single command

Posted under » MySQL on 07 July 2012

You can just copy SQLite files over via rsync but you can't do that with MySQL data because of the indexing.

The only sensible way is to make a mysql dump and then import it. There are other methods which can sync the data but it will involve a mysql restart and both mySQL servers version must be the same.

If you have a small database, then this single command is the best. What it does is to make a dump file from the source, drop the destination server's database, then import the SQL dump all with just one command. Here are the prerequisites.

• You must have access to both target (localhost should be no issue) and destination mysql servers.
• You must have MySQL Workbench Utilities installed

Ok here is the command that I used.

mysqldbcopy --source=root:password123@localhost:3306 --destination=root:password456@123.456.789.123:3306 hehdb:copyhehdb --force

The --force parameter will drop the destination db (copyhehdb) if it exists.