MySQL 日志管理

2021-02-16 | 129 浏览

MySQL日志管理

错误日志

查询日志

慢查询日志

二进制日志

日志作用作用

error log 错误日志记录mysql启停错误信息

general log 普通日志记录客户端查询日志

bin log 二进制日志增量备份 DDL DML DCL

Relay log 中继日志接收 replication master

slow log 慢查询日志查询时间超过指定值

日志	作用	作用
error log	错误日志	记录mysql启停错误信息
general log	普通日志	记录客户端查询日志
bin log	二进制日志	增量备份 DDL DML DCL
Relay log	中继日志	接收 replication master
slow log	慢查询日志	查询时间超过指定值

1.错误日志

MySQL的错误日志error log记录mysqld服务进程启动/关闭或运行过遇到的错误日志。
1.在配置文件增加如下行

[root@sql ~]# grep log-error  /etc/my.cnf
[mysqld]
log-error=/log/mysql/err.log

2.创建对应目录与日志文件, 并授权

[root@sql ~]# mkdir -p /log/mysql/
[root@sql ~]# touch /log/mysql/err.log
[root@sql ~]# chown -R mysql.mysql /log/mysql/err.log

//重启数据库生效
[root@sql ~]# systemctl restart mysqld

3.查看错误日志

[root@vm-70-161 ~]# grep -i "error" /var/log/mysqlerr.log
2018-05-07T02:46:08.487851Z 0 [ERROR] unknown option '--dasdasdsadas'

4.通过终端查看日志

mysql> show variables like "log_error%";
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| log_error           | /log/mysql/err.log |
| log_error_verbosity | 3                  |
+---------------------+--------------------+
2 rows in set (0.00 sec)

2.查询日志

普通查询日志general query log记录客户端连接信息和执行的所有SQL语句信息, 默认关闭
1.开启general日志

[root@sql ~]# cat /etc/my.cnf
[mysqld]
general_log=ON
general_log_file=/log/mysql/select.log

2.创建对应目录与日志文件, 并授权

[root@sql ~]# mkdir -p /log/mysql
[root@sql ~]# touch /log/mysql/select.log 
[root@sql ~]# chown mysql.mysql /log/mysql/select.log

//重启数据库生效
[root@sql ~]# systemctl restart mysqld

3.查看日志

[root@sql ~]# cat /log/mysql/select.log 
/usr/sbin/mysqld, Version: 5.7.22-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2018-05-07T03:05:28.890758Z     4 Query insert into t5 values (1,"test")
2018-05-07T03:06:08.983337Z     4 Query update t5 set name='tt' where name='test'
2018-05-07T03:06:19.528444Z     4 Query show databases
2018-05-07T03:06:25.949473Z     4 Query drop database test
2018-05-07T03:06:25.972178Z     4 Query SELECT DATABASE()

4.终端查询日志情况

mysql> show variables like 'general_log%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | ON                    |
| general_log_file | /log/mysql/select.log |
+------------------+-----------------------+
2 rows in set (0.01 sec)

//临时开启查询日志
mysql> set global general_log = On;

//临时关闭查询日志
mysql> set global general_log = Off;

3.慢查询日志

慢查询日志slow query log记录执行过长的SQL语句, 可利用慢查询优化进行调整优化
1.开启慢查询日志

[root@sql ~]# cat /etc/my.cnf
[mysqld]
slow_query_log = on
slow_query_log_file=/log/mysql/slow.log
long_query_time=2
log_queries_not_using_indexes  #没有启用索引的查询语句

2.创建对应目录与日志文件, 并授权

[root@sql ~]# mkdir -p /log/mysql
[root@sql ~]# touch /log/mysql/slow.log 
[root@sql ~]# chown mysql.mysql /log/mysql/slow.log

//重启数据库生效
[root@sql ~]# systemctl restart mysqld

3.执行过慢语句进行测试

mysql> SELECT BENCHMARK(50000000,2*3);

4.检查慢日志是否记录

[root@sql ~]# cat /var/lib/mysql/mysql-slow.log
Time                 Id Command    Argument
# Time: 2018-05-07T03:27:07.301336Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 23.956456  Lock_time: 0.000365 Rows_sent: 0  Rows_examined: 200000
use bgx;
SET timestamp=1525663627;
# Query_time: 8.766179  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1525651122;
select BENCHMARK(500000000,2*3);

4.二进制日志

二进制日志binary log记录数据被修改的相关信息
1.开启binlog二进制日志
[root@sql ~]# cat /etc/my.cnf
[mysqld]
server-id = 161
log-bin = /log/mysql/bin/sql_70_161
expire_logs_days = 30 #多少天天数后的日志将会被自动删除
2.创建对应目录与日志文件, 并授权
[root@vm-70-161 ~]# mkdir /log/mysql/bin
[root@vm-70-161 ~]# chown -R mysql.mysql /log/mysql/
[root@vm-70-161 ~]# systemctl restart mysqld

//重启数据库生效
[root@sql ~]# systemctl restart mysqld
3.查看binlog日志信息
[root@sql ~]# mysqlbinlog -v /log/mysql/bin/sql_70_161.000001
4.日志截断

1.切割binlog日志

重启mysqld服务, 在mysql控制台执行mysql> flush logs

在mysql控制台执行reset master会清空所有binlog(危险)

2.删除部分binlog日志

mysql> purge master logs to 'sql_70_161.000004'; #删除指定日志以前的日志文件

mysql> purge binary logs before '2018-05-07 11:57:33';#删除指定时间以前的日志文件

5.恢复日志
//使用时间节点进行恢复
# mysqlbinlog mysql.000002 --start-datetime="2018-05-07 12:05:00" 
# mysqlbinlog mysql.000002 --stop-datetime="2018-05-07 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" -stop-datetime="2018-12-05 11:02:54"

//使用位置点进行恢复
# mysqlbinlog mysql.000002 --start-position=124 
# mysqlbinlog mysql.000002 --stop-position=124 
# mysqlbinlog mysql.000002 --start-position=124 --stop-position=336

数据库