MySQL can log all queries in binary format, creating the famous binlog files. BinLogs are posthumous files, created by mysqld after that queries have been sent to the engine for committing.
While it’s not compulsory to use binlogs, they are a required if you want:
- replicating queries to another server;
- create point in time recovery.
Point in Time Recovery
A Point in Time Recovery is a sort of Time Machine, returning a snapshot of the db at a given time. You can simply aggregate all your binlogs with:
# mysqlbinlog binlog.000001 binlog.000002 > my-pitr.sql
# for bl in binlog*; do
done >> my-pitr.sql
Once you’ve aggregate *all* your binlog, you can import the my-pitr.sql:
# mysql -e “source /path/to/my-pitr.sql”
- you cannot import each binlog file separately, because you will lose temporary tables.
- binlogs archive all your databases, not a single one.
As binlog tend to grow an occupy a lot of space, you can set up an expiration policy based on the backup one, and know some basic operation to be done on your binlogs:
- SHOW MASTER STATUS; — see the last transaction position in log file
- FLUSH LOGS; — close the latest binlog file and start a new one (so we won’t delete a file in use)
- PURGE BINARY LOGS BEFORE ‘yyyy-mm-dd hh:mm:ss’; — remove logs until date
And let’s see eventual issues in deleting binlogs:
- deleting a file while mysql is using it;
- deleting a file containing transactions not stored on backups.
First issue is solved in two step:
a) know which file is currently used, with SHOW MASTER STATUS;
mysql> show master status \G
b) closing this log file and opening a new one (.00017) with FLUSH LOGS;
If we take a backup now, every transaction we could have missed is contained in binlogs > 00017
| flush binlogs
| take a backup
T2 - (backup successfull)
| purge old binlogs