One of the fastest, easiest, most reliable ways to copy MySQL databases across different hosts/servers

There are many ways to copy MySQL databases across hosts. One of the easiest is to use a graphical tool such as SQLYog. However, if you want to copy from one remote server to another remote server, SQLYog imposes an annoying overhead that you could live without. The overhead involved is that SQLYog will first download data from one remote server to your local machine, and then send it from there to the other remote server.

A faster way to copy databases is to copy the MySQL database files directly from one server to another. There are at least two reasons why you might not want to use this approach. One reason is that views are often not replicated correctly in this manner. Another is that, as far as I know, you can't really do this with InnoDB based tables/databases because these are all stored within a single big file.

At any rate, the most reliable way to copy/recreate a database is to do a mysqldump that outputs the SQL statements needed to create the table. A quick, painless way to do this is to pipe the output of mysqldump directly to the mysql client which will send the commands directly to the server on the remote end. Remote hosting servers usually have very fast connectivity to the 'Net, and you completely avoid the overhead of having to go through your own ISP. Outlined below are the steps on to do this:

1. Login to the host where the MySQL database to be copied resides.

2. From there, create the database to copy to on the other host. -u user refers to a user on the destination server with the proper permissions to create a database, -p means ask for the password for that user.

mysqladmin -h 'destination_host' -u user -p create dest_db

3. After the database is present on the destination server, it is a simple matter to just pipe the output of mysqldump over to it via the mysql client, without the need to create a temporary text file.

mysqldump -u user --password=XXXX --opt source_db | mysql -h 'destination_host' -u user -p dest_db

4. To apply compression and make the copying even faster, you can try the method below

mysqldump -u user --password=XXXX --opt --compress source_db | mysql -h 'destination_host' -u user -p dest_db

This tip can actually be found in the MySQL reference documentation itself, but it is cool enough to be worthy of a mention here. See http://dev.mysql.com/doc/refman/5.1/en/upgrading-to-arch.html for the original source.

Discussion: