mysql日志管理
日志文件：
日志类型：
- 查询日志： 记录查询操作，默认不开启
- 慢查询日志： 分析数据的语句慢的原因，默认开启
- 错误日志： 服务日志、报错日志
- 二进制日志： 执行的sql可能引起数据改变的，保存在此
- 中继日志： 数据复制时的记录
- 事务日志： 事务的ACID的记录
事物日志：
由事务型存储引擎(innodb)自行管理和使用，建议和数据文件分开存放
innodb存储引擎会在内存中开放一块buffer pool，此内存用来存放写入事务日志后，但还没写入到数据磁盘的内容，根据个人需要定于
有日志文件组，一个满了就开始写入，新的存另一个
事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
事务日志文件：
- ib_logfile0， ib_logfile1
transaction log事物日志分为：
- redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
- undo log：保存与执行的操作相反的操作,用于实现rollback
Innodb事务日志相关配置：
innodb_log_buffer_size 内存缓冲池大小
innodb_log_file_size 每个日志文件大小
innodb_log_files_in_group 日志组中，成员数
innodb_log_group_home_dir 日志存放目录
innodb_flush_log_at_trx_commit 事物日志刷新到磁盘的方式，默认为1
事物日志性能优化：
innodb_flush_log_at_trx_commit=0|1|2
解读：
- 0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
- 1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
- 2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失
高并发场景建议：
使用折中方案，选择2级别
- 配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
- 配置为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 variables like '%log_err%'; 查看服务变量
log_error 存放路径
log_warning 记录警告信息到错误日志
log_warning警告日志说明：
- 0，表示不记录警告信息
- 1，表示警告信息一并记录到错误日志中
- 大于1，表示"失败的连接"的信息和创建新连接时"拒绝访问"类的错误信息也会被记录到错误日志中，高版本默认2
通用日志
记录对数据库的通用操作，包括:错误的SQL语句
不推荐开启，因为所有sql执行都会留下日志，影响性能，除了测试、排错时使用，也不建议长时间开启
通用日志可以保存在： file（默认值）或 table（mysql.general_log表）
记录类型：
- 文件： file
- 表： table
配置参数：
show variables like '%gene%'; 查看系统变量
#可写配置文件
general-log 功能开启
general-log_file 文件名 文件名一般是主机名和文件存放位置
log-output 类型 日志文件存放的类型，file、table、none
例：查看数据库中执行次数最多的前三条sql语句
set global log_output="table";
SHOW GLOBAL VARIABLES LIKE 'log_output';
select argument,count(argument) from mysql.general_log group by argument order by count(argument) desc limit 3;
例：对访问的语句进行排序
mysql -e 'select argument from mysql.general_log' | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}'|sort -nr
mysql -e 'select argument from mysql.general_log' |sort |uniq -c |sort -nr
慢查询日志：
执行时长超出指定时间的查询操作。可能是锁、索引等等
生产环境一般启动
配置参数：
show global variables like 'long_query_time'; 查看慢查询时长
show global variables like '%log%';
#可写配置文件（mariadb）
slow_query_log=on|off 慢查询日志是否开启，默认关闭
slow_query_log_file=/data/mysql/slow.log 日志文件路径
long_query_time=3 慢查询的超时时间,超过就是慢了，默认10s
log_queries_not_using_indexes=on|off 未使用索引的sql要记录
log_slow_filter 慢查询日志的过滤器，哪些不记录
log_slow_rate_limit 记录的速率，次数/s
log_slow_verbosity=query_plan,explain 记录详细信息,后面跟参数。log_output='TABLE' 时不支持配置
query_plan日志查询执行计划信息
innodb一个未使用的Percona XtraDB选项，用于记录XtraDB / InnoDB统计信息。
explain在慢查询日志中打印EXPLAIN命令输出。
#mysql才有的变量
log_throttle_queries_not_using_indexes=1000 没有使用索引的sql将会写入到慢查询日志中，该参数将限制每分钟写入的SQL数量。限制条目数后，日志将不会激增
log_slow_filter参数说明：
慢查询日志的过滤器，哪些不记录，多选
| 过滤选项 | 说明 |
|---|---|
| full_join | 完全连接 |
| full_scan | 全表扫描 |
| query_cache | 查询缓存 |
| query_cache_miss | 查询缓存未命中 |
| tmp_table | 临时表查询 |
| tmp_table_on_disk | 磁盘上的临时表查询 |
| admin | |
| filesort | |
| filesort_on_disk |
慢查询分析工具：
自带工具mysqldumpslow：
mysqldumpslow [ OPTS... ] 日志文件
选项：
-v 详细输出
-d 调试模式
-s ORDER 指定排序方式
al：平均锁定时间
ar：平均发送行数
at：平均查询时间默认为“at”
c：计数
l：锁定时间
r：发送行数
t：查询时间
-r 倒序排序，最大值最后
-t NUM 仅显示前n个查询
-a 不要将所有数字抽象为N和字符串为“S”
-n NUM 在名称中至少有n位数字时，抽象数字
-g PATTERN 正则匹配模式，仅考虑包含此字符串的语句
-h HOSTNAME db服务器的主机名的*-slow.log文件名（可以使用通配符），默认为“*”，即匹配所有
-i NAME 服务器实例的名称（如果使用mysql.server启动脚本）
-l 不要从总时间中减去锁定时间
例：
mysqldumpslow -s c -t 10 /data/mysql/slow.log 查看日志文件
tail -f /data/mysql/slow.log
使用profiling工具：
profiling=1 启动功能，改配置文件或变量
show profiles; 查看sql的执行时间记录
show profile [cpu] for query id; 根据记录id查看sql的每一步指令的详细信息，也可查看cpu执行的时间
例：
set profiling=1;
select * from t1 where id=(select avg(id) from t1);
show profiles;
show profile for query 4;
二进制日志（备份）：
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
建议二进制日志和数据文件分开存放
功能： 通过“重放”日志文件中的事件来生成数据副本
日志记录类型：
基于sql语句记录： statement，记录语句，默认模式（10.2.3版本以下），日志量较少
基于行记录： row，也就是记录sql执行后的数据结果，日志量较大，更加安全，建议使用的格式
混合模式： mixed，让系统自行判定基于何种方式（10.2.4及版本以上）
二进制日志文件的构成：
日志文件： mysql-bin.后缀名 二进制格式
索引文件： mysql-bin.index 文本格式，记录二进制文件的索引
配置参数：
| log_bin | 开启二进制日志，并指定存放位置，默认off |
| sql_log_bin | 同上，两者开启一个就ok，但不能只能存放位置k |
| binlog_format | 日志格式： statement、row、mixed |
| max_binlog_size | 日志文件大小，默认1G。到达最大值（≈1G）会自动滚动，单位字节 |
| binlog_cache_size | 每次连接时，开辟内存缓存大小 |
| max_binlog_stmt_cache_size | 日志中基于sql语句的缓存 |
| max_binlog_cache_size | 内存中缓存最大空间 |
| expire_logs_days | 日志的过期时间，超时自动清理，默认为0（不清理） |
| sync_binlog | 启用同步，1同步，0异步默认。同步是事务commit一次，就同步到二进制日志一次。也就是提交一次，缓存数据立即写入磁盘 |
命令查看：
show binary logs; 当前管理的二进制文件。每次重启服务，都新创建一个文件，不会使用旧的
show master logs; 作用同上
show master status; 当前使用的二进制日志
show binlog events in '文件名' [from 字节位置] [limit [起始位,]结束位]; 查看二进制文件内容
输出信息解读：
| 字段 | 说明 |
|---|---|
| Log_name | 文件名 |
| Pos | 二进制文件中的开始位置，第几位字节 |
| Event_type | 事件类型 |
| Server_id | 哪个主机上，主机标识 |
| End_log_pos | 二进制文件中的结束位置，真实结束位置+1 |
| info | 事件信息 |
二进制日志文件查看命令mysqlbinlog：
客户端命令工具，可以远程连接，可离线查看（不起服务）
mysqlbinlog [选项] 二进制日志文件/远端二进制日志
选项：
-u用户 #读取远程二进制日志时才用的上，本地不用
-h主机
-p密码
--start-position= #从哪个地方开始显示
--stop-position= #哪个地方结束
--start-datetime= #从哪个时间戳开始显示
--stop-datetime= #哪里结束，时间：0000-00-00 00:00:00
-v | vvvv #详细信息
-R #开启远程模式，读取远程二进制日志，默认数据中不显示sql，需配合-v才能显示
--raw #binlog日志会以二进制格式存储在磁盘中，读取用mysqlbinlog本地读取；默认以文本形式保存
--stop-never #持续接收远端日志数据
--stop-never-slave-server-id #在备份远程服务器的binlog时，mysqlbinlog本质上就相当于一个从服务器，该选项指定从服务器的server-id的。默认为-1
二进制日志格式解读：
# at 617
#210922 17:15:28 server id 1 end_log_pos 700 CRC32 0xe739f5af Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1632302128/*!*/;
insert hj value(1)
/*!*/;
GTID 0-1-3
解读：
"#"是单行注释
"/* ... */是多行注释"
at 617 事件在日志文件中的起始位置
210922 17:15:28 事件发生的日期和时间
server id 1 事件发生在哪台服务器，唯一性（服务器标识符，方便主从复制时，从主机直接复制master主机的二进制日志，按内容执行一次）
end_log_pos 700 事件在日志文件中的结束位置
Query 事件的类型，查询
thread_id=9 事件执行时的线程id
exec_time=0 sql的产生时间和写入日志文件的时间差
error_code=0 错误代码
SET TIMESTAMP=1632302128/*!*/; 事件执行时的时间戳，唯一性
insert hj value(1) 事件内容
GTID 0-1-3 每个主机的mysql的线程的事件id，唯一
清楚二进制日志：
#清指定文件：
purge master logs to 二进制日志 [before 时间]; 删除指定日志之前的记录（n-1）
purge binary logs to 二进制日志 [before 时间]; 同上
#清除全部：
reset master [to num]; 删除所有日志文件，指定起始的index文件号。默认index为1，也就是全部
#刷新日志：
flush logs; 开启新的二进制日志
中继日志：
主从复制架构中，salve主机用于保存从master机的二进制日志中读取的事务
类通用日志
将当前会话的sql记录都记录到文件中，记录格式原样输出
命令
tee 文件; #开启记录
notee; #关闭记录
例:
tee /tmp/sql.txt
show databases;
select version();
notee;
