preface

What is binlog

There are various types of logs in mysql, which record mysql, including startup, operation, connection, change and other operations, and binlog is the most important log. It records the changes of all MySQL data and stores it on disk in binary form

binlg records all table structure changes (CREATE, ALTER TABLE...) and table data modifications (INSERT, UPDATE, DELETE...), which are recorded in the form of events, as well as the time consumed by the execution of statements

The official website explains events as follows: the contents stored in binary logs are called events, and each database Update operation (Insert, Update, Delete, excluding Select) corresponds to an event

The main purpose of binlog is replication and recovery. Through binlog, the data of a MySQL database server (master) can be copied to one or more other MySQL database servers (slave) to realize disaster recovery, horizontal expansion, statistical analysis, remote data distribution and other functions.

The data connection between mysql and other components can be realized through binlog

Mode of use

Enable binlog

First, check whether mysql enables binlog synchronization

show variables like 'log_bin';

It is closed by default. If you need to open it at this time, you need to edit the mysql configuration file. Normally, it is in the etc directory

If not, check the location with which mysqld first

vi /etc/my.cnf

Modify my CNF configuration

mysqld]# Enable binloglog bin = MySQL bin

You can also use SET SQL_LOG_BIN=1 command to enable binlog through set SQL_ LOG_ The bin = 0} command disables binlog.

However, after binlog is enabled, MySQL must be restarted to take effect

Common binlog commands

# Enable binlog log show variables like 'log_bin';
# View detailed binlog log configuration information show global variables like '%log%';
# View binlog directory show global variables like "%log_bin%";
# View the binlog file log list show binary logs;
# Check the latest binlog log file name and Position (pos end point of operation event) show master status;
# Refresh log Log, and a new number will be generated from this moment binlog log file# Whenever the mysqld service is restarted, this command will be automatically executed to refresh the binlog log; Adding the - F option when mysqldump backs up data will also refresh the binlog log; flush logs;
# View the contents of the first binlog file show binlog events  
# View the contents of a specific binlog file show binlog events in 'master 000001';
# Reset (clear) all binlog logs reset master;
# Delete the relay log of the slave and reset the slave;
# Delete the binlog log file purge master logs before '2022-02-22 00:00:00' in the log index before the specified date;
# Delete the specified log file purge master logs to 'master 000001';

Using binlog

1. Use the built-in view command method of mysqlbinlog:
Note: binlog is a binary file. Ordinary file viewer cat more vi cannot be opened. You must use the built-in mysqlbinlog command to view it. The binlog log is under the data of the same directory

For example, I just built a watch

CREATE TABLE IF NOT EXISTS `fungmu_table`(   `id` INT UNSIGNED AUTO_INCREMENT,   `name` VARCHAR(100) NOT NULL,   `age` VARCHAR(40) NOT NULL,   `create_date` DATE,   PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then use the command

mysqlbinlog /usr/local/mysql/data/mysql-bin.000001

...............................................................................SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 423#220224 22:23:40 server id 1  end_log_pos 738 CRC32 0x90972f69  Query thread_id=7 exec_time=0 error_code=0 Xid = 13use `funmu`/*!*/;SET TIMESTAMP=1645712620/*!*/;/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;CREATE TABLE IF NOT EXISTS `fungmu_table`(   `id` INT UNSIGNED AUTO_INCREMENT,   `name` VARCHAR(100) NOT NULL,   `age` VARCHAR(40) NOT NULL,   `create_date` DATE,   PRIMARY KEY ( `id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;...............................................................................server id 1 service number of database host; end_log_pos 738 POS point

2.mysql command line

If you directly view the binlog log, there are many full-text contents, and it is not easy to distinguish and view the pos information. At this time, you can use the mysql command line

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

IN 'log_name 'specifies the binlog file name to query (if not specified, it is the first binlog file)
FROM pos specifies which pos starting point to start from (if not specified, it starts from the first pos point of the whole file)
LIMIT [offset,] offset (0 if not specified)
row_count = total number of queries (all rows if not specified)

Log_name: mysql-bin.000001  # pos starting point:Pos: 11197 # Event type: QueryEvent_type: Query # Identifies which server is executing Server_id: 1# pos End point:11308(I.e. downstream pos starting point)End_log_pos: 11308# Executed sql statement Info: use ` Fangmu ';  INSERT INTO `fungmu_table` VALUES (1,'fangmu','18','2022-02-22')    

Log format of Binlog

The format of events recorded in the binary log depends on the binary recording format. Three format types are supported:

STATEMENT based replication (SBR)

ROW: ROW based replication (RBR)

MIXED: MIXED based replication (MBR)

Before MySQL 5.7.7, the default format was state. In MySQL 5.7.7 and later, the default value was ROW. Specify the format of log, e.g. log format = default log, log format = default log.

Statement

Every sql that will modify data will be recorded in binlog

Advantages: there is no need to record the changes of each line, which reduces the amount of binlog logs, saves IO and improves performance. Disadvantages: since only execution statements are recorded, in order for these statements to run correctly on the slave, it is also necessary to record some relevant information of each statement during execution, so as to ensure that all statements can get the same results in the slave as when they are executed on the master side. In addition, the replication of mysql, such as the functions of some specific functions, and the consistency between slave and master will have many related problems.

Row

MySQL in version 5.1.5 began to support the replication of row level. It does not record the context related information of sql statements, but only saves which record has been modified.

Advantages: binlog can not record the context related information of the executed sql statement, but only record what the record has been modified to. Therefore, the log content of row will clearly record the details of data modification in each row. Moreover, there will be no problem that the stored procedures, function s, and triggers of triggers cannot be copied correctly under certain circumstances

Disadvantages: when all executed statements are recorded in the log, they will be recorded with the modification of each line, which may produce a large amount of log content.

Note: when setting the binary log format to ROW, some changes still use the statement based format, including all DDL statements, such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

Mixed

Since version 5.1.8, MySQL has provided Mixed format, which is actually the combination of Statement and Row.

In the Mixed mode, the general statement modification uses the statement format to save binlog. For example, for some functions, if the statement cannot complete the master-slave copy operation, the row format is used to save binlog. MySQL will distinguish the log format to be recorded according to each specific sql statement executed, that is, select one between statement and row.

Binlog structure and content

The log consists of a set of binary log files (binlog) plus an index file (index); Binlog is a collection of binary files. Each binlog starts with a magic number of 4 bytes, followed by a group of Events;

1. Magic number: 0xfe62696e corresponds to 0xfebin;

2.Event: each event contains two parts: header and data; The header provides the creation time of the event, which server and other information. The data part provides the specific information for the event, such as the modification of the specific data;

3. The first event is used to describe the format version of binlog file. This format is the format in which event is written to binlog file;

4. Other events are written according to the format version of the first Event;

5. The last Event is used to describe the next binlog file;

6. The index file of binlog is a text file, in which the content is the current binlog file list

Binlog common scenarios

1. mysql master-slave replication

2. mysql data recovery

3. Data synchronization, such as MySQL Binlog to kafka and elasticsearch based on Canal

Scan code concerns my official account of WeChat: Java architect's advanced programming to get the latest interview questions, e-books.

Focus on sharing Java technology dry goods, including JVM, SpringBoot, SpringCloud, database, architecture design, interview questions, e-books, etc. we look forward to your attention!