Importing MySQL database from one server to anothe

Question

I have got two dedicated servers with root access. Both are running Linux. I want to import database from Server1 to Server2. I have already created an empty database on Server2. I want to know Linux command through which I can import database directly? Is there such a feature? Can I use mysqldump? I want to avoid first taking database backup on server1, then moving that file to server2, and then importing that file. Can import be done directly using some command?

Thanks

Answer 1

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

Answer 2

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 [e-mail] 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.

Answer 3

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.