666! MySQL binlog format

There are two important MySQL logs: binlog, redo log, and undo log.

1. binlog

MySQL/MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8 / MySQL8

The binlog contains all DDL and DML statements in the form of events. It also contains the elapsed time of the statement execution. Note:

A binlog is a logical log that contains the original logic of an SQL statement, such as +1 for a field. Note that this is different from a physical log called a redo log (what changes were made on a data page).
When the binlog file is full, it automatically switches to the next log file and does not overwrite the previous log. This is also different from the redo log, where redo logs are written in a loop.
Generally, when configuring the binlog file, you can specify the validity period of the binlog file. After the validity period expires, the log file will be automatically deleted to avoid occupying too much storage space.

According to the MySQL documentation, there is about a 1% performance loss when binlog is enabled, but this is acceptable. In general, binlog has two important uses:

For MySQL primary/secondary replication: Enable the binlog function on the host. The host synchronizes the binlog function to the secondary host. The secondary host uses the binlog function to synchronize data between the host and secondary host.
MySQL data recovery, by using the mysqlBinlog tool combined with the binlog file, you can restore data to a time in the past.

2. Configuration binlog

For the sake of demonstration, Songo here installed MySQL in Docker, we use this as an example to start today’s demonstration. If you do not know how to use Docker, you can reply to Docker in the background of the official account, which has a tutorial written by Songgo.

MySQL > install MySQL in docker; MySQL > install MySQL in docker;

OFF means that binlog is OFF, not on.

You can view the format of binlog logs by running the following command:

As you can see, the binlog format is ROW.

So here’s the problem, the format of the binlog.

2.1 Binlog format

Binlog has three formats:

Statement (Statement-based Replication,SBR) : Each SQL Statement that modifies data is recorded in a binlog.
Row (ROW-based Replication,RBR) : The SQL statement context information is not recorded. Only the records that have been modified are saved.
Mixed (mixed-based Replication,MBR) : a mixture of Statement and Row.

2.1.1 the Statement

In Statement mode, only SQL statements are recorded and data changes in each row are not recorded. Therefore, the number of binlog logs is greatly reduced and I/O operations are avoided, improving system performance.

However, because the Statement schema only records SQL, if some SQL contains functions, inconsistent execution results can occur. The uuid() function, for example, generates a random string each time it is executed. The uUID is recorded in the master, and when synchronized to the slave, it is executed again to obtain a different result.

Therefore, data consistency issues arise when using Statement format.

2.2.2 Row

Starting with MySQL5.1.5, binlog introduced the Row format, which does not record the context of an SQL statement, but only how a particular record has been modified.

Row logs clearly record the details of data changes in each Row, so that data cannot be copied as in the Statement.

A large number of logs are generated for batch UPDATE, delete, and ALTER operations. A large number of logs are generated to record data changes in each Row, which may cause I/O performance problems.

2.2.3 Mixed

Since MySQL5.1.8, MySQL has introduced a Mixed format, which is essentially a combination of Statement and Row.

In Mixed mode, the system automatically determines whether Statement or Row should be used. For general Statement modification, Statement format is used to store binlog. For some statements that cannot accurately replicate the primary and secondary data, the Row format is used to save binlogs.

In Mixed mode, MySQL selects a different log format for each particular SQL Statement executed, that is, between Statement and Row.

2.2 configuration

Let’s look at the configuration of binlog.

2.2.1 open binlog

Enable binlog to modify the MySQL configuration file mysqld. CNF in the /etc/mysql.conf. d directory of the container.

For this configuration file, we make the following changes:

# this parameter indicates that binlog is enabled. Log-bin =javaboy_logbin # set maximum bytes of a binlog file # set maximum 100MB max_binlog_size=104857600 # Set binlog Validity period of documents (Unit: #binlog-do-db=javaboy_db #binlog does not record the update of the specified database (used for the primary/secondary replication) #binlog-ignore-db=javaboy_no_db # Write cache number of times, flush a disk, default 0 indicates that this operation is determined by the operating system based on its own load # 1 indicates that each transaction commits to write disks immediately, Sync_binlog =0 # select a unique id for the current service (required after MySQL5.7)Copy the code

The meaning of each configuration has been explained by Songo in gaze. Screenshot below:

After the configuration is complete, run the following command to restart the mysql container (mysql1 is my container name) :

docker restart mysql1
Copy the code

After the restart, run show variables like ‘log_bin%’ again; You can see that binlog is enabled.

In addition to the log_bin variable, there are two other variable names worth paying attention to:

Log_bin_basename: This is the name prefix of the binlog file that will be generated in the future, in other words, the name of the binlog file that will be generated based on the configuration you have seen so farjavaboy_logbin.xxxThis file will be used to record all DDL and DML statement events.
Log_bin_index: This is the binlog index file that holds all binlog directories, as there may be multiple binlogs. We can take a look at the currentjavaboy_logbin.indexFile:

As you can see, there is currently only one logbin file.

2.2.2 modify binlog_format

There are several variations of binlog_format:

Change the binlog_format of the current session. This change is valid only for the current session:

You can also change the global binlog_format. This change is invalid when MySQL restarts:

If you want to fix this thing once and for all, can modify the/etc/mysql/mysql. Conf. D/mysqld. CNF configuration file, in the configuration file, add binlog_format options, as follows:

This is a permanent modification.

3. Common binlog operations

Let’s take a look at some common binlog commands.

View all binlog logs

We can view the list of binlog logs in the following way:

show master logs;
Copy the code

As you can see, I currently have only one log file named javaboy_logbin.000001. File_size indicates that the size of this file is 154 bytes.

Checking master Status

MySQL > alter table MySQL > alter table MySQL > alter table MySQL > alter table MySQL > alter table MySQL > alter table MySQL

You can see the name of the latest binlog file and the Position value of the last action event (more on this later).

Refresh the binlog

Normally, when a binlog is full, it automatically switches to the next binlog. However, you can run flush logs to flush the binlog manually. After flushing the binlog manually, a new binlog file is generated. All subsequent binlog logs will be recorded in the new file. As follows:

As can be seen from the above figure, after refreshing the log, we run show master logs to check the log file, and find that there is a new log file, and then run show master status to check the latest log file information. The discovery is changed to Javaboy_logbin.000002.

Reset the binlog

The reset master command resets the binlog file to record logs starting from 000001. However, this command does not work if one or more slave machines are running on the current host. The host has cleared the binlog and cannot find the binlog error from the opportunity log.

Check the binlog

Binlog is a binary log file, so if you open it directly, you will not be able to read it:

I didn’t see anything useful.

Mysqlbinlog = mysqlbinlog; mysqlbinlog = mysqlbinlog;

It looks messy, but look closely and there are signs. Since I have a newly installed database, I just created a library named Javaboy, then created a table named User and added two pieces of data, and did nothing else, so the script to create the library can actually be found in a bunch of files.

One end_log_pos point in the generated log file is the pos point of the log file, which will be useful for data recovery in the future.

This is not a very user-friendly way to view logs. We say that binlog is logged by events, so it would be better if we could view logs by events. Let’s look at the following command:

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
Copy the code

This means viewing the binlog as an event. There are several parameters involved:

Log_name: Specifies the name of the binlog file to be queried. If this parameter is not specified, the earliest binlog file is queried.
Pos: the point of pos from which to view the log. Every operation recorded in the binlog has a point of pos. This means that we can specify the operation from which to view the log.
Offset: This is the offset. If not specified, the default value is 0.
Row_count: How many rows to view, if not specified, all rows to view.

Let’s look at a simple example:

show binlog events in 'javaboy_logbin.000001';
Copy the code

This is much clearer, we can see all the previous operations, for example:

A library was created between Pos 219-322.
A table is created between Pos 387-537.
Added a record between Pos 677-780.
.

This is essentially a Row format binlog.

5. Summary

In the next article, Songo will use two specific cases to demonstrate the problems existing in different binlog_formats