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