第14章 日志管理

第14章 日志管理《MySQL数据库应用教程》刘瑞新主编配套资源目录第14章 日志文件MySQL日志简介错误日志文件二进制日志文件通用查询日志文件慢查询日志文件MySQL日志文件分为4种，分别是二进制日志（binarylog）文件、错误日志（errorlog）文件、通用查询日志（general-querylog）文件和慢查询日志（slow-query

log）文件。14.1

MySQL日志简介14.2.1查看错误日志1.查看错误日志的存储路径和文件名查看错误日志的存储路径和文件名分别使用的SQL语句为：SHOW

VARIABLES

LIKE

"datadir";SHOW

VARIABLES

LIKE

"log_error";14.2

错误日志文件【例14-1】查看错误日志的存储路径和文件名。14.2

错误日志文件2.查看错误日志的内容【例14-2】使用记事本查看MySQL错误日志。14.2

错误日志文件14.2.2设置错误日志通过my.ini文件中的[mysqld]组中的log-error选项设置，语法格式为：[mysqld]log-error

[=path/[filename]]14.2

错误日志文件14.2.3删除错误日志文件在Windows系统中，如果希望创建新的错误日志文件，需要先把正在使用的错误日志文件删除，或者改为其他文件名。可以使用“文件资源管理器”删除错误日志文件或者改为其他文件名。14.2

错误日志文件14.2.4创建新的错误日志文件创建新的错误日志文件的方法有3种。重新启动MySQL服务会自动创建一个新的错误日志文件。使用mysqladmin命令创建新的错误日志，其命令的基本语法格式为：mysqladmin-u

root-p

flush-logs也可以执行FLUSH

LOGS语句来创建新的错误日志，SQL语句为：FLUSH

LOGS;14.2

错误日志文件【例14-3】创建一个新的错误日志文件。C:\Users\Administrator>mysqladmin

-u

root

-p

flush-logs按Enter键，显示：Enter

password:

******14.2

错误日志文件14.3.1查看是否启用了二进制日志使用SHOW

VARIABLES语句可以查看二进制日志的开启状态，其语句为：SHOW

VARIABLES

LIKE

"log_bin";【例14-4】查看是否启用了二进制日志。在Navicat

for

MySQL的命令列界面中运行，SQL语句如下：SHOW

VARIABLES

LIKE

"log_bin";14.3

二进制日志文件14.3.2查看日志文件保存的位置SHOW

VARIABLES

LIKE

"datadir";【例14-5】查看二进制日志文件保存的位置。在Navicat

for

MySQL的命令列界面中运行，SQL语句如下：SHOW

VARIABLES

LIKE

"datadir";14.3

二进制日志文件14.3.3设置二进制日志log-bin选项在[mysqld]组中，语法格式为：[mysqld]log-bin

[

=

path[filename]]expire_logs_days

=

10max_binlog_size

=

100M14.3

二进制日志文件【例14-6】配置my.ini文件，启用二进制日志，并把二进制日志文件保存到d:/mysql_log，文件名为binlog。先创建文件夹d:/mysql_log。使用文件资源管理器浏览到C:/ProgramData/MySQL/MySQL

Server

8.0，用记事本打开my.ini文件。右击“记事本”，从快捷菜单中选“以管理员身份运行”，使用“记事本”的“文件”菜单“打开”my.ini文件。在[mysqld]组中添加选项如下。[mysqld]log-bin

=

"d:/mysql_log/binlog"expire_logs_days

=

5max_binlog_size

=

100M添加完毕后保存文件并关闭“记事本”。其中重启MySQL服务进程，即可启用二进制日志。重启MySQL服务后，在

d:/mysql_log文件夹下可以看到binlog.000001文件和binlog.index文件。14.3

二进制日志文件14.3.4查看二进制日志设置使用SHOW

VARIABLES语句查看二进制日志设置，其SQL语句为：SHOW

VARIABLES

LIKE

"log_bin%";【例14-7】使用SHOW

VARIABLES语句查询二进制日志的设置。SHOW

VARIABLES

LIKE

"log_bin%";14.3

二进制日志文件14.3.5生成新的与查看当前的二进制日志文件生成新的二进制文件

FLUSH

LOGS;查看当前正在写入的二进制日志文件

SHOW

MASTER

STATUS;14.3

二进制日志文件【例14-8】生成新的二进制文件，查看当前正在写入的二进制日志文件。在Navicat

for

MySQL的命令列界面中运行。为了多生成几个二进制文件，多次执行下面SQL语句：FLUSHLOGS;FLUSHLOGS;FLUSHLOGS;FLUSH

LOGS;查看当前正在写入的二进制文件，SQL语句如下：SHOW

MASTER

STATUS;14.3

二进制日志文件14.3.6查看所有的二进制日志文件SHOW

{BINARY

|

MASTER}

LOGS;【例14-9】列出当前服务器中所有的二进制日志文件。SQL语句如下：SHOW

BINARY

LOGS;14.3

二进制日志文件14.3.7删除二进制日志文件1.根据编号删除二进制日志文件PURGE

{BINARY

|

MASTER}

LOGS

TO

"filename.number";【例14-10】删除binlog.000003之前的二进制日志文件。1）删除二进制日志文件之前，先查看二进制文件的文件列表，SQL语句如下：SHOW

BINARY

LOGS;14.3

二进制日志文件删除指定编号binlog.000003之前的二进制日志文件，SQL语句如下：PURGE

BINARY

LOGS

TO

"binlog.000003";查看删除二进制日志文件后的文件列表，SQL语句如下：SHOW

BINARY

LOGS;14.3

二进制日志文件2.根据创建时间删除二进制日志文件PURGE

{BINARY

|

MASTER}

LOGS

BEFORE

"yyyy-mm-dd

hh:MM:ss";【例14-11】删除2022年6月3日9:53之前创建的二进制日志。PURGE

BINARY

LOGS

BEFORE

"2022-6-3

9:53:00";14.3

二进制日志文件3.删除所有二进制日志文件删除所有二进制日志文件使用的语句为：RESET

MASTER;14.3

二进制日志文件14.3.8查看二进制日志的使用情况SHOW

BINLOG

EVENTS

[IN

"filename.number"]

[FROM

pos]

[LIMIT[offset,]

row_count];14.3

二进制日志文件例如，下面查看语句。查询第一个（最早）的二进制日志，SQL语句如下：SHOW

BINLOG

EVENTS;指定查询binlog.000003文件，SQL语句如下：SHOW

BINLOG

EVENTS

IN

"binlog.000003";指定查询binlog.000003文件，从pos点234开始查起，SQL语句如下：SHOW

BINLOG

EVENTS

IN

"binlog.000003"

FROM

234;可使用LIMIT指定pos点的起始偏移、查询条数。指定查询binlog.000003文件，从pos点234开始查起，查询10条，SQL语句如下：SHOW

BINLOG

EVENTS

IN

"binlog.000003"

FROM

234

LIMIT

10;指定查询binlog.000003件，从pos点234开始查起，偏移2行，查询10条，SQL语句如下：SHOW

BINLOG

EVENTS

IN

"binlog.000003"

FROM

234

LIMIT

2,

10;14.3

二进制日志文件【例14-12】查看二进制日志文件binlog.000002，从234位置开始，显示5条。SHOW

BINLOG

EVENTS

IN

"binlog.000002"

FROM

234

LIMIT

5;14.3

二进制日志文件14.3.9导出二进制日志文件中的内容mysqlbinlog

[option]filename.number[>outerFilename

|>>outerFilename]例14-13】使用mysqlbinlog命令，查看指定二进制日志文件的内容。1）FLUSH

LOGS;SHOW

BINARY

LOGS;14.3

二进制日志文件①创建一个数据库temp_test，SQL语句如下：CREATE

DATABASE

temp_test;创建数据库temp_test的语句保存在binlog.000002二进制日志文件中。②产生新的二进制日志文件，查看日志文件名列表，SQL语句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;③在temp_test数据库中创建test表，并且向表中插入3条记录，SQL语句如下：USE

temp_test;CREATE

TABLE

test(tid

INT,

tname

CHAR(10));INSERT

INTO

test(tid,

tname)

VALUES

(1,

"aa"),

(2,

"bb"),

(3,

"cc");14.3

二进制日志文件④产生新的二进制日志文件，查看日志文件名列表，SQL语句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;产生新的二进制日志文件binlog.000004。⑤最后执行DELETE语句，删除test表中的所有记录。SQL语句如下：DELETE

FROM

test;删除表记录的语句保存在binlog.000004文件中。3）查看binlog.000003文件的命令如下：C:\Users\Administrator>mysqlbinlog

"d:/mysql_log/binlog.000003"14.3

二进制日志文件14.3

二进制日志文件【例14-14】使用mysqlbinlog命令，把指定的二进制日志文件导出为一个文本文件。把二进制日志文件binlog.000004导出为一个文本文件d:/bin-004.txt，命令如下：C:\Users\Administrator>mysqlbinlog

"d:/mysql_log/binlog.000004"

>"d:/bin-004.txt"14.3

二进制日志文件14.3.10使用二进制日志恢复数据库mysglbinlog

[option]

filename.number

|

mysql

-u

username

-p[password]--start-datetime="dtl"--stop-datetime="dt2"：按照二进制日志中指定的开始时间点dt1和结束时间点dt2恢复数据库，其范围为[dt1,dt2)。--start-position=n1--stop-position=n2：按照二进制日志中指定的开始位置n1和结束位置n2恢复数据库。14.3

二进制日志文件1.完全恢复【例14-15】从二进制日志文件binlog.000003完全恢复数据库。为了演示恢复test表和其中的记录，先删掉test表，SQL语句如下：USE

temp_test;DROP

TABLE

test;由于binlog.000003中完整地保存了创建test表，并且向表中插入3条记录的数据，所以用binlog.000003文件恢复。在“命令提示符”窗口中，执行如下完全恢复命令：C:\Users\Administrator>mysqlbinlog

"d:/mysql_log/binlog.000003"

|mysql

-uroot

-pEnter

password:

******查看test表及其中的记录，SQL语句如下：SELECT

*

FROM

test;14.3

二进制日志文件2.基于时间点的恢复【例14-16】使用基于时间点来恢复数据。1）为了更好地查看日志文件中的内容，先产生一个新的日志文件，SQL语句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;14.3

二进制日志文件2）准备数据。①在temp_test数据库的test表中添加3条记录，SQL语句如下：USE

temp_test;INSERT

INTO

test(tid,

tname)

VALUES

(555,

"eee"),

(666,

"fff"),

(777,

"ggg");上面插入记录的语句，保存在binlog.000006文件中。②假设这时发生了误删除操作，删掉了两条记录，SQL语句如下：DELETE

FROM

test

WHERE

tid="2";DELETE

FROM

test

WHERE

tid="555";14.3

二进制日志文件③再向test表中添加1条记录，并查询记录，SQL语句如下：INSERT

INTO

test(tid,

tname)

VALUES

(888,

"hhh");SELECT

*

FROM

test;运行结果如图14-10所示。④产生一个新的日志文件，SQL语句如下：FLUSH

LOGS;14.3

二进制日志文件①把生binlog.000006文件导出为一个文本文件，命令如下。C:\Users\Administrator>mysqlbinlog

"d:/mysql_log/binlog.000006">

"d:/bin-006.txt"②在“命令提示符”窗口中执行上面命令，然后使用记事本打开d:/bin-006.txt文件，从内容中找到要恢复的时间点。14.3

二进制日志文件14.3

二进制日志文件先使用mysglbinlog命令恢复到误操作时间点之前，在“命令提示符”窗口中执行下面命令：C:\Users\Administrator>mysqlbinlog

--stop-datetime="2022-06-05

10:14:08""d:/mysql_log/binlog.000006"

|

mysql

-uroot

-pEnter

password:

******在temp_test数据库的test表中查看记录，SQL语句如下：SELECT

*

FROM

test;14.3

二进制日志文件INSERT

INTO

test(tid,

tname)

VALUES

(555,

"eee"),

(666,

"fff"),

(777,

"ggg");由于该表没有设置主键约束，所以出现了重复记录。tid为2的记录没有被恢复，这是因为在binlog.000006文件中没有该记录的插入语句（该插入语句在例14-15中创建的binlog.000003文件中）。5）然后跳过误操作的时间点，继续执行后面的二进制日志，在“命令提示符”窗口中执行下面命令：C:\Users\Administrator>mysqlbinlog

--start-datetime="2022-06-05

10:27:03""d:/mysql_log/binlog.000006"

|

mysql

-uroot

-pEnter

password:

******14.3

二进制日志文件6）在temp_test数据库的test表中查看记录，SQL语句如下：SELECT

*

FROM

test;显示test表中的记录如图14-14所示。恢复时再次执行了插入记录语句：INSERT

INTO

test(tid,

tname)

VALUES

(888,

"hhh");14.3

二进制日志文件3.基于位置的恢复基于时间点的恢复可能出现一个非常严重的问题，就是在这个时间点中可能存在误操作和正确操作，那么正确的操作也被跳过去了。所以就要使用更为精确的恢复方式，即基于位置恢复。【例14-17】使用基于位置来恢复数据。1）准备数据。①先产生新的日志文件，SQL语句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;②在temp_test数据库中，删除test表中的所有记录，目的是便于查看记录。SQL语句如下：USE

temp_test;DELETE

FROM

test;14.3

二进制日志文件C:\Users\Administrator>mysqlbinlog

--start-position=316

--stop-position=472

"d:/mysql_log/binlog.000006"

|

mysql

-uroot

-pEnter

password:

******其中，--start-position=316--stop-position=472是两个位置。5）最后查看test表的记录，SQL语句如下：SELECT

*

FROM

test;14.3

二进制日志文件2）查找恢复位置。假设恢复binlog.000006文件中删除记录之前的插入记录的操作，执行的SQL语句如下：USE

temp_test;INSERT

INTO

test(tid,

tname)

VALUES

(555,

"eee"),

(666,

"fff"),

(777,

"ggg");14.3

二进制日志文件14.3.11暂时停止二进制日志功能1.临时暂停二进制日志SET

sql_log_bin=

{

0

|

1

};14.3

二进制日志文件2.关闭二进制日志使用SET可以临时暂停二进制日志功能，当重启MySQL服务后，二进制日志将由my.ini文件中的选项决定。如果需要关闭二进制日志，在my.ini文件中的[mysqld]中添加下面选项：[mysqld]disable-log-bin或skip-log-bin14.3

二进制日志文件14.3.12二进制日志的三种格式STATEMENT模式（SBR）ROW模式（RBR）MIXED模式（MBR）14.3

二进制日志文件14.4.1查看是否启用了通用查询日志SHOW

VARIABLES

LIKE

"general_log";【例14-18】查看是否启用了通用查询日志。在Navicat

for

MySQL的命令列界面中运行，SQL语句如下：SHOW

VARIABLES

LIKE

"general_log";14.4

通用查询日志文件14.4.2设置通用查询日志1.在配置文件中设置在配置文件my.ini文件的[mysqld]组中，设置通用查询日志的系统变量，其语法格式为：[mysgld]log-output={NONE

|

FILE

|

TABLE

|

FILE,

TABLE

}general-log={

1

|

0}general_log_file

=[path][filename]14.4

通用查询日志文件【例14-19】设置启用通用查询日志，输出日志为文件，保存在d:/mysql_log/general.log。以管理员身份运行记事本，打开my.ini文件，在mysqld组中，添加或修改启用通用查询日志的系统变量参数：[mysgld]log-output=FILEgeneral-log=1general_log_file="d:/mysql_log/general.log"重启MySQL服务进程，即可启用通用查询日志。查看是否启用了通用查询日志以及保存位置，SQL语句为：SHOW

VARIABLES

LIKE

"%general%";14.4

通用查询日志文件查看通用查询日志的输出方式，SQL语句为：SHOW

VARIABLES

LIKE

"log_output";14.4

通用查询日志文件2.临时修改设置设置日志输出方式为文件，其语法格式为：SET

GLOBAL

log_output={"FLIE"

|

"TABLE"

|

"FILE,TABLE"};其中，"FILE,TABLE"为文件和表混合输出通用日志。设置通用查询日志文件的路径，其语法格式为：

SET

GLOBAL

general_log_file=[path][filename]开启或关闭通用查询日志，其语法格式为：SET

GLOBAL

general_log={on

|

off

|

1

|

0};14.4

通用查询日志文件【例14-20】临时修改通用查询日志的设置。在Navicat

for

MySQL的命令列界面中运行下面SQL语句。SET

GLOBAL

log_output="FILE";SET

GLOBAL

general_log_file="d:/mysql_log/general.log";SET

GLOBAL

general_log=on;查询日志输出方式，SQL语句如下：SELECT

@@global.log_output;SHOW

VARIABLES

LIKE

"log_output";14.4

通用查询日志文件14.4.3查看通用查询日志文件中的内容【例14-21】查看通用查询日志文件d:/mysql_log/general.log中的内容。1）在Navicat

for

MySQL的查询窗格中运行下面SQL语句。USE

studentinfo;SELECT

*

FROM

student

WHERE

Sex="女";INSERT

INTO

selectcourse

(StudentID,

CourseID,

Score)

VALUES("202270010103",

"100101",

99);关闭Navicat

for

MySQL。2）用记事本打开d:/mysql_log/general.log文件，如图14-16所示，左侧显示执行语句的开始时间、语句类别、SQL语句等内容。14.4

通用查询日志文件14.4

通用查询日志文件14.4.4删除通用查询日志通用查询日志记录用户的所有操作。如果数据库的使用非常频繁，那么通用查询日志将会占用非常大的磁盘空间。数据库管理员可以删除很长时间之前的通用查询日志。14.4

通用查询日志文件14.5.1设置慢查询日志1.在配置文件中设置在配置文件my.ini文件的[mysqld]组中，设置慢查询日志的系统变量，其语法格式为：[mysgld]log-output={NONE

|

FILE

|

TABLE

|

FILE,

TABLE

}slow_query_log=

{1

|

0}slow_query_log_file

[=path/[filename]]long_query_time

=

n14.5

慢查询日志文件【例14-22】设置启用慢查询日志，保存在d:/mysql_log/slow.log，超时为2s。使用记事本打开my.ini文件，在[mysqld]组中，添加启用通用查询日志的系统变量参数：[mysgld]slow_query_log=1slow_query_log_file="d:/mysql_log/slow.log"long_query_time=214.4

通用查询日志文件【例14-23】查看是否启用了慢查询日志、保存位置，查看查看超时时间。查看是否启用了慢查询日志、保存位置，SQL语句为：SHOW

VARIABLES

LIKE

"slow_query%";查看通用查询日志和慢查询日志的输出方式，SQL语句为：SHOW

VARIABLES

LIKE

"log_output";14.4

通用查询日志文件查看超时时间，SQL