Database Backups

This Help Center article describes how to create or restore backups of individual or all MySQL databases 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 the CLI

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

Backing Up a Specific MySQL Database

Replace DATABASE_NAME with the database name and backup.sql with the filename of your backup:

mkdir -p /var/backups/mysql/

cd /var/backups/mysql/

mysqldump --single-transaction --no-tablespaces DATABASE_NAME > backup.sql

Backing up all MySQL databases:

Replace backup.sql with the filename of your backup:

mkdir -p /var/backups/mysql/

cd /var/backups/mysql/

mysqldump --single-transaction --no-tablespaces --all-databases  > backup.sql

Restoring a Database Backup via CLI

To restore the 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 DATABASE_NAME database:

cd /var/backups/mysql/

mysql DATABASE_NAME < backup.sql

Scheduled Automatic Backups

Use cd to navigate to the desired script directory and create a shell script named mysql_backup.sh.

cd /var/backups

nano mysql_backup.sh

#!/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 "Creating backup directory $BACKUP_DIR"
    mkdir -p $BACKUP_DIR
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"

Next, make sure the script has execute permissions:

chmod +x mysql_backup.sh

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 set up a cron job through our Customer Center.

Customer Portal Cron Job

Detailed instructions for creating a cron job via our Customer Portal can be found in 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 * * * * /var/backups/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