19、MySQL日志管理
一、日志分类
日志种类 |
作用 |
错误日志 |
记录 MySQL 服务器启动、关闭及运行错误等信息 |
事务日志 |
1、redo log重做日志 |
查询日志 |
记录所有的sql |
慢查询日志 |
记录执行时间超过指定时间的操作,如果是全表查询,即便没有超时也会被记录下来 |
二进制日志 |
又称binlog日志,以二进制文件的方式记录数据库中除 SELECT 以外的操作。即只记录写操作不记录读操作 |
中继日志 |
备库将主库的二进制日志复制到自己的中继日志中,从而在本地进行重放 |
通用日志 |
审计哪个账号、在哪个时段、做了哪些事件 |
二、错误日志
MySQL错误日志是记录MySQL 运行过程中较为严重的警告和错误信息,以及MySQL每次启动和关闭的详细信息。错误日志使用log_error以及log_warnings等参数进行定义。
查看错误日志
-- 方式一
mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name | Value |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
+---------------------+---------------------+
3 rows in set (0.13 sec)
-- 方式二
[root@localhost ~]# mysqladmin -uroot -p123456 variables | grep -w log_error
| log_error | /var/log/mysqld.log
查看警告日志
log_warnings:
0:表示不记录警告信息
1:表示记录警告信息到错误日志
大于1表示"失败的连接"的信息和创建新连接时"拒绝访问"类的错误信息也会被记录到错误日志中。
mysql> show variables like '%log_warnings%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set (0.00 sec)
设置错误日志
设置错误日志的方式也有两种,分别是临时设置和永久设置。
临时设置
[root@localhost mysql-5.7.34]# /usr/local/mysql-5.7.34/support-files/mysql.server start --log_error=/tmp/DB-Server.localdomain.err
[root@localhost mysql-5.7.34]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_error%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /tmp/DB-Server.localdomain.err |
| log_error_verbosity | 3 |
+---------------------+--------------------------------+
3 rows in set (0.01 sec)
mysql>
永久设置
[root@localhost ~]# vim /etc/my.cnf
log-error=/var/log/mysql-error.log
[root@localhost ~]# touch /var/log/mysql-error.log
[root@localhost ~]# chown mysql.mysql /var/log/mysql-error.log
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_error%';
+---------------------+--------------------------+
| Variable_name | Value |
+---------------------+--------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /var/log/mysql-error.log |
| log_error_verbosity | 3 |
+---------------------+--------------------------+
3 rows in set (0.00 sec)
三、事务的日志
innodb事务日志包括redo log和undo log。redo log是重做日志,提供前滚操作,undo log是回滚日志,提供回滚操作。
undo log不是redo log的逆向过程,其实它们都算是用来恢复的日志:
- redo log通常是物理日志,记录的是数据页的物理修改,而不是某一行或某几行修改成怎样怎样,它用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置)。
- undo用来回滚行记录到某个版本。undo log一般是逻辑日志,根据每行记录进行记录。
redo log
redo log包括两部分:
- 内存中的日志缓冲(redo log buffer),该部分日志是易失性的
- 磁盘上的重做日志文件(redo log file),该部分日志是持久的
在概念上,innodb通过force log at commit机制实现事务的持久性,即在事务提交的时候,必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。
为了确保每次日志都能写入到事务日志文件中,在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的,MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中,中间还要经过操作系统内核空间的os buffer,调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。
undo log
undo log有两个作用:提供回滚和多个行版本控制(MVCC)。
在数据修改的时候,不仅记录了redo,还记录了相对应的undo,如果因为某些原因导致事务失败或回滚了,可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。
当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候,也是通过undo log来实现的:当读取的某一行被其他事务锁定时,它可以从undo log中分析出该行记录以前的数据是什么,从而提供该行版本信息,让用户实现非锁定一致性读取。
undo log是采用段(segment)的方式来记录的,每个undo操作在记录的时候占用一个undo log segment。另外,undo log也会产生redo log,因为undo log也要实现持久性保护。
undo log相关的变量
mysql> show variables like "%undo%";
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
+--------------------------+------------+
5 rows in set (0.00 sec)
四、一般查询日志
一般不会开启,因为哪怕你开启事务一顿操作,最后不提交也会记录,生产上程序跑sql很多,会非常非常占地方,从来都不启动,要看操作去binlog。
[root@localhost ~]# vim /etc/my.cnf
general_log=on
general_log_file=/var/log/select.log
[root@localhost ~]# touch /var/log/select.log
[root@localhost ~]# chown mysql.mysql /var/log/select.log
[root@localhost ~]# chmod 640 /var/log/select.log
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p123456
mysql> show variables like '%gen%';
+----------------------------------------+---------------------+
| Variable_name | Value |
+----------------------------------------+---------------------+
| auto_generate_certs | ON |
| general_log | ON |
| general_log_file | /var/log/select.log |
| sha256_password_auto_generate_rsa_keys | ON |
+----------------------------------------+---------------------+
4 rows in set (0.01 sec)
五、慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
慢查询日志相关参数
MySQL 慢查询的相关参数解释:
- slow_query_log:是否开启慢查询日志,1表示开启,0表示关闭。
- log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
- slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
- long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志
- log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。
- log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = on
#指定慢日志文件存放位置(默认在data)
slow_query_log_file=/var/log/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes=ON
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志,少于100行的sql语句查询慢的话不记录,一般不使用
log_output='FILE'
测试慢日志
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = on
#指定慢日志文件存放位置(默认在data)
slow_query_log_file=/var/log/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes=ON
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志,少于100行的sql语句查询慢的话不记录,一般不使用
log_output='FILE'
[root@localhost ~]# touch /var/log/slow.log
[root@localhost ~]# chown mysql.mysql /var/log/slow.log
[root@localhost ~]# systemctl restart mysqld
mysql> show variables like '%slow_query%';
+---------------------+-------------------+
| Variable_name | Value |
+---------------------+-------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/log/slow.log |
+---------------------+-------------------+
2 rows in set (0.01 sec)
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select sleep(3);
+----------+