MySQL中怎么備份和恢復(fù)數(shù)據(jù)表

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

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

使用mysqlimport恢復(fù)數(shù)據(jù)
如 果你僅僅恢復(fù)數(shù)據(jù)，那么完全沒(méi)有必要在客戶機(jī)中執(zhí)行SQL語(yǔ)句，因?yàn)槟憧梢院?jiǎn)單的使用mysqlimport程序，它完全是與LOAD DATA 語(yǔ)句對(duì)應(yīng)的，由發(fā)送一個(gè)LOAD DATA INFILE命令到服務(wù)器來(lái)運(yùn)作。執(zhí)行命令mysqlimport --help，仔細(xì)查看輸出，你可以從這里得到幫助。
shell> mysqlimport [options] db_name filename ...
對(duì)于在命令行上命名的每個(gè)文本文件，mysqlimport剝?nèi)ノ募臄U(kuò)展名并且使用它決定哪個(gè)表導(dǎo)入文件的內(nèi)容。例如，名為“patient.txt”、
“patient.text”和“patient”將全部被導(dǎo)入名為patient的一個(gè)表中。

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

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

看完上述內(nèi)容，你們對(duì)MySQL中怎么備份和恢復(fù)數(shù)據(jù)表有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。