Transfering MySQL database between hosts

The single command approach

mysqldump -h oldhost -u oldusername -poldpassword olddbname | mysql -h newhost -u newusername -pnewpassword newdbname

make sure you can …

mysql -h newhost -u newusername -pnewpassword newdbname

… before attempting the transfer. Common issues are :-

  • Firewall preventing access to MySQL server across the network
  • User configured to only be able to log in from localhost.

Note: there is no space between “-p” and the password.


The slightly longer approach

mysqldump -h oldhost -u oldusername -poldpassword olddbname > somefile.sql
tar -zcvf somefile.sql.tar.gz somefile.sql

** transfer file to new server.. via SSH maybe? **

tar -zxvf somefile.sql.tar.gz
mysql -h mysql.example.com -u username -ppassword newdbname < somefile.sql