12.MySQL开启慢日志

0. 前提

安装MySQL5.7.20，略

1. 创建慢日志目录并改权限

#1.创建目录
mkdir /usr/local/mysql/data

#2.更改权限
chown mysql:mysql /usr/local/mysql/data

2. 修改mysql配置文件

vim /etc/my.cnf
# [mysqld]服务端配置写入如下内容:
#1. 开启慢日志   
#2. 阈值为2秒，超过2秒记录    
#3. 慢日志文件的路径
#4. 允许导入导出数据文件！
slow_query_log  
long_query_time=2
slow_query_log_file="/usr/local/mysql/data/web-slow.log"
secure_file_priv=''

3. 重启mysql

jobs
kill -9 %1
jobs
cd /tmp
nohup mysqld_safe &

netstat -tunlp |grep 3306

4. 查看是否有日志文件产生

ls /usr/local/mysql/data

root@4c16g:/tmp# ls /usr/local/mysql/data
web-slow.log

5. 生成测试数据

1. 前提：msyql允许导入导出数据！

2. 导入数据

#1.生成文件
seq 1 19999999 > /tmp/big

#2.登录数据库
mysql -uroot -p123456

#3.创建库db1
create database db1;

#4.创建表t1
use db1;
create table t1(id int(10)not null) engine=innodb;

#5.导入数据
load data infile '/tmp/big' into table t1;

[root@node-1 mysql]# #1.生成文件
[root@node-1 mysql]# seq 1 19999999 > /tmp/big
[root@node-1 mysql]#
[root@node-1 mysql]# #2.登录数据库
[root@node-1 mysql]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> #3.创建库db1
MySQL [(none)]> create database db1;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]>
MySQL [(none)]> #4.创建表t1
MySQL [(none)]> use db1;
Database changed
MySQL [db1]> create table t1(id int(10)not null) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

MySQL [db1]>
MySQL [db1]> #5.导入数据
MySQL [db1]> load data infile '/tmp/big' into table t1;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

6. 监控慢日志文件

#另起一个窗口
tail -f /usr/local/mysql/data/web-slow.log

7. 执行语句测试

select * from db1.t1 where id=8;

MySQL [db1]> select * from db1.t1 where id=8;
+----+
| id |
+----+
|  8 |
+----+
1 row in set (5.77 sec)

MySQL [db1]>

8. 查看慢日志记录

root@4c16g:~# tail -f /usr/local/mysql/data/web-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.20-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument



# Time: 2022-01-01T13:27:45.692983Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 56.416241  Lock_time: 0.006782 Rows_sent: 0  Rows_examined: 0
use db1;
SET timestamp=1641043665;
load data infile '/tmp/big' into table t1;



# Time: 2022-01-01T13:28:05.016366Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 8.090870  Lock_time: 0.001309 Rows_sent: 1  Rows_examined: 19999999
SET timestamp=1641043685;
select * from db1.t1 where id=8;

发现：每条慢日志有5行组成！！！

而filebeat只能一行一行处理！

# 此时，只使用filebeat就不能满足需求了！
# 所以安装logstash！

---

最后更新: 2022-02-19 13:05:46