MySQL中怎么備份和恢復數據表

今天就跟大家聊聊有關MySQL中怎么備份和恢復數據表，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

你可以使用SELECT INTO OUTFILE語句備份數據，并用LOAD DATA INFILE語句恢復數據。這種方法只能導出數據的內容，不包括表的結構，如果表的結構文件損壞，你必須要先恢復原來的表的結構。
語法：
SELECT * INTO {OUTFILE | DUMPFILE} 'file_name' FROM tbl_name
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
SELECT ... INTO OUTFILE 'file_name'格式的SELECT語句將選擇的行寫入一個文件。文件在服務器主機上被創建，并且不能是已經存在的(不管別的，這可阻止數據庫表和 文件例如“/etc/passwd”被破壞)。SELECT ... INTO OUTFILE是LOAD DATA INFILE逆操作。
LOAD DATA INFILE語句從一個文本文件中以很高的速度讀入一個表中。如果指定LOCAL關鍵詞，從客戶主機讀文件。如果LOCAL沒指定，文件必須位于服務器上。(LOCAL在MySQL3.22.6或以后版本中可用。)
為 了安全原因，當讀取位于服務器上的文本文件時，文件必須處于數據庫目錄或可被所有人讀取。另外，為了對服務器上文件使用LOAD DATA INFILE，在服務器主機上你必須有file的權限。使用這種SELECT INTO OUTFILE語句，在服務器主機上你必須有FILE權限。
為 了避免重復記錄，在表中你需要一個PRIMARY KEY或UNIQUE索引。當在唯一索引值上一個新記錄與一個老記錄重復時，REPLACE關鍵詞使得老記錄用一個新記錄替代。如果你指定IGNORE， 跳過有唯一索引的現有行的重復行的輸入。如果你不指定任何一個選項，當找到重復索引值時，出現一個錯誤，并且文本文件的余下部分被忽略時。
如果你指定關鍵詞LOW_PRIORITY，LOAD DATA語句的執行被推遲到沒有其他客戶讀取表后。
使 用LOCAL將比讓服務器直接存取文件慢些，因為文件的內容必須從客戶主機傳送到服務器主機。在另一方面，你不需要file權限裝載本地文件。如果你使用 LOCAL關鍵詞從一個本地文件裝載數據，服務器沒有辦法在操作的當中停止文件的傳輸，因此缺省的行為好像IGNORE被指定一樣。
當在服務器主機上尋找文件時，服務器使用下列規則：
◆如果給出一個絕對路徑名，服務器使用該路徑名。
◆如果給出一個有一個或多個前置部件的相對路徑名，服務器相對服務器的數據目錄搜索文件。
◆如果給出一個沒有前置部件的一個文件名，服務器在當前數據庫的數據庫目錄尋找文件。
假定表tbl_name具有一個PRIMARY KEY或UNIQUE索引，備份一個數據表的過程如下：
1、鎖定數據表，避免在備份過程中，表被更新
mysql>LOCK TABLES READ tbl_name;
2、導出數據
mysql>SELECT * INTO OUTFILE ‘tbl_name.bak’ FROM tbl_name;
3、解鎖表
mysql>UNLOCK TABLES;
相應的恢復備份的數據的過程如下：
1、為表增加一個寫鎖定：
mysql>LOCK TABLES tbl_name WRITE;
2、恢復數據
mysql>LOAD DATA INFILE ‘tbl_name.bak’
 ->REPLACE INTO TABLE tbl_name;
如果，你指定一個LOW_PRIORITY關鍵字，就不必如上要對表鎖定，因為數據的導入將被推遲到沒有客戶讀表為止：
mysql>LOAD DATA  LOW_PRIORITY  INFILE ‘tbl_name’
 ->REPLACE INTO TABLE tbl_name;
3、解鎖表

使用mysqlimport恢復數據
如 果你僅僅恢復數據，那么完全沒有必要在客戶機中執行SQL語句，因為你可以簡單的使用mysqlimport程序，它完全是與LOAD DATA 語句對應的，由發送一個LOAD DATA INFILE命令到服務器來運作。執行命令mysqlimport --help，仔細查看輸出，你可以從這里得到幫助。
shell> mysqlimport [options] db_name filename ...
對于在命令行上命名的每個文本文件，mysqlimport剝去文件名的擴展名并且使用它決定哪個表導入文件的內容。例如，名為“patient.txt”、
“patient.text”和“patient”將全部被導入名為patient的一個表中。

常用的選項為：
-C, --compress 如果客戶和服務器均支持壓縮，壓縮兩者之間的所有信息。
-d, --delete 在導入文本文件前倒空表格。
l, --lock-tables 在處理任何文本文件前為寫入所定所有的表。這保證所有的表在服務器上被同步。
--low-priority，--local，--replace，--ignore分別對應LOAD DATA語句的LOW_PRIORITY，LOCAL，REPLACE，IGNORE關鍵字。
例如恢復數據庫db1中表tbl1的數據，保存數據的文件為tbl1.bak，假定你在服務器主機上：
shell>mysqlimport --lock-tables --replace db1 tbl1.bak
這樣在恢復數據之前現對表鎖定，也可以利用--low-priority選項：
shell>mysqlimport --low-priority --replace db1 tbl1.bak
如果你為遠程的服務器恢復數據，還可以這樣：
shell>mysqlimport -C --lock-tables --replace db1 tbl1.bak
當然，解壓縮要消耗CPU時間。
象其它客戶機一樣，你可能需要提供-u，-p選項以通過身分驗證，也可以在選項文件my.cnf中存儲這些參數，具體方法和其它客戶機一樣，這里就不詳述了。
mysql->UNLOCAK TABLES;
用mysqldump備份數據
同mysqlimport一樣，也存在一個工具mysqldump備份數據，但是它比SQL語句多做的工作是可以在導出的文件中包括SQL語句，因此可以備份數據庫表的結構，而且可以備份一個數據庫，甚至整個數據庫系統。
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
如果你不給定任何表，整個數據庫將被傾倒。
通過執行mysqldump --help，你能得到你mysqldump的版本支持的選項表。

1、備份數據庫的方法
例如，假定你在服務器主機上備份數據庫db_name
shell> mydqldump db_name
當然，由于mysqldump缺省時把輸出定位到標準輸出，你需要重定向標準輸出。
例如，把數據庫備份到bd_name.bak中：
shell> mydqldump db_name>db_name.bak
你可以備份多個數據庫，注意這種方法將不能指定數據表：
shell> mydqldump --databases db1 db1>db.bak
你也可以備份整個數據庫系統的拷貝，不過對于一個龐大的系統，這樣做沒有什么實際的價值：
shell> mydqldump --all-databases>db.bak
雖然用mysqldump導出表的結構很有用，但是恢復大量數據時，眾多SQL語句使恢復的效率降低。你可以通過使用--tab選項，分開數據和創建表的SQL語句。
-T，--tab= 在選項指定的目錄里，創建用制表符（tab）分隔列值的數據文件和包含創建表結構的SQL語句的文件，分別用擴展名.txt和.sql表示。該選項不能與 --databases或--all-databases同時使用，并且mysqldump必須運行在服務器主機上。
例如，假設數據庫db包括表tbl1，tbl2，你準備備份它們到/var/mysqldb
shell>mysqldump --tab=/var/mysqldb/  db
其效果是在目錄/var/mysqldb中生成4個文件，分別是tbl1.txt、tbl1.sql、tbl2.txt和tbl2.sql。
2、mysqldump實用程序時的身份驗證的問題
同其他客戶機一樣，你也必須提供一個MySQL數據庫帳號用來導出數據庫，如果你不是使用匿名用戶的話，可能需要手工提供參數或者使用選項文件：
如果這樣：
shell>mysql -u root –pmypass db_name>db_name.sql
或者這樣在選項文件中提供參數：
[mysqldump]
user=root
password=mypass
然后執行
shell>mysqldump db_name>db_name.sql
那 么一切順利，不會有任何問題，但要注意命令歷史會泄漏密碼，或者不能讓任何除你之外的用戶能夠訪問選項文件，由于數據庫服務器也需要這個選項文件時，選項 文件只能被啟動服務器的用戶（如，mysql）擁有和訪問，以免泄密。在Unix下你還有一個解決辦法，可以在自己的用戶目錄中提供個人選項文件 （~/.my.cnf）,例如，/home/some_user/.my.cnf，然后把上面的內容加入文件中，注意防止泄密。

看完上述內容，你們對MySQL中怎么備份和恢復數據表有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。