MySQL灾难恢复

MySQL灾难恢复

author	ahulxt
version	1.0
date	2019-09-06

• 误删 - binlog
• 宕机…

---

开启binlog

MySQL8MySQL8默认开启binlog

MySQL8默认开启binlog，可在/etc/my.cnf 中看到

# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin

MySQL 5 开启方法

[mysqld]
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
server-id = 1
log_bin = /var/log/mysql/mysql-bin #定义binarylog所在的目录及binlog以什么名字开始。
max_binlog_size = 1000M
binlog-format = row

查询是否开启：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

关闭方法

编辑my.cnf文件，一般路径为/etc/my.cnf，在mysqld下面添加：skip-log-bin 或 disable-log-bin。

mysql> show variables like 'log_bin'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

相关说明

所需工具

• mysqldump
• mysqladmin
• mysqlbinlog

相关信息

在mysql中查询：show VARIABLES like '%log_bin%'; 可得如下结果：

Variable_name	Value
log_bin	ON
log_bin_basename	/var/lib/mysql/binlog
log_bin_index	/var/lib/mysql/binlog.index
log_bin_trust_function_creators	OFF
log_bin_use_v1_row_events	OFF
sql_log_bin	ON

mysqld进程在开启binlog的情况下，会在第一次生成一个binlog.00001文件，之后每次重启进程递增1

在MySQL中查询：show master logs; 可得如下结果：

Log_name	File_size	Encrypted
binlog.000001	178	No
binlog.000002	178	No
binlog.000003	178	No
binlog.000004	155	No

默认binlog备份文件在/var/lib/mysql下，相关文件为binlog.index,binlog.00001~binlog.xxxxx，其中binlog.index 文件是所有binlog文件的列表（列出所有binary log所在路径和名字）。

查看当前数据库binary log的位置：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |     4899 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 当前使用的bin log是“binlog.000004”，位置是4899.

演示恢复

1. 建库、建表、插入数据

# 建库
create database student default character set utf8 collate utf8_general_ci;

use student;

# 建表
create table student(sno int(10) NOT NULL COMMENT '学号',sname varchar(16) NOT NULL COMMENT '姓名',ssex char(2) NOT NULL COMMENT '性别', sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄',sdept varchar(16) default NULL COMMENT '学生所在系别',PRIMARY KEY (sno)) ENGINE=Innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 插入数据
insert into student values(0001,'路亚','男','24','计算机网络');
insert into student values(0002,'elain','男','26','computer');
insert into student values(0003,'zhangxiao','男','28','物流');
insert into student values(0004,'jeacen','男','28','compter');
insert into student values(0005,'张扬','男','29','计算机科学');

2. 全量备份数据库

shell中全量导出，加了-F参数，备份后binlog会生成新文件

mysqldump -F -p123123123 --lock-all-tables -B student > /tmp/student.sql

man mysqldump 后在man界面输入 #714，可见–F和–lock-all-tables解释，-B=–database

–flush-logs, -F

Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. If you use this option in combination with the --all-databases option, the logs are flushed for each database dumped.

The exception is when using --lock-all-tables, --master-data, or --single-transaction: In this case, the logs are flushed only once, corresponding to the moment that all tables are locked by FLUSH TABLES WITH READ LOCK.

If you want your dump and the log flush to happen at exactly the same moment, you should use --flush-logs together with --lock-all-tables, --master-data, or --single-transaction.

在开始dump之前flush mysql 日志文件。该选项需要 RELOAD 权限。如果你将该选项与 --all-databases 结合使用，每个数据库的日志都会被flush for dump。

例外是当使用 --lock-all-tables, --master-data, 或 --single-transaction：这种情况下，日志会被flush一次，对应于所有表锁定（FLUSH TABLES WITH READ LOCK）的那一刻。

如果你希望dump 和 log flush 发生在完全相同的时刻，你应该把 --flush-logs 连同 --lock-all-tables, --master-data, 或 --single-transaction使用。

---

–lock-all-tables, -x

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.

请求锁定所有数据库中的所有表,以保证数据的一致性。这是由一个在整个导出期间的全局读锁实现的。这个选项会自动让–single-transaction 和 --lock-tables失效。

3. 增量数据插入并删库

# 增量数据
insert into student values(0006,'jason','男','20','compter');
insert into student values(0007,'tomsion','男','27','人力资源');

select * from student;

# 删库
drop database student;

SHOW DATABASES;

4. 恢复binlog生成sql语句

立即刷新并备份出需要的binlog日志文件：

使用mysqladmin重新刷新一次日志，新增了一个binlog文件，则新增的文件的上一个文件是包含了增量的数据

mysqladmin -uroot -pLxt@1552655742 flush-logs

flush-logs命令的作用就是关闭当前使用的binary log，然后打开一个新的binary log文件，文件的序号加1.

cp binlog.000002 /tmp/

将增量的备份文件转化为对应的sql文件

mysqlbinlog /tmp/binlog.000002 > /tmp/binlog.sql

可通过egrep -v "^#|^$|\*|^--" /tmp/binlog.sql 查看无注释的sql文件，编辑的语句已加密

BINLOG '
mchxXQ8BAAAAeAAAAHwAAAAAAAQAOC4wLjE2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgG4DNdf
BEGIN
BINLOG '
qchxXRMBAAAARgAAAH8BAAAAAHoAAAAAAAEACHN0dWRlbnQxAAdzdHVkZW50AAUDD/4BDwYwAP4G
MAAQAQEAAgEhc38jPQ==
qchxXR4BAAAAWgAAANkBAAAAAHoAAAAAAAEAAgAF/wAGAAAABWphc29uA+eUtxQHY29tcHRlcgAH
AAAAB3RvbXNpb24D55S3GwzkurrlipvotYTmupAL8OwU
drop database student1
DELIMITER ;

可以看到上面的语句中有drop，即之前执行的命令，将其删除，否则恢复的同时也会执行此命令。

5. 恢复数据

mysql -p123123123 < /tmp/student1.sql # 全量数据的恢复

mysql -p123123123  -e "select * from student1.student;" # 5条

mysql -p123123123 < /tmp/binlog.sql # 增量数据的恢复

mysql -p123123123 -e "select * from student1.student;" # 7条

基于时间点的增量恢复

mysqlbinlog /tmp/mysql-bin.000004 --start-datetime='2018-03-20 00:00:07' --stop-datetime='2018-03-20 00:10:07' -r /tmp/time.sql

基于位置点的增量恢复

mysqlbinlog /tmp/mysql-bin.000004 --start-position=510 --stop-position=1312 -r /tmp/pos.sql

没指定开始或结束和上面时间的定义一样。

无论是基于时间或基于位置，目的都是跳过误删的sql语句，哪种种更好，要根据实际情况来定。