本篇內(nèi)容介紹了“MySQL怎么用SQL語(yǔ)句備份和恢復(fù)表數(shù)據(jù)”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

我們提供的服務(wù)有：成都網(wǎng)站制作、網(wǎng)站建設(shè)、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、祁縣ssl等。為上千企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的祁縣網(wǎng)站制作公司

 
1. 使用SQL語(yǔ)句備份和恢復(fù)表數(shù)據(jù)（提一下，不細(xì)講）
 
用戶可以使用SELECT INTO…OUTFILE語(yǔ)句把表數(shù)據(jù)導(dǎo)出到一個(gè)文本文件中，并用LOAD DATA …INFILE語(yǔ)句恢復(fù)數(shù)據(jù)。但是這種方法只能導(dǎo)出或?qū)霐?shù)據(jù)的內(nèi)容，不包括表的結(jié)構(gòu)，如果表的結(jié)構(gòu)文件損壞，則必須先恢復(fù)原來的表的結(jié)構(gòu)。
 
SELECT INTO…OUTFILE格式：
 
SELECT *  INTO OUTFILE 'file_name' export_options
 
          |DUMPFILE 'file_name'
 
其中，export_options為：
 
[FIELDS
 
       [terminated BY 'string']
 
       [[optionally] ENCLOSED BY 'char']
 
       [escaped BY 'char' ]
 
]
 
[LINES TERMINATED BY 'string' ]
 
說明：
 
這個(gè)語(yǔ)句的作用是將表中SELECT語(yǔ)句選中的行寫入到一個(gè)文件中，file_name是文件的名稱。文件默認(rèn)在服務(wù)器主機(jī)上創(chuàng)建，并且文件名不能是已經(jīng)存在的（這可能將原文件覆蓋）。如果要將該文件寫入到一個(gè)特定的位置，則要在文件名前加上具體的路徑。在文件中，數(shù)據(jù)行以一定的形式存放，空值用“\N”表示。
 
使用OUTFILE時(shí)，可以在export_options中加入以下兩個(gè)自選的子句，它們的作用是決定數(shù)據(jù)行在文件中存放的格式：
 
●  fields子句：在FIELDS子句中有三個(gè)亞子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS子句，則這三個(gè)亞子句中至少要指定一個(gè)。
 
（1）TERMINATED BY用來指定字段值之間的符號(hào)，例如，“TERMINATED BY ','”指定了逗號(hào)作為兩個(gè)字段值之間的標(biāo)志。
 
（2）ENCLOSED BY子句用來指定包裹文件中字符值的符號(hào)，例如，“ENCLOSED BY ' " '”表示文件中字符值放在雙引號(hào)之間，若加上關(guān)鍵字OPTIONALLY表示所有的值都放在雙引號(hào)之間。
 
（3）ESCAPED BY子句用來指定轉(zhuǎn)義字符，例如，“ESCAPED BY '*'”將“*”指定為轉(zhuǎn)義字符，取代“\”，如空格將表示為“*N”。
 
●   LINES子句：在LINES子句中使用TERMINATED BY指定一行結(jié)束的標(biāo)志，如“LINES TERMINATED BY '?'”表示一行以“?”作為結(jié)束標(biāo)志。

如果FIELDS和LINES子句都不指定，則默認(rèn)聲明以下子句：
 
FIELDS TERMINATED BY '\t'ENCLOSED BY '' ESCAPED BY '\\'
 
LINES TERMINATED BY '\n'
 
如果使用DUMPFILE而不是使用OUTFILE，導(dǎo)出的文件里所有的行都彼此緊挨著放置，值和行之間沒有任何標(biāo)記，成了一個(gè)長(zhǎng)長(zhǎng)的值。
 
5.LOAD DATA …INFILE語(yǔ)句是SELECT INTO…OUTFILE語(yǔ)句的補(bǔ)語(yǔ)，該語(yǔ)句可以將一個(gè)文件中的數(shù)據(jù)導(dǎo)入到中。
 
LOAD DATA …INFILE格式：
 
LOAD DATA [LOW_PRIORITY | concurrent][LOCAL] INFILE 'file_name.txt'
 
   [replace | ignore]
 
   INTO TABLE tbl_name
 
   [FIELDS
 
       [TERMINATED BY 'string']
 
       [[OPTIONALLY] ENCLOSED BY 'char']
 
       [ESCAPED BY 'char' ]
 
   ]
 
   [LINES
 
       [STARTING BY 'string']
 
       [TERMINATED BY 'string']
 
   ]
 
   [IGNORE number LINES]
 
   [(col_name_or_user_var,...)]
 
   [SET col_name = expr,...)]
 
說明：  
 
●   LOW_PRIORITY | CONCURRENT：若指定LOW_PRIORITY，則延遲語(yǔ)句的執(zhí)行。若指定CONCURRENT，則當(dāng)LOAD DATA正在執(zhí)行的時(shí)候，其他線程可以同時(shí)使用該表的數(shù)據(jù)。
 
●   LOCAL：若指定了LOCAL，則文件會(huì)被客戶主機(jī)上的客戶端讀取，并被發(fā)送到服務(wù)器。文件會(huì)被給予一個(gè)完整的路徑名稱，以指定確切的位置。如果給定的是一個(gè)相對(duì)的路徑名稱，則此名稱會(huì)被理解為相對(duì)于啟動(dòng)客戶端時(shí)所在的目錄。若未指定LOCAL，則文件必須位于服務(wù)器主機(jī)上，并且被服務(wù)器直接讀取。與讓服務(wù)器直接讀取文件相比，使用LOCAL速度略慢，這是因?yàn)槲募膬?nèi)容必須通過客戶端發(fā)送到服務(wù)器上。
 
●  file_name：待載入的文件名，文件中保存了待存入數(shù)據(jù)庫(kù)的數(shù)據(jù)行。輸入文件可以手動(dòng)創(chuàng)建，也可以使用其他的程序創(chuàng)建。可以指定文件的絕對(duì)路徑，如“D:/file/myfile.txt”，則服務(wù)器根據(jù)該路徑搜索文件。若不指定路徑，如“myfile.txt”，則服務(wù)器在默認(rèn)數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)目錄中讀取。若文件為“./myfile.txt”，則服務(wù)器直接在數(shù)據(jù)目錄下讀取，即MySQL的data目錄。出于安全原因，當(dāng)讀取位于服務(wù)器中的文本文件時(shí)，文件必須位于數(shù)據(jù)庫(kù)目錄中，或者是全體可讀的。
 
注意：這里使用正斜杠指定Windows路徑名稱，而不是使用反斜杠。
 
●  tb_name：需要導(dǎo)入數(shù)據(jù)的表名，該表在數(shù)據(jù)庫(kù)中必須存在，表結(jié)構(gòu)必須與導(dǎo)入文件的數(shù)據(jù)行一致。
 
●  REPLACE | IGNORE：如果指定了REPLACE，則當(dāng)文件中出現(xiàn)與原有行相同的唯一關(guān)鍵字值時(shí)，輸入行會(huì)替換原有行。如果指定了IGNORE，則把與原有行有相同的唯一關(guān)鍵字值的輸入行跳過。
 
●   FIELDS子句：此處的FIELDS子句和SELECT..INTO OUTFILE語(yǔ)句中類似。用于判斷字段之間和數(shù)據(jù)行之間的符號(hào)。
 
●  LINES子句：TERMINATED BY亞子句用來指定一行結(jié)束的標(biāo)志。STARTING BY亞子句則指定一個(gè)前綴，導(dǎo)入數(shù)據(jù)行時(shí)，忽略行中的該前綴和前綴之前的內(nèi)容。如果某行不包括該前綴，則整個(gè)行被跳過。
 
●  IGNORE number LINES：這個(gè)選項(xiàng)可以用于忽略文件的前幾行。例如，可以使用IGNORE 1 LINES來跳過第一行。
  www.2cto.com  
●  col_name_or_user_var：如果需要載入一個(gè)表的部分列或文件中字段值順序與表中列的順序不同，就必須指定一個(gè)列清單，其中可以包含列名或用戶變量。
 
SET子句：SET子句可以在導(dǎo)入數(shù)據(jù)時(shí)修改表中列的值。
 
例： 備份XSCJ數(shù)據(jù)庫(kù)中的KC表中數(shù)據(jù)到D盤FILE目錄中，要求字段值如果是字符就用雙引號(hào)標(biāo)注，字段值之間用逗號(hào)隔開，每行以“？”為結(jié)束標(biāo)志。最后將備份后的數(shù)據(jù)導(dǎo)入到一個(gè)和KC表結(jié)構(gòu)一樣的空表COURSE表中。
 
首先導(dǎo)出數(shù)據(jù)：
 
USE XSCJ;
 
SELECT * FROM KC
 
   INTO OUTFILE'D:/FILE/myfile1.txt'
 
     FIELDS  TERMINATED BY ' , '
 
        OPTIONALLYENCLOSED BY ' " '
 
     LINES TERMINATED BY '? ';
 
文件備份完后可以將文件中的數(shù)據(jù)導(dǎo)入到COURSE表中，使用以下命令：
 
LOAD DATA INFILE 'D:/FILE/myfile1.txt'
 
   INTO TABLE COURSE
 
     FIELDS  TERMINATED BY ' , '
 
          OPTIONALLYENCLOSED BY ' " '
 
     LINES TERMINATED BY '? ';
  www.2cto.com  
注意：在導(dǎo)入數(shù)據(jù)時(shí)，必須根據(jù)文件中數(shù)據(jù)行的格式指定判斷的符號(hào)。例如，在myfile1.txt文件中字段值是以逗號(hào)隔開的，導(dǎo)入數(shù)據(jù)時(shí)一定要使用“TERMINATED BY ','”子句指定逗號(hào)為字段值之間的分隔符，與SELECT…INTOOUTFILE語(yǔ)句相對(duì)應(yīng)。
 
因?yàn)镸ySQL表保存為文件形式，所以備份很容易。但是在多個(gè)用戶使用MySQL的情況下，為了得到一個(gè)一致的備份，在相關(guān)的表上需要做一個(gè)讀鎖定，防止在備份過程中表被更新；當(dāng)恢復(fù)數(shù)據(jù)時(shí)，需要一個(gè)寫鎖定，以避免沖突。在備份或恢復(fù)完以后要對(duì)表進(jìn)行解鎖。
 
2.   啟用日志
 
二進(jìn)制日志可以在啟動(dòng)服務(wù)器的時(shí)候啟用，這需要修改C:\Program Files\MySQL文件夾中的my.ini選項(xiàng)文件。打開該文件，找到[d]所在行，在該行后面加上以下格式的一行：
 
 log-bin[=filename]
 
說明：加入該選項(xiàng)后，服務(wù)器啟動(dòng)時(shí)就會(huì)加載該選項(xiàng)，從而啟用二進(jìn)制日志。如果filename包含擴(kuò)展名，則擴(kuò)展名被忽略。MySQL服務(wù)器為每個(gè)二進(jìn)制日志名后面添加一個(gè)數(shù)字?jǐn)U展名。每次啟動(dòng)服務(wù)器或刷新日志時(shí)該數(shù)字增加1。如果filename未給出，則默認(rèn)為主機(jī)名。假設(shè)這里filename取名為bin_log。若不指定目錄，則在MySQL的data目錄下自動(dòng)創(chuàng)建二進(jìn)制日志文件。由于下面使用mysqlbinlog工具處理日志時(shí)，日志必須處于bin目錄下，所以日志的路徑就指定為bin目錄，添加的行改為以下一行：
 
log-bin=C:/Program Files/MySQL/MySQLServer 5.1/bin/bin_log
 
保存，重啟服務(wù)器。重啟服務(wù)器的方法可以是：
 
先關(guān)閉服務(wù)器，
 
net stop mysql 
 
再啟動(dòng)服務(wù)器：
 
net start mysql
 
此時(shí)，MySQL安裝目錄的bin目錄下多出兩個(gè)文件：bin_log.000001和bin_log.index。bin_log.000001就是二進(jìn)制日志文件，以二進(jìn)制形式存儲(chǔ)，用于保存數(shù)據(jù)庫(kù)更新信息。當(dāng)這個(gè)日志文件大小達(dá)到最大，MySQL還會(huì)自動(dòng)創(chuàng)建新的二進(jìn)制文件。bin_log.index是服務(wù)器自動(dòng)創(chuàng)建的二進(jìn)制日志索引文件，包含所有使用的二進(jìn)制日志文件的文件名。
 
使用mysqlbinlog實(shí)用工具可以檢查二進(jìn)制日志文件。命令格式為：  mysqlbinlog[options] log-files...  www.2cto.com  
 
說明：log-files是二進(jìn)制日志的文件名。
 
例如，運(yùn)行以下命令可以查看bin_log.000001的內(nèi)容：
 
mysqlbinlog bin_log.000001
 
由于二進(jìn)制數(shù)據(jù)可能非常龐大，無(wú)法在屏幕上延伸，可以保存到文本文件中：
 
mysqlbinlogbin_log.000001>D:/FILE/lbin-log000001.txt
 
使用日志恢復(fù)數(shù)據(jù)的命令格式如下：
 
mysqlbinlog [options] log-files… |mysql [options]
 
例: 假設(shè)用戶在星期一下午1點(diǎn)使用mysqldump工具進(jìn)行數(shù)據(jù)庫(kù)XSCJ的完全備份，備份文件為file.sql。從星期一下午1點(diǎn)開始用戶啟用日志，bin_log.000001文件保存了從星期一下午1點(diǎn)到星期二下午1點(diǎn)的所有更改，在星期二下午1點(diǎn)運(yùn)行一條SQL語(yǔ)句：
 
Flush logs;
 
此時(shí)創(chuàng)建了bin_log.000002文件，在星期三下午1點(diǎn)時(shí)數(shù)據(jù)庫(kù)崩潰?，F(xiàn)要將數(shù)據(jù)庫(kù)恢復(fù)到星期三下午1點(diǎn)時(shí)的狀態(tài)。首先將數(shù)據(jù)庫(kù)恢復(fù)到星期一下午1點(diǎn)時(shí)的狀態(tài)，在DOS窗口輸入以下命令：
 
mysqldump -uroot -p123456 XSCJ 
使用以下命令將數(shù)據(jù)庫(kù)恢復(fù)到星期二下午時(shí)的狀態(tài)：
 
mysqlbinlog bin_log.000001 | mysql-uroot -p123456
 
再使用以下命令即可將數(shù)據(jù)庫(kù)恢復(fù)到星期三下午1點(diǎn)時(shí)的狀態(tài)：
 
mysqlbinlog bin_log.000002 | mysql-uroot -p123456
 
由于日志文件要占用很大的硬盤資源，所以要及時(shí)將沒用的日志文件清除掉。以下這條SQL語(yǔ)句用于清除所有的日志文件：  www.2cto.com  
 
Reset master;
 
如果要?jiǎng)h除部分日志文件，可以使用purge master logs語(yǔ)句。
 
語(yǔ)法格式為：PURGE {MASTER |BINARY} LOGS TO 'log_name'
 
或: PURGE {MASTER | BINARY} LOGS BEFORE 'date'
 
說明：第一個(gè)語(yǔ)句用于刪除特定的日志文件，log_name為文件名。第二個(gè)語(yǔ)句用于刪除時(shí)間date之前的所有日志文件。MASTER和BINARY是同義詞。

“MySQL怎么用SQL語(yǔ)句備份和恢復(fù)表數(shù)據(jù)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！