In daily work, because I am not familiar with the InnoDB engine, many people in the group have deleted InnoDB ibdata files by mistake) and ib_logfileredo log redo transaction log files. If you have master-slave replication and synchronization, it's okay. What if it's a single machine? How to restore?
See the recovery demo below:
1. You can use sysbench to simulate Data Writing, for example:
- sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --max-requests=10000
- --num-threads=90 --mysql-host=192.168.110.140 --mysql-port=3306 --mysql-user=admin --mysql-password=123456
- --mysql-db=test --oltp-table-name=uncompressed --mysql-socket=/tmp/mysql.sock run
2. rm-f ib *
3. At this time, I guess you are scared and have a trembling face. If you see this article, your mind will be stable and you will be able to recover.
4. At this time, you will find that the database can still work normally and data can still be written. Remember, do not kill the mysqld process at this time; otherwise, you will only have to skip the building and the gods will not be able to save you.
5. First find the mysqld process pid
- # netstat -ntlp | grep mysqld
- tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 30426/mysqld
Here I am 30426
6. Key Steps
- # Ll/proc/30426/fd | egrep 'ib _ | ibdata'
- Lrwx ------ 1 root 64 September 24 16:51 10->/u2/mysql/data/ib_logfile1
- Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile2
- Lrwx ------ 1 root 64 September 24 16:51 4->/u2/mysql/data/ibdata1
- Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile0
10, 11, 4, and 9 are the files to be restored.
7. You can disable the front-end service or execute flush tables with read lock. This step prevents the database from being written for subsequent recovery.
8. How can I verify that no write operation is performed? Take the following steps and remember to observe them together.
- Set global innodb_max_dirty_pages_pct = 0;
- # Refresh the dirty pages to the disk as soon as possible.
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000002 | 107 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
Make sure that the File and Position values do not change.
- Show engine innodb status \ G;
- ------------
- TRANSACTIONS
- ------------
- Trx id counter A21837
- Purge done for trx's n: o <A21837 undo n: o <0
- # Ensure that the background Purge process clears all undo logs, and the transaction IDs must be consistent.
- -------------------------------------
- INSERT BUFFER AND ADAPTIVE HASH INDEX
- -------------------------------------
- Ibuf: size 1, free list len 65, seg size 67, 0 merges
- # Insert buffer merge insert cache equal to 1
-
- ---
- LOG
- ---
- Log sequence number 18158813743
- Logflushed up to 18158813743
- Last checkpoint at 18158813743
- # Make sure these three values do not change
-
- ----------------------
- BUFFER POOL AND MEMORY
- ----------------------
- Total memory allocated 643891200; in additional pool allocated 0
- Dictionary memory allocated 39812
- Buffer pool size 38400
- Free buffers 37304
- Database pages 1095
- Old database pages 424
- Modified db pages 0
- # Make sure 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. 30426, id 140111500936976, state: waiting for server activity 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
- # Ensure that the values of insert, update, and delete are 0
9. After the above series of validation work is completed, we can recover. Do you still remember the deleted files we recorded just now?
- # Ll/proc/30426/fd | egrep 'ib _ | ibdata'
- Lrwx ------ 1 root 64 September 24 16:51 10->/u2/mysql/data/ib_logfile1
- Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile2
- Lrwx ------ 1 root 64 September 24 16:51 4->/u2/mysql/data/ibdata1
- Lrwx ------ 1 root 64 September 24 16:51->/u2/mysql/data/ib_logfile0
Copy these files to the original directory and modify user attributes.
- #cd /proc/10755/fd
- #cp 10 /u2/mysql/data/ib_logfile1
- #cp 11 /u2/mysql/data/ib_logfile2
- #cp 4 /u2/mysql/data/ibdata1
- #cp 9 /u2/mysql/data/ib_logfile0
And modify user attributes
- #cd /u2/mysql/data/
- #chown mysql:mysql ib*
10. All you can do is restart MySQL.
- /etc/init.d/mysql restart
How? It's that simple. You can also try it.
This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1004810