Migrate a database to a different server by command line

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.

Backup the Database

To start with, take a full dump of the database using the below command:

mysqldump -pyour-password -u your-username database-name > database.sql

Secure copy the database to the remote server

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. 

Create the new database

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;

Import the database 

SOURCE /path/to/copied/file/database.sql;

Remember you'll also need to setup appropriate privileges for the database once the import is complete.

Sign Up

NEXT: Attach a file to an email in PHP using PHPMailer

How to attach a file to an email in PHP using the PHPMailer library.

comments powered by Disqus

Popular Tags

Need a web developer?

If you'd like to work with code synthesis on your next project get in touch via the contact page.