Backup and Restore MySql Database to Remote Server

Backup and restore MySQL database to and from the remote server is very essential and basic necessary task as a freelance developer to create test server, migrate/upgrade to the new server.

For codebeautify, I have been doing this every month to test data with the new code on a test server.

Here are steps requires to backup, send and restore the database on new servers.

Creating A Backup

First, we need to create a backup of existing database using MySQL’s dump command. this command will help you to the backup single database
mysqldump database-name > database-name.sql
For multiple databases,

mysqldump --databases database_1 database_2 > multi_databases.sql

if the backup file size is large enough, we can zip/archive the file using tar command. This will also save bandwidth and time.

tar -czvf db.tar.gz database.sql

Transfer file to new server

Using sftp command it’s very faster way to transfer the file to remote server.

Here is the list of command to transfer file.

this command will login to remote server via sftp.

sftp username@remote_hostname_or_IP

Enter the password to login to the server. Once the login is successful, move to a directory where would you like to copy the backup file using the cd command.

Now to send the file to remote server use put command.

put database.sql

If you have zip/archive the file use this command to unzip.

tar -xvzf database.tar.gz

Restore a database backup on the new server and you are all set.

mysql database_name < database.sql

with Username and Password:

mysql database_name < database.sql -u root -p

These are very useful commands to migrate MySQL database to other servers to do the backup and restore of MySQL database.

I hope this article will help beginners to backup and restore MySQL database.