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 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
