Database backups

This help center article describes how backups of individual or all MySQL databases can be created or restored using the Linux software mysqldump or a shell script. The automated database backups are suitable for creating hourly backups of all databases, for example.

Create database backup via CLI

The Linux software mysqldump can be used to create backups of individual databases in just a few steps. To do this, first log in to your creoline server via SSH.

Back up a specific MySQL database

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

mkdir -p /var/backups/mysql/

cd /var/backups/mysql/

mysqldump --single-transaction DATABASE_NAME > backup.sql

Backup all MySQL databases:

Replace backup.sql with the file name of your backup:

mkdir -p /var/backups/mysql/

cd /var/backups/mysql/

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

Restore database backup via CLI

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

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=<database name>

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

# Create Backup Directory if not exists
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"

mysqldump --single-transaction --host=$MYSQL_HOST --user=$MYSQL_USER --password=$MYSQL_PASS --max_allowed_packet=1024M $MYSQL_DBNAME > $BACKUP_DIR/$BACKUP_FILENAME.sql
echo "MySQL Backup $BACKUP_FILENAME has been created succesfully"

gzip > $BACKUP_DIR/$BACKUP_FILENAME.sql
echo "MySQL Backup $BACKUP_FILENAME has been compressed succesfully"

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

echo "Old MySQL Backups has been cleaned succesfully"

Then make sure that the script has the right to execute:

chmod +x mysql_backup.sh

The script can then be tested as follows:

./mysql_backup.sh

The first complete MySQL backup should then be mapped in the directory /var/backups/mysql:

ls -lah /var/mysql/backups

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

Create cronjob

For the automatic execution of database backups, either a Linux cronjob or a cronjob can be created via our customer center.

Customer Center Cronjob

You can find detailed instructions on how to create a cronjob via our customer center in the help center article Cronjobs.

Linux cronjob

Open the crontab editor with 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 00:00:

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 key combination CTRL + X. You will then be asked whether the changes should be saved. To do this, use the Y key (for Yes) and then press the Enter key. The crontab editor confirms the successful adjustment via the output:

crontab: installing new crontab