How To Copy A Database To Another Machine

It could a number of reasons to recreate a database on another machine and there are several ways to achieve this:

• Copy the data files from the source to the target database if and only if the tables are MyISAM.
• Use mysqldump utility provided by MySQL.

Here we recreate a database with the same name (db_name) from local machine (db1.example.com) to a remote machine (db2.example.com) by using mysqldump. In the following case, our source is on Windows, the target is on Linux.
C:>mysqldump -u root -ppassword db_name | mysql -u root -ppassword -h db2.example.com db_name
You can see the combined command can make the recreation possible in one line. Please note that, there is no space between the command option '-p'  and the 'password' string.

If you met the following error, it could be the firewall problem. Please allow port 3306 to accept connections.
ERROR 2003 (HY000): Can't connect to MySQL server on 'db2.example.com' (110)
Of course, you can copy a database from a remote machine to local, just switch the places like this:
[root@localhost ~]# mysqldump -u root -ppassword -h db1.example.com db_name | mysql -u root -ppassword db_name
It could be interrupted by some errors in the process of recreation, you should solve them before issuing the command again.