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 email@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
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 firstname.lastname@example.org:/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.