MySQL日志管理

概述

MySQL提供了丰富的日志

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

事务日志

事务日志：transaction log

事务型存储引擎自行管理和使用，建议和数据文件分开存放，redo log和undo log

Innodb事务日志相关配置：

show variables like '%innodb_log%';

SELECT * FROM information_schema.INNODB_TRX;
innodb_log_file_size 50331648 每个日志文件大小
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为1

事务日志性能优化

如果发现数据库特别慢，可以修改这个flush这个值，从1修改为2.
事务日志是不能永久保存数据的。

innodb_flush_log_at_trx_commit=0|1|2

• 追求高性能用1
• 安全用0和2
• 2把数据放操作系统缓冲区，兼顾性能和安全性

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

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

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内
核态，但毕竟只是内存的数据拷贝，速度很快
2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置
为2，只要操作系统不奔溃，也绝对不会丢数据

说明：
设置为1，同时sync_binlog = 1表示最高级别的容错
innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB 10.2.6
后废弃）

错误日志

错误日志概述

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

错误文件路径

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)

是否记录警告信息至错误日志文件

#默认值1
log_warnings=1|0

范例：

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set (0.001 sec)

通用日志

默认事没有启动的
通用日志：记录对数据库的通用操作，包括错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
通用日志相关设置

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

谁，什么时间，干了什么事

找到对访问的语句进行排序

慢查询日记

找到慢的命令，默认超出10秒就是慢了，需要人为开启。

慢查询相关变量

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 版后已删除

启动了，会出现在数据库默认目录
-slow.log记录的就是慢的日志

不仅告诉慢，还告诉哪慢。

二进制日志（备份）

概述

已经提交的日志，对数据库的更新
一个小的监控，把每一个sql语句，增删改，全都记录在数据库中了。
不记录读。只记录操作。
可利用重放机制，重做，可以进行数据还原。

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

二进制日志记录三种格式

• 基于“语句”记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
• 基于“行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式 不会丢数据，详细的，完整的。
• 混合模式：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)

二进制日志文件的构成

有两类文件
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，即不自动删除

建议放在一个专门的目录中

此功能是必须配置的功能

二进制日志相关配置

• 查看mariadb自行管理使用中的二进制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

默认为1个G的数据。二进制日志是不会删的。

• 查看使用中的二进制日志文件

SHOW MASTER STATUS

• 在线查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

• 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=678 --stop-position=752 /var/lib/mysql/mariadbbin.
000003 -v
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-
30 20:35:22" mariadb-bin.000003 -vvv

二进制日志事件的格式：

# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1
exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

未完待续……