Mysql日志篇

一、Innodb存储引擎执行流程

1.1 不开启事务情况

1. 加载指定数据的一页数据到缓存中（buffer poll）
2. 修改内存数据（buffer poll）
3. 更新redo log buffer（将物理修改信息写入，比如在磁盘的第0X01918修改了某某数据）
4. 写入bin log日志
5. 写入redo log提交标记（commit）
6. mysql后台线程定时将缓存（buffer poll）数据同步到磁盘
7. 开机时读取redo log中的数据，如果有未持久化到指定的IBD文件数据则进行持久化操作

1.2 开启事务情况

1. 加载指定数据的一页数据到缓存中（buffer poll）
2. 修改内存数据（buffer poll）
3. 写入undo log日志文件，用于事务失败时的回滚
4. 更新redo log buffer（将物理修改信息写入，比如在磁盘的第0X01918修改了某某数据）
5. 写入bin log日志
6. 写入redo log提交标记（commit）
7. mysql后台线程定时将缓存（buffer poll）数据同步到磁盘
8. 开机时读取redo log中的数据，如果有未持久化到指定的IBD文件数据则进行持久化操作

---

二、redo log日志

提升mysql写入磁盘性能，如果修改数据是不同的数据表，也就是不同的ibd文件，它是分不到磁盘不同的位置的，写入性能稍差；我们可以将修改的内容记录到redo log的一个文件，也就是让其支持了磁盘顺序写，从而提高整体性能。

2.1 重点参数设置

2.1.1 缓存区（redo log buffer）大小设置

innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

--查看缓存区大小
show variables like '%innodb_log_buffer_size%';

2.1.2 文件存储位置设置

innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。

-- 查看redo log存储位置
show variables like '%innodb_log_group_home_dir%';

2.1.3 设置redo log文件个数

设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。

-- 查看文件个数
show variables like '%innodb_log_files_in_group%';

2.1.4 设置单个文件大小

设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

-- 查看单个文件大小
show variables like '%innodb_log_file_size%';

2.2 磁盘写入分析

redo log可以是多个文件，写入的时候是轮询写的，C语音内部维护两个指针（write pos）写入指针，持久化指针（check point）。两个指针之间的偏移量是允许写入的，一旦两个指针相同，mysql此时会暂停写入，现将没有持久化的内容强制持久化，并且重新设置持久化指针（check point），然后再接着写入。

2.2.1 写入策略

innodb_flush_log_at_trx_commit： 这个参数控制 redo log 的写入策略，它有三种可能取值：

• 设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。
• 设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
• 设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 操作系统函数 write 写到文件系统的 page cache，然后调用操作系统函数 fsync 持久化到磁盘文件

page cache说明：它是内存的一部分，我们知道内存的运行速度是远远大于磁盘的，写入磁盘数据之前会先经过内存，然后再写入磁盘，为了提高整体的写入速度，内存上开辟了一块页缓存（page cache），只要内存中将数据写入到这块区域，我们就人为已经写入到磁盘了，操作系统会定时的将这块数据内容给持久化到磁盘中。但是操作系统如果还没来得及将这块数据写入到磁盘就停机了，这部分数据就会丢失。

---

三、bin log日志

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。

启动binlog记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。

MySQL5.7 版本中，binlog默认是关闭的，8.0版本默认是打开的。上图中log_bin的值是OFF就代表binlog是关闭状态，打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。

在配置文件中的[mysqld]部分增加如下配置:

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

重启数据库后我们再去看data数据目录会多出两个文件，第一个就是binlog日志文件，第二个是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。

发生以下任何事件时, binlog日志文件会重新生成：

• 服务器启动或重新启动
• 服务器刷新日志，执行命令flush logs
• 日志文件大小达到 max_binlog_size 值，默认值为 1GB

3.1 查看开启状态

show variables like '%log_bin%';

log_bin #日志是否打开状态 OFF：未开启，ON：已开启
log_bin_basename#是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index#指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin#sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

3.2 重点参数设置

3.2.1 日志文件格式（binlog_format）

用参数 binlog_format 可以设置binlog日志的记录格式，mysql支持三种格式类型：

• STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。
• ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
• MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

3.2.2 文件存储位置和名字（log-bin）

log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下

log-bin=mysql-binlog

3.2.3 服务ID

server-id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错

server-id=1

3.2.4 文件过期时间

执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除，这个参数要结合设置的mysql备份策略来设置，如下图所示，中间不能有空挡时间，否则会丢失数据。

expire_logs_days = 15 

3.3 删除、重置binlog文件

--重置所有的binlog文件
reset master;
--删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';
--删除指定日期前的日志索引中binlog日志文件
purge master logs before '2023-01-21 14:00:00';

3.4 查看 binlog 日志文件

可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

查出来的binlog日志文件内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230127 21:13:51 server id 1  end_log_pos 123 CRC32 0x084f390f  Start: binlog v 4, server v 5.7.25-log created 230127 21:13:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#230127 21:13:51 server id 1  end_log_pos 154 CRC32 0x672ba207  Previous-GTIDs
# [empty]
# at 154
#230127 21:22:48 server id 1  end_log_pos 219 CRC32 0x8349d010  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230127 21:22:48 server id 1  end_log_pos 291 CRC32 0xbf49de02  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1674825768/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#230127 21:22:48 server id 1  end_log_pos 345 CRC32 0xc4ab653e  Table_map: `test`.`account` mapped to number 99
# at 345
#230127 21:22:48 server id 1  end_log_pos 413 CRC32 0x54a124bd  Update_rows: table id 99 flags: STMT_END_F
### UPDATE `test`.`account`
### WHERE
###   @1=1
###   @2='lilei'
###   @3=1000
### SET
###   @1=1
###   @2='lilei'
###   @3=2000
# at 413
#230127 21:22:48 server id 1  end_log_pos 444 CRC32 0x23355595  Xid = 10
COMMIT/*!*/;
# at 444
。。。

能看到里面有具体执行的修改伪sql语句以及执行时的相关情况。

3.5 数据恢复

3.5.1 binlog文件恢复

可以根据如下条件进行恢复：

• 全日志文件，恢复文件中的所有内容
• 根据偏移量（at XXX），一条binlog 中的 sql 都会有begin 和 commit ，我们找begin之前的at XXX和commit之后的at为条件恢复
• 根据时间恢复

#全量恢复
mysqlbinlog  --no-defaults --database=数据库名字 mysql-binlog.000009 | mysql -uroot -p123456 -v 数据库名字
#按照偏移量恢复
mysqlbinlog  --no-defaults --start-position=开始的at --stop-position=结束的at --database=数据库名字 mysql-binlog.000009 | mysql -uroot -p123456 -v 数据库名字
#按照时间恢复
mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23"  --database=数据库名字 mysql-binlog.000009 | mysql -uroot -p123456 -v 数据库名字

3.5.2 数据库备份恢复

如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。

一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

备份数据库一般可以用mysqldump 命令工具

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

mysql -u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test