Today, I saw some people in the group who were unfamiliar with innodb and deleted ibdata (data file) and ib_logfile (transaction log) files by mistake. I don't know how to solve it. I didn't know what to do then. Later, relevant information was consulted. Find a solution. In fact, recovery is quite simple. It's hard when we don't know. Who says that's not the case?
Next we will simulate the production environment, artificially delete data files and redo log files. Then the recovery steps are described in detail.
1. Simulate the writing of data with sysbench, as follows:
[root@yayun-mysql-server ~]# 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 benchmark Creating table 'sbtest'... Creating 1000000 records in table 'sbtest'...
2. Use the command rm-f ib* to delete data files and transaction log files:
[root@yayun-mysql-server mysql]# ls employees ib_logfile1 mysql-bin.000003 mysql-bin.000008 performance_schema world_innodb yayun-mysql-server.pid general.log menagerie mysql-bin.000004 mysql-bin.000009 sakila world_myisam host mysql mysql-bin.000005 mysql-bin.000010 sbtest xtrabackup_binlog_pos_innodb ibdata1 mysql-bin.000001 mysql-bin.000006 mysql-bin.index slow-query.log yayun ib_logfile0 mysql-bin.000002 mysql-bin.000007 percona test yayun-mysql-server.err [root@yayun-mysql-server mysql]# rm -f ib* [root@yayun-mysql-server mysql]#
Let's take a look at how to recover:
If you find that the database is still working properly, and the data is still readable and writable, remember: at this time, don't kill the mysqld process, otherwise it can't be saved, you can wait to cry.
(root@yayun 20:42:25pm> ) [yayun]>select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | yayun | | 2 | atlas | | 3 | mysql | +----+-------+ 3 rows in set (0.00 sec) (root@yayun 20:42:28pm> ) [yayun]>insert into t1 select 4,'python'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 (root@yayun 20:42:48pm> ) [yayun]>select * from t1; +----+--------+ | id | name | +----+--------+ | 1 | yayun | | 2 | atlas | | 3 | mysql | | 4 | python | +----+--------+ 4 rows in set (0.00 sec) (root@yayun 20:42:50pm> ) [yayun]>
I read and write normally here. So we can recover.
(1) First, find the pid of the mysqld process, as follows:
[root@yayun-mysql-server ~]# netstat -nltp | grep mysqld tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5725/mysqld [root@yayun-mysql-server ~]#
So mysqld's pid is 5725, which is a key step.
(2) Use the following commands to view the results (very important)
[root@yayun-mysql-server ~]# ll /proc/5725/fd | egrep 'ib_|ibdata' lrwx------. 1 root root 64 Apr 30 20:44 10 -> /data/mysql/ib_logfile1 (deleted) lrwx------. 1 root root 64 Apr 30 20:44 4 -> /data/mysql/ibdata1 (deleted) lrwx------. 1 root root 64 Apr 30 20:44 9 -> /data/mysql/ib_logfile0 (deleted) [root@yayun-mysql-server ~]#
Here is the relevant important knowledge. When children look up and delete a file by themselves, they do not really delete it, but mark it. Similarly, in our mysql database, delete does not record the actual deletion operation.
Among the results shown above, 10,4,9 are the files we need to restore.
(3) Before restoring files, we need to execute flush tables with read lock to ensure that the database is not written so that we can complete the recovery.
(root@yayun 20:55:26pm> ) [(none)]>flush tables with read lock; Query OK, 0 rows affected (0.00 sec) (root@yayun 20:55:31pm> ) [(none)]>
So how do we make sure that no data is written? Look at it in several steps
(1) Set the refresh ratio of dirty pages (let dirty pages refresh to disk as soon as possible)
(root@yayun 20:55:31pm> ) [(none)]>set global innodb_max_dirty_pages_pct=0; Query OK, 0 rows affected (0.00 sec) (root@yayun 20:57:42pm> ) [(none)]>
(2) Check the binlog log writes to ensure that the values of File and Postion remain unchanged
(root@yayun 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) (root@yayun 20:59:11pm> ) [(none)]>
(3) Check innodb status information to ensure that dirty pages have been refreshed to disk
(root@yayun 20:59:11pm> ) [(none)]>show engine innodb status\G ------------ TRANSACTIONS ------------ Trx id counter B9E0F Purge done for trx's n:o < B9E0C undo n:o < 0 #Make sure the background thread purge wipes out all undo log s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 2543, seg size 2545, 0 merges #Ensure that merge insert cache equals 1 --- LOG --- Log sequence number 6173930288 Log flushed up to 6173930288 #Make sure that the three values here are consistent and do not change Last checkpoint at 6173930288 Buffer pool size 65534 Free buffers 50513 Database pages 15020 Old database pages 5506 Modified db pages 0 #Ensure that the number of dirty pages is 0 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 5725, id 140014471358208, state: waiting for server activity Number of rows inserted 1000004, updated 1995, deleted 0, read 2008 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s #Ensure insertion, update, deletion is 0
After we confirm the above, we can resume the operation. Remember the previous command to look at the files to be restored, our mysqld process pid is 5725, let's look at the files that need to be restored again.
[root@yayun-mysql-server ~]# ll /proc/5725/fd | egrep 'ib_|ibdata' lrwx------. 1 root root 64 Apr 30 20:44 10 -> /data/mysql/ib_logfile1 (deleted) lrwx------. 1 root root 64 Apr 30 20:44 4 -> /data/mysql/ibdata1 (deleted) lrwx------. 1 root root 64 Apr 30 20:44 9 -> /data/mysql/ib_logfile0 (deleted) [root@yayun-mysql-server ~]#
Put 10,4,9 files cp into the original mysql data directory:
[root@yayun-mysql-server ~]# cd /proc/5725/fd [root@yayun-mysql-server fd]# cp 10 /data/mysql/ib_logfile1 [root@yayun-mysql-server fd]# cp 4 /data/mysql/ibdata1 [root@yayun-mysql-server fd]# cp 9 /data/mysql/ib_logfile0 [root@yayun-mysql-server fd]#
Modify File Permissions
[root@yayun-mysql-server ~]# cd /data/mysql [root@yayun-mysql-server mysql]# chown -R mysql.mysql ib* [root@yayun-mysql-server mysql]# ll | egrep 'ib_|ibdata1' -rw-r--r-- 1 mysql mysql 866123776 Apr 30 21:13 ibdata1 -rw-r--r-- 1 mysql mysql 67108864 Apr 30 21:13 ib_logfile0 -rw-r--r-- 1 mysql mysql 67108864 Apr 30 21:11 ib_logfile1 [root@yayun-mysql-server mysql]#
Restart mysql, repair completed
[root@yayun-mysql-server mysql]# /etc/init.d/mysqld restart Shutting down MySQL... [ OK ] Starting MySQL.... [ OK ] [root@yayun-mysql-server mysql]#