Written on: 2006/06/15.

A quick and dirty way to make effective MySQL backups on a Linux (or other modern unixy) web server. This tip assumes you have shell access.

Make a script called mysqlbackup in a handy folder, like ~/bin. Write in it mysqldump -h mysql_server_hostname -u mysql_username --password=mysql_password mysql_database_name > ~/some/folder/somewhere/private/mysql_backup_`date +%d`.sql, filling in of course your database connectivity info.

Now you'll need to make a ~/some/folder/somewhere/private folder. Inside that, make a .htaccess file and write in it Deny from all. Then run crontab -e and write in it 0 3 * * * sh ~/bin/mysqlbackup. This will run the backup script at 3am server time every day.

Here's some reasons why this approach is handy.

Firstly, the entire backup process can be done automatically from within one script file without requiring user intervention. This allows you to generate a backup file automatically using a cronjob. The password in the shell command is in plain text, which does suck, so you might want to chmod 700 ~/bin/mysqlbackup to prevent other users on the server from seeing your password.

Another perk of this approach is it creates ~30 or so redundant backups of your database, but no more than that. Since it makes one for every day of the month, if you should need to restore a backup from 20 days ago, you can do that. But it doesn't fill up all your server's hard drive space either with backups from 10 years ago.