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

How to analyze binlog and redo in MySQL, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

There is a small problem that many people may have thought of, that is, since there is already binlog in MySQL, why do you still need redo? this problem seems to be very simple, but there are still a lot of things worth paying attention to in detail.

For data recovery, especially in the case of abnormal downtime, when starting again, how to recover and the data basis for recovery is particularly important. In MySQL, there is checkpoint technology to do a basic checkpoint control, that is, LSN. For transactional databases, the metropolis will adopt the strategy of write ahead log, that is, when the current transaction commits, write redo first and modify the corresponding page. If the data is lost due to downtime, the recovery of the data can be completed by redoing the log, but what is special about MySQL and some other databases is this binlog, which is not implemented in checkpoint. We can imagine a situation in which when a transaction commits, the information is written to redo, and in the process of this operation, the writing of binlog is also synchronous. If the information of redo may not be refreshed to disk in redo log buffer, and downtime occurs, the data transferred by binlog may be applied to the data from the slave database, and before it is submitted in redo, there may be data inconsistencies. If you start the database in an abnormal state, the data recovery mode will be enabled, and the data from the slave database may be inconsistent.

This situation sounds special, but it is helpful for us to understand the problems with redo and binlog. Let's do a test, which is only used for debugging in the test environment.

First of all, in order to reduce the impact of data changes, let's first do a flush logs operation to keep as few new log contents as possible.

Switch logs on the main database:

Flush logs

To check the situation of binlog, you can use show master status or show binary logs.

Mysql > show master status\ G

* * 1. Row *

File: binlog.000014

Position: 230

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 1bb1b861-f776-11e6-be42-782bcb377193:1

25ee7482-07cd-11e7-a40c-0026b935eb76:1-1502468

1 row in set (0.00 sec)

We got the process number of the mysql service.

# ps-ef | grep-w mysqld | grep-v grep | awk'{print $2}'

1751

We create a table with test fields of id and name (id int, name varchar (20))

Four pieces of data already exist as follows:

Mysql > select * from test.test

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

| | 3 | cc |

| | 4 | dd |

+-+ +

4 rows in set (0.00 sec)

Viewing data from the library is synchronized with the main library at this time. This is a basis for our testing.

We can do simple debugging through gdb.

# gdb-p 1751

We immediately enter debug mode, and we can set a breakpoint.

We insert 2 pieces of data before setting the breakpoint, and there are 5 pieces of data from the library at this time.

Mysql > insert into test values (5)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into test values (6)

Query OK, 1 row affected (0.00 sec)

Then set a breakpoint, which is the key.

(gdb) b MYSQL_BIN_LOG::process_commit_stage_queue

Breakpoint 1 at 0xec73ca: file / export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/sql/binlog.cc, line 8430. (2 locations)

Then try to insert a record in the main library

Insert into test values (7)

There is no doubt that this sentence will hang. Because our breakpoint is at the time of submission.

At this point, let's take a small step forward and use c, which means continue.

(gdb) c

Continuing.

[Switching to Thread 0x409c0940 (LWP 1798)]

Breakpoint 1, MYSQL_BIN_LOG::process_commit_stage_queue (this=0x1e8ba00, thd=0xec254e0, first=0xec254e0)

At / export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/sql/binlog.cc:8430

8430 / export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/sql/binlog.cc: No such file or directory.

In / export/home/pb2/build/sb_0-21378219-1480347226.17/mysql-5.7.17/sql/binlog.cc

At this time, the SQL statement is still in the state of hang, but you can see that the stack, binlog, has been written.

Data changes are applied to the slave library at this time, and there are 7 pieces of data at this time.

We can also grab the binlog to see if the data has been written in it.

[root@grtest S1] # / usr/local/mysql/bin/mysqlbinlog-vv binlog.0000014

You can clearly see the following statement:

...

BINLOG'

DK3KWBPqDAAALgAAAHcDAAAAAOUAAAAAAAEABHRlc3QABHRlc3QAAgMPAhQAAw==

DK3KWB7qDAAAJwAAAJ4DAAAAAOUAAAAAAAEAAgAC//wHAAAAAmdn

'/ *! * /

# INSERT INTO `test`.`test`

# SET

# @ 1room7 / * INT meta=0 nullable=1 is_null=0 * /

# @ 2 meta=20 nullable=1 is_null=0 meta=20 nullable=1 is_null=0 / * VARSTRING (20)

# at 926

# 170316 23:19:40 server id 3306 end_log_pos 953 Xid = 55

Com _ MIT _ blank /

At this time, we simulate the downtime and kill the process.

Kill-9 1751 29617

Then change the name of binlog and close log_bin.

[root@grtest s1] # mv binlog.000014 binlog.000014.bak

After starting it again, you will find that there are still 6 pieces of data in the master library and 7 pieces of data in the slave library.

And if we change binlog back, open log_bin and start the main library.

Mv binlog.000014.bak binlog.000014

Then look at the data again, and you will find that the data of the master-slave library is actually different at this time. There is obviously more data from the database, which reflects our initial idea from one side. In the case of abnormal downtime, the data of redo has not been refreshed into the redo file, and the binlog has been written at this time, which leads to the inconsistency of master-slave data at such a critical point.

Of course, I did the test with a debugging attitude, and there are still a lot of skills to consolidate.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.