Simple Automated phpBB Database Backups

I run a small phpBB forum hosted with WebFaction and I was looking for a really simple way to make automated daily backups of the database in case I accidentally destroy it somehow. 😬

The Backup Script

So I threw together a quick bash script to do the job. Below is a sample and I’ll outline the parts of what it does.

#!/bin/bash
mysqldump db_name \
    --defaults-extra-file=/path/to/config.cnf \
    --single-transaction \
	--quick \
	--ignore-table=db_name.phpbb_search_wordmatch \
	--ignore-table=db_name.phpbb_search_wordlist \
	--ignore-table=db_name.phpbb_sessions \
	> "/path/to/backups/filename-$(date '+%u').sql"
Code language: Bash (bash)

mysqldump – This is a client utility designed to make backups of mysql databases

--defaults-extra-file=/path/to/config.cnf – This part tells the script to include a config file which contains the username and password needed to access the database. Since we’re automating the process, we need this because we won’t be there to enter the password when prompted!

I’d suggest making a MySQL user with read-only permissions on the phpBB database to use specifically for this script.

The .cnf file included here is very simple and should look like this:

[mysqldump]
user=YOUR_DB_USER
password=YOUR_DB_USER_PASSWORDCode language: Bash (bash)

--single-transaction – This basically marks a point in time for the db to use to dump data prior to. It ensures that any new writes that occur while to the database during the export are not included.

--quick – This forces mysqldump to retrieve rows one at a time rather than retrieving it all at once and buffering it in memory before writing it out. It’s helpful for large tables, such as the posts table in a decently-large phpBB installation.

--ignore-table=db_name.phpbb_search_wordmatch
--ignore-table=db_name.phpbb_search_wordlist
--ignore-table=db_name.phpbb_sessions – These 3 tables are not critical to my backup. Two of them are related to search and can be rebuilt from the other tables during a restore, and the third is for storing user sessions. I don’t mind forcing people to log in again if I have to restore the database.

"/path/to/backups/filename-$(date '+%u').sql" – This tells the script where to store the backup. In my case, I felt like a week’s worth of backups was plenty, so the $(date '+&u') is the numerical day of the week, with 1 being Monday. This allows the script to only store 7 days worth of backups and then begin overwriting previous ones.

Automating The Backup

Now that we have out script and it’s storing a file, we want to make sure it runs automatically, so we simply need to add this script to the crontab.

In an SSH session on your server, type crontab -e. This will open VIM (or your default editor) to edit the cron schedule.

In this file, add 15 2 * * * /path/to/backupscript. The first part of this tells the script to execute at 2:15AM every day. The second part is the path pointing to where you stored the backup script we created above.

Now early each morning, I get a full backup created of my phpBB database!