Migrate MySQL database

If you are moving to another hosting provider, or for any other reason you need to move or migrate your MySQL database you can use these instructions.

Make the backup of the database

mysqldump -u root -p --opt [database_name] > /tmp/[database_name].sql

Where database_name is the database you want to move.

Copy the database to the new server

You can use rsync, scp or ftp, I will show you how to do it with scp

scp /tmp/[database_name].sql user@newserver.com:/tmp/

Create the database in the new server

mysql -u root -p

create database [database_name];
grant all privileges on [database_name].* to "some-user"@"hostname" identified by "some-strong-password";
flush privileges;
exit

Import the backup

mysql -u root -p [database_name] < /tmp/[database_name].sql