MySQL 5.6.26 mistaken deletion of ibdata for restoration

MySQL 5.6.26 mistaken deletion of ibdata for restoration

MySQL 5.6.26 mistaken deletion of ibdata for restoration

[Root @ hank-yoon ~] # Ps-ef | grep mysql
Root 1129 1 0 00:00:00 pts/0/bin/sh/export/servers/mysql/bin/mysqld_safe -- datadir =/export/data/mysql/data/-- pid-file = /export/data/mysql. pid
Mysql 2284 1129 2 00:00:01 pts/0/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. pid -- socket =/export/data/mysql/tmp/mysql. sock -- Ports = 3306
Root 2343 1101 0 00:00:00 pts/0 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.20.01 performance_schema sakila yoon
Auto. cnf B. SQL hank ibdata2 ib_logfile1 modify. pl mysql-bin.000034 mysql-bin.index for mysql-bin % Y % M % d. index rollback. pl test

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

Note: If you delete ibdata files without backup, do not restart the database!
[Root @ hank-yoon data] # ps-ef | grep mysql
Root 1129 1 0 00:00:00 pts/0/bin/sh/export/servers/mysql/bin/mysqld_safe -- datadir =/export/data/mysql/data/-- pid-file = /export/data/mysql. pid
Mysql 2284 1129 1 00:00:01 pts/0/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. pid -- socket =/export/data/mysql/tmp/mysql. sock -- Ports = 3306
Root 2362 1101 0 00:00:00 pts/0 grep mysql

[Root @ hank-yoon data] # ll/proc/2284/fd | egrep 'ib _ | ibdata'
Lrwx ------. 1 root 64 Jul 12 :35 10->/export/data/mysql/data/ib_logfile0
Lrwx ------. 1 root 64 Jul 12 :35 11->/export/data/mysql/data/ib_logfile1
Lrwx ------. 1 root 64 Jul 12 12->/export/data/mysql/data/ib_logfile2
Lrwx ------. 1 root 64 Jul 12 :35 4->/export/data/mysql/data/ibdata1 (deleted)
Lrwx ------. 1 root 64 Jul 12 :35 9->/export/data/mysql/data/ibdata2 (deleted)

Lock the table and prohibit writing data again for recovery
[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,201 5, 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)>

Businesses cannot be stopped. Dirty pages are refreshed after tables are locked.
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)

Use show mater status to determine whether the file and position do not change.
Root (none)> show master status;
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +
| Mysql-bin.000035 | 120 |
+ ------------------ + ---------- + -------------- + -------------------- + ------------------- +

You can use root (none)> show engine innodb status \ G to check whether any write operations exist.
TRANSACTIONS
------------
Trx id counter 59143
Purge done for trx's n: o <58676 undo n: o <0 state: running but idle
# Ensure that the background Purge process clears all undo logs, and the transaction ID must be consistent
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 merge insert cache equal to 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
Logflushed up to 14149940429
Pages flushed up to 14149940429
Last checkpoint at 14149940429
# The three values are the same
----------------------
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
# Make sure that the dirty data is 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
# Make sure to insert, update, and delete to 0

Find these files again and restore them.
[Root @ hank-yoon data] # ll/proc/2284/fd | egrep 'ib _ | ibdata'
Lrwx ------. 1 root 64 Jul 12 :35 10->/export/data/mysql/data/ib_logfile0
Lrwx ------. 1 root 64 Jul 12 :35 11->/export/data/mysql/data/ib_logfile1
Lrwx ------. 1 root 64 Jul 12 12->/export/data/mysql/data/ib_logfile2
Lrwx ------. 1 root 64 Jul 12 :35 4->/export/data/mysql/data/ibdata1 (deleted)
Lrwx ------. 1 root 64 Jul 12 :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 4096 Sep 30 2015 performance_schema
Drwx ------. 2 mysql 4096 Sep 30 2015 mysql
-Rw ----. 1 mysql 56 Sep 30 2015 auto. cnf
Drwx ------. 2 mysql 4096 Nov 18 2015 test
-Rw-r --. 1 root 15886 Dec 24 2015 binlog-rollback.pl
-Rwxr-xr-x. 1 root 2766 Jan 4 2016 modify. pl
-Rw-r --. 1 root 2766 Jan 5 2016 rollback. pl
Drwx ------. 2 mysql 4096 Mar 22 hank
-Rw ----. 1 mysql 25 Apr 13 10:04 mysql-bin % Y % M % d. index
-Rw ----. 1 mysql 143 Apr 13 mysql-bin % Y % M % d.20.01
-Rw ----. 1 mysql 268435456 Jun 21 ib_logfile1
Drwx ------. 2 mysql 4096 Jun 21 sakila
Drwx ------. 2 mysql 4096 Jun 29 yoon
-Rw-r --. 1 root 5087 Jun 29 14:53 a. SQL
-Rw-r --. 1 root 124 Jun 29 14:55 B. SQL
-Rw-r --. 1 root 47 Jun 29 14:56 c. SQL
-Rw ----. 1 mysql 2027 Jul 12 mysql-bin.000034
-Rw ----. 1 mysql 120 Jul 12 mysql-bin.000035
-Rw ----. 1 mysql 38 Jul 12 :30 mysql-bin.index
-Rw ----. 1 mysql 268435456 Jul 12 ib_logfile2
-Rw ----. 1 mysql 268435456 Jul 12 ib_logfile0
-Rw-r -----. 1 root 2147483648 Jul 12 :55 ibdata1
-Rw-r -----. 1 root 2147483648 Jul 12 :55 ibdata2

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

Restart database Logon
[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,201 5, 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)>

This article permanently updates the link address: