MySQL之：日志

日志：

• 事务日志：transaction log
• 中继日志：reley log
• 错误日志：error log
• 通用日志：general log
• 慢查询日志：slow query log
• 二进制日志：binary log

事务日志：transaction log

• 事务型存储引擎自行管理和使用，建议和数据文件分开存放
  修改配置文件：/etc/my.cnf

  [mysqld]
  innodb_log_file_size=1G
  innodb_log_file_in_group=5
  innodb_log_group_home_dir=/data/innodb_log      #自定义目录，和数据不要放在同一磁盘上
  

• 功能：
  • redo log
  • undo log
• Innodb事务日志相关配置：

  MariaDB [test]> show variables like '%innodb_log%';
  +---------------------------+---------+
  | Variable_name             | Value   |
  +---------------------------+---------+
  | innodb_log_block_size     | 512     |      #事务日志块大小
  | innodb_log_buffer_size    | 8388608 |      #事务日志写入缓存大小
  | innodb_log_file_size      | 5242880 |      #每个事务日志文件大小
  | innodb_log_files_in_group | 2       |      #事务日志组成员个数
  | innodb_log_group_home_dir | ./      |      #事务文件路径
  +---------------------------+---------+
  5 rows in set (0.00 sec)
  

中继日志：relay log

主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的事件

错误日志：

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

错误日志相关配置：

SHOW GLOBAL VARIABLES LIKE 'log_error';

• 错误文件路径：
  log_error=/PATH/TO/LOG_ERROR_FILE
• 是否记录警告信息至错误日志文件
  log_warnings=1|0默认值1

通用日志：

记录对数据库的通用操作，包括错误的SQL语句，通常在排错时才会启用

• 文件：file，默认值
• 表：table

通用日志相关设置：

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

慢查询日志：

记录执行查询时长超出指定时长的操作

• 开启或关闭慢查询
  slow_query_log=ON|OFF
• 慢查询的阈值，单位秒
  long_query_time=N
• 慢查询日志文件
  slow_query_log_file=HOSTNAME-slow.log
• 定义哪些类型查询时长超过long_query_time的值，则记录日志
  log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
• 不使用索引或使用全索引扫描的，不论是否达到慢查询阈值的语句是否需要记录日志；默认OFF，即不记录
  log_queries_not_using_indexes=ON
• 多少次查询才记录，mariadb特有
  log_slow_rate_limit=1
• 记录内容的详细程度
  log_slow_verbosity=Query_plan,explain
• 同slow_query_log，新版已废弃
  log_slow_queries=OFF

profileing变量：

分析查询语句的执行过程，每一步所花费的时间，进而优化查询语句

MariaDB [test]> SHOW VARIABLES LIKE 'profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> SHOW profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00026302 | select * from students        |
|        2 | 0.00017646 | SELECT * FROM students        |
|        3 | 0.00039128 | SHOW VARIABLES LIKE 'profil%' |
+----------+------------+-------------------------------+
3 rows in set (0.00 sec)

MariaDB [test]> SHOW profile FOR QUERY 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000050 |
| Opening tables       | 0.000035 |
| After opening tables | 0.000004 |
| System lock          | 0.000003 |
| Table lock           | 0.000003 |
| After table lock     | 0.000005 |
| init                 | 0.000009 |
| optimizing           | 0.000007 |
| statistics           | 0.000014 |
| preparing            | 0.000008 |
| executing            | 0.000003 |
| Filling schema table | 0.000187 |
| executing            | 0.000004 |
| Sending data         | 0.000022 |
| end                  | 0.000003 |
| query end            | 0.000003 |
| closing tables       | 0.000001 |
| removing tmp table   | 0.000003 |
| closing tables       | 0.000002 |
| freeing items        | 0.000003 |
| updating status      | 0.000019 |
| cleaning up          | 0.000003 |
+----------------------+----------+
22 rows in set (0.00 sec)

二进制日志

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

注意：建议二进制日志和数据文件分开存放

二进制日志记录格式：

• 二进制日志记录三种格式：
  1. 基于“语句”记录：statement，记录语句，默认模式
  2. 基于“行”记录：row，记录数据，日志量较大（强烈建议生产环境中配置此格式）
  3. 混合模式：mixed，让系统自行判定该基于哪种方式进行
• 格式配置
  show variables like '%binlog_format%';

二进制日志文件的构成：

有两类文件：

1. 日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
     如：mysql-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（建议改成 ROW）
• max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G（文件达到上限时的大小未必为指定的精确值）
• sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
• expire_log_days=N：二进制日志可以自动删除的天数。默认为0，即不自动删除

二进制日志相关配置

• 查看mariadb自行管理使用中的二进制日志文件列表，及大小
  SHOW {BINARY|MASTER} LOGS
• 查看使用中的二进制日志文件
  SHOW MASTER STATUS
• 查看二进制文件中的指定内容
  SHOW BINLOG EBENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 6516 LIMIT 2,3;

二进制日志的客户端命令工具：mysqlbinlog

• 格式：
  mysqlbinlog [OPTIONS] log_file...
  --start-position=# 指定开始位置
  --stop-position=#
  --start-datetime=
  --stop-datetime=
  时间格式：YYYY-MM-DD hh:mm:ss
  --base64-output[=name]
• 示例：
  mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003
mysqlbinlog --start-datetime=:2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003;

清除指定二进制日志：

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

• 示例：
  PURGE BINARY LOGS TO 'mariadb-bin.000003';：删除3之前的日志
  PURGE BINARY LOGS BEFORE '2017-01-23';：删除指定日期之前的日志
  PURGE BINARY LOGS BEFORE '2017-01-23 09:25:30';：删除指定时间之前的日志
• 删除所有二进制日志，index文件重新记数
  RESET MASTER [TO #];：日志文件从 # 开始记数，默认从1开始，一般是 master 第一次启动时执行，MariaDB10.1.6 开始支持 TO #
• 切换日志文件：
  FLUSH LOGS;