MySQL - automatic backup of a database

Last updated on 06 Sep 2023, 13:18:29.
Category: Cloud servers

backup Cron Linux MariaDB MySQL

Mysql automatic backup script


Kinamo Virtual Server (VPS) and Kinamo Cloud Server customers can use a simple shell script to dump the contents of MySQL or MariaDB databases to a chronologically ordered map.

Isn't my database backed up already?

The advantage of such an easy backup is the ease of data protection in case of a database crash and the flexibility to quickly restore an entire database, without having to ask a file level based restore by Kinamo support. Another advantage is the ease of restoring InnoDB databases in case of a system crash.
Working with a MySQL or MariaDB database dump makes a restore easier, since working with IDB files can quickly become a problem when trying to do a restore.

Kinamo offers the following script, we advise you to put the script in /usr/sbin/local.
In order not to over-complexify this post we have named it

Step 1: Copy the script


# Basic configuration: datestamp e.g. YYYYMMDD

DATE=$(date +"%Y%m%d")

# Location of your backups (create the directory first!)


# MySQL login details


# MySQL executable locations (no need to change this)


# MySQL databases you wish to skip


# Number of days to keep the directories (older than X days will be removed)


# ---- DO NOT CHANGE BELOW THIS LINE ------------------------------------------
# Create a new directory into backup directory location for this date

mkdir -p $BACKUP_DIR/$DATE

# Retrieve a list of all databases


# Dumb the databases in seperate names and gzip the .sql file

for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"

# Remove files older than X days

find $BACKUP_DIR/* -mtime +$RETENTION -delete

Copy the entire contents of the block above to a script names and put it in the folder /usr/local/sbin/.

Change the constant variables as you wish (for example, your password) and make sure the folder /backup/mysql exists.

Step 2: Modify the script's rights

Give the script execution rights

chmod 755

Step 3: Create a cron job

Next thing is to set up a simple cronjob to schedule a backup every night at 1 am:

vi /etc/crontab

0 1 * * * root /usr/local/sbin/

Restart the cron daemon

Save the crontab and restart the cron service with one of the following commands, depending on your Linux distro:

service cron restart
service crond restart

Every night, the script will add a backup of all your databases in the folder /backup/mysql/YYYYMMDD/<yourdatabase dump>

Related articles

Kinamo mail server settings (mail server cheat sheet)

The following article provides the basic settings and server names (domain names) for the Kinamo mail infrastructure. These settings allow...

Read more

Create an automatic SPAM filter in the Kinamo Webmail

This article explains how you may create a filter that will automatically place SPAM messages in a special folder, so...

Read more

Order an SSL certificate? What is an SSL certificate?

You need to order an SSL certificate? But what is an SSL certificate? And why is everyone saying that it...

Read more

Need extra help?

Were not all your questions answered?
Don't worry, we will be happy to help you via a support request!

Select your language

All languages: