MySQL数据库不能启动实现数据恢复的例子
发布时间：2022-06-29 02:31:01 所属栏目：MySql教程 来源：互联网
导读：利用工具获取MySQL数据字典的例子这篇文章介绍的是有一些技巧,本文章是介绍在mysql未启动的情况利用dul或者其他三方工具直接读取数据文件中数据并进行恢复了,具体如下. 熟悉Oracle的朋友都知道,当Oracle数据库无法正常启动之时,可以通过dul或者其他三方工
|
利用工具获取MySQL数据字典的例子这篇文章介绍的是有一些技巧,本文章是介绍在mysql未启动的情况利用dul或者其他三方工具直接读取数据文件中数据并进行恢复了,具体如下. 熟悉Oracle的朋友都知道,当Oracle数据库无法正常启动之时,可以通过dul或者其他三方工具直接读取数据文件中数据,从而来抢救数据,减少损失,在mysql中如果使用了innodb引擎也可以在mysql数据库不启动启动下抽取相关记录.本文为抽取数据字典篇章,后续将继续提供drop恢复,truncate 恢复,delete恢复等mysql非常规恢复篇章. 创建一张get_dict测试表: mysql> use xifenfei; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------------+ | Tables_in_xifenfei | +--------------------+ | t_delete | +--------------------+ 1 row in set (0.00 sec) mysql> create table get_dict(id int not null primary key,name varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> insert into get_dict value(1,'www.phpfensi.com'); Query OK, 1 row affected (0.00 sec) mysql> insert into get_dict value(2,'www.phpfensi.com-xifenfei'); Query OK, 1 row affected (0.00 sec) mysql> insert into get_dict value(3,'xifenfei-www.phpfensi.com'); Query OK, 1 row affected (0.00 sec) mysql> show tables; +--------------------+ | Tables_in_xifenfei | +--------------------+ | get_dict | | t_delete | +--------------------+ 2 rows in set (0.00 sec mysql> select TABLE_NAME,TABLE_SCHEMA,TABLE_TYPE from information_schema.tables -> where table_name='get_dict'; +------------+--------------+------------+ | TABLE_NAME | TABLE_SCHEMA | TABLE_TYPE | +------------+--------------+------------+ | get_dict | xifenfei | BASE TABLE | +------------+--------------+------------+ 1 row in set (0.01 sec) mysql> select TABLE_NAME,NON_UNIQUE,TABLE_SCHEMA,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAME from -> INFORMATION_SCHEMA.STATISTICS where TABLE_NAME='get_dict'; +------------+------------+--------------+--------------+------------+-------------+ | TABLE_NAME | NON_UNIQUE | TABLE_SCHEMA | INDEX_SCHEMA | INDEX_NAME | COLUMN_NAME | +------------+------------+--------------+--------------+------------+-------------+ | get_dict | 0 | xifenfei | xifenfei | PRIMARY | id | +------------+------------+--------------+--------------+------------+-------------+ 1 row in set (0.00 sec) mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION from -> information_schema.COLUMNS where table_name='get_dict'; +--------------+------------+-------------+------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | +--------------+------------+-------------+------------------+ | xifenfei | get_dict | id | 1 | | xifenfei | get_dict | name | 2 | +--------------+------------+-------------+------------------+ 2 rows in set (0.01 sec) 关闭mysql数据库: [root@localhost recovery_mysql]# service mysql stop Shutting down MySQL..[ OK ] [root@localhost recovery_mysql]# ps -ef|grep mysql root 18876 15827 0 18:05 pts/1 00:00:00 grep mysql 使用工具解析innodb文件: [root@localhost recovery_mysql]# ./stream_parser -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1 File information: Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2054 inode number: 1782889 ID of device containing file: 2054 protection: 100660 inode number: 1782889 (regular file) protection: 100660 number of hard links: 1 (regular file) user ID of owner: 101 number of hard links: 1 group ID of owner: 102 user ID of owner: 101 device ID (if special file): 0 group ID of owner: 102 blocksize for filesystem I/O: 4096 device ID (if special file): 0 number of blocks allocated: 24616 blocksize for filesystem I/O: 4096 Opening file: /var/lib/mysql/ibdata1 number of blocks allocated: 24616 File information: ……………… user ID of owner: 101 group ID of owner: 102 device ID (if special file): 0 blocksize for filesystem I/O: 4096 ID of device containing file: 2054 number of blocks allocated: 24616 inode number: 1782889 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 101 group ID of owner: 102 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 24616 time of last access: 1417922668 Sun Dec 7 11:24:28 2014 time of last modification: 1418294104 Thu Dec 11 18:35:04 2014 time of last status change: 1418294104 Thu Dec 11 18:35:04 2014 time of last access: 1417922668 Sun Dec 7 11:24:28 2014 total size, in bytes: 12582912 (12.000 MiB) time of last modification: 1418294104 Thu Dec 11 18:35:04 2014 time of last status change: 1418294104 Thu Dec 11 18:35:04 2014 Size to process: 12582912 (12.000 MiB) total size, in bytes: 12582912 (12.000 MiB) Size to process: 12582912 (12.000 MiB) All workers finished in 0 sec 主要文件介绍: [root@localhost recovery_mysql]# ls -l pages-ibdata1/FIL_PAGE_INDEX/ total 1388 -rw-r--r-- 1 root root 16384 Dec 11 18:51 0000000000000001.page -rw-r--r-- 1 root root 16384 Dec 11 18:51 0000000000000002.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000003.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000004.page -rw-r--r-- 1 root root 16384 Dec 11 18:51 0000000000000005.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000011.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000012.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000013.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000014.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000015.page -rw-r--r-- 1 root root 147456 Dec 11 18:51 0000000000000016.page -rw-r--r-- 1 root root 98304 Dec 11 18:51 0000000000000017.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000018.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000019.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000020.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000021.page -rw-r--r-- 1 root root 65536 Dec 11 18:51 0000000000000025.page -rw-r--r-- 1 root root 16384 Dec 11 18:51 18446744069414584320.page 0000000000000001.page主要是记录mysql中表信息文件 0000000000000002.page主要是记录mysql中的表的列的信息文件 0000000000000003.page主要是记录mysql中表的index信息文件 抽取table数据: [root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql [root@localhost recovery_mysql]# grep get dumps/default/SYS_TABLES | head -5 000000000D1D 95000001510110 SYS_TABLES "xifenfei/get_dict" 23 2 1 0 80 "" 9 [root@localhost recovery_mysql]# cat dumps/default/SYS_TABLES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/tmp/recovery_mysql/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLESt' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`); 抽取column数据: [root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql [root@localhost recovery_mysql]# cat dumps/default/SYS_COLUMNS -- Page id: 10, Format: REDUNDANT, Records list: Valid, Expected records: (115 115) 000000000300 800000012D0123 SYS_COLUMNS 11 0 "ID" 1 4 0 0 000000000300 800000012D0138 SYS_COLUMNS 11 1 "FOR_NAME" 1 4 0 0 ………… 000000000D1D 95000001510129 SYS_COLUMNS 23 0 "id" 6 1283 4 0 000000000D1D 9500000151013E SYS_COLUMNS 23 1 "name" 1 524303 100 0 -- Page id: 10, Found records: 115, Lost records: NO, Leaf page: YES [root@localhost recovery_mysql]# more dumps/default/SYS_COLUMNS.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/tmp/recovery_mysql/dumps/default/SYS_COLUMNS' REPLACE INTO TABLE `SYS_COLUMNS` FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_COLUMNSt' (`TABLE_ID`, `POS`, `NAME`, `MTYPE`, `PRTYPE`, `LEN`, `PREC`); 抽取index数据: [root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql [root@localhost recovery_mysql]# more dumps/default/SYS_INDEXES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/tmp/recovery_mysql/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXESt' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`); [root@localhost recovery_mysql]# more dumps/default/SYS_INDEXES -- Page id: 11, Format: REDUNDANT, Records list: Valid, Expected records: (13 13) 000000000300 800000012D0177 SYS_INDEXES 11 11 "ID_IND" 1 3 0 302 ………… 000000000B02 820000013504C8 SYS_INDEXES 20 22 "GEN_CLUST_INDEX" 0 1 6 3 000000000D1D 9500000151016B SYS_INDEXES 23 25 "PRIMARY" 1 3 9 3 启动mysql数据库: [root@localhost recovery_mysql]# service mysql start Starting MySQL..[ OK ] [root@localhost recovery_mysql]# ps -ef|grep mysql root 18948 1 0 19:57 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid mysql 19049 18948 14 19:57 pts/1 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/lib/mysql/localhost.localdomain.pid root 19078 15827 0 19:58 pts/1 00:00:00 grep mysql 创建抽取数据字典表: mysql> source dictionary/SYS_TABLES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ………… mysql> source dictionary/SYS_INDEXES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ………… mysql> source dictionary/SYS_COLUMNS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ………… mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | SYS_COLUMNS | | SYS_INDEXES | | SYS_TABLES | +----------------+ 3 rows in set (0.00 sec) 加载抽取数据字典数据: mysql> source dumps/default/SYS_TABLES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 11 rows affected (0.03 sec) Records: 11 Deleted: 0 Skipped: 0 Warnings: 0 mysql> source dumps/default/SYS_INDEXES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 39 rows affected (0.01 sec) Records: 39 Deleted: 0 Skipped: 0 Warnings: 0 mysql> source dumps/default/SYS_COLUMNS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 115 rows affected (0.00 sec) Records: 115 Deleted: 0 Skipped: 0 Warnings: 0 验证抽取数据字典数据: mysql> desc SYS_TABLES -> ; +--------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+-------+ | NAME | varchar(255) | NO | PRI | | | | ID | bigint(20) unsigned | NO | | 0 | | | N_COLS | int(10) | YES | | NULL | | | TYPE | int(10) unsigned | YES | | NULL | | | MIX_ID | bigint(20) unsigned | YES | | NULL | | | MIX_LEN | int(10) unsigned | YES | | NULL | | | CLUSTER_NAME | varchar(255) | YES | | NULL | | | SPACE | int(10) unsigned | YES | | NULL | | +--------------+---------------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> SELECT NAME,ID from SYS_TABLES WHERE NAME='xifenfei/get_dict'; +-------------------+----+ | NAME | ID | +-------------------+----+ | xifenfei/get_dict | 23 | +-------------------+----+ 1 row in set (0.00 sec) mysql> desc SYS_COLUMNS -> ; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | TABLE_ID | bigint(20) unsigned | NO | PRI | NULL | | | POS | int(10) unsigned | NO | PRI | NULL | | | NAME | varchar(255) | YES | | NULL | | | MTYPE | int(10) unsigned | YES | | NULL | | | PRTYPE | int(10) unsigned | YES | | NULL | | | LEN | int(10) unsigned | YES | | NULL | | | PREC | int(10) unsigned | YES | | NULL | | +----------+---------------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> SELECT TABLE_ID,NAME,MTYPE FROM SYS_COLUMNS WHERE TABLE_ID=23; +----------+------+-------+ | TABLE_ID | NAME | MTYPE | +----------+------+-------+ | 23 | id | 6 | | 23 | name | 1 | +----------+------+-------+ 2 rows in set (0.01 sec) mysql> SELECT TABLE_ID,ID,NAME,TYPE FROM SYS_INDEXES WHERE TABLE_ID=23; +----------+----+---------+------+ | TABLE_ID | ID | NAME | TYPE | +----------+----+---------+------+ | 23 | 25 | PRIMARY | 3 | +----------+----+---------+------+ 1 row in set (0.00 sec) 这里基本上可以看出来,在mysql数据库未启动情况下,使用工具可以正常抽取mysql数据字典信息. （编辑：云计算网_宿迁站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐