进行一次完全备份：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
mysqldump -u root -p cl cls >/mysql-bak/cls-$(date +%F).sql
Enter password:
ll /mysql-bak/
total 4
-rw-r--r--. 1 root root 1952 Sep 13 12:22 cls-2020-09-13.sql
mysqladmin -u root -p flush-logs
Enter password:
ll /usr/local/mysql/data/...省略部分...-rw-r-----. 1 mysql mysql 1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql 154 Sep 13 12:24 mysql-bin.000002
-rw-r-----.
1 mysql mysql 78 Sep 13 12:24 mysql-bin.index
...省略部分...
录入新的内容并进行一次增量备份：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> use cl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into cls values ('004','滕森','10000',4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into cls values ('005','金娇','10000',5);
Query OK, 1 row affected (0.00 sec)
mysql> select*from cls;+--------+-----------+--------+--------+| 工号 | 姓名 | 工资 | 部门 |+--------+-----------+--------+--------+| 001 | 熏无空 | 18888 | 1 || 002 | 沙屋静 | 16666 | 2 || 003 | 竹巴结 | 14444 | 3 || 004 | 滕森 | 10000 | 4 || 005 | 金娇 | 10000 | 5 |+--------+-----------+--------+--------+
5 rows in set(0.00 sec)
mysqladmin -u root -p flush-logs;#再次查看二进制日志文件，看操作是否被记录生成文件
Enter password:
ll /usr/local/mysql/data/...省略部分...-rw-r-----. 1 mysql mysql 1849 Sep 13 12:24 mysql-bin.000001
-rw-r-----. 1 mysql mysql 753 Sep 13 12:30 mysql-bin.000002
-rw-r-----. 1 mysql mysql 154 Sep 13 12:30 mysql-bin.000003
-rw-r-----. 1 mysql mysql 117 Sep 13 12:30 mysql-bin.index
...省略部分...cp/usr/local/mysql/data/mysql-bin.000002 /mysql-bak/#将二进制日志文件复制到咱们创建的备份目录下
模拟失误操作删除cls表；
mysql -u root -p -e 'drop table cl.cls'
Enter password:
mysql -u root -p -e 'select * from cl.cls'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'cl.cls' doesn't exist
恢复操作：（完全备份恢复）
mysql -u root -p cl < /mysql-bak/cls-2020-09-13.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select*from cl.cls'
Enter password:
+--------+-----------+--------+--------+
| 工号 | 姓名 | 工资 | 部门 |
+--------+-----------+--------+--------+
| 001 | 熏无空 | 18888 | 1 |
| 002 | 沙屋静 | 16666 | 2 |
| 003 | 竹巴结 | 14444 | 3 |
+--------+-----------+--------+--------+
增量备份恢复：
mysqlbinlog --no-defaults /mysql-bak/mysql-bin.000002 |mysql -u root -p Enter password:
mysql -u root -p -e 'select*from cl.
cls'
Enter password:
+--------+-----------+--------+--------+| 工号 | 姓名 | 工资 | 部门 |+--------+-----------+--------+--------+| 001 | 熏无空 | 18888 | 1 || 002 | 沙屋静 | 16666 | 2 || 003 | 竹巴结 | 14444 | 3 || 004 | 滕森 | 10000 | 4 || 005 | 金娇 | 10000 | 5 |+--------+-----------+--------+--------+
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
基于位置恢复：
模拟删除表cls：
删除表cls---恢复下完全备份cl.2020.9.13.sql--查看二进制日志文件内想要恢复的位置
mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 ####--base64-output=decode-rows 64解码 ### -v 换行显示.........省略部分...........#at 289#200913 12:27:59 server id 1 end_log_pos 344 CRC32 0x25b12913 Table_map: `cl`.`cls` mapped to number 225#at 344#200913 12:27:59 server id 1 end_log_pos 399 CRC32 0xbd609658 Write_rows: table id 225 flags: STMT_END_F### INSERT INTO `cl`.`cls`### SET### @1='004'### @2='滕森'### @3='10000'### @4='4'# at 399#200913 12:27:59 server id 1 end_log_pos 430 CRC32 0x3561ca6a Xid = 74
COMMIT/*!*/;# at 565#200913 12:28:26 server id 1 end_log_pos 620 CRC32 0x5e09ca65 Table_map: `cl`.`cls` mapped to number 225# at 620#200913 12:28:26 server id 1 end_log_pos 675 CRC32 0x9a65af81 Write_rows: table id 225 flags: STMT_END_F### INSERT INTO `cl`.`cls`### SET### @1='005'### @2='金娇'### @3='10000'### @4='5'# at 675#200913 12:28:26 server id 1 end_log_pos 706 CRC32 0x510d9e90 Xid = 75
COMMIT/*!*/;.........省略部分...........
