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.
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.
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.
# yum install https://repo.twindb.com/twindb-release-latest.noarch.rpm
After the repository is configured we can install the agent:
# yum install twindb Loaded plugins: fastestmirror Setting up Install Process Loading mirror speeds from cached hostfile * base: mirror.cs.vt.edu * epel: mirror.dmacc.net * extras: mirror.cs.vt.edu * updates: mirrors.loosefoot.com Resolving Dependencies --> Running transaction check ---> Package twindb.noarch 0:0.1.35-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================= Package Arch Version Repository Size ============================================================= Installing: twindb noarch 0.1.35-1 twindb 26 k Transaction Summary ============================================================= Install 1 Package(s) Total download size: 26 k Installed size: 85 k Is this ok [y/N]: y Downloading Packages: twindb-0.1.35-1.noarch.rpm | 26 kB 00:00 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : twindb-0.1.35-1.noarch 1/1 Stopping ntpd service Shutting down ntpd: [ OK ] Starting ntpd service Starting ntpd: [ OK ] Starting twindb client Starting TwinDB agent ... OK Verifying : twindb-0.1.35-1.noarch 1/1 Installed: twindb.noarch 0:0.1.35-1 Complete!
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.
To do so we need to run this command on all three servers.
# twindb --register ea29cf2eda74bb308a6cb80a910ab19a
2015-05-03 04:12:24,588: twindb: INFO: action_handler_register():1050: Registering TwinDB agent with code ea29cf2eda74bb308a6cb80a910ab19a
2015-05-03 04:12:26,804: twindb: INFO: action_handler_register():1075: Reading SSH public key from /root/.ssh/twindb.key.pub.
2015-05-03 04:12:28,356: twindb: INFO: action_handler_register():1129: Received successful response to register an agent
2015-05-03 04:12:29,777: twindb: INFO: get_config():609: Got config:
{
"config_id": "8",
"mysql_password": "********",
"mysql_user": "twindb_agent",
"retention_policy_id": "9",
"schedule_id": "9",
"user_id": "9",
"volume_id": "8"
}
2015-05-03 04:12:30,549: twindb: INFO: create_agent_user():1159: Created MySQL user twindb_agent@localhost for TwinDB agent
2015-05-03 04:12:31,084: twindb: INFO: create_agent_user():1160: Congratulations! The server is successfully registered in TwinDB.
2015-05-03 04:12:31,662: twindb: INFO: create_agent_user():1161: TwinDB will backup the server accordingly to the default config.
2015-05-03 04:12:32,187: twindb: INFO: create_agent_user():1162: You can change the schedule and retention policy on https://console.twindb.com/
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.
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.
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.
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“:
Then choose an incremental copy to restore:
Then enter directory name where the restored database will be:
Then press “Restore” and it should show a confirmation window:
The restore job is scheduled and it’ll start after five minutes:
When the restore job is done the database files will be restored in directory /var/lib/mysql.restored on server db03:
[root@db03 mysql.restored]# cd /var/lib/mysql.restored/ [root@db03 mysql.restored]# ll total 79908 -rw-r-----. 1 root root 295 May 5 03:36 backup-my.cnf -rw-r-----. 1 root root 79691776 May 5 03:36 ibdata1 drwx------. 2 root root 4096 May 5 03:36 mysql drwx------. 2 root root 4096 May 5 03:36 performance_schema drwx------. 2 root root 4096 May 5 03:36 sakila drwx------. 2 root root 4096 May 5 03:36 twindb -rw-r-----. 1 root root 25 May 5 03:36 xtrabackup_binlog_info -rw-r-----. 1 root root 91 May 5 03:36 xtrabackup_checkpoints -rw-r-----. 1 root root 765 May 5 03:36 xtrabackup_info -rw-r-----. 1 root root 2097152 May 5 03:36 xtrabackup_logfile -rw-r-----. 1 root root 80 May 5 03:36 xtrabackup_slave_info [root@db03 mysql.restored]#
And that’s it. /var/lib/mysql.restored/ is ready to be used as MySQL datadir.
The post How to setup MySQL incremental backup appeared first on Backup and Data Recovery for MySQL.