With MySQL it is relatively easy to create point in time restores. All you need is a recent(ish) backup, and a bunch of saved binary logs. You can restore the backup you have, and when it is completed, you can use mysqlbinlog utility to apply your saved binary logs to the desired state of your database.
I have created a simple go application to make your life easier. You can find it at my github page.
The app works as reading its config file for the mysql server connection credentials, a local directory where the binary logs will be kept, and the will path of the mysqlbinlog utility.
- It checks the binlogs on the remote server which able to streamed
- Checks the local directory where the binlogs are kept, to check which logs are already there
- The incomplete (file size differs local and on remote server) files will be renamed as
- It spawns a mysqlbinlog process to stream the missing binlog files (starts from the first missing)
Well, simple enough.
There are also needed a few more features to make this app fancy, like removing the old files from the local directory after a configurable time, or making it a bit more verbose logging wise (right now there is only errors logged) but it is working out of the box right now, and basically does what is supposed to do.
I’ll continue adding these features there.
Special thanks to Peter Boros – the original streamer were created by him in bash, which worked really well at us, but there were problems starting it on CentOS 7 (systemd), so I decided to rewrite it in go.