一、概述

Mysql作为一个数据库服务，必然要有诸多日志来记录当前性能和事件，并且在出现故障时用以排错。

二、日志介绍

MySQL 支持丰富的日志类型，如下： 事务日志：transaction log 事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging 事务日志文件： ib_logfile0， ib_logfile1 错误日志 error log 通用日志 general log 慢查询日志 slow query log 二进制日志 binary log 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

1、事务日志

事务日志：transaction log 事务型存储引擎自行管理和使用，建议和数据文件分开存放，redo log和undo log 可以通过配置服务器选项来定义事务日志存放位置、数量、文件大小等信息。

show variables like '%innodb_log%';

image.png 事务日志性能优化

show variables like '%innodb_flush_log_at_trx%';

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性 0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供 更好的性能，但服务器崩溃可能丢失最后一秒的事务 2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系 统或停电可能导致最后一秒的交易丢失

高并发业务行业最佳实践，是使用第三种折衷配置（=2）

2、错误日志

mysqld启动和关闭过程中输出的事件信息 mysqld运行中产生的错误信息 event scheduler运行一个event时产生的日志信息 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件的路径：

#查看错误日志位置
SHOW GLOBAL VARIABLES LIKE 'log_error'
#查看错误记录级别
SHOW GLOBAL VARIABLES LIKE 'log_warnings';

image.png

image.png

3、通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句 通用日志可以保存在：file（默认值）或 table（mysql.general_log表）

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

4、慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作 慢查询相关变量

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒
slow_query_log_file=HOSTNAME-slow.log #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
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 版后已删除

5、二进制日志

记录导致数据改变或潜在导致数据改变的SQL语句 记录已提交的日志 不依赖于存储引擎类型 功能：通过“重放”日志文件中的事件来生成数据副本 注意：建议二进制日志和数据文件分开存放 二进制日志记录三种格式

  • 基于“语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
  • 基于“行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式
  • 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

查看当前的二进制记录模式

show variables like 'binlog_format';

image.png

二进制文件的构成

#有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： mariadb-bin.000001
2.索引文件：mysql|mariadb-bin.index，文本格式

二进制相关的服务器选项

sql_log_bin=ON|OFF：		#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：	#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开
启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：	#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m 		#此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m 	#限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：		#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：		#二进制日志可以自动删除的天数。 默认为0，即不自动删除

例如以下配置文件：

[mysqld]
server_id=05
log_bin=/data/logbin/testbin
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

查看二进制日志信息

mysql> show master logs;

image.png 查看二进制日志中信息

image.png 清除二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

切换二进制日志

FLUSH LOGS;

image.png

三、总结

以上的几个日志对于日常排障、优化和备份都有很重要。