1. How to setup MySQL incremental backup

How to setup MySQL incremental backup

TwinDB console

Incremental backups in MySQL were always a tricky exercise. Logical backup tools like mysqldump or mydumper don’t support incremental backups, although it’s possible to emulate them with binary logs. And with snapshot-based backup tools it’s close to impossible to take incremental copies.

Percona’s XtraBackup does support incremental backups, but you have to understand well how it works under the hood and be familiar with command line options. That’s not so easy and it’s getting worse when it comes to restoring the database from an incremental copy. Some shops even ditch incremental backups due to complexity in scripting backup and restore procedures.

With TwinDB incremental backups are easy. In this post I will show how to configure MySQL incremental backups for a replication cluster with three nodes – a master and two slaves.

Configure MySQL Incremental Backups in TwinDB – online backup service for MySQL

TwinDB is online backup service for MySQL. It’s available on https://console.twindb.com/. Once you get there you’ll see a read-only demo. It shows how we backup our TwinDB servers.

Welcome page

Create Account in TwinDB

A new user has to create an account so they can backup their own servers.

For now we are in the by-invitations beta, drop me a mail to aleks@twindb.com for an invitation code.

UPDATE: Registration in TwinDB is open as of 19 May 2015.

Signup form

Once you’re registered it’ll bring you to your environment where you can manage MySQL servers and storage, change schedule and retention policy.

Install Packages Repository

The next step is to install TwinDB agent on MySQL servers. It’s a python script that receives and executes commands from TwinDB. We distribute the TwinDB agent via packages repository. There are repositories for RedHat based systems as well as for Debian based systems.

For the demonstration we will register a cluster with one master and two slaves.

Let’s install TwinDB RPM repository.

After the repository is configured we can install the agent:

The agent should be installed on all three servers. TwinDB discovers replication topology and makes sure the backup is taken from a slave.

Register TwinDB Agents

Now we need to register the MySQL servers in TwinDB.

Screenshot 2015-05-04 19.50.56-highlight

To do so we need to run this command on all three servers.

When a MySQL server registers in TwinDB few things happen:

  • The agent generates a GPG keys pair to encrypt backups and for secure communication with TwinDB dispatcher
  • The agent generates a SSH keys for secure file transfers
  • TwinDB creates a schedule, retention policy for the server and allocates storage in TwinDB for backup copies.
  • The agent creates a MySQL user on the local MySQL instance.

At the registration step the agent has to connect to MySQL with root permissions. It’s preferable to set a user and password in ~/.my.cnf file. It is also possible to specify the user and password with -u and -p options.

After five minutes TwinDB will discover the replication topology, and will find a feasible MySQL server to take backup and will schedule a backup job.

In “Server farm” -> “All servers” we see all registered MySQL servers.

Screenshot 2015-05-04 20.30.26

After TwinDB discovers replication cluster nodes it starts scheduling backup jobs. By default a full copy is taken every week and incremental copy is taken every hour. You can change the schedule if you click on  “Schedule” -> “Default“.

On the dashboard there is a list of jobs. I was writing this post several days, so TwinDB managed to schedule a dozen of jobs.

Screenshot 2015-05-04 20.33.45

For each newly registered server TwinDB schedules a full job, that’s why there are jobs for db01 and db02. But then it picked db03 and all further backups are taken from it.

To see what backup copies are taken from the replication cluster let’s open db03 server details, tab “Backup copies“. Here you can see full copies from db01, db02, and db03 and further incremental copies from db03.

Screenshot 2015-05-04 20.34.10

Restore MySQL Incremental Backup

So far, taking an incremental backup was easy, but what about restoring a server from it?

Let’s go to the server list, right-click on a server where we want to restore a backup copy and choose “Restore server“:
Screenshot 2015-05-04 20.34.50

Then choose an incremental copy to restore:

Screenshot 2015-05-04 20.35.02

Then enter directory name where the restored database will be:

Screenshot 2015-05-04 20.35.30

Then press “Restore” and it should show a confirmation window:

Screenshot 2015-05-04 20.35.50

The restore job is scheduled and it’ll start after five minutes:

Screenshot 2015-05-04 20.41.16

When the restore job is done the database files will be restored in directory /var/lib/mysql.restored on server db03:

And that’s it. /var/lib/mysql.restored/ is ready to be used as MySQL datadir.

Have a question? Ask the experts!

Previous Post Next Post
  • Mathias

    Nice!!