Importing MySQL database from one server to another

If you want to avoid creating a file, transferring it, and loading it, you can just pipe mysqldump into either an mysql running on the other server, or an ssh mysql on the other server.

Using mysql to connect to the remote box:

mysqldump --all-databases | mysql -h yourserver.com 

Using ssh to connect to the other server

mysqldump --all-databases | ssh user@yourserver.com mysql 

Use the mysqldump --all-databases to transfer them all, or just specify database names. Refer to the mysqldump documentation for more options.

You can also use the MySQL "Replication" feature, although that will take a bit more time to setup, and is rather tricky. Probably not worth all the time and trouble just for one single migration.

Stop mysqld on the first server, copy the data directory (usually /var/lib/mysql) from server 1 to server 2, start mysqld on the second server, and it will now be identical to the first.

You do not have to use the import/export tools if you can stop the server while you copy the data files. Especially if you can compress the files before copying them, this will be the fastest way.

mysqldump –add-drop-table –extended-insert –force –log-error=error.log -uUSER -pPASS OLD_DB_NAME | ssh -C user@newhost “mysql -uUSER -pPASS NEW_DB_NAME”

Dumps a MySQL database over a compressed SSH tunnel and uses it as input to mysql

source