Backing up a Wordpress database

My wife’s website is built on Wordpress. Wordpress uses a MySQL (or MariaDB) database to store pages, posts, comments, sessions, and everything else except media. That database needs to be backed up regularly in case of data loss. Backing up a MySQL database is easy - just run mysqldump - so why would I need anything else? I wrote backup-wordpress to wrap some extra functionality around mysqldump:

  • Extract the username, password, and database name from the Wordpress wp-config.php, so that you can just run backup-wordpress /path/to/wordpress-base rather than passing username, password, and database name as arguments to mysqldump, e.g. in crontab(5).
  • Run mysqlcheck to repair and optimise the database.
  • Run mysqldump with the right arguments and compress the output.
  • Run tmpreaper to delete old backups. tmpreaper is run under flock so that parallel invocations of backup-wordpress won’t result in parallel invocations of tmpreaper. When two tmpreaper instances run in parallel, it’s possible that both will call readdir(3), one will stat(2) then unlink(2) a file, and the second will then fail to stat(2) that file, resulting in error messages.

I run this for both the dev and production instances of my wife’s website every hour from cron(8).