1. How to Backup MySQL Binary logs

How to Backup MySQL Binary logs

MySQL binary logs make possible to implement point-in-time recovery and greatly reduce Recovery Point Objective. In this post I will show how to copy MySQL binary logs with TwinDB Backup.

MySQL binary logs was an easy way to implement replication. Whenever the server executes a DML query it also writes the query to a local file – the binary log a.k.a. binlog. When replicas connect to the master they receive the queries from the binlog and replay them on their copy of data. This way the databases on the master and replica stay in-sync.

Database backups is another important application of the binlog. As long as it contains data changes it can be used to restore state of the database.

When you take a full or incremental copy of the MySQL database you probably noticed binary log coordinates in the xtrabackup_binlog_info and xtrabackup_binlog_pos_innodb files. This is the state of the database as of this position in the binlog. The restored database will be in that state.

Now, if you have binlogs after the (mysql-bin.000001, 954694568) point, you can replay them up to the desired point. For example, until the erroneous DROP DATABASE statement.

We introduce beta support for MySQL binary logs in the 2.16.1 version of TwinDB Backup.

Configuring MySQL Binlog Backups.

The tool enables binlog backups by default. No special configuration is need to enabled them; if the server enables the binlogs  TwinDB Backup tool will copy them.

In the mysql section you can specify for how long should the tool keep the binlogs:

The binary logs are copied with every TwinDB Backup run. However we recommend to backup binlogs more often. Command line option --binlogs-only will copy only binary logs. It’s lightweight and can be run every five minutes. So you will need to amend the cron configuration.

Binary logs status

To see what binary logs are actually copied to the destination you can with following command.

Getting started with TwinDB Backup

As usual, we distribute TwinDB Backup from our repository. We support several Linux based systems: CentOS 6 and 7, Ubuntu trusty and xenial and Debian jessie.

Give it a try and leave your feedback on our GitHub.

Have a safe database!

Previous Post Next Post