The way load statements are recorded in binlog

Mysql > mysql > load statement in binlog

Consolidate from actual combat 45 speak + own some understanding

A: binlog_format = statement

After the load statement is recorded in the binlog, how can we replay it in the standby database?

Since the /server_tmp/t.csv file is only stored on the host where the primary database is located, writing the file to the binlog will cause primary and standby synchronization to stop if it is not available on the standby database’s local machine during the standby database execution.

So, the full flow of this statement actually looks something like this.

When the main library is finished, the contents of the /server_tmp/t.csv file are written directly to the binlog file.

LOAD DATA LOCAL INFILE ‘/ TMP /SQL_LOAD_MB-1-0’ INTO TABLE DB2.T

Pass the binlog to the standby database.

When the standby database’s Apply thread executes the transaction log:

A. First read out the contents of t.csv file from binlog and write it to the local temporary directory/TMP/sql_load_mb-1-0;

B. Then execute LOAD DATA statement to insert the same game data into DB2.T table of standby database as in primary database.

The execution process is shown in Figure 2:

Figure 2 Synchronization flow of Load Data

Note that there is a “local” in the load data statement executed by the standby database. It means “load the contents of the local file/TMP/sql_load_MB-1-0 on the machine where the client is executing this command into the target table DB2.T”.

That is, the load data command can be used in two ways:

This file must be in the directory or subdirectory specified by secure_file_priv.

Add “local” to read the client’s file, as long as the MySQL client has permission to access the file. At this time, the MySQL client will first pass the local file to the server, and then execute the above load data process.

2: binlog_format = row

If binlog_row_image=full, then you can see all the fields that record the data in each row after formatting the binlog!

Something like this:

INSERT INTO liuwenhe.items_1

SET

@ 1 = 10009 / INT meta=0 nullable=0 is_null=0 /

Mysql > restore a statement with a binlog format

After executing the following LOAD statement:

root@localhost : (none) 13:11:59>load data infile ‘/var/lib/mysql-files/1.txt’ into table liuwenhe.items_3;

If you want to restore data using binlog, if you want to restore data using binlog_format=statement, if you want to restore data using mysqlbinlog-vv, if you want to restore data using binlog, if you want to restore data using binlog_format=statement, if you want to restore data using mysqlbinlog-vv, you can see the following:

LOAD DATA LOCAL INFILE ‘/tmp/SQL_LOAD_MB-3-0’ INTO TABLE liuwenhewww.sangpi.com.items_3 FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ‘\’

LINES TERMINATED BY ‘\n’ (itemid)

If you want to use binlog for incremental recovery, can you find the file/TMP/sql_load_mb-3-0?

When you run mysqlbinlog mysql-bin.000154, you automatically read the contents of the binlog file and write them to/TMP/sql_load_MB-3-0. This means that when you run mysqlbinlog, you automatically write the contents of the file into/TMP/sql_load_MB-3-0. Automatically generate SQL_LOAD_MB-3-0 file in the corresponding directory! Then you can recover!

Summary: So note that if you are using binlog for incremental recovery, you must make sure that the machine on which you are formatting the binlog is the same machine on which you are executing the restore command, because formatting will generate temporary files corresponding to load