Introduction to MySQL Binlog

Introduction to MySQL Binlog

Basic definition of binlog: Binary logs are also binary logs that record SQL statements that have occurred or potentially changed data and are saved in the disk in binary format;

Role: MySQL is similar to Oracle archive logs and can be used to view Database Change History (all SQL operations at specific time points), database Incremental backup and recovery (Incremental backup and point-in-time recovery), Mysql replication (master-master database replication, master-slave database replication)

Binary log information:

File Location: the default storage location is under the directory where the database files are located.

File naming: Name Is hostname-bin.xxxxx (a new binlog is automatically generated once mysql is restarted)

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

Mysql> show variables like '% log_bin % ';

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

| Variable_name | Value |

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

| Log_bin | ON | // indicates that binary logs are currently enabled //

| Log_bin_trust_function_creators | OFF |

| SQL _log_bin | ON |

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

3 rows in set (0.00 sec)

 

Binary log management:

1. Enable binary log Configuration

Method 1. Modify the my. cnf parameter file. Restart this method.

 

Log-bin = mysql-bin # Open the log (the host needs to be opened). This mysql-bin can also be customized. You can add a path here, for example: /home/www/mysql_bin_log/mysql-bin

To disable binary logs, comment out log-bin = mysql-bin.
 

 

Method 2: Do not restart to modify the binary log configuration. This method requires mysql versions later than 5.6.

SET @ global. log_bin = 1 | 0 (1 is enabled, 0 is disabled)

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

 

3. Pause binary logs.

SET SQL _log_bin = {0 | 1}

 

4. Modify the size of binary logs

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

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

 

5. Switch the binary log. By default, the switch is performed only when the binary log is full or the database is restarted.

Mysql> flush logs;

 

6. Other parameters:

Binlog-cache-size = 100 m to set the binary log cache size

Sync-binlog = N (writes the binary log records in the cache back to the hard disk every N seconds. The default value is 0. However, you often fall into the conflict between the group commit function and I/O. If durability and consistency are considered in the replication environment, set 1. At the same time, innodb_flush_log_at_trx_commit = 1 and innodb-support-xa = 1 are also required ;)

Binary log Writing Process

View the binary log Content:

Binlog cannot be opened directly in text. mysql provides the corresponding viewing tool mysqlbinlog to directly view a single binary log file: mysqlbinlog filename

For example: mysqlbinlog/data/mysql/mysql-bin.000001

Of course, you can also use binary logs to restore the database. The specific usage will be described in the Backup Recovery of the database.

Delete binary logs

Binary logs increase constantly and generate multiple files. Therefore, you need to develop backup plans and management policies. Remember to delete useless binary logs in time.

There are three methods to delete slow query logs:

1. Directly Delete operating system commands

2. reset master

3. purge binary logs before '2017-07-09 12:40:26 ′;

Summary: Binary logs are also very important for a wide range of purposes. In most cases, they will be enabled. For databases with frequent business operations, appropriate backup policies and deletion policies need to be formulated;

MySQL Data Recovery-binlog

Clear binlog logs in MySQL

How to safely delete binlog logs under MySQL

MySQL -- binlog log data recovery

How does MySQL Delete binlog and restore Data?

Introduction and Analysis of Three MySQL binlog formats

MySQL uses binlog Incremental backup + restore instance

MySQL deletes binlog logs and restores data.

This article permanently updates the link address: