MySQL 数据恢复

直接恢复

直接恢复是使用备份文件做全量恢复,这是最常见的场景.

mysqldump 备份全量恢复

gzip -d backup.sql.gz | mysql -u<user> -h<host> -P<port> -p

xtrabackup 备份全量恢复

# 步骤一：解压（如果没有压缩可以忽略这一步）
innobackupex --decompress <备份文件所在目录>

# 步骤二：应用日志
innobackupex --apply-log <备份文件所在目录> 

# 步骤三：复制备份文件到数据目录
innobackupex --datadir=<MySQL数据目录> --copy-back <备份文件所在目录>

基于时间点恢复

新建测试表

恢复一个表
从 mysqldump 备份恢复一个表
从 xtrabackup 备份恢复一个表
MyISAM 表
假设从备份文件中恢复表 mytest.t_myisam.从备份文件中找到 t_myisam.frm, t_myisam.MYD, t_myisam.MYI 这 3 个文件,复制到对应的数据目录中,并授权.进入 MySQL.检查表情况
show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| mytest           |
| t_myisam         |
+------------------+
2 rows in set (0.00 sec)

chengqm-3306>>check table t_myisam;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| mytest.t_myisam | check | status   | OK       |
+-----------------+-------+----------+----------+
1 row in set (0.00 sec)

Innodb 表
假设从备份文件中恢复表 mytest.t_innodb,恢复前提是设置了 innodb_file_per_table = on

起一个新实例
在实例上建一个和原来一模一样的表
执行 alter table t_innodb discard tablespace; 删除表空间,这个操作会把 t_innodb.ibd 删除
从备份文件中找到 t_innodb.ibd 这个文件,复制到对应的数据目录,并授权
执行 alter table t_innodb IMPORT tablespace; 加载表空间
执行 flush table t_innodb;check table t_innodb; 检查表
使用 mysqldump 导出数据,然后再导入到要恢复的数据库

跳过误操作 SQL
使用备份文件恢复跳过
不开启 GTID
使用备份文件恢复的步骤和基于时间点恢复的操作差不多,区别在于多一个查找 binlog 操作.举个例子,我这里建立了两个表 a 和 b,每分钟插入一条数据，然后做全量备份,再删除表 b,现在要跳过这条 SQL.
删除表 b 后的数据库状态
show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a                |
+------------------+
1 row in set (0.00 sec)

找出备份时的日志位置
head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=38414;

找出执行了 drop table 语句的 pos 位置
开启 GTID

找出备份时的日志位置
找出执行了 drop table 语句的 GTID 值
导出备份时日志位置到最新的 binglog 日志
恢复备份文件
跳过这个 GTID

SET SESSION GTID_NEXT='对应的 GTID 值';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;


应用步骤 3 得到的增量 binlog 日志

使用延迟库跳过
不开启 GTID
使用延迟库恢复的关键操作在于 start slave until.我在测试环境搭建了两个 MySQL 节点,节点二延迟 600 秒,新建 a,b 两个表,每秒插入一条数据模拟业务数据插入.
localhost:3306 -> localhost:3307(delay 600)

当前节点二状态
show slave status \G;
...
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000039
          Read_Master_Log_Pos: 15524
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 22845
        Relay_Master_Log_File: mysql-bin.000038
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
        Seconds_Behind_Master: 600
...

当前节点二表
show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a                |
| b                |
+------------------+

在节点一删除表 b
drop table b;
Query OK, 0 rows affected (0.00 sec)

chengqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a                |
+------------------+
1 row in set (0.00 sec)

接下来就是跳过这条 SQL 的操作步骤
延迟库停止同步
stop slave;

找出执行了 drop table 语句的前一句的 pos 位置
开启 GTID

停止同步
找出执行了 drop table 语句的 GTID
执行这个 GTID 的事务

SET SESSION GTID_NEXT='对应的 GTID 值';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;


继续同步

闪回
闪回操作就是反向操作,比如执行了 delete from a where id=1,闪回就会执行对应的插入操作 insert into a (id,...) values(1,...),用于误操作数据,只对 DML 语句有效,且要求 binlog 格式设为 ROW.
binlog2sql
安装
wget https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip
unzip binlog2sql.zip
cd binlog2sql-master/

# 安装依赖
pip install -r requirements.txt

生成回滚 SQL
python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name>\
--start-file='<binlog_file>' \
--start-datetime='<start_time>' \
--stop-datetime='<stop_time>' > ./flashback.sql

python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \
--start-file='<binlog_file>' \
--start-position=<start_pos> \
--stop-position=<stop_pos> > ./flashback.sql

MyFlash
安装
# 依赖(centos)
yum install gcc*  pkg-config glib2 libgnomeui-devel -y

# 下载文件
wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip -O MyFlash.zip
unzip MyFlash.zip
cd MyFlash-master

# 编译安装
gcc -w  `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c  -o binary/flashback
mv binary /usr/local/MyFlash
ln -s /usr/local/MyFlash/flashback /usr/bin/flashback

使用
生成回滚语句
flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<start_pos> --stop-position=<stop_pos> 

执行后会生成 binlog_output_base.flashback 文件,需要用 mysqlbinlog 解析出来再使用
mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p

限制

binlog 格式必须为 row,且 binlog_row_image=full
仅支持 5.6 与 5.7
只能回滚 DML(增、删、改)