MySQL数据库表误删除恢复(一) 场景一：MySQL 从库单张表误删除恢复 适用于： 测试环境： 测试数据： 主库： 模拟误删除操作： 从库： 主库继续插入数据： 此时从库同步报错： 详细信息如下： 检查t2对应的存储过程还在 解决方案： 一.从库上忽略该表的同步： 此时并不影响其他表数据的同步。 查看主从同步状态： Replicate_Wild_Ignore_Table: cjc.t2 详细信息如下： 此时SQL线程恢复正常 主库继续插入数据： 二：恢复从库t2表数据 0).关闭从库同步 1).主库添加读锁 读锁和写锁说明： 读锁 当前会话可以读取t2表数据，无法写入数据，立即返回错误ERROR 1099。 其他会话可以读取t2表数据，无法写入数据，写入时会卡住，Waiting for table metadata lock。 写锁 锁定后，当前会话仍然可以读、写t2表，其他会话对t2表不可读、不可写。 2).主库备份 注意： mysqldump中如果添加了--master-data=2或--flush-logs参数会导致备份时执行： 由于主库已经将t2表改只读状态，导致mysqldump时FLUSH TABLES操作卡住，无法正常备份表。 所以备份时需要去掉--master-data=2和--flush-logs参数。 备份 传到备库 3).导入到从库 从库 恢复 4).忽略过滤 从库检查表数据 忽略过滤 注意：无单引号 检查同步： 5).从库：启动同步 6).主、从：验证数据量是否一致 7).主库：解锁表 适用于： 测试环境： 创建测试数据 先看一下mysqldump备份选项： 执行数据库全备 全库备份 指定数据库备份 指定表备份 只备份表结构 只备份表数据 其中： --single-transaction 参数会添加下面额外执行： --master-data=2和--flush-logs都会添加下面额外操作，注意锁表： 继续插入数据 查看数据 场景二：MySQL恢复指定表结构 通常定时备份是备份所有数据库--all-databases，如何通过备份文件恢复所需的数据？ 例如：cjc库t2表 全备备份文件 恢复表结构 方式1：全备数据量很小时，直接通过vi进行查找 方式2：通过sed方式查找中备份文件中备份t2表结构部分 示例如下： 场景三：MySQL恢复指定表数据 例如：cjc库t1表 方式1：全备数据量很小时，直接通过vi进行查找 方式2：通过grep方式查找中备份文件中备份t1表数据 示例如下： 场景四：MySQL恢复指定库 通过--all-databases方式备份文件恢复指定数据库数据。 例如:cjc库数据 方式1：使用参数--one-database 还原： 通过general_log可以看到，只还原了cjc库。 方式2：sed 查看文件 示例如下： 场景五：MySQL恢复所有库数据 还原--all-databases备份的所有数据。 由于恢复了mysql库，还原完数据库后需要执行flush privileges;操作，或在备份是指定--flush-privileges。 参数说明如下： 场景六：MySQL恢复指定表到指定时间点 生成测试数据 备份表 继续插入数据库 更新数据 查看当前数据 删除数据，模拟误删除 如何将数据恢复到删除前时刻，恢复误删除的数据？ 主库查看信息 查看binlog信息 查看event信息 解析binlog数据 信息如下： 或者 备份数据还原 查询数据 已将t1表还原到备份时间点数据。 需要通过binlog将数据推进到误删除数据前一时刻。 查看备份文件位置信息 查询delete位置信息 前面查到的 或者 误删除开始位置 1312 这个位置应该在delete的上一个事务COMMIT下面的位置，也就是delete前面SET @@SESSION.GTID_NEXT对应的位置信息。 生成还原语句 先生成可读的文件，通过vi等校验是否有问题 在去掉-vvv --base64-output=decode-rows参数，生成最终恢复文件 注意： 这里必须选择上一条语句commit之后的position，不能是删除语句开始的position 1439，否则会有这个警告。 警告：打印的事件范围以未设置STMT_END_F标志的行事件或表映射事件结束。这可能是因为最后一条语句没有完全写入日志，或者是因为您使用了--stop-position或--stop-datetime来引用语句中间的事件。分部语句中的事件尚未写入输出。 使用 --skip-gtids=true 参数 如果要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例，那么就要使用该参数，否则无法恢复成功的。 因为相同的GTID事务已经在源数据库执行过了，根据 GTID 特性，一个 GTID 信息在一个数据库只能执行一次，所以默认不会恢复成功。 查看日志内容 刷新binlog 还原误删除的数据： 通常会先将数据还原到测试库里，确保没问题以后，在导出导入到生产库。 查看数据 已恢复到误删除前的数据 ###chenjuchao 20230512###