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.
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.
Comments