Environment: centos 7.4+ mysql 5.7.19
When the database is damaged without backup, the data can be recovered from the disk file, which may not be 100% successful. The tool used is undo-for-innodb-master.zip
First, recover the table structure from frm (using MySQL utilities tool)
1. Install MySQL utilities
#yum localinstall mysql-connector-python-2.1.7-1.el7.x86_64 -y #yum localinstall autoconf mysql-utilities-1.5.6-1.el7.noarch -y
2. Recover table structure from frm file:
#service mysql start #mysqlfrm --server=root:root@localhost:3306 --port=3310 --user=mysql /root/a.frm #3310 this port can be written at will, but it can't be occupied. root:root@localhost:3306, which means the user name, password and port information for logging in to the local mysql WARNING: Using a password on the command line interface can be insecure. # Source on localhost: ... connected. # Spawning server with --user=mysql. # Starting the spawned server on port 3310 ... done. # Reading .frm files # # Reading the a.frm file. # # CREATE statement for ./a.frm: # CREATE TABLE `a` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 #...done. [root@slave1 ~]# mysqlfrm --server=root:root@localhost:3306 --diagnostic --show-stats --port=3310 --user=mysql ./servers.frm ########diagnostic mode
Second, recover data from innodb file
Create test table, insert data
[root@master ~]# yum -y install flex bison [root@master ~]# unzip undrop-for-innodb-master.zip [root@master ~]# make mysql> select * from test.aa; | id | name | +----+------+ | 1 | guo | | 2 | jia | | 3 | tt | mysql> drop table aa;
1. Start recovery. Stop the database first to prevent data writing and then overwrite the previous file
[root@master ~]# service mysql stop
2. Create table structure statement of aa table:
[root@master ~]# cd undrop-for-innodb-master/ [root@master undrop-for-innodb-master]# cat aa.sql CREATE TABLE `aa` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3. Backup and parse the ibdata1 file:
[root@master ~]# cp /data/mysql/ibdata1 undrop-for-innodb-master/ibdata1 [root@master ~]# cd undrop-for-innodb-master/ [root@master ~]# ./stream_parser -f ./ibdata1 //You can see that the pages-ibdata1 folder is generated in the current directory: [root@master undrop-for-innodb-master]# ll pages-ibdata1/ drwxr-xr-x 2 root root 4096 Oct 9 11:06 FIL_PAGE_INDEX drwxr-xr-x 2 root root 4096 Oct 9 11:06 FIL_PAGE_TYPE_BLO FIL_PAGE_INDEX In order to index the data page directory, the data we want to recover is in this folder. FIL_PAGE_TYPE_BLOB: If you encounter large data (for example comments There are text There are many types of data and contents), InnoDB Will use BLOB class PAGE Store data. You need to check if there are files in this directory. If yes, this table uses BLOB，After the extraction command needs to use the-b Parameter to specify this directory for extraction
4. Parse the page file containing aa table information and index
[root@master undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql |grep aa SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-master/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`); 0000011FCC26 3C0000012E19FE SYS_TABLES "test/aa" 379 2 33 0 80 "" 527
5. According to the main index value (379), query other tables for other information of aa table:
aa Table structure"dictionary/SYS_INDEXES.sql"You can see in the file, and the data page file corresponding to this table is the third data page 0000000000000003.page，Therefore: [root@master undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql |grep 379 SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-master/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`); 0000011FCC26 3C0000012E1957 SYS_INDEXES 379 479 "PRIMARY" 1 3 527 4294967295
6. Search the data in the page file according to the index value 479
After finding the primary key index information of aa table, its corresponding index value in mysql storage is 479, and all data of the index is stored in the data page file corresponding to the index label, so scan the whole disk to find the corresponding index file: (if there is no 479 index file in pages ibdata1 directory, no disk scanning is needed, step 6 can be skipped.) [root@master undrop-for-innodb-master]# df -h Filesystem Size Used Avail Use% Mounted on /dev/vda1 99G 83G 11G 89% / devtmpfs 3.9G 0 3.9G 0% /dev tmpfs 3.9G 0 3.9G 0% /dev/shm [root @ master undo for InnoDB master]. / stream ﹐ Parser - F / dev / vda1 - t 70000000 K ﹐ MySQL data is stored in this partition, so you need to scan all page files of this partition
7. Recover data:
[root@master undrop-for-innodb-master]# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000479.page -t aa.sql #aa.sql is a table structure sql statement created at the beginning [root@master undrop-for-innodb-master]# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000479.page -t aa.sql > dumps/default/t_aa 2> dumps/default/t_aa.sql [root@master undrop-for-innodb-master]# mysql -uroot -p123456 test > source /root/undrop-for-innodb-master/aa.sql; > source /root/undrop-for-innodb-master/dumps/default/t_aa.sql; > select * from aa; | id | name | +----+------+ | 1 | guo | | 2 | jia | | 3 | tt | //So far, all data has been recovered successfully!