Xtrabackup + mysqlbinlog还原数据库

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

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

还原步骤分为：

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

##准备工作 修改/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