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