How to Backup MySQL Binary logs
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_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.
$ cat xtrabackup_binlog_* mysql-bin.000001 954694568 mysql-bin.000001 954694568
Now, if you have binlogs after the (
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.
mysql section you can specify for how long should the tool keep the binlogs:
[mysql] mysql_defaults_file=/etc/twindb/.my.cnf full_backup=daily expire_log_days=10
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.
*/5 root twindb-backup backup --binlogs-only hourly @hourly root twindb-backup backup hourly @daily root twindb-backup backup daily @weekly root twindb-backup backup weekly @monthly root twindb-backup backup monthly @yearly root twindb-backup backup yearly
Binary logs status
To see what binary logs are actually copied to the destination you can with following command.
$ twindb-backup ls --type binlog 2018-09-24 21:33:25,607: INFO: ls.list_available_backups():32: Destination 2018-09-24 21:33:26,096: INFO: ls._print_binlog():70: Binary logs: s3://twindb-website-database/ip-10-2-0-241/binlog/mysql-bin.000012.gz s3://twindb-website-database/ip-10-2-0-241/binlog/mysql-bin.000013.gz s3://twindb-website-database/ip-10-2-0-241/binlog/mysql-bin.000014.gz s3://twindb-website-database/ip-10-2-0-241/binlog/mysql-bin.000015.gz s3://twindb-website-database/ip-10-2-0-241/binlog/mysql-bin.000016.gz
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!