mysql備份還原庫命令方法解析（長文）

關於mysqlDatabase Backup與還原的方法，這裡首先講到備份的工具：mysqlhotcopy，使用mysqlhotcopy工具可進行快速備份，然後資料還原，使用MySQL命令還原；最後需要匯出資料庫表。詳細的介紹還得閱讀本文。

1.資料備份

週期性備份資料庫，使得在意外情況發生的時候，盡量的減少損失。

1.使用mysqldump命令備份

mysqldump是MySQL提供的一個Database Backup工具，mysqldump命令執行的時候，將Database Backup成一個文字檔，該檔案中包含了多個CREATE 和INSERT語句，使用這些語句可以重新建立表和插入資料；

【使用mysqldump備份單個資料庫中】

mysqldump -u user -h host -p password dbname>filename.sql

【使用mysqldump備份資料庫中的指定表】

mysqldump -u user -h host -p password dbname[tbname,[tbname…]]>filename.sql

【使用mysqldump備份多個資料庫】

mysqldump -u user -h host -p password --databases[dbname,[dbname…]]>filename.sql

使用--databases參數之後，必須指定至少一個資料庫的名稱，多個資料庫之間使用空格隔開；

【備份系統中所有的資料庫】

mysqldump -u user -h host -p password --all-databases>filename.sql

提示：如果在伺服器上進行備份，並且表均為MyISAM，應考慮使用mysqlhotcopy，因為可以更快的進行備份和恢複；

2.直接複製整個資料庫目錄

因為MySQL表儲存為檔案方式，所以可以直接複製MySQL資料庫的儲存目錄以及檔案進行備份。

這是一種簡單、快速、有效備份方式，要想保持備份的一致性，備份前需要對相關表執行LOCK TABLES 操作，然後對錶執行FLUSH TABLES（確保開始備份前將所有啟用的索引頁寫入硬碟）。這樣當複製資料庫目錄的檔案時，允許其他的使用者繼續查詢表。

這種方法對InnoDB儲存引擎的表不適用。使用這種方法備份資料最好還原到相同版本的伺服器中，不同版本可能不相容；

3.使用mysqlhotcopy工具快速備份

mysqlhotcopy是一個Perl指令碼。

只能運行在資料庫目錄所在的機器上，並且只能備份MyISAM和ARCHIVE類型的表；

2.資料還原

1.使用MySQL命令還原

mysql -u username -p [dbname] < filename.sql

注意：如果filename.sql檔案為mysqldump工具建立的包含建立資料庫語句的檔案，執行的時候不需要指定資料庫名；

如果已經登入到MySQL伺服器，還可以使用source命令匯入SQL檔案。

source filename

提示：執行source命令之前，必須使用use語句選擇資料庫。不然，恢複過程中會出現錯誤；

2.直接複製到資料庫目錄

如果資料庫通過複製資料庫檔案備份，可以直接複本備份的檔案到MySQL資料目錄下實現還原。

通過該方式還原的時候，必須保持備份資料庫和待還原的資料庫伺服器的主要版本號相同。而且這種方式只是對MyISAM引擎的表有效，對於InnoDB引擎的表不可用；

執行還原以前關閉MySQL服務，將備份的檔案或者目錄覆蓋MySQL的data目錄，啟動MySQL服務。

對於Linux/Unix作業系統來講，複製完檔案需要將檔案的使用者或者使用者組更改為mysql啟動並執行使用者和組，通常使用者是mysql，組也是mysql；

3.mysqlhotcopy快速恢複

mysqlhotcopy備份之後的檔案也可以用來恢複資料庫，在MySQL伺服器停止啟動並執行時候，將備份的資料庫檔案複製到MySQL存放的位置（MySQL的data檔案夾），重新啟動MySQL服務即可。

如果以根使用者執行該操作，必須指定資料庫檔案的所有者

chown -R mysql.mysql /var/lib/mysql/dbname

cp -R /usr/backup/test usr/local/mysql/data

執行完該語句，重啟伺服器，MySQL將恢複到備份狀態

提示：如果需要恢複的資料庫已經存在，則在使用DROP語句刪除已經存在的資料庫之後，恢複才可以成功，另外MySQL不同版本之間必須相容；

3.資料庫遷移

資料庫遷移就是把資料從一個系統移動到另一個系統上。資料移轉有以下原因：

1.相同版本的MySQL資料庫之間的遷移

相同版本的MySQL資料庫之間的遷移就是指在主要版本號相同的MySQL資料庫之間進行資料庫移動。

舉例：

將www.abc.com主機上的MySQL資料庫全部遷移到www.bcd.com主機上：

mysqldump -h www.abc.com -u root -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword

說明：

mysqldump匯入的資料直接通過管道符|，傳給mysql命令匯入到主機www.bcd.com資料庫中，dbname為需要遷移的資料庫名稱，如果需要遷移全部的資料庫，可以使用參數 --all-databases

2.不同版本的MySQL資料庫之間的遷移

MySQL伺服器升級的時候，需要先停止服務，然後卸載舊版本，並安裝新版本MySQL，這種更新方法很簡單，如果想保留舊版本中的使用者存取控制資訊，需要備份MySQL中的mysql資料庫，在新版本MySQL安裝完成之後，重新讀入mysql備份檔案中的資訊；

舊版本與新版本的字元集不同時，遷移過程需要對預設字元集進行修改，不然可能無法正常顯示結果；

對於InnoDB引擎的表，一般只能使用mysqldump工具將資料匯出，然後使用mysql命令匯入到目標伺服器上。

從新版本向舊版本遷移資料的時候，需要特別的小心，最好使用mysqldump命令匯出，然後匯入目標資料庫中；

3.不同資料庫之間的遷移

資料庫遷移可以使用一些工具，例如在Windows系統下，可以使用MyODBC實現MySQL和SQL Server之間的遷移。

MySQL官方提供的工具MySQL Migration Toolkit也可以實現在不同資料庫間進行資料移轉；

4.表的匯出和匯入

MySQL資料庫中的資料可以匯出成SQL文字檔、xml檔案或者HTML檔案。

1.使用SELECT…INTO OUTFILE匯出文字檔

MySQL資料庫匯出資料的時候，允許使用包含匯出定義的SELECT語句進行資料匯出操作。該檔案被建立到伺服器主機上，因此必須擁有檔案寫入許可權（FILE許可權），才能使用此文法。

文法格式：

SELECT columnlist FORM table WHERE condition INTO OUTFILE ‘filename’ [OPTIONS]

[OPTIONS]選項：

FIELDS TERMINATED BY ‘value’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

FIELDS ESCAPED BY ‘value’

LINES STARTING BY ‘value’

LINES TERMINATED BY ‘value’

說明：filename不能是一個已經存在的檔案；

OPTIONS部分文法包括FIELDS部分的文法和LINES子句，其可能的取值有：

FIELDS TERMINATED BY ‘value’：

設定欄位之間的分隔字元，可以為單個或者多個字元，預設情況下為定位字元‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

設定欄位的包圍字元，只能為單個字元，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字元資料欄位被包括；

FIELDS ESCAPED BY ‘value’：

設定如何寫入或者讀取特殊字元，只能為單個字元，即設定逸出字元，預設值為“\”

LINES STARTING BY ‘value’：

設定每行資料開始字元，可以為單個或者多個，預設不使用任何字元

LINES TERMINATED BY ‘value’：

設定每行資料結尾的字元 可以為單個或者多個字元，預設值為‘\n’;

注意：FIELDS和LINES兩個子句是可選的，如果同時指定，FIELDS必須位於LINES的前面；

2.使用mysqldump命令匯出文字檔

mysqldump工具不僅可以將資料匯出為包含CREATE、INSERT的SQL檔案，也可以匯出為純文字檔案；

mysqldump -T path-u root -p dbname [tables] [OPTIONS]

--OPTIONS選項：

--fields-terminated-by=value

--fields-enclosed-by=value

--fields-optionally-enclosed-by=value

--fields-escaped-by=value

--lines-terminated-end-by=value

說明：只有指定了T參數才可以匯出為純文字檔案；path表示匯出資料的目錄；tables為指定要匯出表的名稱；如果不指定，將匯出資料庫dbname中的所有的表；

[options]取值：

--fields-terminated-by=value：

設定欄位之間的分隔字元，可以為單個或者多個字元，預設情況下為定位字元‘\t’

--fields-enclosed-by=value：

設定欄位的包圍字元；

--fields-optionally-enclosed-by=value：

設定欄位的包圍字元，只能為單個字元，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字元資料欄位被包括；

--fields-escaped-by=value：

控制如何寫入或者讀取特殊字元，只能為單個字元，及設定逸出字元，預設為反斜線“\”;

--lines-terminated-end-by=value：

設定每行資料結尾的字元，可以為單個或者多個字元，預設值為‘\n’

3.使用MySQL命令匯出文字檔

mysql是一個功能豐富的工具命令，使用MySQL還可以在命令列模式下執行SQL指令將查詢結果匯入到文字檔中。相比mysqldump，MySQL工具匯出的結果可讀性更強。

如果MySQL伺服器是一個單獨的機器，使用者是在一個client上進行操作，使用者要把資料匯入到client機器上，可以使用mysql -e語句；

使用MySQL匯出資料文字檔語句的基本格式如下：

mysql -u root -p --execute=”SELECT語句” dbname > filename.txt

使用MySQL命令還可以指定查詢結果的顯示格式：

如果某行記錄欄位很多，可能一行不能完全顯示，可以使用--vartical參數，將每條記錄分為多行顯示；

【將查詢結果匯出到HTML檔案中】

mysql -u root -p --html --execute=”SELECT語句” dbname > filename.html

【將查詢結果匯出到xml檔案中】

mysql -u root -p --xml --execute=”SELECT語句” dbname > filename.xml

4.使用LOAD DATA INFILE方式匯入文字檔

LOAD DATA INFILE 語句用於高速的從一個文字檔中讀取行，並裝入一個表中。檔案名稱必須為文字字串。

LOAD DATA INFILE ‘路徑+檔案名稱.txt’ INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

注意：如果匯出的.txt檔案中指定了一些特殊的字元，因此還原語句中也要指定這些字元，以確保還原之後資料的完整性和正確性；

--OPTIONS選項

FIELDS TERMINATED BY ‘value’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

FIELDS ESCAPED BY ‘value’

LINES STARTING BY ‘value’

LINES TERMINATED BY ‘value’

可以看到LOAD DATA 語句中，關鍵字INFILE後面的filename檔案為匯入資料的來源；

tablename表示待匯入的資料表名稱；

OPTIONS部分文法包括FIELDS部分的文法和LINES子句，其可能的取值有：

FIELDS TERMINATED BY ‘value’：

設定欄位之間的分隔字元，可以為單個或者多個字元，預設情況下為定位字元‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

設定欄位的包圍字元，只能為單個字元，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字元資料欄位被包括；

FIELDS ESCAPED BY ‘value’：

設定如何寫入或者讀取特殊字元，只能為單個字元，即設定逸出字元，預設值為“\”

LINES STARTING BY ‘value’：

設定每行資料開始字元，可以為單個或者多個，預設不使用任何字元

LINES TERMINATED BY ‘value’：

設定每行資料結尾的字元 可以為單個或者多個字元，預設值為‘\n’;

[IGNORE number LINES]

選項表示忽略檔案開始處的行數，number表示忽略的行數。執行LOAD DATA語句需要FILE許可權；

5.使用mysqlimport命令匯入文字檔

使用mysqlimport命令可以匯入文字檔，並且不需要登入MySQL用戶端。

使用mysqlimport語句需要指定所需的選項、匯入的資料庫名稱以及匯入的資料檔案的路徑和名稱。

mysqlimport命令的基本文法如下：

mysqlimport -u root -p dbname filename.txt [OPTIONS]

[options]取值：

--fields-terminated-by=value：

設定欄位之間的分隔字元，可以為單個或者多個字元，預設情況下為定位字元‘\t’

--fields-enclosed-by=value：

設定欄位的包圍字元；

--fields-optionally-enclosed-by=value：

設定欄位的包圍字元，只能為單個字元，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字元資料欄位被包括；

--fields-escaped-by=value：

控制如何寫入或者讀取特殊字元，只能為單個字元，及設定逸出字元，預設為反斜線“\”;

--lines-terminated-end-by=value：

設定每行資料結尾的字元，可以為單個或者多個字元，預設值為‘\n’

--ignore-lines=n

忽略資料檔案的前n行；

注意：mysqlimport命令不能指定匯入資料庫的表名稱，資料表的名稱由匯入檔案名稱決定，即檔案名稱作為表名，匯入資料之前該表必須存在。

相關推薦：

mysql 資料庫 備份還原命令

MysqlDatabase Backup和還原命令備忘