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