MySQL數(shù)據(jù)庫應(yīng)用教程 課件 機(jī)械 第13、14章 備份和恢復(fù)、日志管理

第13章 備份和恢復(fù)《MySQL數(shù)據(jù)庫應(yīng)用教程》目錄第13章 備份和恢復(fù)備份與恢復(fù)概述使用mysqldump命令備份數(shù)據(jù)數(shù)據(jù)的恢復(fù)其他備份與恢復(fù)數(shù)據(jù)的方法表記錄的導(dǎo)出與導(dǎo)入13.1.1數(shù)據(jù)備份的原因系統(tǒng)故障事務(wù)故障介質(zhì)故障其他災(zāi)害13.1

備份與恢復(fù)概述13.1.2數(shù)據(jù)庫備份的分類1.按備份時(shí)服務(wù)器是否在線劃分熱備份溫備份冷備份13.1

備份與恢復(fù)概述2.按備份的內(nèi)容劃分邏輯備份物理備份13.1

備份與恢復(fù)概述3.按備份涉及的數(shù)據(jù)范圍劃分完整備份增量備份差異備份13.1

備份與恢復(fù)概述13.1.3備份的周期和頻率13.1.4數(shù)據(jù)恢復(fù)的方法13.1

備份與恢復(fù)概述13.2.1備份數(shù)據(jù)庫或表mysqldump

[-h

[hostname]]

-u

username

-p[password]

db_name[

tb_name1

tb_name

2

...]

>

filename.sql13.2 使用mysqldump命令備份數(shù)據(jù)【例13-1】使用mysqldump命令，備份studentinfo數(shù)據(jù)庫中的所有表，備份文件studentinfo.sql存儲(chǔ)在d:/db文件夾中。操作步驟如下：務(wù)必先創(chuàng)建d:/db文件夾。在“命令提示符”窗口中，以root用戶登錄MySQL服務(wù)器C:\Users\Administrator>mysqldump

-u

root

-p

studentinfo

>

d:/db/studentinfo.sql13.2 使用mysqldump命令備份數(shù)據(jù)【例13-2】使用mysqldump命令備份studentinfo數(shù)據(jù)庫中的class、student表，備份文件studentinfo_class_student.sql存儲(chǔ)在D:/db文件夾中。C:\Users\Administrator>mysqldump

-u

root

-p123456

studentinfo

class

student

>d:/db/studentinfo_class_student.sql運(yùn)行上面命令后，系統(tǒng)提示：mysqldump:

[Warning]

Using

a

password

on

the

command

line

interface

canbe

insecure.13.2 使用mysqldump命令備份數(shù)據(jù)13.2.2查看備份文件用記事本打開studentinfo.sql文件，文件內(nèi)容如圖13-2所示。13.2 使用mysqldump命令備份數(shù)據(jù)13.2.3使用mysqldump命令備份多個(gè)數(shù)據(jù)庫mysqldump

-u

usermame

-p[password]

--databases

db_name1

[db_name2

...]

>

filename.sql【例13-3】使用mysqldump命令備份數(shù)據(jù)庫studentinfo。C:\Users\Administrator>mysqldump-u

root-p--databases

studentinfo>

d:/db/studentinfo2.sql輸入上面?zhèn)浞輸?shù)據(jù)庫的命令，按Enter鍵，顯示：Enter

password:

******13.2 使用mysqldump命令備份數(shù)據(jù)【例13-3】使用mysqldump命令備份數(shù)據(jù)庫studentinfo。C:\Users\Administrator>mysqldump

-u

root

-p

--databases

studentinfo

>d:/db/studentinfo2.sql輸入上面?zhèn)浞輸?shù)據(jù)庫的命令，按Enter鍵，顯示：Enter

password:

******13.2 使用mysqldump命令備份數(shù)據(jù)13.2 使用mysqldump命令備份數(shù)據(jù)13.2.4使用mysqldump命令備份服務(wù)器上的所有數(shù)據(jù)庫mysgldump

-u

username

-p

--all-databases

>

filename.sql【例13-4】使用mysqldump命令備份MySQL服務(wù)器上的所有數(shù)據(jù)庫。在“命令提示符”窗口中輸入下面?zhèn)浞莘?wù)器上全部數(shù)據(jù)庫的命令：C:\Users\Administrator>mysqldump

-u

root

-p

--all-databases

>d:/db/all_mysql.sqlEnter

password:

******13.2 使用mysqldump命令備份數(shù)據(jù)11.3.1使用mysql命令恢復(fù)數(shù)據(jù)1.使用mysgl命令恢復(fù)表mysql

-u

username

-p

db_name

<

filename.sql【例13-5】使用mysql命令恢復(fù)studentinfo數(shù)據(jù)庫中的class、student表，備份文件是例13-2中使用mysqldump命令備份的腳本文件studentinfo_class_student.sql，存儲(chǔ)在D:/db文件夾中。C:\Users\Administrator>mysql

-u

root

-p

studentinfo

<

d:/db/studentinfo_class_student.sqlEnter

password:

******13.3

數(shù)據(jù)的恢復(fù)2．使用mysql命令還原單個(gè)數(shù)據(jù)庫或者多個(gè)數(shù)據(jù)庫mysql

-u

username

-p

<

filename.sql【例13-6】使用mysql命令還原studentinfo數(shù)據(jù)庫，備份文件是例13-3中使用mysqldump命令備份的腳本文件studentinfo2.sql，存儲(chǔ)在D:/db文件夾中。C:\Users\Administrator>mysql

-u

root

-pEnter

password:

******mysql>

DROP

DATABASE

studentinfo;mysql>

QUIT13.3

數(shù)據(jù)的恢復(fù)在“命令提示符”窗口中，使用mysql命令恢復(fù)studentinfo數(shù)據(jù)庫，命令如下：C:\Users\Administrator>mysql

-u

root

-p

<

d:/db/studentinfo2.sqlEnter

password:

******再次登錄MySQL，登錄命令如下：

C:\Users\Administrator>mysql-u

root-p Enter

password:******查看數(shù)據(jù)庫，SQL語句如下：mysql>

SHOW

DATABASES;打開studentinfo數(shù)據(jù)庫，查看該數(shù)據(jù)庫中的表，SQL語句如下：mysql>

USE

studentinfo;mysql>

SHOW

TABLES;查詢student表中的記錄，SQL語句如下：mysql>

SELECT

*

FROM

student;13.3

數(shù)據(jù)的恢復(fù)11.3.2使用source語句恢復(fù)數(shù)據(jù)SOUCE

filename.sql1.恢復(fù)指定數(shù)據(jù)庫中的表【例13-7】在studentinfo數(shù)據(jù)庫中，刪除student表中的所有記錄。用source語句恢復(fù)該數(shù)據(jù)庫中student表中被刪除的記錄。mysqldump命令，備份腳本文件存儲(chǔ)在d:/db/studentinfo_student.sql。 C:\Users\Administrator>mysqldump-u

root-p

studentinfo

student>

d:/db/studentinfo_student.sqlEnter

password:

******在“命令提示符”窗口中登錄到MySQL客戶端，登錄命令如下：C:\Users\Administrator>mysql

-u

root

-pEnter

password:

******13.3

數(shù)據(jù)的恢復(fù)打開studentinfo數(shù)據(jù)庫，查詢student表中的記錄，SQL語句如下：mysql>

USE

studentinfo;Database

changedmysql>

SELECT

*

FROM

student;模擬數(shù)據(jù)丟失，刪除student表中的所有記錄，SQL語句如下：mysql>

DELETE

FROM

student;Query

OK,

11

rows

affected

(0.01

sec)mysql>

SELECT

*

FROM

student;Empty

set

(0.00

sec)或者刪掉student表，SQL語句如下：mysql>

DROP

TABLE

student;Query

OK,

0

rows

affected

(0.03

sec)mysql>

SHOW

TABLES;13.3

數(shù)據(jù)的恢復(fù)使用SOURCE語句，把保存在d:/db/studentinfo_student.sql路徑下的student表的備份文件恢復(fù)到studentinfo數(shù)據(jù)庫中。SQL語句如下：mysql>

SOURCE

d:/db/studentinfo_student.sql;將顯示多行下面的提示：Query

OK,

0

rows

affected

(0.00

sec)…Query

OK,

0

rows

affected

(0.00

sec)Query

OK,

11

rows

affected

(0.01

sec)Records:

11

Duplicates:

0

Warnings:

0…Query

OK,

0

rows

affected

(0.00

sec)查詢student表中的記錄，SQL語句如下：mysql>

SELECT

*

FROM

student;13.3

數(shù)據(jù)的恢復(fù)2.恢復(fù)指定數(shù)據(jù)庫中的所有表【例13-8】用source語句將例13-1中生成的備份文件studentinfo.sql恢復(fù)到studentinfo數(shù)據(jù)庫中。操作步驟如下。在“命令提示符”窗口中登錄到MySQL客戶端，登錄命令如下：C:\Users\Administrator>mysql

-u

root

-pEnter

password:

******模擬數(shù)據(jù)丟失，刪除studentinfo數(shù)據(jù)庫，SQL語句如下：mysql>

DROP

DATABASE

studentinfo;Query

OK,

5

rows

affected

(0.07

sec)13.3

數(shù)據(jù)的恢復(fù)由于例13-1中生成的備份文件studentinfo.sql中不含創(chuàng)建studentinfo數(shù)據(jù)庫的語句，所以要提前創(chuàng)建該數(shù)據(jù)庫。然后打開使用該數(shù)據(jù)庫。SQL語句如下：mysql>

CREATE

DATABASE

studentinfo;Query

OK,

1

row

affected

(0.01

sec)mysql>

USE

studentinfo;Database

changed使用SOURCE語句，把備份腳本恢復(fù)到MySQL中。SQL語句如下：mysql>

SOURCE

d:/db/studentinfo.sql;查看恢復(fù)后的數(shù)據(jù)庫中的表，例如查詢student表中的記錄，SQL語句如下：mysql>

USE

studentinfo;Database

changedmysql>

SELECT

*

FROM

student;13.3

數(shù)據(jù)的恢復(fù)13.4.1使用Navicat

for

MySQL菜單方式備份與恢復(fù)數(shù)據(jù)1.備份數(shù)據(jù)庫【例13-9】備份studentinfo數(shù)據(jù)庫。13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法2.恢復(fù)數(shù)據(jù)庫【例13-10】恢復(fù)studentinfo數(shù)據(jù)庫。13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法3.提取SQL可以把所有備份的內(nèi)容導(dǎo)出為一個(gè)腳本文件.sql，以后使用這個(gè)腳本文件還原數(shù)據(jù)庫。【例13-11】從備份文件中提取SQL腳本文件。13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法4.轉(zhuǎn)儲(chǔ)SQL文件【例13-12】生成studentinfo數(shù)據(jù)庫的備份腳本文件。13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法5.運(yùn)行SQL文件【例13-13】從腳本文件恢復(fù)studentinfo數(shù)據(jù)庫。13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4.2通過復(fù)制數(shù)據(jù)庫目錄備份數(shù)據(jù)庫1.備份數(shù)據(jù)文件SHOW

GLOBAL

VARIABLES

LIKE

"%datadir%";13.4

其他備份與恢復(fù)數(shù)據(jù)的方法2.恢復(fù)數(shù)據(jù)文件停掉MySQL服務(wù)，將備份的文件復(fù)制到原來的data目錄中（路徑和上面的一樣在ProgramData文件夾中）。復(fù)制好后，啟動(dòng)MySQL服務(wù)，用數(shù)據(jù)庫連接工具連接數(shù)據(jù)庫即可看到導(dǎo)進(jìn)來的數(shù)據(jù)庫。13.5

表記錄的導(dǎo)出與導(dǎo)入13.5.1使用SELECT...INTO

OUTFILE語句導(dǎo)出文本文件SELECT

{*

|

column1[,

column2...]}

FROM

tb_name

[WHERE

conditions]

INTOOUTFILE

"filename"

[options];[FIELDS[TERMIATED

BY

"string"][[OPTIONALLY]

ENCLOSED

BY

"CHAR"][ESCAPED

BY

"CHAR"]][LINES[STARTING

BY

"string"][TERMINATED

BY

"string"]]13.4

其他備份與恢復(fù)數(shù)據(jù)的方法【例13-14】把studentinfo數(shù)據(jù)庫中的student表中的所有記錄導(dǎo)出到在服務(wù)器上的默認(rèn)文件夾C:/ProgramData/MySQL/MySQL

Server

8.0/Uploads/st2022-1.txt。USE

studentinfo;SELECT

*

FROM

student

INTO

OUTFILE

"C:/ProgramData/MySQL/MySQL

Server8.0/Uploads/st2022-1.txt";13.4

其他備份與恢復(fù)數(shù)據(jù)的方法【例13-15】在studentinfo數(shù)據(jù)庫中，導(dǎo)出student表中的所有女生記錄，保存到在服務(wù)器上的默認(rèn)文件夾C:/ProgramData/MySQL/MySQL

Server8.0/Uploads/st2022-3.txt。其中，列值之間用“,”分隔，字符型數(shù)據(jù)用雙引號(hào)括起來，每條記錄以“<”開頭，以“>\r\n”結(jié)尾。SQL語句如下：SELECT

StudentID,StudentName,Sex,Birthday,Address,100

FROM

studentWHERE

Sex="女"ORDER

BY

StudentIDINTO

OUTFILE

"C:/ProgramData/MySQL/MySQL

Server

8.0/Uploads/st2022-3.txt"CHARACTER

SET

utf8mb4FIELDS

TERMINATED

BY","OPTIONALLY

ENCLOSED

BY"""#"""單引號(hào)中是1個(gè)雙引號(hào)LINES

STARTING

BY

"<"

TERMINATED

BY

">\r\n";13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.5.2使用LOAD

DATA

INFILE語句導(dǎo)入文本文件MySQL中，可以使用LOAD

DATA

INFILE語句將文本文件中的記錄導(dǎo)入到MySQL數(shù)據(jù)庫中，其基本語法格式為：LOAD

DATA

[{LOW_PRIORITY

|

CONCURRENT}]

INFILE

"filename"[{REPLACE

|

IGNORE}]

INTO

TABLE

tb_name[options]

[IGNORE

n

LINES]

[({column

|

UserVariables}],

…)]

[SETcolumn=expression,

….)];13.4

其他備份與恢復(fù)數(shù)據(jù)的方法【例13-16】把服務(wù)器上C:/ProgramData/MySQL/MySQL

Server8.0/Uploads/st2022-1.txt文件導(dǎo)入到studentinfo數(shù)據(jù)庫中的student表中。在Navicat

for

MySQL中打開studentinfo數(shù)據(jù)庫，在查詢窗格中運(yùn)行下面SQL語句：LOAD

DATA

INFILE

"C:/ProgramData/MySQL/MySQL

Server

8.0/Uploads/st2022-1.txt"REPLACE

INTO

TABLE

student;為避免主鍵沖突，要用REPLACE選項(xiàng)直接將記錄進(jìn)行替換來恢復(fù)數(shù)據(jù)。打開student表，可以看到記錄已經(jīng)恢復(fù)。13.4

其他備份與恢復(fù)數(shù)據(jù)的方法【例13-17】在studentinfo數(shù)據(jù)庫中創(chuàng)建一張新表student3，使用LOAD

DATAINFILE語句將C:/ProgramData/MySQL/MySQL

Server

8.0/Uploads/st2022-3.txt中的記錄導(dǎo)入到student3表中。1）在Navicat

for

MySQL的查詢窗格中首先創(chuàng)建student3表，表的結(jié)構(gòu)要與待恢復(fù)的st2022-3.txt記錄的結(jié)構(gòu)相同。SQL語句如下。CREATE

TABLE

studentinfo.student3

(StudentID

CHAR(12)

PRIMARY

KEY,StudentName

VARCHAR(20)

NOT

NULL,Sex

CHAR(2)

NOT

NULL,Birthday

DATE

NULL

DEFAULT

NULL,Address

VARCHAR(30),Number

INT)

ENGINE=InnoDB

CHARACTER

SET=

utf8mb4;13.4

其他備份與恢復(fù)數(shù)據(jù)的方法從st2022-3.txt文本文件恢復(fù)記錄，SQL語句如下。LOAD

DATA

INFILE

"C:/ProgramData/MySQL/MySQL

Server

8.0/Uploads/st2022-3.txt"INTO

TABLE

studentinfo.student3CHARACTER

SET

utf8mb4FIELDS

TERMINATED

BY","OPTIONALLY

ENCLOSED

BY"""#"""單引號(hào)包括1個(gè)雙引號(hào)LINES

STARTING

BY

"<"

TERMINATED

BY

">\r\n";語句成功運(yùn)行后，查詢student3表中的數(shù)據(jù)，SQL語句如下。SELECT

*

FROM

studentinfo.student3;13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.5.3設(shè)置導(dǎo)出文本文件的路徑使用SELECT...INTO

OUTFILE語句導(dǎo)出的文本文件默認(rèn)保存在服務(wù)器的C:/ProgramData/MySQL/MySQL

Server

8.0/Uploads文件夾中。如果要將導(dǎo)出的文件保存到其他文件夾，首先要設(shè)置該文件夾具有保存該文件的權(quán)限。1）本地保存數(shù)據(jù)由secure-file-priv參數(shù)控制，查看該值使用下面語句：SHOW

VARIABLES

LIKE

"%secure%";13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法以管理員身份用記事本打開my.ini文件，查找到secure-file-privsecure-file-priv="d:/db"13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.4

其他備份與恢復(fù)數(shù)據(jù)的方法【例13-18】把studentinfo數(shù)據(jù)庫中的class表中的所有記錄導(dǎo)出到設(shè)置的文件夾D:/db中，文件名是classt2.txt。1）導(dǎo)出文本文件的SQL語句如下：SELECT

*

FROM

class

INTO

OUTFILE

"D:/db/class2.txt";13.4

其他備份與恢復(fù)數(shù)據(jù)的方法刪除class表中的所有記錄，SQL語句如下：DELETE

FROM

class;把D:/db/class2.txt文件導(dǎo)入到class表中，在查詢窗格中運(yùn)行下面SQL語句：LOAD

DATA

INFILE

"D:/db/class2.txt"

REPLACE

INTO

TABLE

class;打開class表，可以看到記錄已經(jīng)恢復(fù)。13.4

其他備份與恢復(fù)數(shù)據(jù)的方法13.5.4用mysql命令導(dǎo)出文本文件mysql命令除了用來登錄MySQL服務(wù)器和還原備份文件外，也可以導(dǎo)出文本文件。使用mysql命令導(dǎo)出文本文件的基本語法形式為：mysql

-u

usermame

-p[password]

{-e

|

--execute=}"select

statement"db_name

>

filename.txt13.4

其他備份與恢復(fù)數(shù)據(jù)的方法【例13-19】使用mysql命令將studentinfo數(shù)據(jù)庫中的student表中的女生記錄導(dǎo)出到文本文件。在“命令提示符”窗口執(zhí)行下面命令：C:\Users\Administrator>mysql-u

root-p-e

"SELECT

*

FROM

student

WHERESex="女";"studentinfo>d:/db/st2022-5.txt或C:\Users\Administrator>mysql-u

root-p--execute="SELECT

*

FROM

studentWHERE

Sex="女";"studentinfo>d:/db/st2022-6.txtEnter

password:

******13.4

其他備份與恢復(fù)數(shù)據(jù)的方法第14章 日志管理《MySQL數(shù)據(jù)庫應(yīng)用教程》目錄第14章 日志文件MySQL日志簡介錯(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日志簡介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ò)誤日志文件刪除，或者改為其他文件名?？梢允褂谩拔募Y源管理器”刪除錯(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)罩疚募穆窂剑湔Z法格式為：

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)行下面