1 日志分类
MySQL 支持丰富的日志类型
-
查询日志:query log
-
慢查询日志:slow query log
-
错误日志:error log
-
二进制日志: binary log
-
中继日志:reley log
- 在主从复制架构中,从服务器用于保存从主服务器的二进制日志中读取的事件
-
事务日志:transaction log
-
事务日志的写入类型为"追加",因此其操作为"顺序IO";通常也被称为:预写式日志 write ahead logging
-
事务日志文件: ib_logfile0, ib_logfile1
-
查看日志相关变量
show global variables like '%log%';
1.1 查询日志(记录查询操作)
查询日志:记录对数据库的查询操作,包括错误的SQL语句
查询日志可以保存在:file(默认值)或 table(mysql.general_log表)
相关配置:
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
示例:启用通用日志并记录至文件中
# 默认没有启用通用日志
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
# 启用
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
# 默认通用日志存放在文件中
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
# 通用日志存放的文件路径
mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file |
+----------------------------+
| /var/lib/mysql/centos8.log |
+----------------------------+
1 row in set (0.00 sec)
示例:通用日志记录到表中
# 修改通用日志,记录通用日志至mysql.general_log表中
MariaDB [mysql]> set global log_output="table";
MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'log_output';
# general_log表是CSV格式的存储引擎
mysql> show table status like 'general_log'\G
#general_log表是CSV的文本文件
[root@centos8 ~]#file /var/lib/mysql/mysql/general_log.CSV
/var/lib/mysql/mysql/general_log.CSV: ASCII text
[root@centos8 ~]#head /var/lib/mysql/mysql/general_log.CSV
MariaDB [mysql]> select * from mysql.general_log\G
示例:查找执行次数最多的前三条语句
MariaDB [mysql]> select argument,count(argument) num from mysql.general_log group by argument order by num desc limit 3;
示例:对访问的语句进行排序
[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr
[root@centos8 ~]#mysql -e 'select argument from mysql.general_log' |sort |uniq -c |sort -nr
1.2 慢查询日志
慢查询日志:记录执行查询时长超出指定时长的操作
相关配置
header 1 | header 2 |
---|---|
slow_query_log=ON|OFF | 开启或关闭慢查询,支持全局和会话,只有全局设置才会生成慢查询文件 select @@slow_query_log; |
long_query_time=N | 慢查询的阀值,单位秒,默认为10s select @@long_query_time; |
slow_query_log_file=HOSTNAME-slow.log | 定义慢查询的名称和存放路径 |
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,<br>query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | 定义慢查询的级别,上述查询类型且查询时长超过 long_query_time,则记录日志 |
log_queries_not_using_indexes=ON | 不使用索引或使用全索引扫描,不论是否达到慢查询阀值的语句都会记录日志,默认OFF,即不记录 select @@log_queries_not_using_indexes; |
log_slow_rate_limit = 1 | 定义慢查询速率,多少次查询才记录,mariadb特有 |
log_slow_verbosity= Query_plan,explain | 记录内容 |
log_slow_queries = OFF | 同slow_query_log,MariaDB 10.0/MySQL 5.6.1 版后已删除 |
示例:慢查询分析工具mysqldumpslow
[root@centos8 ~]#mysqldumpslow --help
[root@centos8 ~]#mysqldumpslow -s c -t 2 /var/lib/mysql/centos8-slow.log
查看当前时长
show global variables like '%long_query_time%';
查看单个参数
select @@global.long_query_time;
1.3 错误日志
-
mysqld启动和关闭过程中输出的事件信息
-
mysqld运行中产生的错误信息
-
event scheduler运行一个event时产生的日志信息
-
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
相关配置
log_error=/search/mariadb/data/bjyf_17_71.err
log_warnings=0|1 是否记录警告信息至错误日志文件中
错误文件路径
SHOW GLOBAL VARIABLES LIKE 'log_error';
示例
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.001 sec)
记录哪些警告信息至错误日志文件
# CentOS7 mariadb 5.5 默认值为1
# CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3... # MySQL5.7之前
log_error_verbosity=0|1|2|3... # MySQL8.0
示例
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set (0.001 sec)
示例:MySQL8.0变量变化
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| log_error_verbosity | 2 |
+---------------------+-------+
1 row in set (0.00 sec)
1.4 二进制日志
-
记录导致数据改变或潜在导致数据改变的SQL语句(查询语句不记录二进制日志)
-
记录已提交的日志
-
不依赖于存储引擎类型
功能:用于通过"重放"日志文件中的事件来生成数据副本
注意:建议二进制日志和数据文件分开存放
1.4.1 二进制日志记录格式:
-
基于"语句"记录:statement,记录sql语句,默认模式( MariaDB 10.2.3 版本以下 ),日志量较少
# 如果有15条记录,修改 stuid 大于等于 10 的数据,statement只有一条记录 # statement 数据不全,在不同的时间执行结果不同 update stu set age=20 where stuid >= 10;
-
基于"行"记录:row,记录行的内容,记两条,更新前和更新后都有,日志量较大,更加安全,建议使用的格式,MySQL8.0默认格式,也是生产中建议的格式
# 如果有15条记录,修改 stuid 大于等于 10 的数据,row 是有 6 条记录的 # row 数据完全,在不同的时间执行结果相同 update stu set age=20 where stuid = 10; update stu set age=20 where stuid = 11; update stu set age=20 where stuid = 12; ...... update stu set age=20 where stuid = 15;
-
混合模式:mixed, 让系统自行判定该基于哪种方式进行,默认模式( MariaDB 10.2.4及版本以上
)
格式配置
MariaDB [hellodb]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.001 sec)
# MySQL 8.0 默认使用ROW方式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.07 sec)
1.4.2 二进制日志文件的构成
两类文件
-
1.日志文件:mysql|mariadb-bin.文件名后缀,二进制格式,如: on.000001,mariadb-bin.000002
-
2.索引文件:mysql|mariadb-bin.index,文本格式,记录当前已有的二进制日志文件列表
1.4.3 相关设置
header 1 | header 2 |
---|---|
sql_log_bin=ON|OFF | 是否记录二进制日志,默认ON,支持动态修改,系统变量,而非服务器选项 select @@sql_log_bin; |
log_bin=/PATH/BIN_LOG_FILE | 指定文件位置;默认OFF,表示不启用二进制日志功能,上述两项都开启(sql_log_bin=ON)才可以 select @@log_bin; |
binlog_format=STATEMENT|ROW|MIXED | 二进制日志记录的格式,mariadb 5.5 默认STATEMENT,生产中建议ROW |
max_binlog_size=1073741824 | 单个二进制日志文件的最大体积,到达最大值会自动滚动,默认为1G 说明:文件达到上限时的大小未必为指定的精确值 |
binlog_cache_size=4m | 此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接) |
max_binlog_cache_size=512m | 限制用于缓存多事务查询的字节大小。 |
sync_binlog=1|0 | 设定是否启动二进制日志即时同步磁盘功能,默认0,由操作系统负责同步日志到磁盘 |
expire_logs_days=N | 二进制日志可以自动删除的天数。 默认为0,即不自动删除 |
1.4.4 mysqlbinlog
mysqlbinlog:二进制日志的客户端命令工具,支持离线查看二进制日志
命令格式
mysqlbinlog [OPTIONS] log_file…
--start-position= # 指定开始位置
--stop-position=
--start-datetime= # 时间格式:YYYY-MM-DD hh:mm:ss
--stop-datetime=
--base64-output[=name]
-v -vvv
示例
mysqlbinlog --start-position=320 --stop-position=450 /search/data/mysql/mariadb.bin.000006 -v
mysqlbinlog --start-datetime="2019-05-07 20:30:10" --stop-datetime="2019-05-07 20:35:22" mariadb-bin.000007 -vvv
1.4.5 二进制日志文件格式
# mysqlbinlog --no-defaults mysql-bin.000001
# at 27147
#170523 10:13:01 server id 1 end_log_pos 27269 Query thread_id=2 exec_time=0 error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1495505581/*!*/;
CREATE TEMPORARY TABLE tmp_proxies_priv LIKE proxies_priv;
/*!*/;
事件的起始位置:at 27147
事件发生的日期和时间:170523 10:13:01
事件发生的服务器标识:server id 1
事件的结束位置:end_log_pos 27269
事件的类型:Query
事件发生时所在服务器执行此事件的线程的ID:thread_id=2
语句的时间戳与将其写入二进制文件中的时间差:exec_time=0
错误代码:error_code=0
事件内容:
专属属性:GTID(Global Transaction ID)
1.4.6查看Mariadb自行管理使用中的二进制日志文件列表
show binary logs; 或 show master logs;
1.4.7 在线查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
示例
SHOW BINLOG EVENTS;
show binlog events in 'mysql-bin.000002' from 461 limit 2,3;
1.4.8 查看mariadb自行管理使用中的二进制日志文件列表,及大小
SHOW {BINARY | MASTER} LOGS
查看使用中的二进制日志文件
show master status
1.4.9 清除指定二进制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
示例
PURGE BINARY LOGS TO 'mariadb-bin.000003'; # 删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2020-01-23';
PURGE BINARY LOGS BEFORE '2020-03-15 09:25:30';
1.4.10 删除所有二进制日志,index文件重新记数
# 删除所有二进制日志文件,并重新生成日志文件,文件名从#开始记数,默认从1开始,一般是master主机第一次启动时执行,MariaDB 10.1.6开始支持TO
RESET MASTER [TO #];
1.4.11 切换日志文件
flush logs;
示例
[root@master ~]#file /var/lib/mysql/mariadb-bin.000001
/var/lib/mysql/mariadb-bin.000001: MySQL replication log, server id 18 MySQL
V5+, server version 10.3.17-MariaDB-log
示例:切换二进制日志
# 方法1
[root@centos8 ~]#mysqladmin flush-binary-log
# 方法2
[root@centos8 ~]#mysqladmin flush-logs
# 方法3
[root@centos8 ~]#mysql
MariaDB [hellodb]> flush logs;
1.5 中继日志
复制架构中,从服务器用于保存从主服务器的二进制日志中读取到的事件
1.6 事务日志
事务日志:transaction log
-
Redo log不是记录数据页“更新之后的状态”,而是记录这个页 “做了什么改动”
-
redo log:实现 WAL(Write-Ahead Logging),它的关键点就是先写日志(数据更新前先记录redo log ),再写磁盘,具体来说,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log 里面,并更新内存,这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做
-
undo log:保存与执行的操作相反的操作,用于实现rollback
事务型存储引擎自行管理和使用,建议和数据文件分开存放
Innodb事务日志相关配置
show variables like '%innodb_log%';
innodb_log_file_size 50331648 # 每个日志文件大小
innodb_log_files_in_group 2 # 日志组成员个数
innodb_log_group_home_dir ./ # 事务文件路径
事务日志性能优化
innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值,日志缓冲区将写入日志文件,并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能,但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区,但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些,但操作系统或停电可能导致最后一秒的交易丢失
高并发业务行业最佳实践,是使用第三种折衷配置(=2)
1.配置为2和配置为0,性能差异并不大,因为将数据从Log Buffer拷贝到OS cache,虽然跨越用户态与内核态,但毕竟只是内存的数据拷贝,速度很快
2.配置为2和配置为0,安全性差异巨大,操作系统崩溃的概率相比MySQL应用程序崩溃的概率,小很多,设置为2,只要操作系统不奔溃,也绝对不会丢数据
但是,丁奇大佬的建议是:
redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议你设置成 1,这样可以保证 MySQL 异常重启之后数据不丢失。
说明:
-
设置为1,同时 sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1,这样可以保证 MySQL 异常重启之后 binlog 不丢失。表示最高级别的容错
-
innodb_use_global_flush_log_at_trx_commit=0 时,将不能用SET语句重置此变量( MariaDB 10.2.6 后废弃)
1.7 两阶段提交
MySQL 如何恢复到半个月内任意一秒的状态?
我们现在看看 redo log
InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,设置每个文件的大小是 1GB,总共就可以记录 4GB 的操作。从头开始写,写到末尾就又回到开头循环写,如下面这个图所示。
有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为 crash-safe。
redo log 是 InnoDB 引擎特有的日志,而 Server 层也有自己的日志,称为 binlog(归档日志)。
对比项 | redo log | binlog |
---|---|---|
使用引擎 | InnoDB | 所有引擎都可以使用 |
日志类型 | 物理日志 | 逻辑日志,记录的是这个语句的原始逻辑 |
写磁盘方式 | 循环写,空间固定 | 追加写 |
两阶段提交
简单说,redo log 和 binlog 都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。
为了保证数据库的一致性,必须要保证两份日志一致,使用的两阶段式提交;其实很像事务,要么都成功要么都失败,不能让中间环节出现,也就是一个成功,一个失败