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

Introduction

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 kinamo_mysqlbackup.sh.

Step 1: Copy the script

#!/bin/bash

# Basic configuration: datestamp e.g. YYYYMMDD

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

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

BACKUP_DIR="/backup/mysql"

# MySQL login details

MYSQL_USER="root"
MYSQL_PASSWORD="YOURSECUREPASSWORD"

# MySQL executable locations (no need to change this)

MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# MySQL databases you wish to skip

SKIPDATABASES="Database|information_schema|performance_schema|mysql"

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

RETENTION=14

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

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

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

# Remove files older than X days

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

Copy the entire contents of the block above to a script names kinamo_mysqlbackup.sh 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 kinamo_mysqlbackup.sh

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/kinamo_mysqlbackup.sh

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

Managed services in the spotlight

Our system administrators have lifted a couple of cargo ships filled with containers of work. (yes, that pun was on...

Read more

What information is necessary to register a .be domain name?

Registering a .be domain name is easy. Any person or legal entity can register a .be domain name. Kinamo will...

Read more

Apache - Disable SSL 2.0, SSL 3.0 and opt fore a modern safe SSL config

This article shows you how to disable the SSL 2.0, SSL 3.0 and older TLS version protocols on your Apache...

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!

Kinamo

Select your language

All languages: