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.


An active SSH session with the user 'root' is required for this article.


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/sh
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 $MYSQL_DBNAME | gzip > $BACKUP_DIR/$BACKUP_FILENAME.sql.gz

echo "MySQL Backup has been created succesfully for $MYSQL_DBNAME"

# 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"


In this example, the --single-transaction option for InnoDB tables is used to start a global transaction to ensure the integrity of the data to be saved. The storage engine MyISAM does not support transactions. If you also want to back up MyISAM tables with this script, the --lock-tables option should be used instead.


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


The MySQL backups are compressed using gzip. If the backup is to be restored, the backup must first be unpacked. ( E.g.: gunzip 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 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 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