MySQL数据库(十四)_日志

By | 2019年 11月 23日
0 Comment

日志

  1. 事务日志 transaction log
  2. 错误日志 error log
  3. 通用日志 general log
  4. 慢查询日志 slow query log
  5. 二进制日志 binary log
  6. 中继日志 reley log

事务日志：

事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
事务日志文件： ib_logfile0， ib_logfile1
事务日志：transaction log

  1. 事务型存储引擎自行管理和使用，建议和数据文件分开存放
    redo log
    undo log
  2. Innodb事务日志相关配置：
MariaDB [hellodb]> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |      #每个日志文件大小
| innodb_log_checksums        | ON       |      
| innodb_log_compressed_pages | ON       |      
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |      #日志组成员个数
| innodb_log_group_home_dir   | ./       |      #事务文件路径
| innodb_log_optimize_ddl     | ON       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+
  1. innodb_flush_log_at_trx_commit
    说明：设置为1，同时sync_binlog = 1表示最高级别的容错 innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
    • 1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
    • 0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
    • 2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
在这里插入图片描述

图解

  1. 日志优化
    事务日志建议放在单独的一块磁盘上。
vim /etc/my.cnf
    [mysqld]
    innodb_log_group_home_dir=/yourpath

systemctl restart mariadb

错误日志

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

  1. 错误日志相关配置
    SHOW GLOBAL VARIABLES LIKE 'log_error'
  2. 错误文件路径
    log_error=/PATH/TO/LOG_ERROR_FILE
  3. 是否记录警告信息至错误日志文件
    log_warnings=1 默认值1

通用日志

通用日志：记录对数据库的通用操作，包括错误的SQL语句

  1. 通用日志相关设置
#默认值OFF关闭，ON启用
general_log=OFF
#通用日志存放路径
general_log_file=HOSTNAME.log
#存放在类型，默认FILE。如果选择TABLE，则放到mysql库的general_log这张表
log_output=TABLE|FILE|NONE

慢查询日志

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

  1. 相关配置
#开启或关闭慢查询
slow_query_log=ON|OFF 
#慢查询的阀值，单位秒，默认10秒
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 
#新版已废弃
log_slow_queries = OFF 同slow_query_log
  1. 示例
#临时开启慢查询日志
MariaDB [hellodb]> set global slow_query_log=on;

#查看是否开启
MariaDB [hellodb]> show global variables like 'slow_query_log%';
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| slow_query_log      | ON                 |
+---------------------+--------------------+

#默认10秒为长查询
MariaDB [(none)]> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
  1. 查询分析参数
#默认是OFF
MariaDB [hellodb]> show variables like 'profiling';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| profiling      | OFF   |
+----------------+-------+

查看使用过的命令

MariaDB [hellodb]> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration   | Query                            |
+----------+------------+----------------------------------+
|        1 | 0.00202081 | show variables like '%profiling' |
|        2 | 0.01857000 | select * from students           |
+----------+------------+----------------------------------+

根据ID就可以找到某条语句的执行过程

MariaDB [hellodb]> show profile for query 2;
+------------------------+----------+
| Status                 | Duration |
+------------------------+----------+
| Starting               | 0.000241 |
| Checking permissions   | 0.000021 |
| Opening tables         | 0.000930 |
| After opening tables   | 0.000176 |
| System lock            | 0.000272 |
| Table lock             | 0.000113 |
| Opening tables         | 0.009604 |
| After opening tables   | 0.000112 |
| System lock            | 0.000022 |
| Table lock             | 0.000591 |
| Closing tables         | 0.000013 |
| Unlocking tables       | 0.000013 |
| Closing tables         | 0.000070 |
| Init                   | 0.000215 |
| Optimizing             | 0.000030 |
| Statistics             | 0.002576 |
| Preparing              | 0.000037 |
| Executing              | 0.000007 |
| Sending data           | 0.003259 |
| End of update loop     | 0.000021 |
| Query end              | 0.000008 |
| Commit                 | 0.000152 |
| Closing tables         | 0.000016 |
| Unlocking tables       | 0.000006 |
| Closing tables         | 0.000011 |
| Starting cleanup       | 0.000007 |
| Freeing items          | 0.000010 |
| Updating status        | 0.000028 |
| Reset for next command | 0.000008 |
+------------------------+----------+

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

二进制日志

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型

功能：通过“重放”日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放

二进制日志记录格式

  1. 二进制日志记录三种格式
    • 基于“语句”记录：statement，记录语句，默认模式
    • 基于“行”记录：row，记录数据，日志量较大
    • 混合模式：mixed, 让系统自行判定该基于哪种方式进行
  2. 格式配置
    show variables like ‘binlog_format';

二进制日志文件的构成

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

二进制日志相关的服务器变量：

  1. 临时启用
set log_bin=ON;
  1. 写到配置文件
vim /etc/my.cnf
    [mysqld]
    log_bin=/yourpath
#这样就会在指定的路径下生成mariadb-bin的二进制文件。默认OFF，表示不启用二进制日志功能
#要生成二进制日志，还需要开启sql_log_bin=ON，不过这样默认是开启的
  1. 其它的配置
#二进制日志记录的格式，默认STATEMENT(语句)。ROW(行)，MIXED(混合)
binlog_format=STATEMENT|ROW|MIXED
#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
max_binlog_size=1073741824
说明：文件达到上限时的大小未必为指定的精确值
#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
sync_binlog=1|0
#二进制日志可以自动删除的天数。 默认为0，即不自动删除
expire_logs_days=N

二进制日志相关配置

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

#示例 
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

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

  1. 命令格式：
mysqlbinlog [OPTIONS] log_file…

[OPTIONS]
--start-position=#  #指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=

时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
-v -vvv
  1. 示例：
#例1
mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v

#例2
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
  1. 利用二进制重放数据库
mysqlbinlog mysql-bin.0000003 -v > testlog.sql

二进制日志事件的格式：

# 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

清除指定二进制日志：

  1. purge命令
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
  1. 示例：
PURGE BINARY LOGS TO 'mariadb-bin.000003';   #删除3之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
  1. 删除所有二进制日志，index文件重新记数
#mariadb-10.1.6可以使用TO #
RESET MASTER [TO #];

#删除所有二进制日志文件，并重新生成日志文件，
#文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行
  1. 切换日志文件：
FLUSH LOGS;
#执行后二进制文件就重新开始新的文件记录

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注