Mysql日志管理
1. Mysql 日志类型
| 日志文件 | 选项 | 文件名 | 程序 |
|---|---|---|---|
| 表名称 | |||
| 错误 | --log-error | host_name.err | N/A |
| 常规 | --general_log | host_name.log | N/A |
| general_log | |||
| 慢速查询 | --slow_query_log long_query_time | host_name-slow. log slow_log | mysqldumpslow |
| 二进制 | --log-bin --expire-logs-days | host_name-bin.000001 | mysqlbinlog |
|---|---|---|---|
| 审计 | --audit_log --audit_log_file ... | audit.log | N/A |
2.错误日志
查看错误日志
查看错误日志
mysql> show variables like 'log_error';
mysql> show variables like '%log%';
配置：
[mysqld]
log-error=/data/mysql/mysql.log
错误日志的作用:
记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。
记录了服务器运行中产生的错误信息
记录了服务在启动和停止是所产生的信息
在从服务器上如果启动了复制进程的时候，复制进程的信息也会被记录
记录event错误日志
3.一般查询日志
genral_log
记录所有的已执行成功的操作。
可以作为审计功能
[mysqld]
general_log=on
general_log_file=/tmp/general.log #修改完成重启数据库
cat /tmp/general.log
4.二进制日志
二进制日志不依赖与存储引擎的，依赖于SQL层
说明：默认开启，精确的记录了用户对数据库中的数据进行操作的命令和操作的数据对象。
二进制日志文件的作用：
- 提供了增量备份的功能
- 提供了数据基于时间点的恢复，这个恢复的时间点可以由用户控制
- 为mysql的复制架构提供基础，将这主服务器的二进制日志复制到从服务器上并执行同样的操作,就可将数据进行同步，可以做主从
二进制日志格式：
- 基于语句 statement
- 基于行 row 数据变化过程
- 混合方式 mixed
二进制日志事件：
- position 基于位置
- datetime 基于时间
查看是否开启
mysql> show variables like '%log%bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
开启方式
vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin #配置文件修改，完成后重启数据库
查询二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------
查询bin log设置
mysql> show variables like '%binlog%';
定义记录方式
设置statement模式
记录量少，将来恢复的时候可能有问题
例子：一个表1000w行，update t1 name=‘a；
如果以statement（语句）记录二进制日志，将会记录整个所有的1000W行变化。
只记录update T1 set name='a';
例子二：update ti set date=now();
如果以statement （语句）记录二进制日志，只记录update ti set date=now();将来恢复的时候
row =-------》推荐 基于行记录，记录数据的过程
以上例子1如果是row（行）模式记录的所有整个1000W行的变化日志。记录的数据准确
缺点 :记录量非常大.
mysql> show variables like '%format%';#查看
设置row模式
[root@db02 data]# vim /etc/my.cnf
[mysqld]
binlog-format=row
命令行修改
mysql> SET GLOBAL binlog_format= 'STATEMENT';
mysql> SET GLOBAL binlog_format= 'ROW';
mysql> SET GLOBAL binlog_format= 'MIXED';
设置mixed模式
在MySQL配置文件my.cnf文件中的mysqld节中添加下面的配置文件：
[mysqld]
#设置日志格式
binlog_format = mixed
#设置日志路径，注意路经需要mysql用户有权限写
log-bin = /data/mysql/logs/mysql-bin.log
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
重启MySQL生效，如果不方便重启服务，也可以直接修改对应的变量即可。
查看**
mysql> show variables like '%format%';
二进制日志操作者
[root@db02 data]# ll mysql-bin.*
-rw-rw---- 1 mysql mysql 143 Nov 21 10:12 mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 Nov 21 10:12 mysql-bin.000002
-rw-rw---- 1 mysql mysql 82 Nov 21 10:12 mysql-bin.index
二进制文件是可以滚动的，条件是:
重启数据库的时候会自动滚动一个新的
执行mysql> flush logs;命令的时候
mysql命令行汇总的查看：
查看当前在使用的二进制文件
mysql> show binary logs;
删除二进制文件
默认情况下，不会删除旧的日志文件
根据存在时间删除日志：
SET GLOBAL expire_logs_days= 7;#以天为单位
…或者…
PURGE BINARY LOGS BEFORE now() -INTERVAL 3 day;
根据文件名删除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';
查看二进制文件内容
1.events 事件
二进制日志如何定义：命令的最小发生单元
2.position
每个事件在整个二进制文件中的相对位置号就是position
截取一段有用的日志
mysqlbinlog --start-position=120 --stop-position=384 mysql-bin.000004 >/tmp/inc.sql
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
--start-datetime "2019-11-22 14:00:00" \
--database test_binlog mysql-bin.000034 | less
恢复
1.临时会话关闭二进制日志
mysql> set sql_log_bin=0;
5.慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。
默认情况下，MySQL数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
慢查询日志
- 是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
- 通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。
- 作为优化的一中工具日志。帮我们定位问题
慢日志设置
long_query_time：设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10
slow_query_log：指定是否开启慢查询日志
slow_query_log_file：指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引
2. 开启慢查询日志
查看：
mysql> show variables like '%slow_query_log%';
+---------------------+---------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /application/mysql/data/db02-slow.log |
+---------------------+---------------------------------------+
开启：
配置文件修改
vim /etc/my.cnf
slow_query_log=on
slow_query_log_file=/tmp/slow.log
6.慢日志分析工具mysqldumpslow
mysqldumpslow命令/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log这会输出记录次数最多的10条SQL语句，其中：
-s 按照那种方式排序
c：访问计数
l：锁定时间
r:返回记录
al：平均锁定时间
ar：平均访问记录数
at：平均查询时间
-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。
例子：
/path/mysqldumpslow-s r -t 10 /database/mysql/slow-log得到返回记录集最多的10个查询。
/path/mysqldumpslow-s t -t 10 -g “left join”/database/mysql/slow-log得到按照时间排序的前10条里面含有左连接的查询语句。
