Introduction to MySQL Binlog

Binlog basic definition: A binary log, also a binary log, that records SQL statements that occur to or potentially change data, and is stored in a binary form on disk;

Role: MySQL functions similarly to Oracle's archive logs, which can be used to view the history of changes to the database (all SQL operations at a specific point in time), database incremental backups and restores (incremental and point-in-time recovery), replication of MySQL (primary primary database replication, master-slave database replication)

Information for binary logs:

File location: The default location is the directory where the database files are located

File naming: Name hostname-bin.xxxxx (restarting MySQL once will automatically generate a new Binlog)

View of the state:mysql> show variables like '%log_bin% ';

Mysql> Show variables like '%log_bin% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| Log_bin | On | Indicates that the binary log is currently open//

| log_bin_trust_function_creators | OFF |

| Sql_log_bin | On |

+---------------------------------+-------+

3 Rows in Set (0.00 sec)

Management of binary logs:

1. Turn on the binary log configuration

Method One, modify the my.cnf parameter file, the method needs to restart

Log-bin = Mysql-bin #打开日志 (the host needs to open), this mysql-bin can also be customized, here can also add a path, such as:/home/www/mysql_bin_log/mysql-bin

method to turn off binary logging: Log-bin = mysql-bin comment out

Method Two, do not restart the modified binary log configuration, the method MySQL version needs more than 5.6

SET @ @global. log_bin=1|0 (1 is on, 0 is off)

SET @ @global. binlog_size=37268 (unit bytes)

3. Pause Binary Log

SET Sql_log_bin={0|1}

4. Change the size of the binary log

Modify the value of the max_binlog_size in the my.cnf parameter file;

Note: If your binary is larger than max_binlog_size, it is automatically creating a new binary file. Of course, if the log file reaches its maximum size when a large transaction is written, the log file will still exceed the size of max_binlog_size.

5, the binary log switching, by default, when the binary log is full or the database is restarted to switch, but can also manually switch the action

mysql> flush logs;

6. Other parameters:

binlog-cache-size=100m Setting the binary log cache size

Sync-binlog=n (every N seconds writes binary log records from the cache back to the hard disk, the default value is 0.) However, you often get caught in the contradiction between the group commit function and the I/O two choices. If you are in a replication environment, you need to set 1 because durability and consistency are considered. Also, you need to set the Innodb_flush_log_at_trx_commit=1 and innodb-support-xa=1 to open by default;)

To view the contents of the binary log:

Binlog cannot be opened directly with text, MySQL provides the appropriate viewing tool: Mysqlbinlog, view a single binary log file directly: Mysqlbinlog filename

Example: mysqlbinlog/data/mysql/mysql-bin.000001

Of course, you can also complete the recovery of the database through the binary log, the specific use will be described in the backup restore of the database.

Delete binary Log

The binary log will grow continuously and produce multiple files. Therefore, backup planning and management policies need to be developed. Useless binary logs to remember to delete in time.

There are three ways to delete a slow query log:

1. Direct deletion of operating system commands

2. Reset Master

3, PURGE BINARY LOGS before ' 2014-07-09 12:40:26′;

Summary: The use of binary logs is also very important, how many cases will be opened, for the business operations of the database needs to develop a corresponding backup strategy and deletion strategy;

Introduction to MySQL Binlog