MYSQL日志

日志

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

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

Innodb事务日志：

mysql中使用命令查看配置
show variables like ‘%innodb_log%’;

配置：
innodb_log_file_size 5242880 每个日志文件大小
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为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略好一些，但操作系统或停电可能导致最后一秒的数据丢失

错误日志

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 慢查询日志文件
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_verbosity= Query_plan,explain 记录内容

分析语句哪个部分慢
show variables like ‘profiling’;
如果是OFF设置成ON

在执行SQL语句后使用
show profiling;
这条命令列出有记录的语句，前面是查询ID，使用命令
show profiling for query 查询ID;
即可显示详细的语句查询过程耗时

二进制日志

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

启用二进制日志直接在文件里加上log-bin即可
如要定义文件名则
log-bin=test
此时生成的日志文件名是test，修改存放路径加上路径即可

二进制日志相关的变量参数

sql_log_bin=ON|OFF：是否记录二进制日志，默认ON
可以在会话级直接set修改来临时启用或者禁用二进制日志

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能
只启用不修改文件名或者路径就加上log-bin即可
sql_log_bin和log_bin都为on才能开启二进制日志

binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认
STATEMENT
STATEMENT只记录执行的命令
ROW记录修改过的行
MIXED是混合模式，由数据库决定是记录命令还是行

max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
说明：文件达到上限时的大小未必为指定的精确值

sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除

二进制日志相关配置
查看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 1000 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]
-v -vvv

示例：

mysqlbinlog  /var/lib/mysql/logbin.000001 > db.sql
mysqlbinlog --start-position=1000 --stop-position=2000 /var/lib/mysql/logbin.000001 > db.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

清除指定二进制日志：
PURGE { BINARY | MASTER } LOGS
{ TO ‘log_name’ | BEFORE datetime_expr }

例如：
PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志
PURGE BINARY LOGS BEFORE ‘2020-02-18’;
PURGE BINARY LOGS BEFORE ‘2020-02-18 09:25:30’;

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

切换日志文件：
FLUSH LOGS;

中继日志

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