第14章 日志管理

第14章 日志管理《MySQL數(shù)據(jù)庫應(yīng)用教程》劉瑞新主編配套資源目錄第14章 日志文件MySQL日志簡(jiǎn)介錯(cuò)誤日志文件二進(jìn)制日志文件通用查詢?nèi)罩疚募樵內(nèi)罩疚募﨧ySQL日志文件分為4種，分別是二進(jìn)制日志（binarylog）文件、錯(cuò)誤日志（errorlog）文件、通用查詢?nèi)罩荆╣eneral-querylog）文件和慢查詢?nèi)罩荆╯low-query

log）文件。14.1

MySQL日志簡(jiǎn)介14.2.1查看錯(cuò)誤日志1.查看錯(cuò)誤日志的存儲(chǔ)路徑和文件名查看錯(cuò)誤日志的存儲(chǔ)路徑和文件名分別使用的SQL語句為：SHOW

VARIABLES

LIKE

"datadir";SHOW

VARIABLES

LIKE

"log_error";14.2

錯(cuò)誤日志文件【例14-1】查看錯(cuò)誤日志的存儲(chǔ)路徑和文件名。14.2

錯(cuò)誤日志文件2.查看錯(cuò)誤日志的內(nèi)容【例14-2】使用記事本查看MySQL錯(cuò)誤日志。14.2

錯(cuò)誤日志文件14.2.2設(shè)置錯(cuò)誤日志通過my.ini文件中的[mysqld]組中的log-error選項(xiàng)設(shè)置，語法格式為：[mysqld]log-error

[=path/[filename]]14.2

錯(cuò)誤日志文件14.2.3刪除錯(cuò)誤日志文件在Windows系統(tǒng)中，如果希望創(chuàng)建新的錯(cuò)誤日志文件，需要先把正在使用的錯(cuò)誤日志文件刪除，或者改為其他文件名。可以使用“文件資源管理器”刪除錯(cuò)誤日志文件或者改為其他文件名。14.2

錯(cuò)誤日志文件14.2.4創(chuàng)建新的錯(cuò)誤日志文件創(chuàng)建新的錯(cuò)誤日志文件的方法有3種。重新啟動(dòng)MySQL服務(wù)會(huì)自動(dòng)創(chuàng)建一個(gè)新的錯(cuò)誤日志文件。使用mysqladmin命令創(chuàng)建新的錯(cuò)誤日志，其命令的基本語法格式為：mysqladmin-u

root-p

flush-logs也可以執(zhí)行FLUSH

LOGS語句來創(chuàng)建新的錯(cuò)誤日志，SQL語句為：FLUSH

LOGS;14.2

錯(cuò)誤日志文件【例14-3】創(chuàng)建一個(gè)新的錯(cuò)誤日志文件。C:\Users\Administrator>mysqladmin

-u

root

-p

flush-logs按Enter鍵，顯示：Enter

password:

******14.2

錯(cuò)誤日志文件14.3.1查看是否啟用了二進(jìn)制日志使用SHOW

VARIABLES語句可以查看二進(jìn)制日志的開啟狀態(tài)，其語句為：SHOW

VARIABLES

LIKE

"log_bin";【例14-4】查看是否啟用了二進(jìn)制日志。在Navicat

for

MySQL的命令列界面中運(yùn)行，SQL語句如下：SHOW

VARIABLES

LIKE

"log_bin";14.3

二進(jìn)制日志文件14.3.2查看日志文件保存的位置SHOW

VARIABLES

LIKE

"datadir";【例14-5】查看二進(jìn)制日志文件保存的位置。在Navicat

for

MySQL的命令列界面中運(yùn)行，SQL語句如下：SHOW

VARIABLES

LIKE

"datadir";14.3

二進(jìn)制日志文件14.3.3設(shè)置二進(jìn)制日志log-bin選項(xiàng)在[mysqld]組中，語法格式為：[mysqld]log-bin

[

=

path[filename]]expire_logs_days

=

10max_binlog_size

=

100M14.3

二進(jìn)制日志文件【例14-6】配置my.ini文件，啟用二進(jìn)制日志，并把二進(jìn)制日志文件保存到d:/mysql_log，文件名為binlog。先創(chuàng)建文件夾d:/mysql_log。使用文件資源管理器瀏覽到C:/ProgramData/MySQL/MySQL

Server

8.0，用記事本打開my.ini文件。右擊“記事本”，從快捷菜單中選“以管理員身份運(yùn)行”，使用“記事本”的“文件”菜單“打開”my.ini文件。在[mysqld]組中添加選項(xiàng)如下。[mysqld]log-bin

=

"d:/mysql_log/binlog"expire_logs_days

=

5max_binlog_size

=

100M添加完畢后保存文件并關(guān)閉“記事本”。其中重啟MySQL服務(wù)進(jìn)程，即可啟用二進(jìn)制日志。重啟MySQL服務(wù)后，在

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

二進(jìn)制日志文件14.3.4查看二進(jìn)制日志設(shè)置使用SHOW

VARIABLES語句查看二進(jìn)制日志設(shè)置，其SQL語句為：SHOW

VARIABLES

LIKE

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

VARIABLES語句查詢二進(jìn)制日志的設(shè)置。SHOW

VARIABLES

LIKE

"log_bin%";14.3

二進(jìn)制日志文件14.3.5生成新的與查看當(dāng)前的二進(jìn)制日志文件生成新的二進(jìn)制文件

FLUSH

LOGS;查看當(dāng)前正在寫入的二進(jìn)制日志文件

SHOW

MASTER

STATUS;14.3

二進(jìn)制日志文件【例14-8】生成新的二進(jìn)制文件，查看當(dāng)前正在寫入的二進(jìn)制日志文件。在Navicat

for

MySQL的命令列界面中運(yùn)行。為了多生成幾個(gè)二進(jìn)制文件，多次執(zhí)行下面SQL語句：FLUSHLOGS;FLUSHLOGS;FLUSHLOGS;FLUSH

LOGS;查看當(dāng)前正在寫入的二進(jìn)制文件，SQL語句如下：SHOW

MASTER

STATUS;14.3

二進(jìn)制日志文件14.3.6查看所有的二進(jìn)制日志文件SHOW

{BINARY

|

MASTER}

LOGS;【例14-9】列出當(dāng)前服務(wù)器中所有的二進(jìn)制日志文件。SQL語句如下：SHOW

BINARY

LOGS;14.3

二進(jìn)制日志文件14.3.7刪除二進(jìn)制日志文件1.根據(jù)編號(hào)刪除二進(jìn)制日志文件PURGE

{BINARY

|

MASTER}

LOGS

TO

"filename.number";【例14-10】刪除binlog.000003之前的二進(jìn)制日志文件。1）刪除二進(jìn)制日志文件之前，先查看二進(jìn)制文件的文件列表，SQL語句如下：SHOW

BINARY

LOGS;14.3

二進(jìn)制日志文件刪除指定編號(hào)binlog.000003之前的二進(jìn)制日志文件，SQL語句如下：PURGE

BINARY

LOGS

TO

"binlog.000003";查看刪除二進(jìn)制日志文件后的文件列表，SQL語句如下：SHOW

BINARY

LOGS;14.3

二進(jìn)制日志文件2.根據(jù)創(chuàng)建時(shí)間刪除二進(jìn)制日志文件PURGE

{BINARY

|

MASTER}

LOGS

BEFORE

"yyyy-mm-dd

hh:MM:ss";【例14-11】刪除2022年6月3日9:53之前創(chuàng)建的二進(jìn)制日志。PURGE

BINARY

LOGS

BEFORE

"2022-6-3

9:53:00";14.3

二進(jìn)制日志文件3.刪除所有二進(jìn)制日志文件刪除所有二進(jìn)制日志文件使用的語句為：RESET

MASTER;14.3

二進(jìn)制日志文件14.3.8查看二進(jìn)制日志的使用情況SHOW

BINLOG

EVENTS

[IN

"filename.number"]

[FROM

pos]

[LIMIT[offset,]

row_count];14.3

二進(jìn)制日志文件例如，下面查看語句。查詢第一個(gè)（最早）的二進(jìn)制日志，SQL語句如下：SHOW

BINLOG

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

BINLOG

EVENTS

IN

"binlog.000003";指定查詢binlog.000003文件，從pos點(diǎn)234開始查起，SQL語句如下：SHOW

BINLOG

EVENTS

IN

"binlog.000003"

FROM

234;可使用LIMIT指定pos點(diǎn)的起始偏移、查詢條數(shù)。指定查詢binlog.000003文件，從pos點(diǎn)234開始查起，查詢10條，SQL語句如下：SHOW

BINLOG

EVENTS

IN

"binlog.000003"

FROM

234

LIMIT

10;指定查詢binlog.000003件，從pos點(diǎn)234開始查起，偏移2行，查詢10條，SQL語句如下：SHOW

BINLOG

EVENTS

IN

"binlog.000003"

FROM

234

LIMIT

2,

10;14.3

二進(jìn)制日志文件【例14-12】查看二進(jìn)制日志文件binlog.000002，從234位置開始，顯示5條。SHOW

BINLOG

EVENTS

IN

"binlog.000002"

FROM

234

LIMIT

5;14.3

二進(jìn)制日志文件14.3.9導(dǎo)出二進(jìn)制日志文件中的內(nèi)容mysqlbinlog

[option]filename.number[>outerFilename

|>>outerFilename]例14-13】使用mysqlbinlog命令，查看指定二進(jìn)制日志文件的內(nèi)容。1）FLUSH

LOGS;SHOW

BINARY

LOGS;14.3

二進(jìn)制日志文件①創(chuàng)建一個(gè)數(shù)據(jù)庫temp_test，SQL語句如下：CREATE

DATABASE

temp_test;創(chuàng)建數(shù)據(jù)庫temp_test的語句保存在binlog.000002二進(jìn)制日志文件中。②產(chǎn)生新的二進(jìn)制日志文件，查看日志文件名列表，SQL語句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;③在temp_test數(shù)據(jù)庫中創(chuàng)建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

二進(jìn)制日志文件④產(chǎn)生新的二進(jìn)制日志文件，查看日志文件名列表，SQL語句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;產(chǎn)生新的二進(jìn)制日志文件binlog.000004。⑤最后執(zhí)行DELETE語句，刪除test表中的所有記錄。SQL語句如下：DELETE

FROM

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

"d:/mysql_log/binlog.000003"14.3

二進(jìn)制日志文件14.3

二進(jìn)制日志文件【例14-14】使用mysqlbinlog命令，把指定的二進(jìn)制日志文件導(dǎo)出為一個(gè)文本文件。把二進(jìn)制日志文件binlog.000004導(dǎo)出為一個(gè)文本文件d:/bin-004.txt，命令如下：C:\Users\Administrator>mysqlbinlog

"d:/mysql_log/binlog.000004"

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

二進(jìn)制日志文件14.3.10使用二進(jìn)制日志恢復(fù)數(shù)據(jù)庫mysglbinlog

[option]

filename.number

|

mysql

-u

username

-p[password]--start-datetime="dtl"--stop-datetime="dt2"：按照二進(jìn)制日志中指定的開始時(shí)間點(diǎn)dt1和結(jié)束時(shí)間點(diǎn)dt2恢復(fù)數(shù)據(jù)庫，其范圍為[dt1,dt2)。--start-position=n1--stop-position=n2：按照二進(jìn)制日志中指定的開始位置n1和結(jié)束位置n2恢復(fù)數(shù)據(jù)庫。14.3

二進(jìn)制日志文件1.完全恢復(fù)【例14-15】從二進(jìn)制日志文件binlog.000003完全恢復(fù)數(shù)據(jù)庫。為了演示恢復(fù)test表和其中的記錄，先刪掉test表，SQL語句如下：USE

temp_test;DROP

TABLE

test;由于binlog.000003中完整地保存了創(chuàng)建test表，并且向表中插入3條記錄的數(shù)據(jù)，所以用binlog.000003文件恢復(fù)。在“命令提示符”窗口中，執(zhí)行如下完全恢復(fù)命令：C:\Users\Administrator>mysqlbinlog

"d:/mysql_log/binlog.000003"

|mysql

-uroot

-pEnter

password:

******查看test表及其中的記錄，SQL語句如下：SELECT

*

FROM

test;14.3

二進(jìn)制日志文件2.基于時(shí)間點(diǎn)的恢復(fù)【例14-16】使用基于時(shí)間點(diǎn)來恢復(fù)數(shù)據(jù)。1）為了更好地查看日志文件中的內(nèi)容，先產(chǎn)生一個(gè)新的日志文件，SQL語句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;14.3

二進(jìn)制日志文件2）準(zhǔn)備數(shù)據(jù)。①在temp_test數(shù)據(jù)庫的test表中添加3條記錄，SQL語句如下：USE

temp_test;INSERT

INTO

test(tid,

tname)

VALUES

(555,

"eee"),

(666,

"fff"),

(777,

"ggg");上面插入記錄的語句，保存在binlog.000006文件中。②假設(shè)這時(shí)發(fā)生了誤刪除操作，刪掉了兩條記錄，SQL語句如下：DELETE

FROM

test

WHERE

tid="2";DELETE

FROM

test

WHERE

tid="555";14.3

二進(jìn)制日志文件③再向test表中添加1條記錄，并查詢記錄，SQL語句如下：INSERT

INTO

test(tid,

tname)

VALUES

(888,

"hhh");SELECT

*

FROM

test;運(yùn)行結(jié)果如圖14-10所示。④產(chǎn)生一個(gè)新的日志文件，SQL語句如下：FLUSH

LOGS;14.3

二進(jìn)制日志文件①把生binlog.000006文件導(dǎo)出為一個(gè)文本文件，命令如下。C:\Users\Administrator>mysqlbinlog

"d:/mysql_log/binlog.000006">

"d:/bin-006.txt"②在“命令提示符”窗口中執(zhí)行上面命令，然后使用記事本打開d:/bin-006.txt文件，從內(nèi)容中找到要恢復(fù)的時(shí)間點(diǎn)。14.3

二進(jìn)制日志文件14.3

二進(jìn)制日志文件先使用mysglbinlog命令恢復(fù)到誤操作時(shí)間點(diǎn)之前，在“命令提示符”窗口中執(zhí)行下面命令：C:\Users\Administrator>mysqlbinlog

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

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

|

mysql

-uroot

-pEnter

password:

******在temp_test數(shù)據(jù)庫的test表中查看記錄，SQL語句如下：SELECT

*

FROM

test;14.3

二進(jìn)制日志文件INSERT

INTO

test(tid,

tname)

VALUES

(555,

"eee"),

(666,

"fff"),

(777,

"ggg");由于該表沒有設(shè)置主鍵約束，所以出現(xiàn)了重復(fù)記錄。tid為2的記錄沒有被恢復(fù)，這是因?yàn)樵赽inlog.000006文件中沒有該記錄的插入語句（該插入語句在例14-15中創(chuàng)建的binlog.000003文件中）。5）然后跳過誤操作的時(shí)間點(diǎn)，繼續(xù)執(zhí)行后面的二進(jìn)制日志，在“命令提示符”窗口中執(zhí)行下面命令：C:\Users\Administrator>mysqlbinlog

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

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

|

mysql

-uroot

-pEnter

password:

******14.3

二進(jìn)制日志文件6）在temp_test數(shù)據(jù)庫的test表中查看記錄，SQL語句如下：SELECT

*

FROM

test;顯示test表中的記錄如圖14-14所示?；謴?fù)時(shí)再次執(zhí)行了插入記錄語句：INSERT

INTO

test(tid,

tname)

VALUES

(888,

"hhh");14.3

二進(jìn)制日志文件3.基于位置的恢復(fù)基于時(shí)間點(diǎn)的恢復(fù)可能出現(xiàn)一個(gè)非常嚴(yán)重的問題，就是在這個(gè)時(shí)間點(diǎn)中可能存在誤操作和正確操作，那么正確的操作也被跳過去了。所以就要使用更為精確的恢復(fù)方式，即基于位置恢復(fù)?！纠?4-17】使用基于位置來恢復(fù)數(shù)據(jù)。1）準(zhǔn)備數(shù)據(jù)。①先產(chǎn)生新的日志文件，SQL語句如下：FLUSH

LOGS;SHOW

BINARY

LOGS;②在temp_test數(shù)據(jù)庫中，刪除test表中的所有記錄，目的是便于查看記錄。SQL語句如下：USE

temp_test;DELETE

FROM

test;14.3

二進(jìn)制日志文件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是兩個(gè)位置。5）最后查看test表的記錄，SQL語句如下：SELECT

*

FROM

test;14.3

二進(jìn)制日志文件2）查找恢復(fù)位置。假設(shè)恢復(fù)binlog.000006文件中刪除記錄之前的插入記錄的操作，執(zhí)行的SQL語句如下：USE

temp_test;INSERT

INTO

test(tid,

tname)

VALUES

(555,

"eee"),

(666,

"fff"),

(777,

"ggg");14.3

二進(jìn)制日志文件14.3.11暫時(shí)停止二進(jìn)制日志功能1.臨時(shí)暫停二進(jìn)制日志SET

sql_log_bin=

{

0

|

1

};14.3

二進(jìn)制日志文件2.關(guān)閉二進(jìn)制日志使用SET可以臨時(shí)暫停二進(jìn)制日志功能，當(dāng)重啟MySQL服務(wù)后，二進(jìn)制日志將由my.ini文件中的選項(xiàng)決定。如果需要關(guān)閉二進(jìn)制日志，在my.ini文件中的[mysqld]中添加下面選項(xiàng)：[mysqld]disable-log-bin或skip-log-bin14.3

二進(jìn)制日志文件14.3.12二進(jìn)制日志的三種格式STATEMENT模式（SBR）ROW模式（RBR）MIXED模式（MBR）14.3

二進(jìn)制日志文件14.4.1查看是否啟用了通用查詢?nèi)罩維HOW

VARIABLES

LIKE

"general_log";【例14-18】查看是否啟用了通用查詢?nèi)罩?。在Navicat

for

MySQL的命令列界面中運(yùn)行，SQL語句如下：SHOW

VARIABLES

LIKE

"general_log";14.4

通用查詢?nèi)罩疚募?4.4.2設(shè)置通用查詢?nèi)罩?.在配置文件中設(shè)置在配置文件my.ini文件的[mysqld]組中，設(shè)置通用查詢?nèi)罩镜南到y(tǒng)變量，其語法格式為：[mysgld]log-output={NONE

|

FILE

|

TABLE

|

FILE,

TABLE

}general-log={

1

|

0}general_log_file

=[path][filename]14.4

通用查詢?nèi)罩疚募纠?4-19】設(shè)置啟用通用查詢?nèi)罩荆敵鋈罩緸槲募?，保存在d:/mysql_log/general.log。以管理員身份運(yùn)行記事本，打開my.ini文件，在mysqld組中，添加或修改啟用通用查詢?nèi)罩镜南到y(tǒng)變量參數(shù)：[mysgld]log-output=FILEgeneral-log=1general_log_file="d:/mysql_log/general.log"重啟MySQL服務(wù)進(jìn)程，即可啟用通用查詢?nèi)罩?。查看是否啟用了通用查詢?nèi)罩疽约氨４嫖恢?，SQL語句為：SHOW

VARIABLES

LIKE

"%general%";14.4

通用查詢?nèi)罩疚募榭赐ㄓ貌樵內(nèi)罩镜妮敵龇绞剑琒QL語句為：SHOW

VARIABLES

LIKE

"log_output";14.4

通用查詢?nèi)罩疚募?.臨時(shí)修改設(shè)置設(shè)置日志輸出方式為文件，其語法格式為：SET

GLOBAL

log_output={"FLIE"

|

"TABLE"

|

"FILE,TABLE"};其中，"FILE,TABLE"為文件和表混合輸出通用日志。設(shè)置通用查詢?nèi)罩疚募穆窂?，其語法格式為：

SET

GLOBAL

general_log_file=[path][filename]開啟或關(guān)閉通用查詢?nèi)罩?，其語法格式為：SET

GLOBAL

general_log={on

|

off

|

1

|

0};14.4

通用查詢?nèi)罩疚募纠?4-20】臨時(shí)修改通用查詢?nèi)罩镜脑O(shè)置。在Navicat

for

MySQL的命令列界面中運(yùn)行下面SQL語句。SET

GLOBAL

log_output="FILE";SET

GLOBAL

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

GLOBAL

general_log=on;查詢?nèi)罩据敵龇绞?，SQL語句如下：SELECT

@@global.log_output;SHOW

VARIABLES

LIKE

"log_output";14.4

通用查詢?nèi)罩疚募?4.4.3查看通用查詢?nèi)罩疚募械膬?nèi)容【例14-21】查看通用查詢?nèi)罩疚募:/mysql_log/general.log中的內(nèi)容。1）在Navicat

for

MySQL的查詢窗格中運(yùn)行下面SQL語句。USE

studentinfo;SELECT

*

FROM

student

WHERE

Sex="女";INSERT

INTO

selectcourse

(StudentID,

CourseID,

Score)

VALUES("202270010103",

"100101",

99);關(guān)閉Navicat

for

MySQL。2）用記事本打開d:/mysql_log/general.log文件，如圖14-16所示，左側(cè)顯示執(zhí)行語句的開始時(shí)間、語句類別、SQL語句等內(nèi)容。14.4

通用查詢?nèi)罩疚募?4.4

通用查詢?nèi)罩疚募?4.4.4刪除通用查詢?nèi)罩就ㄓ貌樵內(nèi)罩居涗浻脩舻乃胁僮?。如果?shù)據(jù)庫的使用非常頻繁，那么通用查詢?nèi)罩緦?huì)占用非常大的磁盤空間。數(shù)據(jù)庫管理員可以刪除很長(zhǎng)時(shí)間之前的通用查詢?nèi)罩尽?4.4

通用查詢?nèi)罩疚募?4.5.1設(shè)置慢查詢?nèi)罩?.在配置文件中設(shè)置在配置文件my.ini文件的[mysqld]組中，設(shè)置慢查詢?nèi)罩镜南到y(tǒng)變量，其語法格式為：[mysgld]log-output={NONE

|

FILE

|

TABLE

|

FILE,

TABLE

}slow_query_log=

{1

|

0}slow_query_log_file

[=path/[filename]]long_query_time

=

n14.5

慢查詢?nèi)罩疚募纠?4-22】設(shè)置啟用慢查詢?nèi)罩?，保存在d:/mysql_log/slow.log，超時(shí)為2s。使用記事本打開my.ini文件，在[mysqld]組中，添加啟用通用查詢?nèi)罩镜南到y(tǒng)變量參數(shù)：[mysgld]slow_query_log=1slow_query_log_file="d:/mysql_log/slow.log"long_query_time=214.4

通用查詢?nèi)罩疚募纠?4-23】查看是否啟用了慢查詢?nèi)罩?、保存位置，查看查看超時(shí)時(shí)間。查看是否啟用了慢查詢?nèi)罩?、保存位置，SQL語句為：SHOW

VARIABLES

LIKE

"slow_query%";查看通用查詢?nèi)罩竞吐樵內(nèi)罩镜妮敵龇绞?，SQL語句為：SHOW

VARIABLES

LIKE

"log_output";14.4

通用查詢?nèi)罩疚募榭闯瑫r(shí)時(shí)間，SQL