MySQL异常恢复之无主键情况下innodb数据恢复的方法_MySQL
本文讲述了MySQL异常恢复之无主键情况下innodb数据恢复的方法。分享给大家供大家参考，具体如下：
在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复
创建模拟表—无主键
mysql> CREATE TABLE `t1` ( -> `messageId` varchar(30) character set utf8 NOT NULL, -> `tokenId` varchar(20) character set utf8 NOT NULL, -> `mobile` varchar(14) character set utf8 default NULL, -> `msgFormat` int(1) NOT NULL, -> `msgContent` varchar(1000) character set utf8 default NULL, -> `scheduleDate` timestamp NOT NULL default '0000-00-00 00:00:00', -> `deliverState` int(1) default NULL, -> `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00' -> ) ENGINE=INnodb DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 select * from sms_service.sms_send_record; Query OK, 11 rows affected (0.00 sec) Records: 11 Duplicates: 0 Warnings: 0 ………… mysql> insert into t1 select * from t1; Query OK, 81664 rows affected (2.86 sec) Records: 81664 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 163328 rows affected (2.74 sec) Records: 163328 Duplicates: 0 Warnings: 0 mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 326656 | +----------+ 1 row in set (0.15 sec)
解析innodb文件
[root@web103 mysql_recovery]# rm -rf pages-ibdata1/ [root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 time of last access: 1440819443 Sat Aug 29 11:37:23 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 Opening file: /var/lib/mysql/ibdata1 File information: time of last access: 1440819443 Sat Aug 29 11:37:23 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 ID of device containing file: 2049 inode number: 1344553 protection: 100660 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 time of last access: 1440819443 Sat Aug 29 11:37:23 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 time of last access: 1440819443 Sat Aug 29 11:37:23 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 time of last access: 1440819443 Sat Aug 29 11:37:23 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 time of last access: 1440819443 Sat Aug 29 11:37:23 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) time of last access: 1440819443 Sat Aug 29 11:37:23 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2049 inode number: 1344553 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 463312 time of last access: 1440819465 Sat Aug 29 11:37:45 2015 time of last modification: 1440819463 Sat Aug 29 11:37:43 2015 time of last status change: 1440819463 Sat Aug 29 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process: 236978176 (226.000 MiB) All workers finished in 0 sec
恢复数据字典
[root@web103 mysql_recovery]# ./recover_dictionary.sh Generating dictionary tables dumps... OK Creating test database ... OK Creating dictionary tables in database test: SYS_TABLES ... OK SYS_COLUMNS ... OK SYS_INDEXES ... OK SYS_FIELDS ... OK All OK Loading dictionary tables data: SYS_TABLES ... 48 recs OK SYS_COLUMNS ... 397 recs OK SYS_INDEXES ... 67 recs OK SYS_FIELDS ... 89 recs OK All OK
分析数据字典,找出来index_id
这里需要注意对于没有主键的表恢复,我们对应的类型是GEN_CLUST_INDEX
mysql> select * from SYS_TABLES where name='test/t1'; +----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ | NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE | +----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ | test/t1 | 100 | 8 | 1 | 0 | 0 | | 0 | +----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ 40 rows in set (0.00 sec) mysql> SELECT * FROM SYS_INDEXES where table_id=100; +----------+-----+------------------------------+----------+------+-------+------------+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO | +----------+-----+------------------------------+----------+------+-------+------------+ | 100 | 119 | GEN_CLUST_INDEX | 0 | 1 | 0 | 2951 | +----------+-----+------------------------------+----------+------+-------+------------+ 67 rows in set (0.00 sec)
恢复数据
root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000119.page -t dictionary/t1.sql >/tmp/2.txt 2>2.sql [root@web103 mysql_recovery]# more /tmp/2.txt -- Page id: 10848, Format: COMPACT, Records list: Valid, Expected records: (73 73) 00000002141B 0000009924F2 80000027133548 t1 "82334502212106951" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141C 0000009924F2 80000027133558 t1 "82339012756833423" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141D 0000009924F2 80000027133568 t1 "8234322198577796" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141E 0000009924F2 80000027133578 t1 "10235259536125650" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141F 0000009924F2 80000027133588 t1 "10235353811295807" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 000000021420 0000009924F2 80000027133598 t1 "102354211240398235" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 000000021421 0000009924F2 800000271335A8 t1 "102354554052884567" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 000000021422 0000009924F2 800000271335B8 t1 "132213454294519126" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 000000021423 0000009924F2 800000271335C8 t1 "82329022242584577" "SDK-BBX-010-18681" "13718311436" 8 "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥 斯卡客服：400-620-7575。" "2010-01-01 00:00:00" 0 "2015-08-26 22:02:17" ………… [root@web103 mysql_recovery]# cat /tmp/2.txt|grep -v "Page id:"|wc -l 380731
因为没有主键,使得恢复出来记录可能有一些重复,整体而言,可以较为完美的恢复数据
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1359
52
How to solve mysql cannot be started
Apr 08, 2025 pm 02:21 PM
There are many reasons why MySQL startup fails, and it can be diagnosed by checking the error log. Common causes include port conflicts (check port occupancy and modify configuration), permission issues (check service running user permissions), configuration file errors (check parameter settings), data directory corruption (restore data or rebuild table space), InnoDB table space issues (check ibdata1 files), plug-in loading failure (check error log). When solving problems, you should analyze them based on the error log, find the root cause of the problem, and develop the habit of backing up data regularly to prevent and solve problems.
Does mysql optimize lock tables
Apr 08, 2025 pm 01:51 PM
MySQL uses shared locks and exclusive locks to manage concurrency, providing three lock types: table locks, row locks and page locks. Row locks can improve concurrency, and use the FOR UPDATE statement to add exclusive locks to rows. Pessimistic locks assume conflicts, and optimistic locks judge the data through the version number. Common lock table problems manifest as slow querying, use the SHOW PROCESSLIST command to view the queries held by the lock. Optimization measures include selecting appropriate indexes, reducing transaction scope, batch operations, and optimizing SQL statements.
How to use SUBSTRING_INDEX in MySQL
Apr 08, 2025 pm 02:09 PM
In MySQL database operations, string processing is an inevitable link. The SUBSTRING_INDEX function is designed for this, which can efficiently extract substrings based on separators. SUBSTRING_INDEX function application example The following example shows the flexibility and practicality of the SUBSTRING_INDEX function: Extract specific parts from the URL For example, extract domain name: SELECTSUBSTRING_INDEX('www.mysql.com','.',2); Extract file extension to easily get file extension: SELECTSUBSTRING_INDEX('file.pdf','.',-1); Processing does not exist
Does mysql need the internet
Apr 08, 2025 pm 02:18 PM
MySQL can run without network connections for basic data storage and management. However, network connection is required for interaction with other systems, remote access, or using advanced features such as replication and clustering. Additionally, security measures (such as firewalls), performance optimization (choose the right network connection), and data backup are critical to connecting to the Internet.
Can mysql and mariadb coexist
Apr 08, 2025 pm 02:27 PM
MySQL and MariaDB can coexist, but need to be configured with caution. The key is to allocate different port numbers and data directories to each database, and adjust parameters such as memory allocation and cache size. Connection pooling, application configuration, and version differences also need to be considered and need to be carefully tested and planned to avoid pitfalls. Running two databases simultaneously can cause performance problems in situations where resources are limited.
The primary key of mysql can be null
Apr 08, 2025 pm 03:03 PM
The MySQL primary key cannot be empty because the primary key is a key attribute that uniquely identifies each row in the database. If the primary key can be empty, the record cannot be uniquely identifies, which will lead to data confusion. When using self-incremental integer columns or UUIDs as primary keys, you should consider factors such as efficiency and space occupancy and choose an appropriate solution.
Does mysql need a server
Apr 08, 2025 pm 02:12 PM
For production environments, a server is usually required to run MySQL, for reasons including performance, reliability, security, and scalability. Servers usually have more powerful hardware, redundant configurations and stricter security measures. For small, low-load applications, MySQL can be run on local machines, but resource consumption, security risks and maintenance costs need to be carefully considered. For greater reliability and security, MySQL should be deployed on cloud or other servers. Choosing the appropriate server configuration requires evaluation based on application load and data volume.
Can mysql return json
Apr 08, 2025 pm 03:09 PM
MySQL can return JSON data. The JSON_EXTRACT function extracts field values. For complex queries, you can consider using the WHERE clause to filter JSON data, but pay attention to its performance impact. MySQL's support for JSON is constantly increasing, and it is recommended to pay attention to the latest version and features.
