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

What this article shares with you is the analysis of how to restore MySQL power outage. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article. Let's take a look at it.

Today, a netizen asked me a question about the recovery of MySQL. The screenshots provided are as follows.

For this problem, it is still possible in some power outage scenarios. First of all, I want to confirm whether it is an online business or a test environment, which has a great impact on online business. If the database cannot be started, the first task is to start the database, and then check the extent of the lost data on this basis and arrange the data repair.

Of course, from my point of view, how to reproduce this problem quickly. I used my own script to quickly build a test master-slave environment (https://github.com/jeanron100/mysql_slaves, a later Daniel suggested using Python, which I was thinking about recently), which can be done in minutes.

We create a table test that specifies two fields of id,name. Then open the explicit transaction.

Create table test (id int primary key,name varchar (30) not null)

Explicitly open a transaction:

Begin

Insert into test values (1)

Insert into test values (2)

Insert into test values (3)

If you do not submit it, we will directly check the service process of mysql and Kill it directly. The double-1 indicator is enabled by default. We directly simulate power outage and restart to see the processing in the background:

2017-09-13 15:05:11 35556 [Note] InnoDB: Highest supported file format is Barracuda.

2017-09-13 15:05:11 35556 [Note] InnoDB: The log sequence numbers 1625987 and 1625987 in ibdata files do not match the log sequence number 1640654 in the ib_logfiles!

2017-09-13 15:05:11 35556 [Note] InnoDB: Database was not shutdown normally!

2017-09-13 15:05:11 35556 [Note] InnoDB: Starting crash recovery.

2017-09-13 15:05:11 35556 [Note] InnoDB: Reading tablespace information from the .ibd files...

2017-09-13 15:05:11 35556 [Note] InnoDB: Restoring possible half-written data pages

2017-09-13 15:05:11 35556 [Note] InnoDB: from the doublewrite buffer...

InnoDB: 1 transaction (s) which must be rolled back or cleaned up

InnoDB: in total 3 row operations to undo

InnoDB: Trx id counter is 2304

2017-09-13 15:05:11 35556 [Note] InnoDB: 128rollback segment (s) are active.

InnoDB: Starting in background the rollback of uncommitted transactions

2017-09-13 15:05:11 7f5ccc3d1700 InnoDB: Rolling back trx with id 1806, 3 rows to undo

2017-09-13 15:05:11 35556 [Note] InnoDB: Rollback of trx with id 1806 completed

2017-09-13 15:05:11 7f5ccc3d1700 InnoDB: Rollback of non-prepared transactions completed

2017-09-13 15:05:11 35556 [Note] InnoDB: Waiting for purge to start

2017-09-13 15:05:11 35556 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.14-rel62.0 started; log sequence number 1640654

2017-09-13 15:05:11 35556 [Note] Recovering after a crash using binlog

2017-09-13 15:05:11 35556 [Note] Starting crash recovery...

2017-09-13 15:05:11 35556 [Note] Crash recovery finished.

2017-09-13 15:05:11 35556 [Note] RSA private key file not found: / U01/mysql_test/m1//private_key.pem. Some authentication plugins will not work.

2017-09-13 15:05:11 35556 [Note] RSA public key file not found: / U01/mysql_test/m1//public_key.pem. Some authentication plugins will not work.

2017-09-13 15:05:11 35556 [Note] Server hostname (bind-address):'*'; port: 21804

You can see that the backend detected the last abnormal downtime, and then enabled crash recovery. InnoDB detected that the log LSN is 1625987 and the LSN of the system data file ibd is 1625987, which does not match the LSN in the ib_logfiles. This is followed by a series of recoveries, rollforward, recovery, rollback. Finally, the data in the table is empty, which proves that the previous transactions have been rolled back.

Therefore, based on the above situation, we understand that when the transaction is opened, basically this problem will not arise, and when will the initial error be thrown?

We continue testing and start an explicit transaction without committing.

Begin

Insert into test values (1)

Insert into test values (2)

Insert into test values (3)

Then kill the mysql service process, find the mysql data directory, and delete the redo file. When we're done, we restart the database.

At this time, an error similar to the screenshot was thrown.

2017-09-13 16:05:14 36896 [Note] InnoDB: Highest supported file format is Barracuda.

2017-09-13 16:05:14 7f73450a97e0 InnoDB: Error: page 7 log sequence number 1627722

InnoDB: is in the future! Current system log sequence number 1626124.

InnoDB: Your database may be corrupt or you may have copied the InnoDB

InnoDB: tablespace but not the InnoDB log files. See

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

InnoDB: for more information.

The scope of this problem is not obvious at present, because in spite of this, we can still write data.

Mysql > insert into test values (1)

Query OK, 1 row affected (0.04 sec)

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | a |

+-+ +

1 row in set (0.00 sec)

One data parameter that needs particular attention about crash recovery is innodb_force_recovery, which defaults to 0. If it is a non-zero value (range 1-6), it will have the following range of influence.

1 (SRV_FORCE_IGNORE_CORRUPT): ignore checked corrupt pages.

2 (SRV_FORCE_NO_BACKGROUND): blocking the running of the main thread, if the main thread needs to perform full purge operations, will result in crash.

3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback operation is performed.

4 (SRV_FORCE_NO_IBUF_MERGE): merge operations that insert buffers are not performed.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): without viewing the redo log, the InnoDB storage engine treats uncommitted transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO): roll forward is not performed.

Of course, the setting modification of this parameter requires a restart of the MySQL service.

Mysql > set global innodb_force_recovery=2

ERROR 1238 (HY000): Variable 'innodb_force_recovery' is a read only variable

Suppose we set it to 2 and repeat the problem again, you will find that the database can be started temporarily, but the data can only be queried and the DML operation will be thrown wrong.

Mysql > select * from test

Empty set (0.00 sec)

Mysql >

Mysql > insert into test values (1)

ERROR 1030 (HY000): Got error-1 from storage engine

By evaluating the value of force_recovery according to this range of influence, we can make a trade-off accordingly. If the MySQL service does not start properly, you can modify this parameter value to adjust it to meet the basic problem of service sustainability. Then the important data are derived after the evaluation.

The above is how to analyze the recovery of MySQL power outage. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.