1. How To Setup MySQL Incremental Backup

How To Setup MySQL Incremental Backup

TwinDB console

Incremental backups in MySQL have always been 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 you 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 an 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 An Account On TwinDB

New users has to create an account so they can backup their own servers.

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

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

Signup form

Once you’ve 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 the 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 a 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 on TwinDB few things happen:

  • The agent generates a GPG key pair to encrypt backups and for a secure communication with the TwinDB dispatcher.
  • The agent generates a SSH key for secure file transfers.
  • TwinDB creates a schedule, a retention policy for the server, and allocates backup copies storage on TwinDB.
  • 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 discovers the replication topology, finds a feasible MySQL server to take backup, and schedules 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’ve been writing this post for 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, the “Backup copies” tab. 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 the 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

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 the /var/lib/mysql.restored directory 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