Accidental deletion of InnoDB ibdata data Files-recovery

Today in the group to see someone said unfamiliar InnoDB the Ibdata (data file) and Ib_logfile (transaction log) files were deleted by mistake. Do not know how to solve. I didn't know what to do at that time. Later access to relevant information. Finally find a solution. In fact, recovery is quite simple. We find it difficult when we don't know it. Who says it's not?

Below we will simulate the production environment, the human deletion of data files and redo log files. Then specify the recovery steps in detail.

1. Write the Sysbench analog data as follows:

[Email protected] ~]# sysbench--test=oltp--oltp-table-size=1000000--oltp-read-only=off--init-rng=on--num-threads =16--max-requests=0--oltp-dist-type=uniform--max-time=1800--mysql-user=root--mysql-socket=/tmp/mysqld.sock-- mysql-password=123456--db-driver=mysql--mysql-table-engine=innodb--oltp-test-mode=complex prepare      sysbench 0.4.10:  multi-threaded System Evaluation benchmarkcreating table ' Sbtest ' ... Creating 1000000 Records in table ' Sbtest ' ...

2. Use the command rm-f ib* to delete the data file and the transaction log file:

[Email protected] mysql]# lsemployees    ib_logfile1       mysql-bin.000003  mysql-bin.000008  performance_ Schema  World_innodb                  yayun-mysql-server.pidgeneral.log  Menagerie         mysql-bin.000004  mysql-bin.000009  sakila              world_myisamhost         mysql             mysql-bin.000005  mysql-bin.000010  Sbtest              xtrabackup_binlog_pos_innodbibdata1      mysql-bin.000001  mysql-bin.000006  Mysql-bin.index   slow-query.log      yayunib_logfile0  mysql-bin.000002  mysql-bin.000007  Percona           Test                

Let's take a look at how to recover:

If at this time found that the database can still work, the data can still be read and writable, remember: this time do not kill the mysqld process, or really can not be saved, you wait to cry.

([email protected] 20:42:25pm>) [Yayun]>select * from t1;+----+-------+| ID | Name  |+----+-------+|  1 | Yayun | |  2 | Atlas | |  3 | MySQL |+----+-------+3 rows in Set (0.00 sec) ([e-mail protected] 20:42:28pm>) [Yayun]>insert into T1 select 4, ' Pytho n '; Query OK, 1 row affected (0.01 sec) records:1  duplicates:0  warnings:0 ([email protected] 20:42:48pm>) [Yayun] >select * from T1;                +----+--------+| ID | Name   |+----+--------+|  1 | Yayun  | |  2 | Atlas  | |  3 | MySQL  | |  4 | Python |+----+--------+4 rows in Set (0.00 sec) ([email protected] 20:42:50pm>) [yayun]>

I read and write all normal here. So we can get back to success.
(1) First, we first find the PID of the mysqld process, as follows:

[Email protected] ~]# NETSTAT-NLTP | grep mysqldtcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      5725/mysqld         

Visible mysqld pid is 5725, this step is a key step.

(2) Use the following command to view the results (very important)

Here is a very important knowledge, children's shoes self-check, delete a file, not really delete, but to hit a tag, also in our MySQL database, delete a record of the actual deletion did not occur.
In the results shown above, 10,4,9 is the file we need to recover.

(3) Before recovering the file, flush tables with read lock is required to ensure that the database does not have write operations so that we can complete the recovery

([email protected] 20:55:26pm>) [(none)]>flush tables with read lock; Query OK, 0 rows Affected (0.00 sec) ([email protected] 20:55:31pm>) [(None)]>

So how do we make sure that no data is written? Take a few steps to view

(1) Set dirty page refresh scale (let dirty page flush to disk as soon as possible)

([email protected] 20:55:31pm>) [(None)]>set global innodb_max_dirty_pages_pct=0; Query OK, 0 rows Affected (0.00 sec) ([email protected] 20:57:42pm>) [(None)]>

(2) View Binlog Log writes to ensure that the values of file and position are not changed

([email protected] 20:57:42pm>) [(None)]>show Master status;+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000010 | 61704130 |              |                  | +------------------+----------+--------------+------------------+1 row in Set (0.00 sec) ([email protected] 20:59:11pm >) [(None)]>

(3) View InnoDB status information to ensure that dirty pages have been flushed to disk

([email protected] 20:59:11pm>) [(none)]>show engine InnoDB status\g------------transactions------------Trx ID counter b9e0fpurge done for Trx ' s N:o & Lt b9e0c undo N:o < 0 #确保后台线程purge把undo log all brush off-------------------------------------INSERT BUFFER and ADAPTIVE HASH in DEX-------------------------------------ibuf:size 1, free list len 2543, seg size 2545, 0 merges #确保合并插入缓存等于1---LOG---Lo G Sequence number 6173930288Log flushed up to 6173930288 #确保这里三个值保持一致 and no longer changes last checkpoint at 6173930288Buf                       Fer pool size 65534Free buffers 50513Database pages 15020Old database pages 5506Modified db pages 0 #确保脏页数量为0--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 Read views op En inside Innodbmain thread process No. 5725, id 140014471358208, state:waiting for server activitynumber of rows Inserte D 1000004, updated 1995, deleted 0, read 20080.00 inserts/s, 0.00 updates/s, 0.00 DELETES/S, 0.00 ReadS/S #确保插入, update, delete to 0 

Once we have confirmed the above, we can proceed with the recovery operation. Remember the previous command we looked at the files to be recovered, the PID of our mysqld process is 5725, and we look again at the files that need to be recovered

Put the 10,4,9 file CP into the original MySQL data directory:

Modify file Permissions

[[email protected] ~]# cd/data/mysql[[email protected] mysql]# chown-r mysql.mysql ib*[[email protected] mysql]# ll | Egrep ' ib_|ibdata1 '-rw-r--r--1 mysql mysql 866123776 Apr 21:13 ibdata1-rw-r--r--1 mysql mysql  67108864 Apr 30 21: ib_logfile0-rw-r--r--1 MySQL MySQL  

Restart MySQL, repair complete

[Email protected] mysql]#/etc/init.d/mysqld restartshutting down MySQL                                     ... [  OK  ]starting MySQL ....                                         [  OK  

Note: Do not test the production environment, children's shoes can open their own virtual machine for testing.

Resources

"MySQL Management, performance tuning, high availability and monitoring"

The Original Blog:atlas blog Http://www.cnblogs.com/gomysql

Accidental deletion of InnoDB ibdata data Files-recovery