Technology Sharing

MySQL Binlog Detailed Explanation: Core Technology for Improving Database Reliability

2024-07-12

한어Русский язык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

Application ScenarioconceptCase
Master-slave replicationThe master database sends the update operation records in the binlog to the slave database, which reads the binlog and executes SQL statements.E-commerce platform: The master database records order operations in binlog, and the slave database synchronizes order data to maintain consistency across all nodes.
Data RecoveryUse binlog records to reverse SQL statements to recover dataFinancial system: Administrators can restore accidentally deleted transaction records by parsing binlogs to ensure data integrity and avoid financial losses.
data backupBinlog is used for incremental backup, saving backup time and space costsSocial media platform: Back up binlog files regularly every day to quickly restore to the latest status without having to back up the entire database.
Data SubscriptionUse binlog to monitor database update operations in real timeReal-time data analysis for retail companies: By parsing binlog, sales record insertion operations are captured and transmitted to the data analysis platform in real time for sales trend analysis.

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:

Recording Formatdescribeadvantageshortcoming
Statement-Based Logging (SBL)Record the executed SQL statement itselfSmall log volume, suitable for simple SQL operationsIn some cases, data consistency may not be guaranteed, such as non-deterministic functions (such as NOW())
Row-Based Logging (RBL)Record the specific changes of each row of dataMore precise, suitable for complex operations and replicationLarge log volume, high disk and network overhead
Mixed Logging (ML)Switch between Statement and Row modes depending on the situationTaking Advantage of BothRelatively complex to implement and manage

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.