Backing Up MYSQL to a New Server

Backup and Restore a MYSQL Database

This tutorial will show the steps that can be used to backup a mySQL database, move it to a new server and restore it. Although this can be done very quickly with GUIs like Cpanel and phpMyAdmin, the tutorial explanations use the Linux command line. After all, if you want to do synched backups automatically with Cron jobs, the GUI cannot do that for you.

Simple One Time Move

//Copy It
username# mysqldump -u root -p mydb > mydb.sql 
or with password
useraname# mysqldump -u root -pAddPasswordHere mydb > mydb.sql

Move It
scp -P 22 /home/username/Downloads/mydb.sql test@example.com:/home/test/public_html/mydb.sql

//Restore it
Create database, if necessary
mysql -u root -pAddPasswordHere -e "create database mydb"

//Add the tables 
mysql -u root -pAddPasswordHere mydb < mydb.sql

Continual Backups

This setup can be used if you want to empty and overwrite existing databases. Unlike the example above, the -e flag allows you to write single or multiple mySQL commands. With the example below, the database is destroyed, recreated and dumped with a fresh set of data.

You could run a cron job on one host that will move the sql file to the new server. Meanwhile, you could run a cron job on the foreign host so that it dumps the desired data at your preferred time(s). In addition to these commands, you will need to use ssh keys so you can SCP the file without using a password.

//Copy It
username# mysqldump -u root -p mydb > mydb.sql 
or with password
useraname# mysqldump -u root -pAddPasswordHere mydb > mydb.sql

//Move It
scp -P 22 /home/username/Downloads/mydb.sql test@example.com:/home/test/public_html/mydb.sql

//Restore it
mysql -u root -pAddPasswordHere -e "drop database if EXISTS mydb; create database mydb;" ;mysql -u root -pAddPasswordHere mydb < mydb.sql 

Although this article is targeted at copying and restoring mySQL databases, moving files to another server can be done with similar methods. For example, you could create a tar or zip file on one server, SCP the file to another server and extract it when it reaches its destination.

July 15, 2014 by kent