10.MySQL的日志

目录：
1.事务日志 transaction log
2.错误日志 error log
3.通用日志 general log
4.慢查询日志 slow query log
5.二进制日志 binary log
6.mysqlbinlog工具
7.日志管理

1. 事务日志： transaction log

NOTE:
1.记录事务的操作和存储引擎相关，MyISAM不支持事务
2.事务日志和事务相关，记录的事务可能未完成，系统默认启用，且不断覆盖
3.如有必要可以和数据分开存放

1.1 事务型存储引擎自行管理和使用

redo log
undo log

1.2 Innodb事务日志相关配置

show variables like '%innodb_log%';
innodb_log_file_size 5242880 每个日志文件大小，这个值可以稍微大一些，因为可能有比较大的事务
innodb_log_files_in_group 2 日志组成员个数，也可以尽量大一些，设定为4或者5
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为1
innodb_log_buffer_size  8388608   缓存大小
innodb_log_block_size   512      块大小

innodb_flush_log_at_trx_commit

说明：设置为1，同时sync_binlog = 1表示最高级别的容错
innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句
重置此变量
1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。
这是完全遵守ACID特性
0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提
供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一
些，但操作系统或停电可能导致最后一秒的交易丢失
3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持

事务日志优化.jpg

2.错误日志 error log

2.1错误日志介绍

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

2.2错误日志相关配置

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

3.通用日志 general log

3.1通用日志介绍

记录对数据库的通用操作，包括错误的SQL语句
    文件: file，默认值
    表: table

3.2通用日志相关设置

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

4.慢查询日志 slow query log

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

开启或关闭慢查询
    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

5.二进制日志 binary log

5.1二进制日志介绍

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

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

5.2二进制日志格式与构成

二进制日志记录三种格式
    基于“语句”记录： statement，记录语句，默认模式
    基于“行”记录： row，记录数据，日志量较大
    混合模式： mixed, 让系统自行判定该基于哪种方式进行
格式配置
    show variables like ‘binlog_format';
二进制日志文件的构成
    有两类文件
        日志文件： mysql|mariadb-bin.文件名后缀，二进制格式
            如： mariadb-bin.000001
        索引文件： mysql|mariadb-bin.index，文本格式

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

sql_log_bin=ON|OFF：是否记录二进制日志，默认ON
log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
                            说明：文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除

5.4二进制日志相关配置

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

6.mysqlbinlog工具

6.1mysqlbinlog介绍

mysqlbinlog：二进制日志的客户端命令工具
命令格式：
    mysqlbinlog [OPTIONS] log_file…
    --start-position=# 指定开始位置
    --stop-position=#
    --start-datetime=
    --stop-datetime=
    时间格式： YYYY-MM-DD hh:mm:ss
    --base64-output[=name]
    -v -vvv
示例:  mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
       mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop�datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

6.2二进制日志事件的格式

# 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
日志

7.日志管理

7.1清除指定二进制日志

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-03-22 09:25:30';

7.2删除所有二进制日志， index文件重新记数

RESET MASTER [TO #]; 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，
一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #

7.3切换日志文件

切换日志文件：
    FLUSH LOGS;