接着说说mysqlbinlog解析工具如何做数据恢复

看腻了就来听听视频演示吧：https://www.bilibili.com/video/BV1Qh4y1Y7Fm/

mysqldump逻辑备份

• 优点：可备份恢复部分数据
• 缺点：速度慢

mysqlbinlog追binlog日志

# 备份文件查看要恢复的起始点 ooxx

# 解析binlog文件，打印误操作SQL前后各10行，选择要恢复的结束位点 # at xxoo
mysqlbinlog -vvv mysql-bin.000002 | grep "DROP xxoo" -A 10 -B 10

# Point-in-Time Recovery Using Binary Log
mysqlbinlog binlog.000001 binlog.000002 --start-position=ooxx --stop-position=xxoo | mysql -u root -p
1
2
3
4
5
6
7

全量备份

# 导出sakila库（表结构和数据）
mysqldump -uroot -proot -S /mysqldata/sock/mysql-3306.sock sakila > db_sakila.sql
# 导出多个库
mysqldump -uroot -proot --databases DB1 DB2 > db_db1_db2_$(date +%Y%m%d).sql
# 导出所有库
mysqldump -uroot -proot --all-databases > db_all.sql
# 脚步循环导出每个库并以库名文件保存
for DB in $(mysql -uroot -proot -e 'show databases' -s --skip-column-names);
do
	mysqldump -uroot -proot $DB > "$DB.sql";
done

# 备份表
mysqldump -uroot -proot --databases sakila --tables city > sakila_city.sql
mysqldump -uroot -proot sakila city > sakila_city.sql
# 指定多张表
mysqldump -uroot -proot dbname table1 table2 > tb_tbl1_tbl2.sql
# 只备份表结构
--no-data
# 只备份表数据
--no-create-info


# 文本格式备份
mysqldump -uroot -proot \
	--tab=/sakila_data \--备份目录，一个表分root用户写入的表结构SQL文件+mysql写入的表数据文本文件
	--fields-terminated-by=, \--分隔字段值的字符串，默认 "\t"
	--fields-enclosed-by='"' \--将字段值的字符括起来，默认为无
	--lines-terminated-by=0x0d0a \--每行的结束字符串，0x0d0a是回车的编码，默认 "\n"
	sakila                      
# BUG：--tab指定的目录同MySQL配置的不一致                      
[root@localhost ~]# mysqldump -uroot -proot -S /mysqldata/sock/mysql-3306.sock --set-gtid-purged=OFF --tab=/sakila_data --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a sakila
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
# 目录路径：只能导出到配置的目录下；空：没有限制。该变量是可读的，不能用set设置，要写在my.cnf下重启DB
mysql> show variables like "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |	-- 禁止，不能导出
+------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

增量备份

# 开启binlog日志
server_id=3306
log-bin=mysql-bin

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

# 先做全量备份
mysqldump -uroot -proot sakila > db_sakila.sql

# 查看数据目录
mysql> show variables like 'datadir';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| datadir       | /mysqldata/data/ |
+---------------+------------------+
1 row in set (0.00 sec)
[root@localhost ~]# ls -rtlh /mysqldata/data/mysql-bin.*
-rw-r----- 1 mysql mysql  38M Aug 23 08:13 /mysqldata/data/mysql-bin.000001
-rw-r----- 1 mysql mysql   38 Aug 23 08:13 /mysqldata/data/mysql-bin.index
-rw-r----- 1 mysql mysql 363K Aug 23 08:22 /mysqldata/data/mysql-bin.000002
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

增量恢复

• 删除损坏的库
• 使用全量备份恢复表数据
• 在增量备份中找出误操作纪录
• 回放误操作之前的纪录
• 忽略误操作纪录，回放之后的纪录

-- 模拟误删数据
mysql> delete from test;
Query OK, 1 rows affected (0.00 sec)

# 误操作后先刷新binlog日志，倒数第2新的那个binlog日志就是要拿来做恢复的
[root@localhost ～]# ls -rtlh /mysqldata/data/mysql-bin.*
-rw-r----- 1 mysql mysql  38M Aug 23 08:13 /mysqldata/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 859K Aug 23 08:33 /mysqldata/data/mysql-bin.000002
-rw-r----- 1 mysql mysql   57 Aug 23 08:33 /mysqldata/data/mysql-bin.index
# 告警忽略
[root@localhost ～]# mysqladmin -uroot -proot flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ～]# ls -rtlh /mysqldata/data/mysql-bin.*
-rw-r----- 1 mysql mysql  38M Aug 23 08:13 /mysqldata/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 859K Aug 23 08:33 /mysqldata/data/mysql-bin.000002
-rw-r----- 1 mysql mysql   57 Aug 23 08:33 /mysqldata/data/mysql-bin.index
-rw-r----- 1 mysql mysql 3.2K Aug 23 08:33 /mysqldata/data/mysql-bin.000003

# 准备恢复环境 
mysql> drop database sakila;
mysql> create database sakila;
mysql> use sakila
mysql> source /tmp/db_sakila.sql;
# 定位误操作位置，大写
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /mysqldata/data/mysql-bin.000002 | grep "DELETE FROM `sakila`.`text`" -A 10 -B 10
# at 853396		-- 此条日志的位置点
#220823  8:32:35 server id 3306  end_log_pos 853436 CRC32 0x13575540    Delete_rows: table id 685 flags: STMT_END_F		-- 此条日志的执行时间，以此时间为停止重放点
### DELETE FROM `sakila`.`text`
### WHERE
###   @1=2
# at 853436
#220823  8:32:35 server id 3306  end_log_pos 853467 CRC32 0xf25cc501    Xid = 400863
COMMIT/*!*/;
# at 853467
#220823  8:32:36 server id 3306  end_log_pos 853532 CRC32 0x187a0c08    GTID    last_committed=2252       sequence_number=2253    rbr_only=yes
# 基于时间点恢复，指定结束和开始时间点，注意格式
[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2022-08-23 08:32:35' /mysqldata/data/mysql-bin.000002 | mysql -uroot -proot
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2022-08-23 8:32:36' /mysqldata/data/mysql-bin.000002 | mysql -uroot -proot
# 基于位置点恢复，指定结束和开始的位置点
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='853396' /mysqldata/data/mysql-bin.000002 | mysql -uroot -proot
[root@localhost data]# mysqlbinlog --no-defaults --start-position='853467' /mysqldata/data/mysql-bin.000002 | mysql -uroot -proot

# 恢复后检查
mysql> select * from test;
Empty set (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

恢复原理：追赶binlog日志，跳过误操作的事务

MySQL的binlog系列和奇技操作：

先来聊聊MySQL的binlog文件解析
接着说说mysqlbinlog解析工具如何做数据恢复
再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能
接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能
借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能
再来介绍另一个binlog文件解析的第三方工具my2sql
顺带来聊聊MySQL误删ibdata数据文件的恢复
MySQL大表直接复制文件的copy方式