一、开启binlog日志功能
1）编辑打开mysql配置文件/etc/mysql/my.cnf
┌──(root㉿kali)-[~]
└─# vim /etc/mysql/my.cnf在[mysqld] 区块添加 log-bin=mysql-bin 确认是打开状态(mysql-bin 是日志的基本名或前缀名)
注意：
每次服务器（数据库）重启，服务器会调用flush logs;，新创建一个binlog日志！
2）重启mysqld服务使配置生效
┌──(root㉿kali)-[~]
└─# service mysql restart3）查看binlog日志是否开启
MariaDB [(none)]> show variables like 'log%';
+---------------------------------+---------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------+
| log_bin | ON
常用的binlog日志操作命令
1）查看所有binlog日志列表
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 351 |
| mysql-bin.000002 | 328 |
| mysql-bin.000003 | 328 |
| mysql-bin.000004 | 328 |
+------------------+-----------+2）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 328 | | |
+------------------+----------+--------------+------------------+3）flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 351 |
| mysql-bin.000002 | 328 |
| mysql-bin.000003 | 328 |
| mysql-bin.000004 | 375 |
| mysql-bin.000005 | 371 |
+------------------+-----------+注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
4）重置(清空)所有binlog日志
MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.009 sec)
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 328 |
+------------------+-----------+
二、查看binlog日志内容，常用有两种方式
1）使用mysqlbinlog自带查看命令法：
注意：
–>binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看
–>binlog日志与数据库文件在同目录中
–>在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “–no-defaults”选项
“ps -ef|grep mysql”查看mysql的数据存放目录，可知是/var/lib//mysql
┌──(root㉿kali)-[~]
└─# cd /var/lib/mysql
┌──(root㉿kali)-[/var/lib/mysql]
└─# ls
aria_log.00000001 ib_logfile0 mysql-bin.000002 oupeng
aria_log_control ibtmp1 mysql-bin.000003 performance_schema
debian-10.5.flag multi-master.info mysql-bin.000004
ib_buffer_pool mysql mysql-bin.index
ibdata1 mysql-bin.000001 mysql_upgrade_info使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析：
┌──(root㉿kali)-[/var/lib/mysql]
└─# mysqlbinlog mysql-bin.000004
......
# at 459
#220717 23:28:36 server id 1 end_log_pos 609 CRC32 0xb14f9973 Query thread_id=32 exec_time=0 error_code=0
SET @@session.collation_database=DEFAULT/*!*/;
insert into changan(name,sec,age) values('zhangsan','m','20'),('小红','w','20') #执行的sql语句
/*!*/;
# at 609
#220717 23:28:36 server id 1 end_log_pos 640 CRC32 0xcc1b628a Xid = 190 #执行的时间
......解释：
server id 1 ： 数据库主机的服务号，
end_log_pos 609： sql结束时的pos节点，
thread_id=32： 线程号
2）上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息 下面介绍一种更为方便的查询命令：
命令格式
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
#IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
#FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
#LIMIT [offset,] ：偏移量(不指定就是0)
#row_count ：查询总条数(不指定就是所有行)MariaDB [oupeng]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1274 |
| mysql-bin.000002 | 432 |
| mysql-bin.000003 | 432 |
| mysql-bin.000004 | 640 |
+------------------+-----------+
4 rows in set (0.000 sec)
MariaDB [oupeng]> show binlog events in 'mysql-bin.000004' \G;
*************************** 1. row ***************************
Log_name: mysql-bin.000004
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 256
Info: Server ver: 10.5.12-MariaDB-1-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000004
Pos: 256
Event_type: Gtid_list
Server_id: 1
End_log_pos: 299
Info: [0-1-4]
*************************** 3. row ***************************
Log_name: mysql-bin.000004
Pos: 299
Event_type: Binlog_checkpoint
Server_id: 1
End_log_pos: 342
Info: mysql-bin.000003
*************************** 4. row ***************************
Log_name: mysql-bin.000004
Pos: 342
Event_type: Binlog_checkpoint
Server_id: 1
End_log_pos: 385
Info: mysql-bin.000004
*************************** 5. row ***************************
Log_name: mysql-bin.000004
Pos: 385
Event_type: Gtid
Server_id: 1
End_log_pos: 427
Info: BEGIN GTID 0-1-5
*************************** 6. row ***************************
Log_name: mysql-bin.000004
Pos: 427
Event_type: Intvar
Server_id: 1
End_log_pos: 459
Info: INSERT_ID=3
*************************** 7. row ***************************
Log_name: mysql-bin.000004
Pos: 459
Event_type: Query
Server_id: 1
End_log_pos: 609
Info: use `oupeng`; insert into changan(name,sec,age) values('zhangsan','m','20'),('小红','w','20')
*************************** 8. row ***************************
Log_name: mysql-bin.000004
Pos: 609
Event_type: Xid
Server_id: 1
End_log_pos: 640
Info: COMMIT /* xid=190 */
8 rows in set (0.000 sec)
ERROR: No query specified上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！ 如下操作示例：
a）查询第一个(最早)的binlog日志：
mysql> show binlog events\G;b）指定查询 mysql-bin.000004这个文件：
mysql> show binlog events in 'mysql-bin.000004'\G;c）指定查询 mysql-bin.000004这个文件，从pos点:609开始查起：
mysql> show binlog events in 'mysql-bin.000004' from 609\G;d）指定查询 mysql-bin.000004这个文件，从pos点:609开始查起，查询10条（即10条语句
mysql> show binlog events in 'mysql-bin.000004' from 609 limit 10\G;e）指定查询 mysql-bin.000004这个文件，从pos点:609开始查起，偏移2行（即中间跳过2个，查询10条
mysql> show binlog events in 'mysql-bin.000004' from 609 limit 2,10\G;
四、利用binlog日志恢复mysql数据
事先准备一张测试的表，oupeng库changan表
MariaDB [oupeng]> select*from changan;
+----+------------+-----+-----+
| id | name | sec | age |
+----+------------+-----+-----+
| 1 | zhouyvlong | m | 30 |
| 2 | wangyi | m | 30 |
+----+------------+-----+-----+下面开始进行场景模拟：
1） oupeng库会在每天凌晨4点进行一次完全备份的定时计划任务，如下：
┌──(root㉿kali)-[~]
└─# crontab -l 0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x –master-data=2 oupeng |gzip >/opt/backup/oupeng_$(date +%F).sql.gz
这里手动执行下，将oupeng数据库备份到/opt/backup/oupeng
$(date +%F).sql.gz文件中：
┌──(root㉿kali)-[/opt]
└─# mysqldump -uroot -p -B -F -R -x --master-data=2 oupeng |gzip >/opt/backup/oupeng$(date +%F).sql.gz
Enter password:
┌──(root㉿kali)-[/opt/backup]
└─# ls
oupeng2022-07-17.sql.gz
参数说明：
-B：指定数据库 -F：刷新日志 -R：备份存储过程等 -x：锁表 –master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！
由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生 一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作 查看一下：
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 149 | | |
+------------------+----------+--------------+------------------+也就是说， mysql-bin.000005 是用来记录4:00之后对数据库的所有“增删改”操作。
2） 早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。 比如：在ops库下member表内插入、修改了数据等等：
先是早上进行插入数据
MariaDB [oupeng]> insert into changan(name,sec,age) values('zhangsan','m','20'),
('小红','w','20')
MariaDB [oupeng]> select*from changan;
+----+------------+-----+-----+
| id | name | sec | age |
+----+------------+-----+-----+
| 1 | zhouyvlong | m | 30 |
| 2 | wangyi | m | 30 |
| 3 | zhangsan | m | 20 |
| 4 | 小红 | w | 20 |
+----+------------+-----+-----+
4 rows in set (0.001 sec)3） 中午又执行了修改和插入数据操作：
MariaDB [(none)]> update oupeng.changan set name='大牛' where id=1 ;
Query OK, 1 row affected (0.004 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [(none)]> insert into oupeng.changan(name,sec,age) values('小小','m',18);
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> select*from oupeng.changan;
+----+----------+-----+-----+
| id | name | sec | age |
+----+----------+-----+-----+
| 1 | 大牛 | m | 30 |
| 2 | wangyi | m | 30 |
| 3 | zhangsan | m | 20 |
| 4 | 小红 | w | 20 |
| 5 | 小小 | m | 18 |
+----+----------+-----+-----+
5 rows in set (0.000 sec)4）在下午18:00的时候，悲剧莫名其妙的出现了！
手贱执行了drop语句，直接删除了ops库！吓尿了！要不要跑路！
MariaDB [(none)]> drop database oupeng;
Query OK, 1 row affected (0.006 sec)5） 这种时候，一定不要慌张！！！ 先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
先备份一下最后一个binlog日志文件：
┌──(root㉿kali)-[/home/kali2000]
└─# cd /var/lib/mysql
┌──(root㉿kali)-[/var/lib/mysql]
└─# cp -v mysql-bin.000005 /opt/backup/
'mysql-bin.000005' -> '/opt/backup/mysql-bin.000005'
┌──(root㉿kali)-[/var/lib/mysql]
└─# ls /opt/backup
mysql-bin.000005 oupeng2022-07-17.sql.gz接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。
按理说mysql-bin.000005 这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件。
MariaDB [(none)]> flush logs;
Query OK, 0 rows affected (0.007 sec)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 385 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)6） 读取binlog日志，分析问题。 读取binlog日志的方法上面已经说到。
方法一：使用mysqlbinlog读取binlog日志(不推荐)：
┌──(root㉿kali)-[~]
└─# cd /var/lib/mysql
┌──(root㉿kali)-[/var/lib/mysql]
└─# mysqlbinlog mysql-bin.000005 方法二：登录服务器，并查看(推荐此种方法)：
MariaDB [(none)]> show binlog events in 'mysql-bin.000005';
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 146 | Server ver: 10.5.12-MariaDB-1-log, Binlog ver: 4 |
| mysql-bin.000005 | 146 | Gtid_list | 1 | 189 | [0-1-5] |
| mysql-bin.000005 | 189 | Binlog_checkpoint | 1 | 215 | mysql-bin.000005 |
| mysql-bin.000004 | 215 | Gtid | 1 | 237 | BEGIN GTID 0-1-5 |
| mysql-bin.000004 | 237 | Intvar | 1 | 259 | INSERT_ID=3 |
| mysql-bin.000004 | 259 | Query | 1 | 329 | use `oupeng`; insert into changan(name,sec,age) values('zhangsan','m','20'),('小红','w','20')
| mysql-bin.000004 | 329 | Xid | 1 | 352 | COMMIT /* xid=68 */ |
| mysql-bin.000005 | 352 | Gtid | 1 | 384 | BEGIN GTID 0-1-6 |
| mysql-bin.000005 | 384 | Query | 1 | 497 | update oupeng.changan set name='大牛' where id=1 |
| mysql-bin.000005 | 497 | Xid | 1 | 528 | COMMIT /* xid=70 */ |
| mysql-bin.000005 | 528 | Gtid | 1 | 570 | BEGIN GTID 0-1-7 |
| mysql-bin.000005 | 570 | Intvar | 1 | 602 | INSERT_ID=5 |
| mysql-bin.000005 | 602 | Query | 1 | 729 | insert into oupeng.changan(name,sec,age) values('小小','m',18) |
| mysql-bin.000005 | 729 | Xid | 1 | 760 | COMMIT /* xid=72 */ |
| mysql-bin.000005 | 760 | Gtid | 1 | 802 | GTID 0-1-8 |
| mysql-bin.000005 | 802 | Query | 1 | 891 | drop database oupeng |
| mysql-bin.000005 | 891 | Rotate | 1 | 938 | mysql-bin.000006;pos=4 |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------------------------+
17 rows in set (0.000 sec)或者
MariaDB [oupeng]> show binlog events in 'mysql-bin.000005'\G;
......
......
*************************** 12. row ***************************
Log_name: mysql-bin.000005
Pos: 802
Event_type: Query
Server_id: 1
End_log_pos: 891
Info: drop database oupeng
*************************** 13. row ***************************
Log_name: mysql-bin.000005
Pos: 891
Event_type: Rotate
Server_id: 1
End_log_pos: 938
Info: mysql-bin.000006;pos=4
13 rows in set (0.001 sec)通过分析，造成数据库破坏的pos点区间是介于 802–891 之间（这是按照日志区间的pos节点算的），只要恢复到802前就可。
7） 先把凌晨4点全备份的数据恢复：
┌──(root㉿kali)-[~]
└─# cd /opt/backup
┌──(root㉿kali)-[/opt/backup]
└─# ls
mysql-bin.000005 oupeng2022-07-17.sql.gz
┌──(root㉿kali)-[/opt/backup]
└─# gzip -d oupeng2022-07-17.sql.gz
┌──(root㉿kali)-[/opt/backup]
└─# mysql -uroot -p -v < oupeng2022-07-17.sql
Enter password:
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------
--------------
......
......
......
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
--------------这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。
查看一下oupeng库：
MariaDB [(none)]> use oupeng;
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
MariaDB [oupeng]> show tables;
+------------------+
| Tables_in_oupeng |
+------------------+
| changan |
+------------------+
1 row in set (0.001 sec)
MariaDB [oupeng]> select*from changan;
+----+------------+-----+-----+
| id | name | sec | age |
+----+------------+-----+-----+
| 1 | zhouyvlong | m | 30 |
| 2 | wangyi | m | 30 |
+----+------------+-----+-----+
2 rows in set (0.001 sec)数据库以恢复，但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00–18:00之间的数据还没有恢复回来！！ 怎么办呢？
莫慌！这可以根据前面提到的mysql-bin.000005的新binlog日志进行恢复。
8） 从binlog日志恢复数据 恢复命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
——————————————————–
常用参数选项解释：
–start-position=802 起始pos点
–stop-position=891 结束pos点
–start-datetime=”2022-7-18 13:21:26″ 起始时间点
–stop-datetime=”2022-7-18 13:27:52″ 结束时间点
–database=test 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
——————————————————–
不常用选项：
-u –user=name 连接到远程主机的用户名
-p –password[=name] 连接到远程主机的密码
-h –host=name 从远程主机上获取binlog日志
–read-from-remote-server 从某个MySQL服务器上读取binlog日志
——————————————————–
小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；
a）完全恢复(需要手动vim编辑mysql-bin.000005，将那条drop语句剔除掉)
┌──(root㉿kali)-[/opt/backup]
└─# cp /var/lib/mysql/mysql-bin.000005 /opt/backup
┌──(root㉿kali)-[/opt/backup]
└─# mysqlbinlog /opt/backup/mysql-bin.000005 > /opt/backup/000005.sql
┌──(root㉿kali)-[/opt/backup]
└─# vim /opt/backup/000005.sql #删除里面的drop语句
┌──(root㉿kali)-[/opt/backup]
└─# mysql -uroot -p -v < /opt/backup/000005.sql
温馨提示：
在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱！
b）指定pos结束点恢复(部分恢复)：
–stop-position=352 pos结束节点（按照事务区间算，是384）
注意：
此pos结束节点介于“changan表原始数据”与更新“name=’大牛’”之前的数据，这样就可以恢复到更改“name=’大牛’”之前的数据了。 操作如下：
┌──(root㉿kali)-[/opt/backup]
└─# /usr/bin/mysqlbinlog --stop-position=352 --database=oupeng /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v oupeng
Enter password:
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------
......MariaDB [oupeng]> select*from changan;
+----+------------+-----+-----+
| id | name | sec | age |
+----+------------+-----+-----+
| 1 | zhouyvlong | m | 30 |
| 2 | wangyi | m | 30 |
| 3 | zhangsan | m | 20 |
| 4 | 小红 | w | 20 |
+----+------------+-----+-----+
4 rows in set (0.001 sec)恢复截止到更改“name=’大牛’”之间的数据（按照事务区间算，是528）
┌──(root㉿kali)-[/opt/backup]
└─# /usr/bin/mysqlbinlog --stop-position=528 --database=oupeng /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v oupeng
Enter password:MariaDB [oupeng]> select*from changan;
+----+----------+-----+-----+
| id | name | sec | age |
+----+----------+-----+-----+
| 1 | 大牛 | m | 30 |
| 2 | wangyi | m | 30 |
| 3 | zhangsan | m | 20 |
| 4 | 小红 | w | 20 |
+----+----------+-----+-----+
4 rows in set (0.000 sec)c）指定pso点区间恢复(部分恢复)：
更新 name=’大牛’ 这条数据，日志区间是Pos[384] –> End_log_pos[497]，按事务区间是：Pos[353] –> End_log_pos[528]
插入 name=’小小’ 这条数据，日志区间是Pos[602] –> End_log_pos[729]，按事务区间是：Pos[528] –> End_log_pos[760]
c1）
单独恢复 name=’大牛’ 这步操作，可这样：
按照binlog日志区间单独恢复：
┌──(root㉿kali)-[/opt/backup]
└─# /usr/bin/mysqlbinlog --start-position=384 --stop-position=497 --database=oupeng /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v oupeng 按照事务区间单独恢复：
┌──(root㉿kali)-[/opt/backup]
└─# /usr/bin/mysqlbinlog --start-position=352 --stop-position=528 --database=oupeng /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v oupeng c2） 单独恢复 name=’小二’ 这步操作，可这样：
按照binlog日志区间单独恢复：
┌──(root㉿kali)-[/opt/backup]
└─# /usr/bin/mysqlbinlog --start-position=602 --stop-position=729 --database=oupeng /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v oupeng 按照事务区间单独恢复：
┌──(root㉿kali)-[/opt/backup]
└─# /usr/bin/mysqlbinlog --start-position=528 --stop-position=760 --database=oupeng /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v oupeng c3） 将 name=’李四’、name=’小二’ 多步操作一起恢复，需要按事务区间，可这样：
┌──(root㉿kali)-[/opt/backup]
└─# /usr/bin/mysqlbinlog --start-position=352 --stop-position=760 --database=oupeng /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v oupeng 查看数据库：
MariaDB [oupeng]> select*from changan;
+----+----------+-----+-----+
| id | name | sec | age |
+----+----------+-----+-----+
| 1 | 大牛 | m | 30 |
| 2 | wangyi | m | 30 |
| 3 | zhangsan | m | 20 |
| 4 | 小红 | w | 20 |
| 5 | 小小 | m | 18 |
+----+----------+-----+-----+
5 rows in set (0.000 sec)至此，被删除的数据已全部恢复！！！