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

How to do MySQL handle recovery simple attempt, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Today, I suddenly think of a problem, that is, the recovery of ibdata, if we briefly simulate, we will find that it is still quite interesting.

First of all, we get two parameter values, one is the index of the dirty page, and the other is the directory of the data file.

Mysql > show variables like'% pct%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_buffer_pool_dump_pct | 25 | |

| | innodb_compression_failure_threshold_pct | 5 | |

| | innodb_compression_pad_pct_max | 50 | |

| | innodb_max_dirty_pages_pct | 75.000000 | |

| | innodb_max_dirty_pages_pct_lwm | 0.000000 | |

| | innodb_old_blocks_pct | 37 | |

+-+ +

6 rows in set (0.01 sec)

Mysql > show variables like 'datadir'

+-+ +

| | Variable_name | Value |

+-+ +

| | datadir | / home/data/s1/ |

+-+ +

1 row in set (0.00 sec)

The documents at this time are as follows:

[root@grtest s1] # ll ib*

-rw-r- 1 mysql mysql 413 Jun 20 14:01 ib_buffer_pool

-rw-r- 1 mysql mysql 12582912 Jun 20 14:01 ibdata1

-rw-r- 1 mysql mysql 50331648 Jun 20 14:01 ib_logfile0

-rw-r- 1 mysql mysql 50331648 Jun 20 14:01 ib_logfile1

-rw-r- 1 mysql mysql 12582912 Jun 20 14:02 ibtmp1

Among them, ib_buffer_pool is a new feature of 5. 7, which is not opened yet, two redo logs and one temporary file.

We can test the damage and combine it with the transaction.

Mysql > create database test

Query OK, 1 row affected (0.00 sec)

Mysql > use test

Database changed

Mysql > create table test (id int)

Query OK, 0 rows affected (0.01 sec)

Start a transaction manually, but do not commit.

Mysql > start transaction

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test values (1000)

Query OK, 1 row affected (0.01sec)

There is no commit at this time, so there is currently no matching record in the binlog.

# mysqlbinlog-vv binlog.000001 | grep-I INSERT

Once submitted, the binlog will be included.

Commit

[root@grtest S1] # mysqlbinlog-vv binlog.000001 | grep-I-a5 INSERT

BINLOG'

UZNjWRPhYAAAKwAAABIHAAAAANsAAAAAAAEABHRlc3QABHRlc3QAAQMAAQ==

UZNjWR7hYAAAJAAAADYHAAAAANsAAAAAAAEAAgAB//7oAwAA

'/ *! * /

# INSERT INTO `test`.`test`

# SET

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

# at 1846

# 170710 22:47:11 server id 24801 end_log_pos 1873 Xid = 477

Com _ MIT _ blank /

Let's verify the data in this destruction scenario, insert a record, do not commit, and then destroy the file to see the recovery.

Mysql > start transaction

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test values (2000)

Query OK, 1 row affected (0.00 sec)

We deleted these files with the words ib_.

Look at mysqld's pid and find that there are a large number of similar services in the test environment.

# pidof mysqld

30518 29944 29698 29401 15307 10659

Change your position.

# netstat-nltp | grep mysqld | grep 24801

Tcp 0 0: 24801: * LISTEN 29401/mysqld

In the system directory, you will find the following files according to the rule.

# ll / proc/29401/fd | grep ib_* | grep delete

Lrwx- 1 root root 64 Jul 10 22:49 10-> / home/data/s1/ib_logfile1 (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 11-> / home/data/s1/ibtmp1 (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 12-> / tmp/ibHcflkp (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 4-> / home/data/s1/ibdata1 (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 5-> / tmp/ibq7lvQK (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 6-> / tmp/ib59bGj5 (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 7-> / tmp/ibYubRMp (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 8-> / tmp/ib8LAUL4 (deleted)

Lrwx- 1 root root 64 Jul 10 22:49 9-> / home/data/s1/ib_logfile0 (deleted)

We do two things, one thing to lock the current environment, and then make a copy of the file.

[root@grtest s1] # chown mysql:mysql xxxx

[root@grtest s1] # mv 10 / home/data/s1/ib_logfile1

[root@grtest s1] # mv 11 / home/data/s1/ibtmp1

[root@grtest s1] # mv 9 / home/data/s1/ib_logfile0

[root@grtest s1] # mv 4 / home/data/s1/ibdata1

Stop the library normally and open the library.

At this point, the validation data will find that the previous transaction has been rolled back.

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.