第十一章 数据库日志管理
1.简介
在生活中人们可以通过日记来记录每天发生的事情,在工作中人们可以将每天的工作内容通过日志的形式表达出来。在计算机领域中,网络设备、系统服务程序在运行时都会产生名为log(日志)的事件记录,每行日志记录着系统程序或者使用者在不同时间的操作描述。同样,日志文件也是MySQL的重要组成部分,这些日志不仅给数据库的管理工作提供了很大的帮助,而且可以直观地反映出数据库的运行情况。

2.错误日志
MySQL数据库中存在着不同的日志文件,主要包括错误日志、查询日志、二进制日志、慢查询日志,另外还有登录日志和更新日志等。
错误日志记录着MySQL服务器在启动、 停止以及运行过程中发生故障和异常的相关信息。数据库管理者可以通过该日志文件对MySQL运行中出现的问题进行排错分析。MySQL5.7中,系统错误日志功能在默认情况下是开启的,并且默认存储路径为/var/log/mysqld.log。管理者可以在MySQL配置文件(/etc/my.cnf)的[mysqld]项中对错误日志的存储路径进行修改,如下所示。
[mysqld]
log-error=/var/log/mysqld.log
为了方便管理,一般情况下管理者会将错误日志的文件名以 hostname.log 的格式进行修改。例如,主机名称为mysql-1,则会将错误日志文件重新命名为mysql-1.log。另外,登录数据库服务器后,也可以使用SHOW命令查看错误日志的存储路径,如下所示。
MariaDB [Chapter_Four]> show variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| log_error | /var/log/mariadb/mariadb.log |
+---------------+------------------------------+
1 row in set (0.003 sec)

MariaDB [Chapter_Four]>
由于错误日志文件显示的信息较长,在查看错误日志时可以使用tail命令查看最近的错误日志信息,具体的SQL语句如下。
图片 1.png
从上面代码的执行结果可以看出,使用tail命令默认情况下查看出了最近的10条日志记录。

3.二进制日志
二进制日志是MySQL日志中非常重要的一种日志,该日志主要对MySQL中数据的每一个变化(事件)进行记录。需要注意,二进制日志并不记录数据库中所执行的SELECT操作。由于系统记录日志的开销比较大,过多的日志会影响系统的处理性能,所以MySQL默认情况下并没有开启二进制日志。另外,最好将日志单独放到一个专用磁盘上,尽量不要和数据放在同一块磁盘上。
1.开启二进制日志
MySQL中可以通过在配置文件的[mysqld]项中添加log-bin参数的方式来开启二进制日志,如下所示。
[mysqld]
log-bin
server-id=10
在上方的参数配置项中,server-id参数表示的是服务器ID号,一般使用IP地址的最后两位数字。当使用多台服务器搭建主从复制集群时,为了防止多台服务器相互复制数据,系统以此参数来区分不同的服务器。log-bin参数如果不指定二进制文件的存储路径,系统将默认将其存储在/var/lib/msyql目录下。如果需要指定二进制日志的存储路径,可以将参数设置成log-bin=path的形式。例如,将二进制日志文件的存储目录设置为/var/log/mysql-bin/slave2,可以在配置文件中进行以下修改。
[mysqld]
log-bin=/var/log/mysql-bin/slave2
server-id=10
需要注意,指定的二进制文件目录必须存在。另外,由于mysqld进程由mysql用户和mysql组负责,还需要对mysql用户和mysql组设定该目录的访问权限,使系统可以正常调用,具体的操作如下。
图片 2.png
二进制日志配置完成后,需要重新启动MySQL服务,才可以启动二进制日志的收集。
图片 3.png
MySQL 服务重启后即可进入相关目录查看二进制日志文件,具体如下所示。
图片 4.png
从上方代码的执行结果可以看出,数据库服务器重启后,在/var/lib/mysql/目录下出现了两个文件,其中mysql-1-bin.000001为二进制日志文件。mysql-1-bin.index为二进制日志的索引文件,该文件记录着系统中存在多少个二进制日志。需要注意的是,二进制日志文价默认以“主机名-bin.日志编号”的格式来进行命名。
2.查看二进制日志
二进制日志在Shell中是不能被解析出内容的,只有使用对应的工具mysqlbinlog才可以查看二进制日志,具体的操作如下。
图片 5.png
从上方代码的执行结果可以看出,使用常规的CAT命令无法查看二进制日志。接下来使用mysqlbinlog工具进行查看,具体代码如下。
图片 6.png
二进制日志文件中,at参数为偏移量,用于记录数据的位置,从日志文件中还可以看出服务器的ID号等信息。接下来,登录数据库并执行一些操作,查看二进制日志文件是否发生变化。登录MySQL数据库并执行SELECT操作,查看二进制日志文件是否变化,具体的操作流程如下所示。
图片 7.png
进行查询操作后,在MySQL中可以使用“!”查看二进制日志文件,具体代码如下。
图片 8.png
从上方代码的执行结果可以看出,在对数据库进行SELECT操作后,二进制日志文件的偏移量并没有发生改变。接下来,尝试在MySQL数据库中插入一些数据。
图片 9.png
从上面的代码可以看出,在数据库中成功创建了一个名为qianfeng的数据库。随后,查看二进制文件是否发生变化,从上方代码的执行结果可以看出,二进制日志文件的偏移量变为219,从该偏移量可以看出先前执行的CREATE DATABASE操作。通过上面的实验也可以验证二进制日志文件只记录关于数据库的启动、停止和数据修改的相关操作。需要注意,如果对MySQL数据库进行重启操作,系统会自动截断二进制日志文件并产生新的记录文件,如下所示。
图片 10.png
另外,用户可以在MySQL控制台中使用flush logs命令手动截断二进制日志文件。
图片 11.png
从上方代码的执行结果可以看出,二进制文件的存储目录中又多出了一个名为mysql-1-bin.000003的日志文件。需要注意,在MySQL控制台中,也可以通过使用SHOW命令查询关于数据库服务器使用二进制日志的一些信息。例如,使用SHOW命令查看所有的二进制日志文件,具体代码如下。
图片 12.png
查看正在使用的二进制日志文件,具体代码如下。
图片 13.png
查看二进制日志文件中的事件,具体代码如下。
图片 14.png
二进制日志查询的其他操作读者可以自行实验。在实际的应用中,一个二进制日志文件中会存储许多数据操作记录(事件)。为了方便用户检索数据,MySQL支持用户根据自己的需求截取不同时间段的二进制日志文件。例如,截取从某个时间开始的事件,具体语句如下。
图片 15.png
截取到某个时间结束的事件,具体语句如下。
图片 16.png
截取某个时间区间的事件,具体语句如下。
图片 17.png
事件的截取主要用于故障恢复,当在某个时间点因误操作而导致数据异常时,可以将数据恢复到这个时间点之前。但是,在同一个数据库服务器中也会存在误操作与正常操作发生在同一时间的情况,如果使用[start/stop]-datetime 这种方式进行数据回滚,可能会造成部分数据的丢失。为了避免这种情况的发生,MySQL也提供了以position为参数的日志文件截取。例如,截取某个position开始的事件,具体语句如下。
图片 18.png
截取某个位置之前的事件,具体语句如下。
图片 19.png
截取某个位置区间的事件,具体语句如下。
图片 20.png
总的来说,以position为衡量值,不仅可以提高截取事件的精度,而且可以避免数据恢复不完整的情况。
4.删除事件
在实际操作中,如果用户想要删除二进制日志文件中的部分事件记录,例如,删除文件名为mysql-1-bin.000002 的日志文件中的全部事件,可以使用如下命令进行操作。
图片 21.png
同样,用户也可以删除某个时间之前的事件,具体命令如下。
图片 22.png
关于二进制日志文件,MySQL控制台中还有一些操作需要谨慎执行。例如,reset master命令会截断并删除所有的二进制日志文件,一旦执行了此命令,数据则无法恢复。具体演示如下。
图片 23.png
从上面代码的执行结果可以看出,所有的二进制日志文件被删除。因此,需要谨慎使用resetmaster命令。
5.暂停和恢复二进制日志
MySQL中如果开启了二进制日志,系统将会一直进行记录。如果需要暂停二进制日志的记录,则可以在MySQL控制台中使用以下命令。
图片 24.png
上方命令中,0表示暂时关闭二进制日志,1表示重新开启二进制日志。需要注意,该命令只在当前会话生效。

4.慢查询日志
慢查询日志用于记录执行时间超过指定时间的操作,用户可以通过该日志中的内容对相关的SQL语句进行优化,从而提高系统的效率。同样,在MySQL配置文件的[mysqld]项中加入相关配置项即可开启慢查询日志,具体命令如下。
图片 25.png
上方参数中long_query_time用于指定时间,单位是秒;slow_query_log用于开启慢查询日志;slow_query_log_file用于指定慢查询日志文件的名称与存储路径。例如,记录执行时间超过2秒的语句,并存储到/var/log/slow_log 文件中,需要修改的配置具体如下。
图片 26.png
需要注意,slow_query_log参数的值可以为1( 1 表示开启状态),配置修改完成后需要重新启动MySQL服务。
在MySQL中可以使用基准测试函数对慢查询日志进行测试。基准测试是一种测试代码性能的方法,同时也可以用来识别某段代码是否会引起CPU或内存的效率问题。在实际的应用中,许多开发人员会使用基准测试来测试不同的并发模式,或者使用基准测试来辅助配置工作池的数量,以实现系统吞吐量的最大化。常见的基准测试函数是BENCHMARK(),其基本的语法格式如下。
图片 27.png
上方命令中,expr表示公式,count表示数量。BENCHMARK()函数可以实现将一个expr执行count次。下面将通过BENCHMARK()函数演示和说明慢查询日志的工作原理。首先,还是以收集执行时间超过2秒的操作为例。在MySQL配置文件中开启慢日志查询并指定日志存储路径后,需要在指定的路径创建该文件并进行授权,具体的操作如下。
图片 28.png
从上方代码的执行结果可以看出,使用tail命令查看slow.log文件并没有显示任何信息。然后,登录到MySQL服务器并使用BENCHMARK()函数进行测试,操作如下。
图片 29.png
上方命令表示,在MySQL中将2*3计算500000000次。当操作者按下回车键时,系统会进行计算,由于计算的次数较多,所以一段时间后才显示出计算结果(等待时间大于2秒)。最后,查看慢查询日志文件中是否有对这次超时事件的记录,如下所示。
图片 30.png
从上面代码的执行结果可以看出,请求时间为5.829380秒。这也说明系统对此次超时事件进行了记录。

5.中继日志
在MySQL主从复制集群中读取主服务器上的二进制日志,并将读取到的信息写入中继日志;然后在中继日志所在的服务器(即从服务器)上进行数据回放,即可使从服务器与主服务器的数据保持一致。

6.Redo日志和Undo日志
学习Redo日志和Undo日志的工作原理前,首先需要了解事务的执行流程,具体如下。
(1)事务开启时,系统会为每个事务开启一个私有工作区。
(2)事务的读操作会将磁盘中的数据复制到工作区。
(3)事务的写操作会把写入的数据输出到内存的缓冲区,等到有合适的事件再由缓存区管理器将数据写入磁盘。事务的执行流程如图所示。
图片 31.png
在进行写操作的时候,由于 MySQL 中存在数据的立即修改和延迟修改的情况,所以事务在执行过程中 ACID 特性可能会被破坏。
( 1)原子性的破坏:在事务提交前出现故障,但是事务对数据库的部分修改已经写入磁盘数据库。
( 2)持久性的破坏:在系统崩溃前已经提交,但是数据还在内存缓冲区中,并没有写入磁盘,系统恢复时将丢失此次已经提交的数据。
Undo日志主要是为了实现事务的原子性,在MySQL数据库的InnoDB存储引擎中,Undo日志用来实现多版本并发控制。在进行任何数据操作之前,系统首先会将数据备份到Undo日志;如果操作出现了错误或者用户执行了ROLLBACK命令,则系统可以利用Undo日志将数据恢复到事务开始之前的状态。需要注意,Undo日志为逻辑日志,可以简单地理解为当事务执行一条DELETE操作时,Undo日志会记录一条对应的INSERT操作;当执行一条INSERT操作时,Undo日志会记录一条DELETE操作。Redo日志主要是为了实现事务的持久性,其中记录的是新的(写操作)数据。在事务提交前,只需要将Redo日志持久化,并不需要将数据持久化。当系统崩溃时,系统可以根据Redo日志中的内容将数据恢复到最近的状态。总的来说,InnoDB存储引擎通过Redo日志和Undo日志中记录的信息保证了数据在任何情况下的安全性。

7.查询日志
查询日志主要记录用户的查询操作,在并发操作较多的应用场景中,该日志会产生大量的信息,并占用过多的磁盘I/O,可能会影响MySQL的性能。所以,在并发操作多的环境下不建议开启查询日志。

8.日志应用
日志直接地反映了数据库的运行状况,通过二进制日志可以实现数据的恢复。本节将通过具体的案例来带领读者学习日志的相关应用。在实际生产环境中,数据库可能会因为使用者的误操作而丢失数据,对企业而言,这将是非常巨大的损失。数据丢失后可以通过系统日志恢复部分数据,以降低损失。接下来将通过具体的案例演示如何对丢失的数据进行恢复。在robin数据库中创建表t1,并向其中插入不同的数据,如下所示。
图片 32.png
接下来通过删除数据库文件的方式来模仿数据库被误删除的场景,具体操作如下所示。
图片 33.png
从上方代码可以看出,数据库文件的存储目录为/var/lib/mysql。接下来, 删除数据库目录中的robin文件。
图片34.png
文件删除后,重新登录到数据库,查看robin数据库是否存在。
图片 35.png
从上面代码的执行结果可以看出,数据库robin不存在。下面将使用二进制日志对丢失的数据进行恢复,具体代码如下。
图片 36.png
上方代码通过mysql.000001日志文件对数据进行了恢复,而且从代码的执行结果可以看出t1表已经存在。接下来登录到数据库,查看数据的恢复情况。
图片 37.png
从上方代码的执行结果可以看出,robin数据库已经存在,但是其中的数据并没有被恢复。尝试删除robin数据库,并重新对数据进行恢复,具体操作如下所示.
图片 38.png
可以看出代码执行成功,此时登录到数据库中查看robin库中的数据是否存在。
图片 39.png
从上方代码的执行结果可以看出,数据只有两条。下面将通过mysql.000002日志文件和mysql.000003日志文件恢复全部数据,具体代码如下。
图片 40.png
此时,再次登录到数据库,查看robin数据库中的数据是否被恢复。
图片 41.png
从上方代码的执行结果可以看出,robin数据库中的数据被全部恢复。

上一篇 下一篇