Using the MySQL Binary Log

MySQL Point in Time Recovery

MySQL Point in Time Recovery

Enabling and managing binary logging will make it more likely that you can recover your database to a specific point in time.  These point in time recoveries require proper backups as well as having your MySQL server instance configure for binary logging.  At the end of this article, we will demonstrate a simple PITR (Point in Time Recovery), but before we get to the example, let’s get familiar with MySQL binary logging.  I am hoping that after reading this article, you will be comfortable Using the MySQL Binary Log.

How to Enable MySQL Binary Logging

To enable binary logging in MySQL, just add log-bin to your /etc/my.cnf file:

[mysqld]
log-bin = uptimemysql-bin

Another option that could be added to the /etc/my.cnf file is the log-bin-index option which will contain all of the names of the binary log files.

With the binary log enabled, you can now run “show binary logs;” to see all of your binary log files.  You can also rotate your MySQL binary logs using the “flush logs” command as shown in the example below.

Example: We rotate the bin-log for our MySQL server using the “flush logs” command.

mysql> show binary logs;
+------------------------+-----------+
| Log_name               | File_size |
+------------------------+-----------+
| uptimemysql-bin.000001 |       106 |
+------------------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------------+-----------+
| Log_name               | File_size |
+------------------------+-----------+
| uptimemysql-bin.000001 |       155 |
| uptimemysql-bin.000002 |       106 |
+------------------------+-----------+
2 rows in set (0.00 sec)


Rotating MySQL Binary Logs

MySQL Binary Logs are rotated under the following circumstances:

  1. MySQL Server is Shutdown – MySQL will automatically rotate its binary logs whenever it is shutdown or restarted.
  2. Max_binlog_size is Reached – By manipulating this global variable, you can change the frequency of the binary log rotation.
  3. Using Flush Logs – Database administrators are able to force a log rotation using the flush logs command.  This can be 2 ways:  from the mysql> prompt or using mysqladmin.

    Example:  Flushing MySQL binary logs from mysql prompt.

    mysql> flush logs;
    Query OK, 0 rows affected (0.00 sec)

    Example:  Flushing MySQL binary logs using mysqladmin.

    $ mysqladmin flush-logs
  4. MySQL Crashing – From time to time, MySQL will encounter a problem and have to shutdown.  This can occur when MySQL encounters corrupt data or other irregularities.

Reviewing Your Binary Logs

We can find the names and sizes of our binary logs using the “show binary logs” command.

Example:  Select names of all of the binary logs from the bin-log-index using the “show binary logs” command

mysql> show binary logs;
+------------------------+-----------+
| Log_name               | File_size |
+------------------------+-----------+
| uptimemysql-bin.000001 | 155       |
| uptimemysql-bin.000002 | 184638    |
+------------------------+-----------+
2 rows in set (0.00 sec)

We can use the “show binlog events” to review transactions in the binary logs.  Note that your output is in a different format if you use a “\G” after the “show binlog events\G;” command.  Note that without any filtering, it will show us all of the events in the binary log which could get to be very, very long.

mysql> show binlog events;
+------------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name               | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------------+-----+-------------+-----------+-------------+---------------------------------------+
| uptimemysql-bin.000001 | 4   | Format_desc |         1 |         106 | Server ver: 5.1.69-log, Binlog ver: 4 |
| uptimemysql-bin.000001 | 106 | Rotate      |         1 |         155 | uptimemysql-bin.000002;pos=4          |
+------------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

mysql> show binlog events\G;
*************************** 1. row ***************************
 Log_name: uptimemysql-bin.000001
 Pos: 4
 Event_type: Format_desc
 Server_id: 1
End_log_pos: 106
 Info: Server ver: 5.1.69-log, Binlog ver: 4
*************************** 2. row ***************************
 Log_name: uptimemysql-bin.000001
 Pos: 106
 Event_type: Rotate
 Server_id: 1
End_log_pos: 155
 Info: uptimemysql-bin.000002;pos=4
2 rows in set (0.00 sec)

ERROR:
No query specified

We can limit the results of our “show binlog events” command to just the commands in a specific binary log file as show below (note that I deleted about 50 rows here to keep it short):

mysql> show binlog events in 'uptimemysql-bin.000002'\G;
*************************** 1. row ***************************
 Log_name: uptimemysql-bin.000002
 Pos: 4
 Event_type: Format_desc
 Server_id: 1
End_log_pos: 106
 Info: Server ver: 5.1.69-log, Binlog ver: 4
*************************** 2. row ***************************
 ...
*************************** 53. row ***************************
 Log_name: uptimemysql-bin.000002
 Pos: 50392
 Event_type: Query
 Server_id: 1
End_log_pos: 50511
 Info: use `surveys`; create table test (testint int, testchar varchar(20))
*************************** 54. row ***************************
 Log_name: uptimemysql-bin.000002
 Pos: 50511
 Event_type: Query
 Server_id: 1
End_log_pos: 50618
 Info: use `surveys`; insert test values ( 1, 'my test string')

We can also add a binary log position as a filter to get even fewer results as well as adding an offset specifying exactly how many records to return:

mysql> show binlog events in 'uptimemysql-bin.000002' from 50392 limit 2\G;
*************************** 1. row ***************************
 Log_name: uptimemysql-bin.000002
 Pos: 50392
 Event_type: Query
 Server_id: 1
End_log_pos: 50511
 Info: use `surveys`; create table test (testint int, testchar varchar(20))
*************************** 2. row ***************************
 Log_name: uptimemysql-bin.000002
 Pos: 50511
 Event_type: Query
 Server_id: 1
End_log_pos: 50618
 Info: use `surveys`; insert test values ( 1, 'my test string')
2 rows in set (0.00 sec)

Mysql Binary Log Filters

We can limit the records that will be recorded by database using the “binlog-ignore-db” and the “binlog-do-db” options In the my.cnf file:

[mysqld]
binlog-ignore-db=myignoreddb
binlog-do-db=mydodb

So above, you obviously would not do both of these simultaneously. You would instead do one or the other. But these filter out statements from the binary log for either just one database (binlog-do-db), or all but for one database (binlog-ignore-db).

Purging MySQL Binary Logs

If you aren’t paying attention, it is entirely likely that you could fill your filesystem with binary logs.  You will want to manage these logs using one of the options below:

  1. Have MySQL Automatically Purge the Binary Logs – You can configure MySQL to automatically purge old binary logs using the global variable  expire-logs-days.  Set this to the number of days of binary logs that you want to keep.
  2. Purge MySQL Binary Logs Using “Purge Binary Logs” Syntax – You can tell MySQL to delete binary log files using either a date or filename as a point of reference.Example:  Purge MySQL binary logs prior to April 15, 2014
    mysql> purge binary logs before '20140415';
    Query OK, 0 rows affected, 8 warnings (1 min 0.90 sec)

    Example:  Purge MySQL binary logs prior to a binary log named:  uptimemadeeasy-db.000056

    mysql> purge binary logs to 'uptimemadeeasy-db.000056';
    Query OK, 0 rows affected (10.72 sec)

MySQL Binary Log Global Variables and Options

Here are global variables for your MySQL server that you can use to manage your MySQL binary logs:

Option Description
expire-log-days length of time that the binary log should be kept.  This value is defined in days.  This is the method to use for automatic binary log purging
max_binlog_size the largest size that MySQL will allow each binary log to grow before it is rotated.
binlog-cache-size the size in bytes of the memory cache designed to hold a portion of the binlog in cache.
log-bin the basename of the binary log files.
log-bin-index the name of the index file that will keep a list of all of the actual binary log files.

Example of MySQL Backup and Recovery Using Point in Time Recovery

So, as promised, let’s review a simple example of creating a database, backing it up properly and then testing the restore.  By the way, don’t rely solely on these instructions, no two recovery scenarios are alike and each should be considered very carefully.

First, let’s ensure that we have binary logging turned on by setting the “log-bin” option in the /etc/my.cnf file.  If you do, skip this part.

[mysqld]
log-bin=uptimemysql-bin

then restart mysqld and verify that the binary logs are created:

# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
# ls -l /var/lib/mysql/uptimemysql-bin*
-rw-rw---- 1 mysql mysql 106 Jul 24 21:38 uptimemysql-bin.000001
-rw-rw---- 1 mysql mysql 25 Jul 24 21:38 uptimemysql-bin.index

Now, let’s create our database and a table

mysql> create database uptimemadeeasy;
Query OK, 1 row affected (0.00 sec)

mysql> use uptimemadeeasy;
Database changed
mysql> create table uptimemadeeasy ( uptimeint int, uptimechar varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert uptimemadeeasy values ( 1, "row 1" );
Query OK, 1 row affected (0.00 sec)

Now that we have a database, let’s perform a backup of the database using mysqldump.  Note that I am using the “–flush-logs” option to force a binary log rotation.

$ mysqldump --user=root --password=<mypasswordhere> uptimemadeeasy --single-transaction --routines --flush-logs > uptimemadeeasy.sql

Now, let’s add some data, flushing the logs after each:

mysql> insert uptimemadeeasy values ( 2, "row 2");
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> insert uptimemadeeasy values ( 3, "row 3");
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> insert uptimemadeeasy values ( 4, "row 4");
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

We should now have more binary log files due to the log flushing above.

$ ls /var/lib/mysql/uptimemysql-bi*
/var/lib/mysql/uptimemysql-bin.000001
/var/lib/mysql/uptimemysql-bin.000002
/var/lib/mysql/uptimemysql-bin.000003
/var/lib/mysql/uptimemysql-bin.000004
/var/lib/mysql/uptimemysql-bin.000005
/var/lib/mysql/uptimemysql-bin.index

Now, we drop the database and then go through the recovery using the binary logs we just created for MySQL point in time recovery.

mysql> drop database uptimemadeeasy;
Query OK, 1 row affected (0.01 sec)

Recovering a MySQL Database Using Binary Logs

First create the database that needs to be restored

mysql> create database uptimemadeeasy;
Query OK, 1 row affected (0.00 sec)

Now, we restore the backup that we created

$ mysql -uroot -p uptimemadeeasy < ./uptimemadeeasy.sql
Enter password:

We should have our table back and the first row:

mysql> select * from uptimemadeeasy;
+-----------+------------+
| uptimeint | uptimechar |
+-----------+------------+
| 1         | row 1      |
+-----------+------------+
1 row in set (0.00 sec)

Now, let’s go through the binary logs using the mysqlbinlog command

$ mysqlbinlog uptimemysql-bin.000002 | mysql -uroot -p
Enter password:

Let’s see if we have some data back…

mysql> select * from uptimemadeeasy;
+-----------+------------+
| uptimeint | uptimechar |
+-----------+------------+
|         1 | row 1      |
|         2 | row 2      |
+-----------+------------+
2 rows in set (0.00 sec)

Yes!  It worked.  The row that we inserted that made it into that binary log is back, let’s restore the others:

$ mysqlbinlog uptimemysql-bin.000003 uptimemysql-bin.000004 | mysql -uroot -p
Enter password:

Now check the table again to see if the expected rows are back:

mysql> select * from uptimemadeeasy;
+-----------+------------+
| uptimeint | uptimechar |
+-----------+------------+
|         1 | row 1      |
|         2 | row 2      |
|         3 | row 3      |
|         4 | row 4      |
+-----------+------------+
4 rows in set (0.00 sec)

Other Resources

http://www.question-defense.com/2009/12/20/configure-logrotate-to-rotate-and-flush-mysql-logs-without-a-password

http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *