Database Backups

This Help Center article describes how to create or restore backups of individual MySQL databases in our cluster setups using the Linux software mysqldump or a shell script. Automated database backups are useful, for example, for creating hourly backups of all databases.

Creating a Database Backup via CLI

Using the Linux software mysqldump, you can create backups of individual databases in just a few steps. To do this, first log in to your creoline database server via SSH.

Backing up a specific MySQL database:

Replace backup.sql with the filename of your backup:

mysqldump --single-transaction --no-tablespaces \
 -u DB_USER -p -h DB_HOST DB_NAME > /var/backups/mysql/backup.sql

Restoring a Database Backup via CLI

To restore a backup of an existing database, first log in to your creoline server via SSH. Then run the following command to restore the backup.sql backup of the DB_NAME database:

Restoring a Specific MySQL Database

mysql -u DB_USER -p -h DB_HOST DB_NAME < /var/backups/mysql/backup.sql

Scheduled Automatic Backups

Setup

Run the following commands as the system user on your database server:

touch /var/backups/mysql/mysql_backup.sh
chmod +x /var/backups/mysql/mysql_backup.sh

nano /var/backups/mysql/mysql_backup.sh

Insert the following content into the file and adjust the variables MYSQL_HOST, MYSQL_USER, MYSQL_PASS, and MYSQL_DBNAME according to your database credentials:

#!/bin/bash
set -e

KEEP_LATEST=7
BACKUP_DIR=/var/backups/mysql

MYSQL_HOST="<localhost>"
MYSQL_USER="<username>"
MYSQL_PASS="<password>"
MYSQL_DBNAME="<databasename>"

DATE=$(date +"%m_%d_%Y")_$(date +"%H_%M")
BACKUP_FILENAME=backup_$MYSQL_DBNAME_$DATE

# Create backup directory if it does not exist
if [ ! -d $BACKUP_DIR ]; then
    echo -e "The backup directory $BACKUP_DIR does not exist!"
    exit 1
fi

# Create Backup
echo "Creating MySQL Backup $MYSQL_USER@$MYSQL_HOST for $MYSQL_DBNAME"

mariadb-dump --single-transaction --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS --max_allowed_packet=1024M $MYSQL_DBNAME | gzip > $BACKUP_DIR/$BACKUP_FILENAME.sql.gz

# Replace "mariadb-dump" with "mysqldump" if you're using MySQL instead of MariaDB

echo "MySQL backup $BACKUP_FILENAME has been created successfully"

# Remove old backups
cd $BACKUP_DIR
ls -tr | head -n -$(($KEEP_LATEST)) | xargs --no-run-if-empty rm

echo "Old MySQL backups have been successfully cleaned up"

Save the file with Ctrl + s and close the editor with Ctrl + x. 

The script can then be tested as follows:

./mysql_backup.sh

The first full MySQL backup should then appear in the /var/backups/mysql directory:

ls -lah /var/mysql/backups

# Example output:
total 47M
drwxr-xr-x 2 root root 4.0K Dec 21 17:01 .
drwxr-xr-x 3 root root 4.0K Dec 21 16:31 ..
-rw-r--r-- 1 root root  47M Dec 21 17:01 backup_12-21-2022_17:01:00.sql.gz

Create a Cron Job

To run database backups automatically, you can either create a Linux cron job or a cron job through our Customer Center.

Customer Center Cron Job

For detailed instructions on creating a cron job via our Customer Center, see the Help Center article Cron Jobs.

Linux Cron Job

Open the crontab editor using the command crontab -e.

 crontab -e

Hourly backups at minute 0:

0 * * * * mysql_backup.sh

See: https://crontab.guru/every-1-hour

Daily backups at 12:00 a.m.:

0 0 * * * /var/backups/mysql_backup.sh

See: https://crontab.guru/every-day-at-1am

After adding the line, exit the crontab editor using the CTRL + X key combination. You will then be asked if you want to save the changes. Press the Y key (for Yes) and then press the Enter key. The crontab editor confirms the successful change with the following output:

crontab: installing new crontab

Restoring a Specific MySQL Database from Automatic Backups

Since the automatic backup compressed the database dump with gzip, the following command must be used.

The zcat command passes the gzip-compressed file to the standard input of the mysql import command:

zcat /var/backups/mysql/backup.sql.gz | mysql -u DB_USER -p -h DB_HOST DB_NAME