MySQL怎么用SQL語句備份和恢復表數(shù)據(jù)

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

 
1. 使用SQL語句備份和恢復表數(shù)據(jù)（提一下，不細講）
 
用戶可以使用SELECT INTO…OUTFILE語句把表數(shù)據(jù)導出到一個文本文件中，并用LOAD DATA …INFILE語句恢復數(shù)據(jù)。但是這種方法只能導出或導入數(shù)據(jù)的內(nèi)容，不包括表的結構，如果表的結構文件損壞，則必須先恢復原來的表的結構。
 
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' ]
 
說明：
 
這個語句的作用是將表中SELECT語句選中的行寫入到一個文件中，file_name是文件的名稱。文件默認在服務器主機上創(chuàng)建，并且文件名不能是已經(jīng)存在的（這可能將原文件覆蓋）。如果要將該文件寫入到一個特定的位置，則要在文件名前加上具體的路徑。在文件中，數(shù)據(jù)行以一定的形式存放，空值用“\N”表示。
 
使用OUTFILE時，可以在export_options中加入以下兩個自選的子句，它們的作用是決定數(shù)據(jù)行在文件中存放的格式：
 
●  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 '?'”表示一行以“?”作為結束標志。

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

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