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:
- MySQL Server is Shutdown – MySQL will automatically rotate its binary logs whenever it is shutdown or restarted.
- Max_binlog_size is Reached – By manipulating this global variable, you can change the frequency of the binary log rotation.
- 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
- 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:
- 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.
- 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://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
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014