Xtrabackup + mysqlbinlog还原数据库

最新推荐文章于 2024-08-23 14:34:54 发布

转载最新推荐文章于 2024-08-23 14:34:54 发布 · 135 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/deepblue/blog/529380

文章标签：

#数据库 #python

本文介绍了一种MySQL数据库恢复方案，具体包括使用XtraBackup恢复指定时间点的数据状态、利用mysqlbinlog工具精确恢复误删表前后的数据变更，以及整个过程中涉及的关键步骤和技术要点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

假如出现一种情况:由于误操作，比如说在2015年11月04日10:17分删除了kaopu中的一张表，我们需要的是恢复到误操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句。

还原步骤分为：

备份当前mysql_binlog文件
使用XtraBackup恢复到2015年11月04日10:10分的状态。
通过mysqlbinlog恢复到误删除之前的状态。
跳过误删除的语句，恢复到误删除之后的状态。

##准备工作修改/etc/my.cnf文件，设置bind-address = 127.0.0.1，拒绝所有对数据库的外部操作。


[mysqld]
bind-address = 127.0.0.1

或者修改防火墙，拒绝访问3306端口。

然后重启mysql。

##XtraBackup恢复 XtraBackup数据库备份目录为：/niub/mysql_xtrabackup/，base为全备目录，increment为增量备份目录，所有的增备都是基于全备的。

例：

当前全备目录为：/niub/mysql_xtrabackup/base/2015-11-02_17-10-04/

增备目录为：/niub/mysql_xtrabackup/increment/20151104/2015-11-04_10-10-01/

###还原XtraBackup备份在全备上，使用—redo-only只做已提交事务，不回滚未提交事务


innobackupex --apply-log --redo-only /niub/mysql_xtrabackup/base/2015-11-02_17-10-04/

会出现以下结果：


innobackupex: completed OK!

###应用增量备份

innobackupex --apply-log --redo-only /niub/mysql_xtrabackup/base/2015-11-02_17-10-04/ --incremental-dir= /niub/mysql_xtrabackup/increment/20151104/2015-11-04_10-10-01/

###回滚未提交事务

当应用完所有增量备份的时候，就需要回滚所有为完成事务（如果最后一步加了 –redo-only就需要回滚未提交，不执行的话在服务启动阶段服务会处理未提交事务）。


innobackupex --apply-log /niub/mysql_xtrabackup/base/2015-11-02_17-10-04/

###还原备份使用innobackupex –copy-back来还原备份

注：datadir必须是为空的，innobackupex –copy-back不会覆盖已存在的文件，还要注意，还原时需要先关闭服务，如果服务是启动的，那么就不能还原到datadir


innobackupex --copy-back /niub/mysql_xtrabackup/base/2015-11-02_17-10-04/

后需要修改文件的所有者和权限：


chown -R mysql:mysql /niub/mysqldata/*

###还原脚本

#!/bin/bash
db_data=/niub/mysqldata
base_dir=/niub/mysql_xtrabackup/base/2015-11-02_17-10-04
service mysql stop
rm -rf $db_data/*
innobackupex --copy-back $base_dir
chown -R mysql:mysql $db_data/*
service mysql start

##mysqlbinlog恢复

###查询Position信息

SHOW master STATUS;

输出结果


+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |  99      |              |                  |
+------------------+----------+--------------+------------------+

获得XtraBackup恢复后的Position值为：99

使用命令


mysqlbinlog mysql-bin.000001 –database=kaopu

这个误操作的Position为：200，得出它前后操作的id分别为199和201

###恢复误操作之前的数据

mysqlbinlog --start-position="100" --stop-position="199" --database=kaopu | mysql -u root

###恢复误操作之后的数据

mysqlbinlog --start-position="201" --database=kaopu | mysql -u root

转载于:https://my.oschina.net/deepblue/blog/529380