Using mysqldump to create database backups

In this lesson you will use mysqldump to create logical backups of your WordPress databases. You'll learn the handful of options that matter for consistent, reliable backups, why the root account is the right tool for the job, and write a script that dumps and compresses every site on your server.

mysqldump is a command-line utility that creates logical database backups. It's got plenty of options and can be a bit overwhelming if you haven't used it before. Luckily, there are only a handful of options you'll need to learn to use this tool properly. On MariaDB you'll also find it as mariadb-dump, which is the exact same tool under a different name.

Credentials vs root

Running mysqldump typically requires us to provide the hostname, database username and password using the -h, -u and -p options respectively, for example:

mysqldump -hlocalhost -uuncached -pverysecret dbname

Note the slightly uncommon lack of spaces between the option and the value.

While this method generally works, there are a couple of problems with this approach. First, the credentials may appear in the process list, so it's best to either provide them in a secure configuration file, or enter the password interactively by leaving the password blank.

Second, from a backups perspective, we don't really want to use specific site credentials, as these may need to be rotated, sites may be moved or recreated, and so on, creating additional work every time a site is changed.

We can create a special user for backups, with access to all databases, but it's also perfectly fine to simply use root. By default, this user in MySQL and MariaDB does not have a password, is only allowed to connect via a local socket, and requires the connecting Linux user to actually be root. This, of course, works with sudo as well:

sudo mysqldump dbname

Some tutorials recommend setting a strong root password for the MySQL user, however that actually makes it less secure, allowing non-root users to connect as root by knowing (or guessing) the password.

All databases vs single database

The mysqldump program allows us to create a logical backup of a single database, multiple databases, and even --all-databases if necessary.

I personally prefer to always use single database backups, even if it means iterating over multiple sites and running mysqldump more than once. This ensures that one file contains the data for one site, so I can be more confident when running restores.

Single-database backups also allow for more flexibility in retention rates, frequency and so on. However in this course we'll be sticking to a global configuration/script for all our hosted sites.

Single transaction

If you're running InnoDB tables (and you should be!) then --single-transaction is probably the most important option you will use. It ensures data consistency across all tables, and does so without locking all of the tables for the duration of the backup.

This is especially important with larger datasets and highly dynamic sites, such as WooCommerce and LMS, which can change data very often in a non-transactional way.

The database backup script

You can find the full up to date script in the config repository on GitHub, but I will explain some of the logic used to write that script here.

First, we generate our timestamp. Then loop through all the public_html directories available in the /sites/ directory. Note that this includes our phpMyAdmin install and likely other helper utilities in the future. We need to ensure we're looking at WordPress sites:

TIMESTAMP=$(date +%Y%m%d-%H%M%S)
for docroot in /sites/*/public_html; do
    [ -d "$docroot" ] || continue
    [ -f "$docroot/wp-config.php" ] || continue

Next, we figure out the site name, as well as the user that owns our wp-config.php file. This is important for the next step as we do not want to run WP-CLI as the root user for security reasons.

    site=$(basename "$(dirname "$docroot")")
    user=$(stat -c '%U' "$docroot/wp-config.php")

We read the DB_NAME constant using WP-CLI as the user that owns the wp-config.php file using sudo:

    db_name=$(sudo -u "$user" -H -- wp config get DB_NAME --path="$docroot" --skip-plugins --skip-themes 2>/dev/null) || {
        echo "[$site] could not read DB_NAME, skipping" >&2
        continue
    }

Finally, we create the destination directory, run the mysqldump backup and compress the output using gzip:

    dest_dir="/backups/$site"
    mkdir -p "$dest_dir"
    dest_file="$dest_dir/${TIMESTAMP}.sql.gz"

    echo "[$site] dumping $db_name to $dest_file"
    if mysqldump --single-transaction --quick "$db_name" | gzip > "$dest_file"; then
        echo "[$site] done"
    else
        echo "[$site] mysqldump failed" >&2
        rm -f "$dest_file"
    fi
done

I called this script backup-dbs.sh and placed it in the /config/bin/ directory. It needs to be run as root to write the destination backup files, as well as to access the MySQL root account. To invoke this as my karl user I'll need sudo: