MYSQL 日志
概念
Mysql日志记录mysql的日常操作,以及发生的错误信息,不同的日志拥有不同的功能,从日志中可以解决很多问题,比如mysql瓶颈,用户操作信息,修改数据库的信息等等
mysql日志分类
mysql> show variables like 'log_%';
+----------------------------------------+---------------------+
| 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 |
| log_error | /var/log/mysqld.log |错误日志的路径
| log_output | FILE |日志输出形式
| log_queries_not_using_indexes | OFF |日志是否保存没有使用索引的查询语句
| log_slave_updates | OFF |是否记录从库更新
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |是否记录警告记录
+----------------------------------------+---------------------+
1、错误日志:
记录mysql运行时,停止时,所发生的问题
开启错误日志:在/etc/my.cnf中的添加或者查看log-error配置项,如果指定路径会和初始化路径冲突,不写路径默认存放在初始化指定的路径下
错误日志是一个文本文件,可以通过文本编辑器vim进行查看可用通过刷新日志找回,但是找回后日志数据不存在
刷新日志的方式:
1)重启mysql
2)使用mysqlamdin -u user -p密码 flush-logs
3)在mysql命令行中使用flush logs
2、二进制日志:
开启二进制日志:在/etc/my.cnf中的[mysqld]模块下添加log-bin=mysql-bin,在/usr/local/mysql/date目录下会看到二进制文件
管理优化二进制日志:
在/etc/my.cnf中的log-bin下添加
expire_logs_days = 10 //日志自动清除时间 单位天
max_binlog_size = 500M //二进制日志的最大容量
错误日志,查询日志和慢查询日志只会存在一个,而二进制日志不唯一,随着每次刷新日志,或者重启mysql,二进制日志都增加,默认以mysql-bin为前缀,然后结尾000001开始进行递增,以此类推,二进制日志类型为二进制文件,不能通过文本编辑器进行打开,每次mysql只会使用一个日志用于存放更新数据的信息,之前的日志,都不会再一次进行记录数据,mysql记录数据永远使用最新的二进制日志。
mysql二进制日志以一种事务安全的方式,进行记录数据,不包含没有对mysql进行修改的语句(比如:查询)二进制日志存在的最大意义在于可以用于复制和最大程度恢复数据(灾难恢复)。默认不开启
通过使用mysqlamdin -u user -p密码 flush-logs也可以刷新二进制日志文件
记录所有更改数据的语句(可用于主从复制和灾难恢复)
查看二进制日志
mysqlbinlog 二进制日志名
[root@localhost data]# mysqlbinlog master-bin.000003
mysql> show binary logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 168 |
| master-bin.000002 | 143 |
| master-bin.000003 | 120 |
+-------------------+-----------+
删除二进制日志:
1)reset master 清空日志
2)purge master logs to ‘二进制日志文件名’ 删除指定文件名之前的所有二进制日志
3)purge binary lgos before ‘时间’ 删除指定时间 之前的二进制日志,时间格式为’YYYYMMDD’
使用二进制日志恢复数据
mysqlbinlog option 二进制日志名 | mysql -u user -p密码
option 可选项:
–start-postion=开始的位置(偏移量)
–stop-postion=结束的位置(偏移量)
–start-date=开始的时间
–stop-date=结束的时间
例:
mysql> CREATE TABLE bin_test(id INT,name VARCHAR(30));
mysql> DROP TABLE bin_test;
目的想要恢复这个表,查看正在使用的二进制日志文件
[root@localhost data]# mysqlbinlog master-bin.000003
找到刚才创建表的偏移量使用mysqlbinlog命令进行恢复,文件中#at后的数字就是偏移量
[root@localhost data]# mysqlbinlog --stop-position=238 master-bin.000003 | mysql -u root -p123.com
建议恢复是使用start和stop联合使用进行精确恢复
==注意==:在二进制文件中有BEGIN和COMMIT,前面有说到是以事务安全的方式记录数据的,使用恢复的时候一定要BEGIN和COMMIT进行成对匹配、
3、查询日志:
记录所有客户端用户的操作,启动关闭情况。执行的语句等等,默认不开启,通过修改配置文件开启
开启方式:
在/etc/my.cnf中写入 5.5: log=/path
5.6: general_log=ON
重启服务即可,默认在安装路径的data目录下
测试数据
mysql> USE cyj;
mysql> CREATE TABLE class(id INT,name VARCHAR(30));
mysql> INSERT INTO class VALUES(1,'a');
[root@localhost data]# vim localhost.log
/usr/local/mysql/bin/mysqld, Version: 5.6.33-log (Source distribution). started with:
Tcp port: 0 Unix socket: /usr/local/mysql/mysql.sock
Time Id Command Argument
191216 13:38:39 1 Connect root@localhost on
1 Query select @@version_comment limit 1
191216 13:38:49 1 Query CREATE DATABASE cyj
191216 13:38:53 1 Query SELECT DATABASE()
1 Init DB cyj
1 Query show databases
1 Query show tables
191216 13:39:11 1 Query CREATE TABLE class(id INT,name VARCHAR(30))
191216 13:39:14 1 Query SHOW TABLES
191216 13:39:30 1 Query INSERT INTO class VALUES(1,'a')
191216 13:40:17 1 Query select * from cla
191216 13:40:20 1 Query select * from class
/*查询日志中包含了用户在数据中的所有操作*/
查询日志同错误日志一样,一旦被误删,可以通过刷新日志的方式进行找回,且以文本的格式存在
查询日志和二进制日志的区别
1)查询日志中记录了所有操作,二进制日志中记录了DML操作,查询日志中有很多不必要的操作
2)查询日志没有偏移量,不能很好的去恢复指定数据
3)查询日志是文本文件,可以随意修改,不安全,而二进制日志是二进制格式
4)正因为二进制日志是二进制格式存储,所有读取速度比查询日志快
4、慢查询日志(查询的比较慢的语句)
记录所有超过阈值的查询语句,通过慢查询可以找到查询语句的瓶颈,并且优化它,默认不开启s
开启方式:
编辑/etc/my.cnf:
5.5: log-slow_queries=/path
一旦指定了路径,会和初始化的数据冲突,如果想刷新日志,只能通过从起mysql的方式进行刷新,否则失败。
5.6: slow_query_log=ON
设置慢查询阈值: 编辑/etc/my.cnf: long_query_time=5
单位s,如果不设置默认为10s
如果执行select语句查询时间超过5s将会被记录在此日志文件中,此文件中的查询操作,==可以考虑做索引==
重启服务即可
mysql> show variables like 'slow_query%'; /*查看慢查询是否开启*/
+---------------------+------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------+
| slow_query_log | ON | /*慢查询日志开启*/
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log | /*慢查询日志所在位置*/
+---------------------+------------------------------------------+
mysql> show variables like 'long_query%'; /*查询慢查询阈值*/
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
mysql> select sleep(5);
[root@localhost data]# cat localhost-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.6.33-log (Source distribution). started with:
Tcp port: 0 Unix socket: /usr/local/mysql/mysql.sock
Time Id Command Argument
# Time: 191216 13:54:41
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 5.003638 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use cyj;
SET timestamp=1576475681;
select sleep(5);