使用DBRECOVER FOR MYSQL恢复innodb_force_recovery参数无法强制启动的MySQL实例数据
针对MySQL数据库无法打开的场景的恢复
此场景下用户尝试启动MySQL实例，但MySQL实例可能因数据库本身损坏而CRASH进程崩溃。
相关报错可能如下：
InnoDB: Waiting for the background threads to start InnoDB: Error: tablespace size stored in header is 3712 pages, but InnoDB: the sum of data file sizes is only 3072 pages InnoDB: Cannot start InnoDB. The tail of the system tablespace is InnoDB: missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? InnoDB: You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database. InnoDB: Assertion failure in thread 3876 in file ha_innodb.cc line 17352 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: Assertion failure in thread 140154354255616 in file trx0purge.c line 848 InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no InnoDB: We intentionally generate a memory trap. InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace database/table uses space ID: 882 at filepath [Note] InnoDB: Starting crash recovery. [ERROR] InnoDB: Tablespace 11904 was not found at ./example_db/example1.ibd. [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace. [ERROR] InnoDB: Tablespace 11905 was not found at ./example_db/example2.ibd. [ERROR] InnoDB: Cannot continue operation. InnoDB: Error: space header page consists of zero bytes in data file ./ibdata1 InnoDB: Database page corruption on disk or a failed file read of page 660. A table cannot be properly queried with the SELECT statement - additional possible output: MariaDB [psa]> select * from db_example.misc; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
- 建议首先将MYSQL实例关闭
- 在Windows下双击文件start_dbrecover_mysql.bat启动程序
- 在Linux下运行./start_dbrecover_mysql.sh启动程序(请确认在Linux上已安装这些包gtk2 libXtst.x86_64 xclock xorg-x11-xauth.x86_64 unzip: Centos 上yum install gtk2 libXtst.x86_64 xclock xorg-x11-xauth.x86_64 unzip)，推荐使用mobaxterm程序来启动远程图形化(https://zcdn.askmaclean.com/MobaXterm_Portable_v20.2.zip)
- 选择常规模式
- 选择对应的MYSQL版本，PageSize一般保持默认即可
- 点击选择目录，将MySQL数据文件夹所在目录加入，点击开始
- 在数据库树形图中选择你需要的表，双击可以浏览数据，此处最多显示1000行数据
- 点击导出到文件，会将该表的数据以MYSQLDUMP形式存放到SQL文件中
- 点击导出文件路径，可直达文件目录，其内容如下图
- 文件包含建表语句，和插入语句，使用mysql -uroot -p < 导出文件.sql 导入数据。
mysql -uroot -p < employees.sql Enter password: ******** mysql -uroot -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.14 MySQL Community Server - GPL Copyright (c) 2000, 2019, 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. mysql> use employees; Database changed mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec)