The easiest way to transfer a Mysql database from one server to another is by creating a backup, copy the mysql database backup to another server and restore it on the remote server.
The first thing is to create a backup of the mysql database using mysqldump. It creates a backup in the .sql format. You have to use your SSH user/password while backup/restore of database.
To backup a specific database:
# mysqldump -u root -p db_name > db_name.sql
To backup a specific Mysql table:
# mysqldump -u root -p db_name tb_name > tb_name.sql
Copy the database/table backups to the remote server using SCP (ssh):
# scp db_name.sql root@remote_server_ip:/backup/ # scp tb_name.sql root@remote_server_ip:/backup/
Login to the remote server and restore the .sql file. You should be in the backup directory while restoring
# mysql -u root -p db_name < db_name.sql # mysql -u root -p db_name < tb_name.sql
On a cPanel server, the mysql user is ‘root’ and the mysql password.
On a Plesk server the mysql user is ‘admin’ and the Plesk admin password.
March 4th, 2012 at 9:36 am
Adding the –opt option is a good idea, too. E.g.,
mysqldump –opt -u root -p db_name > db_name.sql
March 5th, 2012 at 1:40 pm
Thanks for sharing.. will see a few more combinations/options and will add them to the post as well.