Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the use of binlog in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. Logical and physical expressions of binlog files and files. 1. Binlog files

Binlog files mainly include:

Mysql-bin.000001

Mysql-bin.index

Where:

The binlog file that is not purge in the server is saved in mysql-bin.index, which is kept in the form of "text".

2.binlog file format

Mysql-bin.index has nothing to say, let's just take a look at binary binlog files.

The binlog file format has the following characteristics:

Binlog is composed of event, and event is the logical smallest unit of binlog.

The first four bytes of the header are BINLOG_MAGIC (fe 62 69 6e)

The next four bytes are descriptor event: FORMAT_DESCRIPTION_EVENT

At the end of the file is log-rotation event: ROTATE_EVENT

Between these two event is a variety of different event, each event representing a different operation on the Master.

The following is an explanation of the basic key concepts:

BINLOG_MAGIC

Using hexdump-C to read the contents of mysql-bin.000005

Shown here in hexadecimal and ASCII codes, we can see that the first four bytes of binlog are fixed: fe 6269 6e, and the last three characters ASCII codes are bin, indicating that a binlog file is displayed. These four bytes become BINLOG_MAGIC.

Event

Example of what mysqlbinlog-vvv reads from mysql-bin.000011:

As you can see here, the first event is FORMAT_DESCRIPTION_EVENT, which records the version of this binlog (the version of binlog after MySQL 5. 0 is 4). The last event, ROTATE_EVENT, records the file name of the switch to the next binlog file.

Between them are a variety of other event, and the current event types are:

Enum Log_event_type {

UNKNOWN_EVENT= 0

START_EVENT_V3= 1

QUERY_EVENT= 2

STOP_EVENT= 3

ROTATE_EVENT= 4

INTVAR_EVENT= 5

LOAD_EVENT= 6

SLAVE_EVENT= 7

CREATE_FILE_EVENT= 8

APPEND_BLOCK_EVENT= 9

EXEC_LOAD_EVENT= 10

DELETE_FILE_EVENT= 11

NEW_LOAD_EVENT= 12

RAND_EVENT= 13

USER_VAR_EVENT= 14

FORMAT_DESCRIPTION_EVENT= 15

XID_EVENT= 16

BEGIN_LOAD_QUERY_EVENT= 17

EXECUTE_LOAD_QUERY_EVENT= 18

TABLE_MAP_EVENT = 19

PRE_GA_WRITE_ROWS_EVENT = 20

PRE_GA_UPDATE_ROWS_EVENT = 21

PRE_GA_DELETE_ROWS_EVENT = 22

WRITE_ROWS_EVENT = 23

UPDATE_ROWS_EVENT = 24

DELETE_ROWS_EVENT = 25

INCIDENT_EVENT= 26

HEARTBEAT_LOG_EVENT= 27

IGNORABLE_LOG_EVENT= 28

ROWS_QUERY_LOG_EVENT= 29

WRITE_ROWS_EVENT = 30

UPDATE_ROWS_EVENT = 31

DELETE_ROWS_EVENT = 32

GTID_LOG_EVENT= 33

ANONYMOUS_GTID_LOG_EVENT= 34

PREVIOUS_GTIDS_LOG_EVENT= 35

ENUM_END_EVENT

/ * end marker * /

}

Of course, some of the internal event has been abandoned. Let's not enumerate them one by one. Here are just a few common ones:

QUERY_EVENT: for specific SQL text. If in binlog_format=statement mode, all kinds of SQL, such as insert,update,delete, are recorded in Query event.

WRITE_ROWS_EVENT,UPDATE_ROWS_EVENT,DELETE_ROWS_EVENT: in binlog_format=row mode, the line information of the insert,update,delete operation is recorded in these three event.

In GTID_LOG_EVENT:5.6 's GTID mode, the GTID sequence number of each transaction is recorded in this EVENT.

In GTID mode of PREVIOUS_GTIDS_LOG_EVENT: 5. 6, this event records the GTID collection of all transactions that MySQL has performed before the binlog is generated.

2. How does slave io connect to Master in the source code

Here we mainly describe the function call relationship in the source code.

1. How does slave register and request the binlog of master

The entry function corresponding to the slave io thread is sql/rpl_slave.cc:handle_slave_io ()

The core of the function is to do the following three things:

Safe_connect (thd, mysql, mi)

Register_slave_on_master (mysql, mi, & suppress_warnings)

Request_dump (thd, mysql, mi, & suppress_warnings)

Event_len= read_event (mysql, mi, & suppress_warnings)

In other words, it first connects to master MySQL with a standard connection, then registers itself with master, then calls request_dump to request binlog data from master, and the last event is read and stored in the local relay log.

Safe_connect

The standard way to connect to MySQL, and the connector of MySQL c also connects to MySQL server in this way.

Register_slave_on_master

Slave submits its slave_id,IP, port, and user name to Master to register itself with Master.

Request_dump

According to GTID, if it is GTID mode, then pass the locally executed GTID collection and other related information to master;. If it is not GTID mode, then pass master log file and Pos to the main library. Refer to the next section on how the main library sends the event of binlog based on this information.

Read_event

Read_event calls cli_safe_read (), and cli_safe_read () calls my_net_read (), waiting for the main library to send binlog data.

In other words, read_event passively receives messages from the main library from the network.

2. How does master handle binlog requests from slave

The core function of MySQL to handle various commands is: sql/sql_parse.cc:dispatch_command

This function determines what to do based on the user's request

COM_REGISTER_SLAVE calls register_slave (thd, (uchar*) packet, packet_length) to register slave.

COM_BINLOG_DUMP_GTID calls com_binlog_dump_gtid (thd, packet, packet_length)

COM_BINLOG_DUMP calls com_binlog_dump (thd, packet, packet_length)

Here we take com_binlog_dump as an example to show how master sends binlog event to slave.

The core code of com_binlog_dump is:

Kill_zombie_dump_threads & slave_uuid)

Mysql_binlog_send (thd, thd- > strdup (packet + 10), (my_off_t) pos, NULL)

Kill_zombie_dump_threads () function: if the new server_id is registered with the same slave, master removes the binlog dump thread that matches the server_id of that slave

Com_binlog_dump () calls mysql_binlog_send () to open the file, move the file pointer to the specified location, read the file, and send each event to slave in the order of events.

To sum up

MySQL replication requires that slave first register with Master, then submit binlog and POS to Master, and request to send binlog. After receiving the request, Master first does a series of verification, opens the local binlog file, and sends it to slave in the order of the internal event.

Third, how the SQL thread reads the binary information in the source code and applies it to the local MySQL

Sql threads introduced db-level parallelism in 5.6, so there are two entrances

one

two

Handle_slave_worker

Handle_slave_sql

The handle_slave_ worker thread is the main work function, and the handle_salve_sql function, as the coordinator, starts and allocates the worker thread.

The handle_slave_sql function mainly calls slave_worker_exec_job.

The main functions of slave_worker_exec_job are:

Job_item= pop_jobs_item (worker, job_item); ev= static_cast (job_item- > data)

Error= ev- > do_apply_event_worker (worker)

What this function does is actually get a specific event (ev) from handle_salve_sql (), and then call ev- > do_apply_event_worker (worker) to make use of the polymorphic nature of C++ to call the real event's do_apply_event virtual function, so that different event operations can be done locally.

Here we need to review the concept of event. Event is the smallest unit of binlog, and the parent class of all event is Log_event (abstract base class), which defines a series of virtual functions, including the functions we call here:

Here is an example of a Write_rows_log_event corresponding to an insert statement to briefly illustrate how the data is applied to the local MySQL.

Both Write_rows_log_event,Update_rows_log_event and Delete_rows_log_event 's do_apply_event call the do_apply_event of its base class, Rows_log_event.

The main do_apply_event functions of Rows_log_event are as follows:

Masks table = const_cast (rli)-> m_table_map.get_table (m_table_id)

Error= (this- > * do_apply_row_ptr) (rli)

Get_table () first gets the information about the corresponding table from table map, and then calls the function pointed to by the do_apply_row_ptr function to apply the event corresponding operation to the local MySQL.

The do_apply_row_ptr function pointer may point to the following different functions:

Do_hash_scan_and_update

Do_index_scan_and_update

Do_table_scan_and_update

Do_apply_row

Write_rows_log_event is insert and doesn't have to look up data, so it calls do_apply_row.

The main function of do_apply_row is to call do_exec_row. . Sweat

Do_exec_row is implemented by Write_rows_log_event itself, and its main function is to call write_row. . Sweat

Write_row is also implemented by Write_rows_log_event itself, and its main function is: masked table-> file- > ha_start_bulk_insert (estimated_rows); that is, it gives this line of data directly to the storage engine, which inserts the data.

4. How does MySQL save different data types in binary system under binlog_format=ROW format

The binary representation of various data types of MySQL is described in detail in the sql/log_event.cc:log_event_print_value () function. The excerpt is as follows.

Here are a few interesting places:

MYSQL_TYPE_STRING type, whose length occupies a variable number of bytes. If the string length is less than 255, it takes up 2 bytes, so when defining fields of type String, MySQL is a threshold. If the field cannot exceed 255bytes, it is not recommended to define more than 255bytes, otherwise MySQL will waste a byte when storing data.

MYSQL_TYPE_DATETIME type. Its storage format is to store the year-month-day hours: minutes: seconds in order, for example: 2015-10-1022: 45:55 is stored as 20151010224545. This storage format is wasteful, so it takes up more bytes than timestamp.

This is the end of this article on "what is the use of binlog in MySQL?". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.