二、Mysql运维技术之Mysql日志

•  错误日志（error log）

这类日志记录服务器启动和关闭过程的信息，服务器运行过程中的错误信息，可通过命令show variables like 'log_error';
mysql> show variables like 'log_error';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| log_error     | /usr/local/mysql/log/mysqld.log |
+---------------+---------------------------------+
1 row in set (0.01 sec)

• 二进制日志（bin log） 

这类日志记录修改数据或者有可能引起数据改变的语句，可通过命令show variables like 'log_bin%';
可以在Mysql配置文件中添加log_bin=/usr/local/mysql/binlog/mysql-bin 进行配置，此类日志需用mysqlbinlog来解析。
mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)


[root@localhost bin]# vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 4306
socket=/usr/local/mysql/socket/mysql.sock
symbolic-links=0
log-error=/usr/local/mysql/log/mysqld.log
pid-file=/usr/local/mysql/pid/mysqld.pid
log_bin=/usr/local/mysql/binlog/mysql-bin

[root@localhost data]# service mysql restart 
Shutting down MySQL.... SUCCESS! 
Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/pid/mysqld.pid).

# 启动出现异常,查看日志：
[root@localhost log]# cat /usr/local/mysql/log/mysqld.log
mysqld: File '/usr/local/mysql/binlog/mysql-bin.index' not found (Errcode: 2 - No such file or directory)
2020-03-20T05:44:32.092678Z 0 [ERROR] Aborting

[root@localhost mysql]# mkdir binlog
[root@localhost mysql]# chown -R mysql:mysql binlog/
[root@localhost mysql]# service mysql restart 
# 查看生成的日志文件
[root@localhost binlog]# pwd
/usr/local/mysql/binlog
[root@localhost binlog]# ll
总用量 8
-rw-r-----. 1 mysql mysql 154 3月  20 13:48 mysql-bin.000001
-rw-r-----. 1 mysql mysql  41 3月  20 13:48 mysql-bin.index
# 增加软链接
[root@localhost bin]#  ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin
[root@localhost mysql]# cd binlog/
[root@localhost binlog]# ls
mysql-bin.000001  mysql-bin.index
[root@localhost binlog]# mysqlbinlog mysql-bin.000001

• 通用日志（general log） 

这类日志会记录数据库的所有操作，可通过命令show variables like 'general_log%'; 可以通过mysql配置文件中添加general_log=on来进行配置
mysql> show variables like 'general_log%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | OFF                                 |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
2 rows in set (0.01 sec)

# 默认 Mysql general log是关闭，下面进行开启。

[root@localhost binlog]# vi /etc/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 4306
socket=/usr/local/mysql/socket/mysql.sock
symbolic-links=0
log-error=/usr/local/mysql/log/mysqld.log
pid-file=/usr/local/mysql/pid/mysqld.pid
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
log_bin=/usr/local/mysql/binlog/mysql-bin
general_log=on
[root@localhost binlog]# service mysql  restart 
Shutting down MySQL..... SUCCESS! 
Starting MySQL. SUCCESS!

mysql> show variables like 'general_log%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
\Connection id:    2
Current database: *** NONE ***

+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | ON                                  |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)
[root@localhost binlog]# tail -f /usr/local/mysql/data/localhost.log
/usr/local/mysql/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 4306  Unix socket: /usr/local/mysql/socket/mysql.sock
Time                 Id Command    Argument
2020-03-20T06:03:53.033315Z	    2 Connect	root@localhost on  using Socket
2020-03-20T06:03:53.033565Z	    2 Query	show variables like 'general_log%'
2020-03-20T06:07:05.308082Z	    3 Connect	root@192.168.43.226 on  using TCP/IP
2020-03-20T06:07:05.308535Z	    3 Query	SET NAMES utf8
2020-03-20T06:07:05.317305Z	    3 Query	SHOW VARIABLES LIKE 'lower_case_%'
2020-03-20T06:07:05.319408Z	    3 Query	SHOW VARIABLES LIKE 'profiling'
2020-03-20T06:07:05.320778Z	    3 Query	SHOW DATABASES
2020-03-20T06:07:06.680541Z	    4 Connect	root@192.168.43.226 on  using TCP/IP
2020-03-20T06:07:06.680833Z	    4 Query	SET NAMES utf8
2020-03-20T06:07:06.680991Z	    4 Init DB	issue
2020-03-20T06:07:06.681278Z	    4 Query	SELECT @@character_set_database, @@collation_database
2020-03-20T06:07:07.310605Z	    4 Query	SHOW FULL TABLES WHERE Table_type != 'VIEW'
2020-03-20T06:07:10.629871Z	    5 Connect	root@192.168.43.226 on  using TCP/IP
2020-03-20T06:07:10.630188Z	    5 Query	SET NAMES utf8
2020-03-20T06:07:10.630752Z	    5 Init DB	issue
2020-03-20T06:07:10.631278Z	    5 Query	SHOW TABLE STATUS LIKE 'bu_issue'
2020-03-20T06:07:10.844625Z	    5 Query	SHOW ENGINES
2020-03-20T06:07:10.845162Z	    4 Query	SHOW CREATE TABLE `bu_issue`
2020-03-20T06:07:10.934508Z	    5 Query	SELECT * FROM `bu_issue` LIMIT 0, 1000
2020-03-20T06:07:10.941783Z	    5 Query	SHOW COLUMNS FROM `issue`.`bu_issue`

 慢查询日志（slow log）

这类日志会记录执行时间超过指定时间的查询语句，可通过命令show variables like 'slow%';查看是否开启，同时可以通过命令show variables like 'long%';查看超时时间的设置，可以设置Mysql配置文件中添加slow_query_log=on 来开启，同时用long_query_time= 1来设置超时时间为1秒.

mysql> show variables like 'slow%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_launch_time    | 2                                        |
| slow_query_log      | OFF                                      |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
3 rows in set (0.00 sec)
mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

# 开启慢日志查询
[root@localhost binlog]# vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 4306
socket=/usr/local/mysql/socket/mysql.sock
symbolic-links=0
log-error=/usr/local/mysql/log/mysqld.log
pid-file=/usr/local/mysql/pid/mysqld.pid
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
log_bin=/usr/local/mysql/binlog/mysql-bin
general_log=on
slow_query_log=on
long_query_time=1

# 重启服务
[root@localhost binlog]# service mysql restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
# 查看设置效果
mysql> show variables like 'slow%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_launch_time    | 2                                        |
| slow_query_log      | ON                                       |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
3 rows in set (0.00 sec)

# 查看慢日志文件
[root@localhost binlog]# tail -f /usr/local/mysql/data/localhost-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 4306  Unix socket: /usr/local/mysql/socket/mysql.sock
Time                 Id Command    Argument
/usr/local/mysql/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 4306  Unix socket: /usr/local/mysql/socket/mysql.sock
Time                 Id Command    Argument