Tuesday, January 16, 2007

mysql copy database over an exisiting one

I face a common situation where I have a number of copies of a database that need to be synced to a master copy every so often. For example a live version, a demonstration version and a sandbox version. The easiest way seems to be the following:

mysqldump -uUsername -pPassword --add-drop-table db_name1 | mysql -uUsername -pPassword db_name2

This avoids having to delete and recreate the destination database. To copy it to another host add the "-h" option. If the destination database does not already exist run:

mysqladmin -uUsername -pPassword create db_name2

Unfortunately, there is still a lot of redundancy in this appoach as it wipes all the data and then replaces it even if there are only a few records different.