2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
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.DML
Operations (such as INSERT, UPDATE, DELETE, etc.), butSELECT is not includedwaitDQL
Read-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
The main functions of Bin Log include: master-slave replication, data recovery, data backup, data subscription
Application Scenario | concept | Case |
---|---|---|
Master-slave replication | The 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 Recovery | Use binlog records to reverse SQL statements to recover data | Financial system: Administrators can restore accidentally deleted transaction records by parsing binlogs to ensure data integrity and avoid financial losses. |
data backup | Binlog is used for incremental backup, saving backup time and space costs | Social 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 Subscription | Use binlog to monitor database update operations in real time | Real-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.
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.
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 Format | describe | advantage | shortcoming |
---|---|---|---|
Statement-Based Logging (SBL) | Record the executed SQL statement itself | Small log volume, suitable for simple SQL operations | In 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 data | More precise, suitable for complex operations and replication | Large log volume, high disk and network overhead |
Mixed Logging (ML) | Switch between Statement and Row modes depending on the situation | Taking Advantage of Both | Relatively complex to implement and manage |
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)
To enable Bin Log, you need to add the following line to the MySQL configuration file (usuallymy.cnf
ormy.ini
) and configure as follows:
[mysqld]
log-bin=mysql-bin
server-id=1
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)
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.Common commands for managing Bin Log files:
View Bin Log file list:
SHOW BINARY LOGS;
View Bin Log file size:
SHOW MASTER STATUS;
Delete old Bin Log files:
PURGE BINARY LOGS TO 'mysql-bin.000010';
or:
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
-- 创建一个名为 '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');
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)
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
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
Bin Log events can be replayed into the MySQL server to achieve data recovery:
mysqlbin logbinlog.000001 | mysql -u root -p
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:
Configure the main library:
[mysqld]
log-bin=mysql-bin
server-id=1
Configure the slave library:
[mysqld]
server-id=2
relay-log=relay-bin
Create a replication user on the primary database:
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
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;
Checking the replication status:
SHOW SLAVE STATUSG
You can monitor the replication status with the following command:
SHOW SLAVE STATUSG
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.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.
Perform a full backup:
mysqldump --all-databases --master-data=2 > full_backup.sql
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;
Backing up Bin Log:
mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql
Restoring a full backup:
mysql < full_backup.sql
Apply incremental backups:
mysql < incremental_backup.sql
MySQL Bin Log
It 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.