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

This article mainly introduces how to delete InnoDB ibdata data files by mistake. It has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

The following example simulates the accidental deletion of data files and redo log files.

1)Delete data files and redo log files

cd /mysql/data

rm -rf ib*

2)If the database can work normally at this time, the data can be written normally, do not kill mysqld, otherwise there is no way to save, find the pid of mysqld

[root@mysql data]# netstat -nltp |grep mysqld

tcp 0 0 :::3306 :::* LISTEN 29691/mysqld

29691.

[root@mysql mysql]# ll /proc/29691/fd |egrep 'ib_|ibdata'

lrwx------ 1 root root 64 Aug 8 13:32 10 -> /mysql/ib_logfile1 (deleted)

lrwx------ 1 root root 64 Aug 8 13:32 4 -> /mysql/ibdata1 (deleted)

lrwx------ 1 root root 64 Aug 8 13:32 9 -> /mysql/ib_logfile0 (deleted)

10, 4, 9 are the files we need to recover.

3)Close the front end business or execute:

flush tables with read lock;

The goal is to keep the database free of write operations.

4)Run the following command to get dirty pages to disk as quickly as possible

set global innodb_max_dirty_pages_pct=0;

5)Then check the binlog entries to make sure the file and position values haven't changed.

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000004 | 980 | | | |

+------------------+----------+--------------+------------------+-------------------+

6)Check InnoDB status information to make sure dirty pages have been flushed to disk.

mysql> show engine innodb status \G

*************************** 1. row ***************************

Type: InnoDB

Name:

Status:

=====================================

2017-08-08 13:46:24 7f4d3e2b2700 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 10 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 8043 srv_idle

srv_master_thread log flush and writes: 8046

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 9

OS WAIT ARRAY INFO: signal count 9

Mutex spin waits 2, rounds 60, OS waits 2

RW-shared spins 6, rounds 180, OS waits 6

RW-excl spins 0, rounds 30, OS waits 1

Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 30.00 RW-excl

------------

TRANSACTIONS

------------

Trx id counter 31247

Purge done for trx's n:o

< 31242 undo n:o < 0 state: running but idle ##确保后天purge进程把undo log全部清除掉，事务ID要一致 History list length 969 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 4, OS thread handle 0x7f4d3e230700, query id 151 10.10.10.1 root ---TRANSACTION 31246, not started MySQL thread id 2, OS thread handle 0x7f4d3e2b2700, query id 160 localhost root init show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 404 OS file reads, 25 OS file writes, 22 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- 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 276671, node heap has 2 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 3401065960 Log flushed up to 3401065960 Pages flushed up to 3401065960 Last checkpoint at 3401065960 ##确保这4个值不在变化 0 pending log writes, 0 pending chkp writes 16 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 63833 Buffer pool size 8191 Free buffers 7802 Database pages 387 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 387, created 0, written 10 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: 387, 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. 29691, id 139969685923584, state: sleeping Number of rows inserted 1, updated 1, deleted 0, read 31 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ##确保插入、更新、删除为0 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.03 sec) 7)开始恢复工作 [root@mysql mysql]# ll /proc/29691/fd |egrep 'ib_|ibdata' lrwx------ 1 root root 64 Aug 8 13:32 10 ->

/mysql/ib_logfile1 (deleted)

lrwx------ 1 root root 64 Aug 8 13:32 4 -> /mysql/ibdata1 (deleted)

lrwx------ 1 root root 64 Aug 8 13:32 9 -> /mysql/ib_logfile0 (deleted)

cd /proc/29691/fd

cp 10 /mysql/ib_logfile1

cp 4 /mysql/ibdata1

cp 9 /mysql/ib_logfile0

8)Change data file and redo log file permissions

cd /mysql/

chown mysql:mysql ib*

9)Restart MySQL service

Thank you for reading this article carefully. I hope that the article "How to delete InnoDB ibdata data files by mistake" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

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.