If you need to migrate your database to a different server, this can be achieved with a simple export and import using interfaces like phpMyAdmin. But for larger databases you'll quickly run into problems with upload size restrictions and it all starts to get a bit fiddly. This article looks at how you can do this more efficiently directly on the command line.
This article assumes you're comfortable using linux on command line but should provide you with every step you need to complete the process. References will be made to the 'local server' where your current database resides, and the 'remote server' where you wish to move the database to.
To get started, use putty or your preferred SSH client to connect to your local server.
To start with, take a full dump of the database using the below command:
mysqldump -pyour-password -u your-username database-name > database.sql
Next we need to copy the database dump across to the remote server using SCP (secure copy):
scp database.sql remote-username@remote-host:/path/on/remote
You may be asked to accept, and then provide a password. It doesn't matter where on the server you place the database file.
Next login to the remote server, connect to MYSQL and create the new database:
mysql -u root -p
Enter your password.
CREATE DATABASE your-database;
Remember you'll also need to setup appropriate privileges for the database once the import is complete.