MySQL Binary Log Format

Log classification

MySQL Storage Engine Layer Log
- innodb
- Redo log
- Rollback Log
MySQL Service Layer Log
- Binary logs
- Slow check logs
- General log

Introduction to Binary Logs

All modification events to the MySQL database, including DDL and DML operations, were recorded. The binlog only logs successful execution, but not events that were rolled back or not executed by Syntax Error.

Enable binary logging

MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

log_bin is OFF, which means that binary logging is not enabled. We try to open it by following instructions

MariaDB [(none)]> set global log_bin = 'mariadb-bin';
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

Well, it's still about modifying my.cnf configuration file, adding [mysqld] groups to my.cnf or custom configuration, and setting the value of log_bin attribute to mariadb-bin to open bin log.

[mysqld]
log_bin = /var/log/mysql/mariadb-bin

Later, when we look at the directory / var/log/mysql / we will find a series of logs like mariadb-bin.000001. Execute after restarting MySQL

MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Now you can also display binary information properly

MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       313 |
+------------------+-----------+
1 row in set (0.00 sec)

NOTE: MySQL > flush logs; used to generate new binary logs for easy debugging.

The format of binary logs

Segment-based log format - statement

This is the default log format before MySQL 5.7, recording the SQL statements of CUD.
```
MariaDB [(none)]> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
```
- view log
The mysqlbinlog tool can be used directly to view logs
```
mysqlbinlog mysql-bin.000001
```
- Advantage:
Recording the SQL statements executed by each event does not require recording the specific changes of each row, so the amount of log records is relatively small, saving disk IO and network IO (if only one record is modified or inserted, the amount of log in ROW format may be less than that in STATEMENT format).
- Disadvantages:
In order to ensure that these SQL statements can be executed correctly from the library, context information should be recorded to ensure consistent behavior during replay. However, if uncertain functions such as UUID() are used, data inconsistencies between master and slave may occur.
Line-based log format - row

In the default format after MySQL 5.7, data is written to binlog whenever a CUD operation is performed to modify a row record.

If we have a modified 10k data, the segment-based log format will only record this SQL, while the row-based log will have 10k records.
- view log
```
mysqlbinlog -vv mysql-bin.000001
```
Advantage
1. This makes master-slave replication safer. Because ROW format records changes in each row, only this change is needed when the log is replayed from the slave library, which makes the uncertainty function safer to use. It also reduces the interruption of replication link caused by inconsistent master-slave data to a greater extent.
2. Row-based replication is more efficient than segment-based replication
shortcoming
1. The amount of logs recorded is large
Mixed Log Format - mixed

Mixed use of STATEMENT and ROW, depending on the SQL statement, the system decides whether to use a segment-based or row-based log format, such as non-deterministic functions will be recorded in ROW format, and others will be recorded in STATEMENT format.

Modify the binlog format

MariaDB [(none)]> set binlog_format = 'statement';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

Relevant optimization of ROW log format

Because of the huge amount of logs recorded in ROW format, after MySQL 5.6, the binlog_row_image parameter was added to improve the way of recording.

MariaDB [(none)]> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.00 sec)

FULL is the default value, meaning to record everything in a row of records, regardless of whether the column has been modified or not.

MINIMAL only records modified columns, which can greatly reduce the amount of records.

NOBLOB and FULL types, but columns of BLOB or TEXT types will not be recorded without modification.

Experiment

If there is a table structure as follows

CREATE TABLE `t_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(32) NOT NULL DEFAULT '',
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are records in the table.

id->1, title->'prontera', content->'hey guy'

FULL (Full Record)

We modify the record with id 1 and then look at the binlog in / var/log/mysql

MariaDB [employees]> update t_test set title = 'solar' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

As you can see, we only set title, but all columns are recorded when binlog_row_image is FULL.

BINLOG '
HPyWWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==
HPyWWBgBAAAASQAAABgCAAAAACEAAAAAAAEAA///+AEAAAAIcHJvbnRlcmEHAGhleSBndXn4AQAA
AAVzb2xhcgcAaGV5IGd1eQ==
'/*!*/;

### UPDATE `employees`.`t_test`


### WHERE


###   @1=1 /* INT meta=0 nullable=0 is_null=0 */


###   @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */


###   @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */


### SET


###   @1=1 /* INT meta=0 nullable=0 is_null=0 */


###   @2='solar' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */


###   @3='hey guy' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

MINIMAL (on-demand recording)

Similarly, updating the title of the record with id 1 to the original'prontera'effectively reduces the log volume compared with the binlog.

BINLOG '
rv2WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==
rv2WWBgBAAAALQAAAPwBAAAAACEAAAAAAAEAAwEC/gEAAAD+CHByb250ZXJh
'/*!*/;

### UPDATE `employees`.`t_test`


### WHERE


###   @1=1 /* INT meta=0 nullable=0 is_null=0 */


### SET


###   @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */

NOBLOB (excluding the full record of BLOB&TEXT)
Columns that do not contain text type when updating

MariaDB [employees]> update t_test set title = 'juno' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Similar to FULL mode, but binlog does not record large data types (BLOB or TEXT types) that have not been updated.

BINLOG '
hP+WWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==
hP+WWBgBAAAAMwAAAAICAAAAACEAAAAAAAEAAwMD/AEAAAAFcGF5b278AQAAAARqdW5v
'/*!*/;

### UPDATE `employees`.`t_test`


### WHERE


###   @1=1 /* INT meta=0 nullable=0 is_null=0 */


###   @2='payon' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */


### SET


###   @1=1 /* INT meta=0 nullable=0 is_null=0 */


###   @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */

Include text columns when updating

MariaDB [employees]> update t_test set title = 'prontera', content = 'google'  where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

When a column with a large data type is updated, the column is recorded, which reduces the log volume to a certain extent.

BINLOG '
PQCXWBMBAAAANwAAAM8BAAAAACEAAAAAAAEACWVtcGxveWVlcwAGdF90ZXN0AAMDD/wDYAACBA==
PQCXWBgBAAAAPgAAAA0CAAAAACEAAAAAAAEAAwMH/AEAAAAEanVub/gBAAAACHByb250ZXJhBgBn
b29nbGU=
'/*!*/;

### UPDATE `employees`.`t_test`


### WHERE


###   @1=1 /* INT meta=0 nullable=0 is_null=0 */


###   @2='juno' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */


### SET


###   @1=1 /* INT meta=0 nullable=0 is_null=0 */


###   @2='prontera' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */


###   @3='google' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

Experiments show that even in the same IDC based on ROW format, it is recommended to use minimal mode to greatly reduce the amount of logs.

Posted by attock on Thu, 11 Apr 2019 11:51:31 -0700

Programmer Group

MySQL Binary Log Format

MySQL Binary Log Format

Log classification

Introduction to Binary Logs

Enable binary logging

The format of binary logs

Modify the binlog format

Relevant optimization of ROW log format

Experiment

Hot Keywords