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

MySQL 5.6.26 mistakenly deleted ibdata how to restore, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

[root@hank-yoon ~] # ps-ef | grep mysql

Root 1129 10 15:30 pts/0 00:00:00 / bin/sh / export/servers/mysql/bin/mysqld_safe-- datadir=/export/data/mysql/data/-- pid-file=/export/data/mysql/mysql.pid

Mysql 2284 1129 2 15:30 pts/0 00:00:01 / export/servers/mysql/bin/mysqld-basedir=/export/servers/mysql-datadir=/export/data/mysql/data/-plugin-dir=/export/servers/mysql/lib/plugin-user=mysql-log-error=/export/data/mysql/log/error.log-open-files-limit=65535-pid-file=/export/data/mysql/mysql.pid-socket=/export/data/mysql/tmp/mysql.sock-port=3306

Root 2343 1101 0 15:31 pts/0 00:00:00 grep mysql

[root@hank-yoon ~] # service mysql status

SUCCESS! MySQL running (2284)

[root@hank-yoon ~] # cd / export/data/mysql/data/

[root@hank-yoon data] # ls

A.sql binlog-rollback.pl c.sql ibdata1 ib_logfile0 ib_logfile2 mysql mysql-bin.000035 mysql-bin%Y%M%d.000001 performance_schema sakila yoon

Auto.cnf b.sql hank ibdata2 ib_logfile1 modify.pl mysql-bin.000034 mysql-bin.index mysql-bin%Y%M%d.index rollback.pl test

[root@hank-yoon data] # rm-rf ibdata1 ibdata2

Note: no backup, mistakenly delete ibdata files, do not restart the database, otherwise the gods will be useless!

[root@hank-yoon data] # ps-ef | grep mysql

Root 1129 10 15:30 pts/0 00:00:00 / bin/sh / export/servers/mysql/bin/mysqld_safe-- datadir=/export/data/mysql/data/-- pid-file=/export/data/mysql/mysql.pid

Mysql 2284 1129 1 15:30 pts/0 00:00:01 / export/servers/mysql/bin/mysqld-basedir=/export/servers/mysql-datadir=/export/data/mysql/data/-plugin-dir=/export/servers/mysql/lib/plugin-user=mysql-log-error=/export/data/mysql/log/error.log-open-files-limit=65535-pid-file=/export/data/mysql/mysql.pid-socket=/export/data/mysql/tmp/mysql.sock-port=3306

Root 2362 1101 0 15:32 pts/0 00:00:00 grep mysql

[root@hank-yoon data] # ll / proc/2284/fd | egrep 'ib_ | ibdata'

Lrwx-. 1 root root 64 Jul 12 15:35 10-> / export/data/mysql/data/ib_logfile0

Lrwx-. 1 root root 64 Jul 12 15:35 11-> / export/data/mysql/data/ib_logfile1

Lrwx-. 1 root root 64 Jul 12 15:35 12-> / export/data/mysql/data/ib_logfile2

Lrwx-. 1 root root 64 Jul 12 15:35 4-> / export/data/mysql/data/ibdata1 (deleted)

Lrwx-. 1 root root 64 Jul 12 15:35 9-> / export/data/mysql/data/ibdata2 (deleted)

Lock the table to prevent data from being written again in order to resume work

[root@hank-yoon data] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.6.26-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Root ((none)) > flush table with read lock

Query OK, 0 rows affected (0.00 sec)

Root ((none)) >

You can't stop business, brush the dirty page after locking the table.

Root ((none)) > flush table with read lock

Query OK, 0 rows affected (0.00 sec)

Root ((none)) > set global innodb_max_dirty_pages_pct=0

Query OK, 0 rows affected (0.00 sec)

Make sure that file and position no longer change through show mater status

Root ((none)) > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000035 | 120 | |

+-+

Check root ((none)) > show engine innodb status\ G to see if there are any more writes

TRANSACTIONS

-

Trx id counter 59143

Purge done for trx's n:o

< 58676 undo n:o < 0 state: running but idle ##确保后台Purge进程把undo log全部清除掉，事务ID要一致 History list length 462 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 1, OS thread handle 0x7f5cec6eb700, query id 9 localhost root init ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges # insert buffer合并插入缓存等于1 merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 2365241, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 14149940429 Log flushed up to 14149940429 Pages flushed up to 14149940429 Last checkpoint at 14149940429 #三个值相同 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 1098907648; in additional pool allocated 0 Dictionary memory allocated 59957 Buffer pool size 65528 Free buffers 65118 Database pages 410 Old database pages 0 Modified db pages 0 #确保脏数据为0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 410, created 0, written 5 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 410, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 2284, id 140037101377280, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s #确保插入，更新，删除为0 再次找到这几个文件，恢复 [root@hank-yoon data]# ll /proc/2284/fd | egrep 'ib_|ibdata' lrwx------. 1 root root 64 Jul 12 15:35 10 ->

/ export/data/mysql/data/ib_logfile0

Lrwx-. 1 root root 64 Jul 12 15:35 11-> / export/data/mysql/data/ib_logfile1

Lrwx-. 1 root root 64 Jul 12 15:35 12-> / export/data/mysql/data/ib_logfile2

Lrwx-. 1 root root 64 Jul 12 15:35 4-> / export/data/mysql/data/ibdata1 (deleted)

Lrwx-. 1 root root 64 Jul 12 15:35 9-> / export/data/mysql/data/ibdata2 (deleted)

[root@hank-yoon data] # cp / proc/2284/fd/4 / export/data/mysql/data/ibdata1

[root@hank-yoon data] # cp / proc/2284/fd/9 / export/data/mysql/data/ibdata2

Modify permissions

[root@hank-yoon data] # cd / export/data/mysql/data/

[root@hank-yoon data] # ls-ltr

Total 4980844

Drwx-. 2 mysql mysql 4096 Sep 30 2015 performance_schema

Drwx-. 2 mysql mysql 4096 Sep 30 2015 mysql

-rw-rw----. 1 mysql mysql 56 Sep 30 2015 auto.cnf

Drwx-. 2 mysql mysql 4096 Nov 18 2015 test

-rw-r--r--. 1 root root 15886 Dec 24 2015 binlog-rollback.pl

-rwxr-xr-x. 1 root root 2766 Jan 4 2016 modify.pl

-rw-r--r--. 1 root root 2766 Jan 5 2016 rollback.pl

Drwx-. 2 mysql mysql 4096 Mar 22 21:54 hank

-rw-rw----. 1 mysql mysql 25 Apr 13 10:04 mysql-bin%Y%M%d.index

-rw-rw----. 1 mysql mysql 143 Apr 13 10:05 mysql-bin%Y%M%d.000001

-rw-rw----. 1 mysql mysql 268435456 Jun 21 10:07 ib_logfile1

Drwx-. 2 mysql mysql 4096 Jun 21 15:02 sakila

Drwx-. 2 mysql mysql 4096 Jun 29 14:50 yoon

-rw-r--r--. 1 root root 5087 Jun 29 14:53 a.sql

-rw-r--r--. 1 root root 124 Jun 29 14:55 b.sql

-rw-r--r--. 1 root root 47 Jun 29 14:56 c.sql

-rw-rw----. 1 mysql mysql 2027 Jul 12 15:30 mysql-bin.000034

-rw-rw----. 1 mysql mysql 120 Jul 12 15:30 mysql-bin.000035

-rw-rw----. 1 mysql mysql 38 Jul 12 15:30 mysql-bin.index

-rw-rw----. 1 mysql mysql 268435456 Jul 12 15:30 ib_logfile2

-rw-rw----. 1 mysql mysql 268435456 Jul 12 15:30 ib_logfile0

-rw-r-. 1 root root 2147483648 Jul 12 15:55 ibdata1

-rw-r-. 1 root root 2147483648 Jul 12 15:55 ibdata2

[root@hank-yoon data] # chown-R mysql.mysql ibdata1 ibdata2

Restart the login database

[root@hank-yoon data] # service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL... SUCCESS!

[root@hank-yoon data] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 1

Server version: 5.6.26-log Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Root ((none)) >

After reading the above, have you mastered the method of how to restore MySQL 5.6.26 mistakenly deleted ibdata? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.