cnetos7安装mysql并开启慢日志查询

参考博客地址https://www.cnblogs.com/luyucheng/p/6265594.html

安装部署(5.7)

#下载Yum Repository
curl -O https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm

yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
#查看可用的repo
yum repolist enabled|grep "mysql.*-community.*"

#安装mysql
yum -y install mysql-community-server

#启动mysql
systemctl start mysqld
systemctl enable mysqld

#设置密码
#查找初始密码
grep 'temporary password' /var/log/mysqld.log

#登录后重置密码
set password=password('admin@123');

#密码设置规则
#取消密码的强度
set global validate_password_mixed_case_count=0; 

set global validate_password_number_count=3; 

set global validate_password_special_char_count=0; 

set global validate_password_length=3;  

#允许所有远程IP来连接这个mysql
grant all privileges on *.* to lzpddd@'%' identified by 'admin@123';

开启慢日志查询

开启和查看mysql慢日志查询

• 查看慢日志相关参数

  mysql> show variables like 'slow_query%';
  +---------------------------+----------------------------------+
  | Variable_name             | Value                            |
  +---------------------------+----------------------------------+
  | slow_query_log            | OFF                              |
  | slow_query_log_file       | /var/lib/mysql/node-slow.log    |
  +---------------------------+----------------------------------+
  
  mysql> show variables like 'long_query_time';
  +-----------------+-----------+
  | Variable_name   | Value     |
  +-----------------+-----------+
  | long_query_time | 10.000000 |
  +-----------------+-----------+
  

• 开启慢日志

  #修改配置文件
  vim /etc/my.cnf
  
  #开启慢日志
  slow_query_log = ON   
  #设置慢日志存储路径
  slow_query_log_file = /var/lib/mysql/node-slow.log 
  #查询超过1秒就记录
  long_query_time = 1
  
  #重启mysql服务
  service mysqld restart
  

• 验证慢日志是否生成

  mysql> show variables like 'slow_query%';
  +---------------------+------------------------------+
  | Variable_name       | Value                        |
  +---------------------+------------------------------+
  | slow_query_log      | ON                           |
  | slow_query_log_file | /var/lib/mysql/node-slow.log |
  +---------------------+------------------------------+
  
  mysql> show variables like 'long_query_time';
  +-----------------+----------+
  | Variable_name   | Value    |
  +-----------------+----------+
  | long_query_time | 1.000000 |
  +-----------------+----------+
  
  #执行一条慢查询语句
  mysql> select sleep(2);
  查看node-slow.log是否生成