mysql日志服务

开启错误日志

错误日志：记录 MySQL 服务器启动、关闭及运行错误等信息

默认开启，配置方式如下

查看默认错误文件位置
mysql -uroot -p123
mysql> show variables like '%log_error%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER               |
| log_error           | /var/lib/mysql/error.log   |
| log_error_verbosity | 3                          |
+---------------------+----------------------------+
3 rows in set (0.00 sec)


修改默认错误日志文件路径
vim /etc/my.cnf
在[mysqld]中添加：
[mysqld]
log_error=/var/log/mysql.errlog  #错误日志文件路径

touch /var/log/mysql.errlog
chmod 640 /var/log/mysql.errlog 
chown mysql.mysql /var/log/mysql.errlog 
systemctl restart mysqld

查看是否修改成功
mysql -uroot -p123
mysql> show variables like '%log_error%';
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| binlog_error_action | ABORT_SERVER               |
| log_error           | /var/log/mysql.errlog   |
| log_error_verbosity | 3                          |
+---------------------+----------------------------+
3 rows in set (0.00 sec)

开启一般查询日志

一般查询日志：记录所有sql语句

默认不开启，开启方法如下

方式一：更改my.cnf配置文件（永久添加）

vim /etc/my.cnf

在[mysqld]中添加：
[mysqld]
general_log=on  #开启日志
general_log_file=/var/log/select.log  #日志文件路径

创建日志文件
touch /var/log/select.log
chmod 640 /var/log/select.log
chown mysql.mysql /var/log/select.log
systemctl restart mysqld

查看是否开启，为on则开启
mysql -uroot -p123
mysql> show variables like 'general_%';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| general_log      | ON                             |
| general_log_file | /var/log/select.log            |
+------------------+--------------------------------+
2 rows in set (0.00 sec)

方式二：使用mysql控制台开启（临时）

mysql -uroot -p123

查看当前日志输入类型（默认输出到file文件）
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

查看普通日志输出文件地址
mysql> show variables like 'general%';
+------------------+------------------------------------------+
| Variable_name    | Value                                    |
+------------------+------------------------------------------+
| general_log      | OFF                                      |
| general_log_file | /usr/local/mysql/data/VM-12-7-centos.log |
+------------------+------------------------------------------+
2 rows in set (0.00 sec)

开启一般日志
mysql> set global general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

修改日志文件路径
mysql> set global general_log_file='/var/log/select.log';
Query OK, 0 rows affected (0.00 sec)


创建日志文件
touch /var/log/select.log
chmod 640 /var/log/select.log
chown mysql.mysql /var/log/select.log
systemctl restart mysqld


查看是否开启
mysql -uroot-p123
mysql> show variables like 'general_%';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| general_log      | ON                             |
| general_log_file | /var/log/select.log            |
+------------------+--------------------------------+
2 rows in set (0.00 sec)

开启慢查询日志

慢查询日志：记录在MySQL中响应时间超过阀值的语句

默认不开启，开启方法如下

方式一：更改my.cnf配置文件（永久添加）

vim /etc/my.cnf

在[mysqld]中添加：
[mysqld]
slow_query_log = 1  #指定是否开启慢查询日志
slow_query_log_file=/var/log/slow.log  #指定慢日志文件存放位置（默认在data）
long_query_time=0.05  #设定慢查询的阀值(默认10s)
log_queries_not_using_indexes=ON  #不使用索引的慢查询日志是否记录到日志

创建日志文件
touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld

mysql -uroot -p123
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/log/slow.log              |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

方式二：mysql控制台开启（临时添加）

查看是否开启慢查询
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/slow.log        |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

查询超过多少秒才记录
mysql> show variables like 'long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

开启慢查询
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)


更改慢日志文件路径
mysql> set global slow_query_log_file='/var/log/slow.log';
Query OK, 0 rows affected (0.00 sec)

查询超过多少秒才记录的时间
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

创建日志文件
touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld

查看是否开启日志
mysql -uroot -p123
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/log/slow.log              |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

配置文件开启日志汇总

 vim /etc/my.cnf
在[mysqld]中添加：
[mysqld]
log_error=/var/log/mysql.errlog       #记录错误日志
general_log=on
general_log_file=/var/log/select.log  #记录所有的sql语句
slow_query_log = 1
slow_query_log_file=/var/log/slow.log # 记录在MySQL中响应时间超过阀值的语句
long_query_time=0.05
log_queries_not_using_indexes=ON


touch /var/log/mysql.errlog
chmod 640 /var/log/mysql.errlog 
chown mysql.mysql /var/log/mysql.errlog 

touch /var/log/select.log
chmod 640 /var/log/select.log
chown mysql.mysql /var/log/select.log

touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld