MySQL8.0 InnoDB日志

1.InnoDB日志介绍

InnoDB日志保存着已经提交的数据变化，用于在崩溃恢复时把数据库的变化恢复到数据文件，除了崩溃恢复，其他时候都不会读日志文件。向日志文件写数据的方式是顺序写，这比离散写的效率要高很多，而向数据文件写数据通常是离散写比较多。

日志缓冲区是一个内存缓冲区，InnoDB使用它来缓冲重做日志事件，然后再将其写入磁盘。日志缓冲区的大小由系统参数innodb_log_buffer_size控制，默认是16 MB，在大多数情况下是够用的。

如果有大型事务或大量较小的并发事务，可以考虑增大innodb_log_buffer_size，这个参数在MySQL 8中可以动态设置。
默认在datadir下有两个48MB的日志文件ib_logfile0和ib_logfile1。

2.日志产生量

InnoDB的日志产生量是衡量数据库繁忙程度的重要指标，也是设置日志文件大小的依据。查询日志产生量的相关信息有两个方法。
第一个方法是查询information_schema.innodb_metrics或sys.metrics视图中的对应计量值。

第二个方法是使用show engine innodb status命令查询日志产生量的相关信息，这些信息在输出的
LOG部分，这种方法不需要激活InnoDB中的相关计量。

查询日志视图
使用下面的命令可以激活这些计量：
mysql> set global innodb_monitor_enable = 'log_lsn_%';

激活后，一个查询结果的例子如下：

root@db 16:20: [(none)]> select name,count,status from information_schema.innodb_metrics where name like 'log_lsn%';
+--------------------------------+-------+----------+
| name | count | status |
+--------------------------------+-------+----------+
| log_lsn_last_flush | 0 | disabled |
| log_lsn_last_checkpoint | 0 | disabled |
| log_lsn_current | 0 | disabled |
| log_lsn_archived | 0 | disabled |
| log_lsn_checkpoint_age | 0 | disabled |
| log_lsn_buf_dirty_pages_added | 0 | disabled |
| log_lsn_buf_pool_oldest_approx | 0 | disabled |
| log_lsn_buf_pool_oldest_lwm | 0 | disabled |
+--------------------------------+-------+----------+
8 rows in set (0.00 sec)

root@db 16:20: [(none)]> set global innodb_monitor_enable = 'log_lsn_%';
Query OK, 0 rows affected (0.00 sec)

root@db 16:20: [(none)]> select name,count,status from information_schema.innodb_metrics where name like 'log_lsn%';
+--------------------------------+-----------+---------+
| name | count | status |
+--------------------------------+-----------+---------+
| log_lsn_last_flush | 281261581 | enabled |
| log_lsn_last_checkpoint | 281261581 | enabled |
| log_lsn_current | 281261581 | enabled |
| log_lsn_archived | 0 | enabled |
| log_lsn_checkpoint_age | 0 | enabled |
| log_lsn_buf_dirty_pages_added | 281261581 | enabled |
| log_lsn_buf_pool_oldest_approx | 0 | enabled |
| log_lsn_buf_pool_oldest_lwm | 0 | enabled |
+--------------------------------+-----------+---------+
8 rows in set (0.01 sec)


这里的log_lsn_checkpoint_age是当前日志量减去最近一次检查点的日志量，等于log_lsn_current减去log_lsn_last_checkpoint，
也就是日志文件的使用量，因为对日志文件的写入是循环覆盖的，检查点之前的日志都已经写入数据文件了，不再需要了，可以被覆盖。这里看到的日志文件的使用量大约是17MB。

3.模拟测试数据

-- 示例表：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 插入一些示例数据
-- 往t1表插入100万行记录
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=1000000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();

-- 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();

使用show engine innodb statu查询日志产生量
mysql> show engine innodb status\G
...
---
LOG
---
Log sequence number 288566500
Log buffer assigned up to 288566500
Log buffer completed up to 288566500
Log written up to 288566500
Log flushed up to 288566141
Added dirty pages up to 288566500
Pages flushed up to 284916015
Last checkpoint at 284916015
Log minimum file id is 0
Log maximum file id is 0
35121 log i/o's done, 328.65 log i/o's/second

...
这里的lsn是288566500，最近一次检查点的lsn是284916015，计算出当前日志文件的使用量是这两个值之差：
root@db 16:38: [(none)]> select round((288566500-284916015)/1024/1024) logsize_MB;
+------------+
| logsize_MB |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec)

当前日志文件的使用量大约3MB。

4.设置日志文件大小大考虑

MySQL默认在数据目录下有两个48MB的日志文件，ib_logfile0和ib_logfile1。对于繁忙的数据库，这样的日志文件通常太小，因为当日志文件写满时，会触发检查点，把内存中的数据写入磁盘，小的日志文件会频繁地触发检查点，增加写磁盘频率，引起系统性能下降。
大的日志文件能容纳的数据变化量大，会造成数据库在崩溃恢复时耗时较长，但新的MySQL版本的崩溃恢复速度已经很快了，因此把日志文件设置得大一些通常不会错，甚至可以设置得和InnoDB缓存池一样大。
另外一些备份工具要备份在备份过程中产生的重做日志，如果日志文件过小，备份工具备份日志的速度跟不上日志产生的速度时，需要备份的日志可能已经被覆盖了，例如XtraBackup工具可能会遇到下面的错误：
xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or log files being too small.

5.计算日志产生量
取消设置的pager：一个合理大小的日志文件应该可以容纳数据库在高峰时1到2个小时的数据变化。下面的例子是查询一分钟产生的日志量：

设置pager只显示lsn：
root@db 16:38: [(none)]> pager grep sequence
PAGER set to 'grep sequence'
root@db 16:41: [(none)]> show engine innodb status \G
Log sequence number 307594177
1 row in set (0.00 sec)

休眠一分钟：
root@db 16:41: [(none)]> select sleep(60);

1 row in set (1 min 0.00 sec)

root@db 16:43: [(none)]> show engine innodb status \G
Log sequence number 315743264
1 row in set (0.00 sec)

root@db 16:43: [(none)]> nopager
PAGER set to stdout

mysql> nopager
PAGER set to stdout
根据一分钟的采样，可以计算出一个小时
产生的日志量：

root@db 16:49: [(none)]> select round((315743264-307594177)*60/1024/1024) "1 hour log(MB)";
+----------------+
| 1 hour log(MB) |
+----------------+
| 466 |
+----------------+
1 row in set (0.00 sec)

这里一个小时的日志量是466MB。

决定日志文件的两个参数
日志文件的大小有两个参数决定：
（1） innodb_log_files_in_group：表示一个组里有多少个文件，默认为2。
（2） innodb_log_file_size：表示单个日志文件的大小，默认为48MB。
因此如果保持innodb_log_files_in_group为3 不变，把innodb_log_file_size设置为1024MB，可以容纳高峰期1024MB*3/466MB 约6个小时的日志。

root@db 16:52: [(none)]> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 3 |
+---------------------------+-------+
1 row in set (0.01 sec)

root@db 16:52: [(none)]> show variables like 'innodb_log_file_size';
+----------------------+------------+
| Variable_name | Value |
+----------------------+------------+
| innodb_log_file_size | 1073741824 |
+----------------------+------------+
1 row in set (0.00 sec)


修改日志文件大小的方法
修改日志文件大小的方法很简单，只需要修改参数文件中的innodb_log_file_size的设置，然后重新启动
MySQL即可。不需要删除当前的日志文件，在启动过程中，MySQL会发现参数值和当前日志文件的大小不一样，然后自动删除旧的日志文件，并创建新的日志文件.

6.采集日志脚本

#!/bin/bash
logfile="/root/scripts/lsn_inc.log"
user="****"
pwd="*****"
port=*****
host="****"

count=0
while [ $count -lt 36000 ]
do
now=$(date +"%Y-%m-%d %H:%M:%S")
# Run the command to get the InnoDB status
innodb_status=$(mysql -h $host -u $user --password=$pwd -P$port -e "SHOW ENGINE INNODB STATUS\G")
# Extract the Log sequence number
log_sequence_number=$(echo "$innodb_status" | grep "Log sequence number" | awk '{print $4}')
# Store the previous log sequence number in a file
if [ -f prev_log_sequence.txt ]; then
prev_log_sequence=$(cat prev_log_sequence.txt)
else
echo "$log_sequence_number" > prev_log_sequence.txt
prev_log_sequence=$log_sequence_number
#exit 0
fi
# Calculate the increment
increment=$(($log_sequence_number - $prev_log_sequence))
# Output the increment
echo "Log sequence number increment: $increment"
if [ $increment -ne 0 ];then
echo $now $increment >>$logfile
fi
# Update the previous log sequence number in the file
echo "$log_sequence_number" > prev_log_sequence.txt
count=$((count+1))
sleep 30
done