09.MySQL日志管理

• 1.错误日志
• 2.查询日志
• 3.慢查询日志
• 4.二进制日志

徐亮伟, 江湖人称标杆徐。多年互联网运维工作经验，曾负责过大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维，曾负责国内某大型电商运维工作。
个人博客"徐亮伟架构师之路"累计受益数万人。
笔者Q:552408925、572891887
架构师群:471443208

日志	作用	作用
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