Archivi tag: binlog

Remote dump of mysqlbinlog

Posted on by

In MySQL 5.6 the mysqlbinlog command allows:
– connecting to a remote server with  “-R”
– get all binlogs passing “–to-last-log”

To continuously dump the logs, use
– stay connected with “–stop-never” which implies “–to-last-log”

You can practice using this MySQL Community Docker Image, which provides all binaries and an easy master-slave setup.

You can try using this

# mkdir /tmp/test; cd /tmp/test
# wget https://raw.githubusercontent.com/ioggstream/mysql-community/master/ga/docker-compose.yml
# docker-compose -p test up # run master and server

# docker run --rm -ti --link=test_master_1:master --entrypoint=/bin/bash ioggstream/mysql-community -l

$mysqlbinlog -R  -h master -u root -p root  --stop-never master-bin.000001

Now just send some data to the master (eg. the sakila db)

For further info, check here http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html

MySQL: Quick&Dirty Point in Time Recovery using binlog

Posted on by

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

or:

# for bl in binlog*; do
mysqlbinlog $bl
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”

Note that:

  1. you cannot import each binlog file separately, because you will lose temporary tables.
  2. binlogs archive all your databases, not a single one.

Purging Binlogs

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:

  1. deleting a file while mysql is using it;
  2. 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;

eg.
mysql> show master status \G
File: mysql-bin.000016
Position: 106

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

T0 -
| flush binlogs
T1 -
| take a backup
T2 - (backup successfull)
| purge old binlogs
T3 -
|
v