Shulou(Shulou.com)06/01 Report--

I read a big Oracle article before, but I also encountered it in the test environment, so I wrote it down easily.

Oracle big article links to http://blog.itpub.net/17203031/viewspace-1077770/

-text-

Background: DB test environment mistakenly deleted MySQL log file ib_logfile1

Environment: MySQL5.6.27

Analysis of the cause of the problem: the hand slipped _ (: slide "∠) _

General idea:

The most important thing is to calm down and don't mess around.

Introduction of file descriptors under Linux, direct excerpts from Oracle greatly describe

So the most important thing: calm down, don't restart the database casually, keep the operation site at that time.

On-site restore: simulation mistakenly deleted ib_logfile1 in the test environment

Then it is found that the data is still running normally and new data can be inserted.

Combined with the previous introduction of Linux's file descriptor, it can be determined that this file still exists.

Then try to find it, and first determine the process of MySQL ID:ps-ef | grep mysql

Then find the folder corresponding to the PID in / proc/fd (file name = pid) and take a look at the contents.

You can see that the file is still there, but it is marked as deleted. Since the file is still there, it is easy to do.

Why don't you copy the file back? No!

Since logs also have buffer (innodb_log_buffer_size), before copying them back, make sure that these logs are flushed to disk and that there are no new transactions operating the MySQL database when copying.

So execute flush tables with readlock; first and then flush logs

Then take a look at the newly generated binlog. In the older binlog, you can see the statement executed after "mistakenly deleted" ib_logfile1.

There is nothing in the newly generated binlog

Confirm that the old redo log has been written to disk and no new transactions are being executed, then copy back the "mistakenly deleted" file

Wrong scenario:

Suppose that when there is a problem at first, the database is closed, and eventually the log is gone, then there will be the following error when starting MySQL

Or there is something wrong with the content, (lazy here, forgot to copy a wrong logfile1 before browsing buffer, just add up the number with logfile0, and regard it as a wrong logfile._ (: mistake "∠) _. In essence, it should have the same effect)

Then copy back the logfile copied before and after processing, modify the owner and permissions of the file, and after starting MySQL, copy! Wrong report ~

Compare with the error message of copying a problematic logfile before (simulation operation: the data of log buffer is not flushed to disk, so the copied logfile is incorrect)

The LSN of the data file and the properly handled logfile are exactly the same.

Why is mysql activated when there is a problem with this ib_logfile?

Take a look at the information output by mysql after replacing the log file twice (above is the logfile that did not refresh the buffer, and below is the refreshed buff)

You can see that the database truncated these problematic logfile according to the data files refreshed to disk, and then regenerated the LSN of the new logfile.

Conjecture: after the LSN of the new logfile is generated, the tag bits in the data file also change from 3117292814 to 3117292824, which is why the error log is printed after the correct logfile is copied in the second time.

-end--

PS: whether you delete data files or log files by mistake, remember not to shut down the database, and make sure that all the cached data are flushed to disk during recovery.

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.