Simple MySQL / MariaDB Backup

There are many ways to back up a MySQL or MariaDB server. Some ways include using mysqldump, mydumper, LVM Snapshots or XtraBackup. However, any robust backup solution boils down to one key requirement:

The ability to restore the databases to a point-in-time.

So, for instance, if your server crashes, you would like to be able to restore to the point in time before the server crashed. If data was deleted accidentally or damaged in some other way, you need to restore the data to the point in time before it was deleted or damaged.

If you use AWS RDS this ability is provided out of the box. However, you can meet this requirement much more cost effectively by using a simple VPS (such as Linode or Ramnode) with a simple setup I will describe below.

This setup will perform the following:

  • Write a log of all transactions and back up this log every 5 minutes to a remote server
  • Create a full database backup with mysqldump every day and copy this to a remote server

The backup keeps the binary logs for two days and the dumps for two weeks. Thus, any data loss should be limited to 5 minutes and full database backups should allow restoring data from up to two weeks ago.

System Landscape

  • Database Server: Runs the MariaDB or MySQL instance you want to back up
  • Backup Server: Used as a remote location to store backups

(any Linux based server will do for these)

 Step 1: Enable Binary Logs

On Database Server:

  • Edit your my.cnf file (e.g. under /etc/my.cnf or /etc/my.cnf.d/server.cnf). Assert the following lines:
  • Create the folder logs in your MySQL data dir (e.g. /var/lib/mysql)
mkdir /var/lib/mysql/logs
  • Set owner to user mysql for folder logs
chown mysql:mysql /var/lib/mysql/logs
  • Restart MySQL server
sudo systemctl restart mysqld

Now a binary logs should be written into the logs folder in your MySQL data dir.

Step 2: Create Script Full Backups with MySQL dump

On Database Server:

  • Create the folder¬†/var/lib/mysql/dumps
  • Create the script¬†/usr/local/ and copy the contents of¬†into this script.
  • Search for the line starting with¬†dumpopts. In this line, provide your mysql username and password.
  • Make the script executable
sudo chmod +x /usr/local/
  • Schedule the script to run once every day using cron or systemd


30 3 * * * /usr/local/


  • Create /etc/systemd/system/mysql_dump.service
Description=Dumps mysql databases to backup directory

  • Create /etc/systemd/system/mysql_dump.timer
Description=Run MySQL dump once per day

OnCalendar=*-*-* 03:13:00
  • And don’t forget to enable and start the timer:
sudo systemctl enable mysql_dump.timer
sudo systemctl start mysql_dump.timer

Step 3: Write Script to Backup Files to Remote Server

On the Backup Server:

  • Log into your Backup Server. Create a user mysqlbackup here:
useradd mysqlbackup
  • Change to mysqlbackup user
sudo su - mysqlbackup
  • Create directories logs and dumps
mkdir logs
mkdir dumps

On the Database Server:

  • Copy public key for root user from /root/.ssh/
  • If the public key for root does not exist, run:
sudo ssh-keygen -t rsa

On the Backup Server:

  • While being logged in as user mysqlbackup, assure the following file exists
  • Into this file, paste the public key for root on Server 1
  • Assure correct permissions for .ssh folder:
chmod 700 .ssh
chmod 600 .ssh/authorized_keys

On the Database Server:

  • Test access to the Backup Server (the sudo is important here, since you want to connect as the root user). Replace¬† with the address/IP of Server 2.
sudo ssh
  • If the SSH does not work for some reason, check this guide for more information.
  • Create the script¬†/usr/local/ Replace with the address/IP of your server.
rsync -avz --delete /var/lib/mysql/logs
rsync -avz --delete /var/lib/mysql/dumps
  • Make the script executable
sudo chmod +x /usr/local/
  • Use crontab or systemd to schedule the job for execution every 5 minutes:


  • Add the following line to the crontab for the user root
*/5 * * * * /usr/local/


  • Create the file¬†/etc/systemd/system/mysql_backup.service
Description=Backs up Mysql binary logs and full backups to remote server

  • Create the file /etc/systemd/system/mysql_backup.timer
Description=Run MySQL binlog backup and full backup sync every 5 minutes

  • Enable and start the timer
sudo systemctl enable mysql_backup.timer
sudo systemctl start mysql_backup.timer

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s