Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

1 Introduction

1.1 What is MySQL Bin Log？

MySQL Bin Log（Binary Log, binary log) is a log file of MySQL database, which is used to record all modifications performed on the database.DMLOperations (such as INSERT, UPDATE, DELETE, etc.), butSELECT is not includedwaitDQLRead-only operation. Bin Log is an important tool for MySQL to achieve replication, recovery and auditing. For details, see:DDL, DML, DQL, and DCL in MySQL

1.2 Bin Log Function and Application Scenarios

The main functions of Bin Log include: master-slave replication, data recovery, data backup, data subscription

Through these specific cases, we can more clearly see the importance and diversity of MySQL binlog in practical applications. Proper use of binlog function can greatly improve the reliability, recovery ability and business response speed of the system.

2. Basic Concepts of Bin Log

2.1 How Bin Log works

When the Bin Log function is enabled on the MySQL server, all modifications to the database will be logged inEvent record formThese events are stored in the order in which they are executed, forming a continuous sequence of operation logs. When you need to restore or copy data, you can replay these events to reproduce the state of the database.

2.2 Three Formats of Bin Log

MySQL Bin Log has three record formats:
The following is a table that organizes the three record formats of MySQL Bin Log into a table:

3. Configure and manage Bin Log

3.1 Enable Bin Log

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14

To enable Bin Log, you need to add the following line to the MySQL configuration file (usuallymy.cnformy.ini) and configure as follows:

[mysqld]
log-bin=mysql-bin
server-id=1
1
2
3

After modifying the configuration, restart MySQL. ExecuteSHOW VARIABLES LIKE 'log_bin'; The Value value is ON.

mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------------+--------------------------------------------------------------------------+
| Variable_name                   | Value                                                                    |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin                         | ON                                                                       |
| log_bin_basename                | C:UsershiszmMySQL5.7.26databinlog       |
| log_bin_index                   | C:UsershiszmMySQL5.7.26databinlog.index |
| log_bin_trust_function_creators | OFF                                                                      |
| log_bin_use_v1_row_events       | OFF                                                                      |
| sql_log_bin                     | ON                                                                       |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.2 Configuring Bin Log Parameters

Common Bin Log configuration parameters include:

• log_bin: Enable Bin Log.
• server_id: Unique ID of the server, used for replication.
• binlog_format: Set the format of Bin Log (STATEMENT, ROW, MIXED).
• expire_logs_days: Set the number of days for automatic expiration and deletion of Bin Log files.
• max_binlog_size: Set the maximum size of a single Bin Log file.

3.3 Managing Bin Log Files

Common commands for managing Bin Log files:

• View Bin Log file list：

  SHOW BINARY LOGS;
  1

• View Bin Log file size：

  SHOW MASTER STATUS;
  1

• Delete old Bin Log files：

  PURGE BINARY LOGS TO 'mysql-bin.000010';
  1

  or:

  PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
  1

---

3.4 View Bin Log Content

-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB;  -- 使用InnoDB存储引擎

-- 插入数据，将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Use the mysqlbinlog tool to view the contents of the Bin Log:

mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2411 |
+---------------+-----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

If you open it directly, it will be garbled.

mysql> show binlog events in 'binlog.000001' from 0 limit 0,4G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
   Log_name: binlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 765
       Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)

ERROR:
No query specified

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

3.5 Using the mysqlbinlog Tool

mysqlbinlog is a command line tool for parsing Bin Log files. Common options include:

• --start-datetime: Specifies the time when parsing starts.
• --stop-datetime: Specifies the time when parsing ends.
• --start-position: Specifies where parsing starts.
• --stop-position: Specifies where the parsing ends.

For example, to view the Bin Log for a specific period of time:

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001
1

3.6 Parsing and replaying Bin Log

Bin Log events can be replayed into the MySQL server to achieve data recovery:

mysqlbin logbinlog.000001 | mysql -u root -p
1

---

4. Application of Bin Log in Replication

4.1 Master-slave replication principle

The basic principle of MySQL master-slave replication is that the master database records Bin Logs, and the slave database reads and replays these logs to achieve data synchronization. The specific steps are as follows:

1. The master database performs write operations and records these operations in the Bin Log.
2. The slave database connects to the master database, reads the Bin Log, and applies it to its own data.

4.2 Configuring Master-Slave Replication

1. Configure the main library：

   [mysqld]
   log-bin=mysql-bin
   server-id=1
   1
   2
   3

2. Configure the slave library：

   [mysqld]
   server-id=2
   relay-log=relay-bin
   1
   2
   3

3. Create a replication user on the primary database：

   CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
   1
   2

4. Configuring replication on the slave：

   CHANGE MASTER TO
       MASTER_HOST='主库IP',
       MASTER_USER='replica',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='binlog.000001',
       MASTER_LOG_POS=0;
   START SLAVE;
   1
   2
   3
   4
   5
   6
   7

5. Checking the replication status：

   SHOW SLAVE STATUSG
   1

4.3 Monitoring and managing replication

You can monitor the replication status with the following command:

SHOW SLAVE STATUSG
1

Explanation of common status fields:

• Slave_IO_Running: IO thread status.
• Slave_SQL_Running: SQL thread status.
• Seconds_Behind_Master: The time that the slave database lags behind the master database.

---

4.4 Incremental backup

Incremental backup means backing up all changes since the last full backup or the last incremental backup. Incremental backup can be achieved using Bin Log.

1. Perform a full backup：

   mysqldump --all-databases --master-data=2 > full_backup.sql
   1

2. Record the current Bin Log location：

   Look for the following line in the full backup file:

   -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;
   1

3. Backing up Bin Log：

   mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
   1

4.5 Recovery based on Bin Log

1. Restoring a full backup：

   mysql < full_backup.sql
   1

2. Apply incremental backups：

   mysql < incremental_backup.sql
   1

---

5. Final Thoughts

MySQL Bin LogIt is a powerful tool that is widely used in scenarios such as data recovery, replication, and auditing.Bin Log, which can greatly improve the reliability and availability of the MySQL database. In practical applications, mastering the usage skills and optimization methods of Bin Log is crucial for database administrators and developers.