Backing up a MySQL database

I you have worked with IT systems you know that backup is a boring task, but to explain why data has been lost is even less attractive. So you should backup whenever you don’t want to lose your work. The best is to do it automatically, so it’s done without you are required to remember to perform the task.

I use WordPress for this site, and to organize information at my home server. WordPress stores data in a MySQL database, and you can’t just run a file copy (xcopy, rsync, cp, and the likes) , you need to log in to the database server and perform a series of queries to extract data and write the data into files. The backup files must be formatted correctly in order to facilitate an easy restore process.

My solution for this was to write a php script that could perform the extraction and writing of backup files. I have designed it specifically with WordPress backup in mind. Installation into a WordPress site is quite easy. The script is placed in the root folder of WordPress, where e.g. the wp-config.inc.php file is located, the backup script read database name and access from the wp configuration file, when initiated it extracts data and places a file, named as the database in the wp-content folder.

I run the setup from a machine at home, having some scheduled tasks (crontab as I’m on Linux). First I use the wget command to initiate the backup https://site/wp-backup.php, the I use wget https://site/wp-content/databasename.sql to bring it back, last i run a small php script that delete the backup file from wp-content. So know I don’t worry about destroying my database during upgrades or system maintenance, I can restore it very easily by uploading the backup file to phpmyadmin.