MySql之binlog与数据恢复（Binlog and Data Recovery in MySQL）

MySql之binlog与数据恢复

什么是binlog

binlog我们一般叫做归档日志，他是mysql服务器层的日志，跟存储引擎无关，他记录的是所有DDL和DML的语句，不包含查询语句，binlog是一种逻辑日志，他记录的是sql语句的原始逻辑，binlog写满后会切换到下一个日志继续写，不会覆盖以前的日志。mysql8版本之后是默认是开的，这之前的版本需要我们手动的开启。

• DDL(Data Definition Language):数据库定义语言，主要用于定义和管理数据库的结构，包括创建，修改，删除库，表，视图，索引等，DDL的常用命令CREATE,ALTER ,DROP等。

• DML(Data Manipulation Language) :数据库操作语言，用于对数据的操作，包括查询，插入，修改，删除等，DML常用的命令INSERT，UPDATE，DELETE等

binlog的作用

binlog主要有两个作用：

• MySql主从复制：主机开启binlog，主机会将binlog同步复制该从节点，从节点通过读取binlog来同步主节点的数据

• mysql的数据恢复：通过使用binlog工具结合binlog可以将数据恢复到数据库某一时刻

binlog的三种模式

基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR） 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR） 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR） 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

binlog的开启

mysql> show variables like '%log_bin%';

默认是没有开启的，所以需要开启，需要编辑/etc/my.cnf 配置文件

#开启bin 日志log-bin=mysql-bin#服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。server-id=1#开启bin-log，并指定文件目录和文件名前缀。log-bin=/home/mysql/logs/binlog/bin-log # 每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。max_binlog_size=500M#日志缓存大小binlog_cache_size=128K #需要同步的数据库名字，如果是多个，就以此格式再写一行即可binlog-do-db=syn_test    #不需要同步的数据库名字，如果是多个，就以此格式再写一行即可。#binlog-ignore-db=mysql #当Slave从Master数据库读取日志时更新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。log-slave-updatesexpire_logs_day=7 #设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。binlog_format="MIXED" #设置bin-log日志文件格式为：MIXED，可以防止主键重复

然后重启mysql 即可  systemctl mysqld resteart

也可以查看当前的模式：

mysql> show variables like '%binlog_format%';

这个时候操作syn_test的话就有日志了，但是生成的日志是二进制的，我们需要工具mysqlbinlog来查看：

[root@node03 mysql]# mysqlbinlog master-bin.000004

使用myslq的命令可以查看：

mysql> show binlog events in 'master-bin.000004'

在这里插入图片描述

数据的恢复与回滚

恢复数据库中的数据的话，指定位置即可：

mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql -uroot -p mytest

或者可以根据时间将日志转为我们可以看的懂的sql:

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime="2023-12-12 00:00:00" --stop-datetime="2023-12-28 00:00:00" mysql-bin.000001 > output.txt

特别需要注意的是binlog通常是用来做数据恢复，而不是回滚，比如说0点数据库备份之后，到早上7点数据库损坏，那么可以根据binlog恢复0点到7点的所有操作数据，因为binlog下大部分记录的是sql语句，那就可以按照sql语句执行一遍即可，如果是数据回滚，在STATEMENT和MIXED模式下，update了某一个数据或者delete数据，binlog记录只是update和delete语句，那么update和delete的完整数据就不存在了，所以没办法进行数据的回滚。在row模式下，必须是binlog_row_image为full的情况下，update和delete是有记录详细的update信息的，是可以回滚的。比如在row模式执行下面语句

update teacher set t_name = '小七' WHERE t_id = '04';delete from  teacher WHERE t_id = '04';使用mysqlbinlog：mysqlbinlog --base64-output=decode-rows -v  mysql-bin.000036 > test.sql在ROW模式下使用mysqlbinlog要添加参数--base64-output=decode-rows 否则看不懂

我们只需要将数据还原回去就可以了，比如DELETE改为INSERT，update则是where和set互换等。这个日志中有记录事务的具体ID的，比如说XID=680，这个在事务中非常用，用于保证事务的持久性，这个需要和数据库引擎产生的日志redolog一起使用，这又涉及到了两阶段提交的概念，这个redolog后面再说。

binlog_row_image 这个参数是在基于行复制的情况下必要的参数，用于控制行镜像（row images）是如何写入到二进制日志（binlog）中，包含FULL，MINIMAL和 NOBLOB。

FULL模式下会记录更改前后的值。

MINMAL只记录行数据的最小部分默认情况下不知道是不是这个值，默认情况下我测试出来没有找打update具体的值，只看到有个操作。

NOBLOB类似语句FULL模式，但是BLOD和TEXT这种字段可能不记录，在没有BLOD和TEXT的情况下和FULL没有区别， 默认情况不写binlog_row_image这个参数的情况下，日志是下面这中：

BEGIN/*!*/;# at 295#241010 15:53:14 server id 1  end_log_pos 354 CRC32 0x39c16fbf   Table_map: `syn_test`.`teacher` mapped to number 427# at 354#241010 15:53:14 server id 1  end_log_pos 400 CRC32 0x107e5e6b   Write_rows: table id 427 flags: STMT_END_F# at 400#241010 15:53:14 server id 1  end_log_pos 431 CRC32 0x157bc910   Xid = 318COMMIT/*!*/;# at 431#241010 15:55:06 server id 1  end_log_pos 496 CRC32 0xe0e36b12   Anonymous_GTID  last_committed=1  sequence_number=2SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 496#241010 15:55:06 server id 1  end_log_pos 572 CRC32 0x76eabfdf   Query  thread_id=16  exec_time=0  error_code=0SET TIMESTAMP=1728546906/*!*/;BEGIN/*!*/;# at 572#241010 15:55:06 server id 1  end_log_pos 631 CRC32 0x5e8da5ca   Table_map: `syn_test`.`teacher` mapped to number 427# at 631#241010 15:55:06 server id 1  end_log_pos 689 CRC32 0x4eb2b41a   Update_rows: table id 427 flags: STMT_END_F# at 689#241010 15:55:06 server id 1  end_log_pos 720 CRC32 0x5a8ed720   Xid = 842COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/

你会发现根本没有具体的相关信息，那就没办法恢复了。回滚数据在少数的情况下我们可以手动的去还原，但是多的话，那就麻烦了，这就需要用到第三方工具了比如开源工具binlog2sql ，美团开源的闪回工具MyFlash等。

binlog日志的删除

手动删除：rest master 删除全部的日志，删除之后，日志从00001开始记录

purge master logs to 'binlog.xxxxx'  删除xxx之前的日志purge master logs before 'yyyy-mm-dd hh24:mi:ss' 删除yyyy-mm-dd hh24:mi:ss之前的日志

自动删除：

• 设置过期时间：可以通过设置expire_logs_days参数来自动删除旧的binlog文件。这个参数定义了MySQL清除过期日志的时间，默认值为0，表示不自动删除。例如，设置expire_logs_days=7表示只保留最近7天的binlog文件

• 设置binlog文件大小：通过max_binlog_size参数可以限制单个binlog文件的最大大小，当超过这个大小时，会自动创建新的binlog文件

• 对于MySQL 8.0及以上版本，建议使用binlog_expire_logs_seconds参数来设置binlog的过期时间，单位是秒。例如，设置binlog_expire_logs_seconds=604800表示binlog文件在7天后过期