Skip to content

How MySQL Copy a Database To Another Server

It could a number of reasons to recreate a database on another server 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 server (db1.example.com) to a remote server (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 server 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.

Leave a Reply

Your email address will not be published. Required fields are marked *