Bulletproof Your Drupal Data With Automated Nightly Backups

Why a Custom Bash Script?

You might be thinking, "Aren't there modules for this?" Yes, there are, but a simple bash script executed via a cron job offers several advantages:

• Reliability: It runs outside of the Drupal environment, meaning if your Drupal site itself is having issues, your backup process isn't affected.
• Performance: It directly interacts with MySQL/MariaDB, often more efficiently for large databases.
• Control: You have granular control over what gets backed up and how, including where sensitive data is stored and ensuring it adheres to your privacy and security policies.

My script is a modification of a solid foundation I found on TecAdmin.net by Rahul Kumar. I tailored it specifically for my needs on my Drupal site, focusing on optimizing the backup size without losing critical information as well as some minor performance enhancements.

The Drupal-Specific Optimization: Handling Cache Tables

Drupal's caching system is fantastic for performance, but it generates a lot of data in tables that start with "cache_" (e.g., cache, cache_render, cache_container). The data within these tables is expendable – it can be regenerated by simply clearing Drupal's caches. Including all this dynamic data in every nightly backup significantly increases the backup file size and the time it takes to create.

My modification ensures that for these cache tables, only their structure (schema) is backed up, not their data. This keeps your backup files lean and fast, while still preserving the necessary table definitions. When you restore, you just clear caches, and Drupal rebuilds what it needs.

My Nightly Drupal Database Backup Script

Here's the script I use. Remember to replace the placeholder values ([path_to_backup_folder], [mysql_host], etc.) with your actual credentials and paths.

#!/bin/bash
 
################################################################
##
##   MySQL Database Backup Script 
##   Written By: Rahul Kumar
##   URL: https://tecadmin.net/bash-script-mysql-database-backup/
##   Last Update: Jan 05, 2019
##
##   Modified by: Ron Ferguson
##   Change log: Modified to exclude cache tables and minor
##   performance enhancements.
##   Last Updated: Nov 01, 2024
##
################################################################
 
export PATH=/bin:/usr/bin:/usr/local/bin
 
################################################################
################## Update below values  ########################
 
DB_BACKUP_PATH='[path_to_backup_folder]'
MYSQL_HOST='[mysql_host]'
MYSQL_PORT='[mysql_port]'
MYSQL_USER='[mysql_user]'
MYSQL_PASSWORD='[mysql_password]'
DATABASE_NAME='[mysql_database_name]'
BACKUP_RETAIN_DAYS=4   ## Number of days to keep local backup copy
 
#################################################################


# Validate required variables
if [ -z "${DATABASE_NAME}" ] || [ -z "${MYSQL_HOST}" ] || [ -z "${MYSQL_USER}" ] || \
   [ -z "${MYSQL_PASSWORD}" ] || [ -z "${DB_BACKUP_PATH}" ]; then
    echo "Error: One or more required environment variables are not set."
    exit 1
fi

# Set the backup date and start message
TODAY=$(date +"%Y%m%d-%H%M")
echo "Backup started for database - ${DATABASE_NAME}"

# Get a list of tables that begin with "cache"
CACHE_TABLES=$(mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" \
    -p"${MYSQL_PASSWORD}" -N -e "SHOW TABLES LIKE 'cache%'" "${DATABASE_NAME}")

# Generate the `--ignore-table` options for each cache table
IGNORE_TABLES=""
for table in ${CACHE_TABLES}; do
    IGNORE_TABLES+="--ignore-table=${DATABASE_NAME}.${table} "
done

# Perform the backup
BACKUP_FILE="${DB_BACKUP_PATH}/${DATABASE_NAME}-live-${TODAY}.sql.gz"
mysqldump --column-statistics=0 \
    ${IGNORE_TABLES} \
    -h "${MYSQL_HOST}" \
    -P "${MYSQL_PORT}" \
    -u "${MYSQL_USER}" \
    -p"${MYSQL_PASSWORD}" \
    "${DATABASE_NAME}" | gzip > "${BACKUP_FILE}" \
    && echo "Database backup successfully completed: ${BACKUP_FILE}" \
    || { echo "Error found during backup"; exit 1; }

# Remove old backups
DBDELDATE=$(date +"%Y%m%d" --date="${BACKUP_RETAIN_DAYS} days ago")
find "${DB_BACKUP_PATH}" -type f -name "*.sql.gz" -mtime +"${BACKUP_RETAIN_DAYS}" -exec rm -f {} \;

 
### End of script ####

How it Works: A Breakdown

Let 's walk through the key parts of the script:

Configuration Variables

At the top, you'll define your database connection details, the backup path, and how many days you want to retain backups. Ensure these are accurate and secure!

Variable Validation

The script includes a simple check to ensure all necessary variables are set. This prevents the script from failing silently if you forget to fill in a placeholder.

Identifying Cache Tables

This is the core Drupal optimization that I made.

mysql -h ... -N -e "SHOW TABLES LIKE 'cache% '" "${DATABASE_NAME}"

This command connects to your MySQL database and queries for all table names that start with cache. The -N option suppresses column names, and -e allows execution of a SQL query.

The output (a list of cache table names) is then stored in the CACHE_TABLES variable.

Building mysqldump Ignore List

The script iterates through each identified cache_ table.

For each table, it constructs an --ignore-table=your_database_name.table_name option.

These options are concatenated into the IGNORE_TABLES variable. This tells mysqldump to skip the data for these tables.

Performing the Backup

mysqldump --column-statistics=0 ${IGNORE_TABLES} ... | gzip > "${BACKUP_FILE}"

mysqldump: The command-line utility for dumping MySQL databases.

--column-statistics=0: This is important for mysqldump versions 8.0+, as it can otherwise cause issues with permissions if the user doesn 't have SELECT privilege on mysql.column_stats.

${IGNORE_TABLES}: This is where our generated list of cache tables (to ignore data for) comes in.

The output of mysqldump is piped (|) to gzip, which compresses the SQL dump.

> "${BACKUP_FILE}": The compressed output is then redirected into your specified backup file path with a timestamp.

&& echo ... || { echo ...; exit 1; }: This is a bash construct that prints a success message if mysqldump is successful (&&) or an error message if it fails (||), exiting with an error code.

Removing Old Backups

find "${DB_BACKUP_PATH}" -type f -name "*.sql.gz" -mtime +"${BACKUP_RETAIN_DAYS}" -exec rm -f {} \;

This command finds all .sql.gz files in your backup path.

-mtime +"${BACKUP_RETAIN_DAYS}": Filters for files that were last modified more than the specified number of days ago.

-exec rm -f {} \;: Executes the rm -f command to forcefully remove each found file. This keeps your backup directory from overflowing.

Beyond the Basics: Further Enhancements

While this script provides a robust solution for daily production backups, its flexibility allows for further powerful modifications. As your development workflow evolves, you might consider enhancing this script to accept command-line input. For instance, you could pass arguments to switch between different database environments (development, testing, or production), allowing you to use the same script for various environment backup needs. Moreover, to prevent errors in automated cron jobs, you could implement logic within the script to set default environment values if no input is provided. This ensures the script always has the necessary parameters, even when running non-interactively, making it even more resilient and versatile for multi-environment Drupal setups. These types of enhancements are on my own to-do list, and they demonstrate how a simple bash script can become a powerful tool in your DevOps toolkit.

Automation with Cron

To automate this, you'll need to set up a cron job on your server or workstation.

Save the script: Save the script to a file (e.g., drupal_db_backup.sh) in a secure location on your server or workstation (e.g., /usr/local/bin/).

Make it executable: chmod +x /usr/local/bin/drupal_db_backup.sh

Edit your crontab by running the command crontab -e

Add a line like this to run it every night at 2:00 AM:

0 2 * * * /usr/local/bin/drupal_db_backup.sh > /dev/null 2>&1

0 2 * * *: Runs at 2:00 AM daily.

> /dev/null 2>&1: Redirects all output (standard output and errors) to /dev/null, preventing unnecessary emails from cron. (You might want to remove this initially for testing to see output.)

Conclusion

Having an automated, optimized database backup system is a cornerstone of responsible Drupal site management. This script provides a lean and reliable way to ensure your most critical data is safe and easily restorable, without bloating your backups with expendable cache data.

What are your go-to backup strategies for Drupal? Share your tips in the comments below!