Migrating a MySQL database from one server to another is a quick process when you have shell access to both locations. mysqldump can be used to create a SQL script that will recreate the database on the new server.
1. Dump the Database
Connect to the first server. Use mysqldump to dump the database to a file.
ssh [email protected] mysqldump -u root -p --opt mydb > mydb_dump.sql
The contents of this file contain the structure and data of the database.
2. Copy the Dump File to the New Server
Use SCP to transfer the file to the second server.
scp mydb_dump.sql [email protected]:/home/admin/
The SQL file will now reside in /home/admin/ on the second server.
3. Import the Database from the Dump File
Use the mysql client to import the database. The contents of the SQL file can be piped into the command which will evaluate and execute the script.
ssh [email protected] mysql -u root -p < /home/admin/mydb_dump.sql
Note that if you do not use root to import the database, the user provided needs to have certain privileges granted on the database in order for the import to succeed.
