DBRECOVER FOR MYSQL 用户手册
source link: https://www.askmaclean.com/archives/dbrecover-for-mysql-user-manual-cn.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
DBRECOVER FOR MYSQL 用户手册
DBRECOVER FOR MYSQL是一个MySQL数据库(Innodb)恢复工具,该工具软件可以在MySQL没有备份的情况下,针对实例崩溃,Inoodb字典损坏无法启动数据库实例,DROP DATABASE,DROP TABLE,TRUNCATE TABLE,DELETE TABLE,磁盘/文件系统损坏等场景恢复数据库数据。
下载连接:https://zcdn.parnassusdata.com/dbrecover-for-mysql-2006.zip
视频教程:
- 使用DBRECOVER恢复MySQL中被drop的database https://zcdn.parnassusdata.com/dbrecover-for-mysql-recover-case-drop-database.mp4
- 使用DBRECOVER恢复奔溃的无法打开的MYSQL实例 https://zcdn.parnassusdata.com/dbrecover-for-mysql-recover-case-crash-instance.mp4
- 使用DBRECOVER读取mySQL中单个frm和ibd文件中的数据 https://zcdn.parnassusdata.com/dbrecover-for-mysql-read-single-ibd-frm.mp4
- 使用DBRECOVER恢复MySQL中被使用SQL DELETE删除的数据 https://zcdn.parnassusdata.com/dbrecover%20for%20mysql%20undelete%20records.mp4
- 使用DBRECOVER恢复MySQL的最简单例子 https://zcdn.parnassusdata.com/dbrecover%20for%20mysql%20quick%20recovery.mp4
- 使用DBRECOVER恢复MYSQL中被使用SQL DELETE删除的数据 https://zcdn.parnassusdata.com/dbrecover-for-mysql-drop-truncate-table-test.mp4
- 使用DBRECOVER恢复因磁盘或文件系统故障而导致不可用的MySQL实例中的数据 https://zcdn.parnassusdata.com/dbrecover-for-mysql-filesystem-disk-failure-recovery.mp4
- 图形化界面操作,无需学习命令行
- 支持版本覆盖MYSQL 5.1 到 MySQL 8.0(2020年5月),支持MYSQL 8.0的全新数据字典结构
- 支持INNODB存储引擎, MYISAM支持仍在开发中
- 支持各种故障导致的MYSQL实例崩溃下的数据恢复
- 恢复结果为MYSQLDUMP格式的SQL文件
- 支持对DELETE 操作的数据行恢复
- 支持对DROP TABLE, TRUNCATE TABLE 操作的表恢复
- 支持对DROP DataBase操作的数据库恢复
- 支持因磁盘故障/文件系统损坏等情况下的恢复
- 免费版支持每张表抽取1000行数据,每张表抽取100行被delete的数据
- 基于JAVA开发,支持Windows/Redhat/Centos/Ubuntu等操作系统
- 支持对FRM文件的恢复,将FRM文件解析为CREATE TABLE的SQL语句
- 支持对LOB/TEXT等大对象的恢复
针对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)
针对drop database场景的恢复
mysql> drop database employees; Query OK, 14 rows affected (0.16 sec) #sync #sync
启动DBRECOVER FOR MYSQL软件,选择DROP DATABASE恢复场景:
选择正确的MYSQL数据库版本:
选择SELECT DIRECTORY,输入@@datadir所在目录路径, 点击start
软件会扫描目录下的ibdata1或mysql.ibd并扫描@datadir所在磁盘:
之后点击dropped databases节点,可以找到相关被drop删除的数据库子节点,并恢复其中的表数据:
针对drop table 及 truncate table 场景的恢复
以下恢复步骤即适用于drop table 也适用于 truncate table
mysql> select count(*) from employees.employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.09 sec) mysql> select @@datadir; +-----------+ | @@datadir | +-----------+ | /m01/ | +-----------+ 1 row in set (0.00 sec) mysql> drop table employees.employees; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> SET FOREIGN_KEY_CHECKS=0; Query OK, 0 rows affected (0.00 sec) mysql> drop table employees.employees; Query OK, 0 rows affected (0.02 sec) mysql> ^DBye #sync #sync
启动DBRECOVER FOR MYSQL软件,选择DROP TABLE恢复场景:
选择正确的MYSQL数据库版本:
选择SELECT DIRECTORY,输入@@datadir所在目录路径, 点击start
软件会扫描目录下的ibdata1或mysql.ibd并扫描@datadir所在磁盘:
!!!注意这里一定要输入原始@@datadir所在目录;不能是出现问题后拷贝原文件的一个目录。因为软件需要扫描@@datadir所在文件系统所在挂载点才能找到被drop的数据。
之后点击对应数据库下的dropped tables节点,看是否存在对应被drop的表:
针对truncate table,只需要在数据库树形图中查看对应的普通数据表节点即可看到数据,并导出数据
可以在界面右侧观察到该表的数据,之后的恢复与常规模式一致。
针对delete table场景的恢复
mysql> use employees; Database changed mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec) mysql> delete from employees; mysql> flush table employees with read lock; Query OK, 0 rows affected (0.01 sec)
例如以上误删除表上记录的场景,我们可以用dbrecover for mysql的undelete功能恢复。
找到该表对应的ibd文件,例如 上例中employees表对应employees.ibd。
启动dbrecover for mysql程序,在开始菜单中选择添加ibd文件,并加入该ibd文件。
右击ibd文件选择扫描,扫描后出现对应的page文件,右键选择输入建表的SQL语句。
建表语句可以通过show create table 命令在mysql中获得:
mysql> show create table employees; +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | employees | CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC | +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
点击确定后,界面右侧出现表信息,点击deleted数据:
点击导出到文件,即可将delete掉的数据恢复为MYSQLDUMP形式的INSERT语句
FRM文件的恢复场景
FRM文件的读取恢复功能是DBRECOVER FOR MYSQL的免费功能。
在某些场景下我们需要从MYSQL的FRM文件中获得建表语句,只需要在软件主界面下选择添加frm文件:
双击加入的frm文件或右键查看文件,可以在右侧界面看到该FRM文件对应的建表语句,用户可复制该语句。
检查数据表上可恢复的行数
在恢复场景中,用户可以通过该功能了解某张表所能恢复的记录数量。
选中数据表,右键选择Show Table Recoverable Row Count:
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK