mysql備份還原庫命令有哪些

這篇文章給大家分享的是有關(guān)mysql備份還原庫命令有哪些的內(nèi)容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

關(guān)于mysql數(shù)據(jù)庫備份與還原的方法，這里首先講到備份的工具：mysqlhotcopy，使用mysqlhotcopy工具可進行快速備份，然后數(shù)據(jù)還原，使用MySQL命令還原；最后需要導(dǎo)出數(shù)據(jù)庫表。詳細的介紹還得閱讀本文。

1.數(shù)據(jù)備份

定期的備份數(shù)據(jù)庫，使得在意外情況發(fā)生的時候，盡量的減少損失。

1.使用mysqldump命令備份

mysqldump是MySQL提供的一個數(shù)據(jù)庫備份工具，mysqldump命令執(zhí)行的時候，將數(shù)據(jù)庫備份成一個文本文件，該文件中包含了多個CREATE 和INSERT語句，使用這些語句可以重新創(chuàng)建表和插入數(shù)據(jù)；

【使用mysqldump備份單個數(shù)據(jù)庫中】

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

【使用mysqldump備份數(shù)據(jù)庫中的指定表】

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

【使用mysqldump備份多個數(shù)據(jù)庫】

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

使用--databases參數(shù)之后，必須指定至少一個數(shù)據(jù)庫的名稱，多個數(shù)據(jù)庫之間使用空格隔開；

【備份系統(tǒng)中所有的數(shù)據(jù)庫】

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

提示：如果在服務(wù)器上進行備份，并且表均為MyISAM，應(yīng)考慮使用mysqlhotcopy，因為可以更快的進行備份和恢復(fù)；

2.直接復(fù)制整個數(shù)據(jù)庫目錄

因為MySQL表保存為文件方式，所以可以直接復(fù)制MySQL數(shù)據(jù)庫的存儲目錄以及文件進行備份。

這是一種簡單、快速、有效的備份方式，要想保持備份的一致性，備份前需要對相關(guān)表執(zhí)行LOCK TABLES 操作，然后對表執(zhí)行FLUSH TABLES（確保開始備份前將所有激活的索引頁寫入硬盤）。這樣當(dāng)復(fù)制數(shù)據(jù)庫目錄的文件時，允許其他的用戶繼續(xù)查詢表。

這種方法對InnoDB存儲引擎的表不適用。使用這種方法備份數(shù)據(jù)最好還原到相同版本的服務(wù)器中，不同版本可能不兼容；

3.使用mysqlhotcopy工具快速備份

mysqlhotcopy是一個Perl腳本。

只能運行在數(shù)據(jù)庫目錄所在的機器上，并且只能備份MyISAM和ARCHIVE類型的表；

2.數(shù)據(jù)還原

1.使用MySQL命令還原

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

注意：如果filename.sql文件為mysqldump工具創(chuàng)建的包含創(chuàng)建數(shù)據(jù)庫語句的文件，執(zhí)行的時候不需要指定數(shù)據(jù)庫名；

如果已經(jīng)登錄到MySQL服務(wù)器，還可以使用source命令導(dǎo)入SQL文件。

source filename

提示：執(zhí)行source命令之前，必須使用use語句選擇數(shù)據(jù)庫。不然，恢復(fù)過程中會出現(xiàn)錯誤；

2.直接復(fù)制到數(shù)據(jù)庫目錄

如果數(shù)據(jù)庫通過復(fù)制數(shù)據(jù)庫文件備份，可以直接復(fù)制備份的文件到MySQL數(shù)據(jù)目錄下實現(xiàn)還原。

通過該方式還原的時候，必須保持備份數(shù)據(jù)庫和待還原的數(shù)據(jù)庫服務(wù)器的主版本號相同。而且這種方式只是對MyISAM引擎的表有效，對于InnoDB引擎的表不可用；

執(zhí)行還原以前關(guān)閉MySQL服務(wù)，將備份的文件或者目錄覆蓋MySQL的data目錄，啟動MySQL服務(wù)。

對于Linux/Unix操作系統(tǒng)來講，復(fù)制完文件需要將文件的用戶或者用戶組更改為mysql運行的用戶和組，通常用戶是mysql，組也是mysql；

3.mysqlhotcopy快速恢復(fù)

mysqlhotcopy備份之后的文件也可以用來恢復(fù)數(shù)據(jù)庫，在MySQL服務(wù)器停止運行的時候，將備份的數(shù)據(jù)庫文件復(fù)制到MySQL存放的位置（MySQL的data文件夾），重新啟動MySQL服務(wù)即可。

如果以根用戶執(zhí)行該操作，必須指定數(shù)據(jù)庫文件的所有者

chown -R mysql.mysql /var/lib/mysql/dbname
cp -R /usr/backup/test usr/local/mysql/data

執(zhí)行完該語句，重啟服務(wù)器，MySQL將恢復(fù)到備份狀態(tài)

提示：如果需要恢復(fù)的數(shù)據(jù)庫已經(jīng)存在，則在使用DROP語句刪除已經(jīng)存在的數(shù)據(jù)庫之后，恢復(fù)才可以成功，另外MySQL不同版本之間必須兼容；

3.數(shù)據(jù)庫遷移

數(shù)據(jù)庫遷移就是把數(shù)據(jù)從一個系統(tǒng)移動到另一個系統(tǒng)上。數(shù)據(jù)遷移有以下原因：

1.相同版本的MySQL數(shù)據(jù)庫之間的遷移

相同版本的MySQL數(shù)據(jù)庫之間的遷移就是指在主版本號相同的MySQL數(shù)據(jù)庫之間進行數(shù)據(jù)庫移動。

舉例：

將www.abc.com主機上的MySQL數(shù)據(jù)庫全部遷移到www.bcd.com主機上：

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

說明：

mysqldump導(dǎo)入的數(shù)據(jù)直接通過管道符|，傳給mysql命令導(dǎo)入到主機www.bcd.com數(shù)據(jù)庫中，dbname為需要遷移的數(shù)據(jù)庫名稱，如果需要遷移全部的數(shù)據(jù)庫，可以使用參數(shù) --all-databases

2.不同版本的MySQL數(shù)據(jù)庫之間的遷移

MySQL服務(wù)器升級的時候，需要先停止服務(wù)，然后卸載舊版本，并安裝新版本MySQL，這種更新方法很簡單，如果想保留舊版本中的用戶訪問控制信息，需要備份MySQL中的mysql數(shù)據(jù)庫，在新版本MySQL安裝完成之后，重新讀入mysql備份文件中的信息；

舊版本與新版本的字符集不同時，遷移過程需要對默認字符集進行修改，不然可能無法正常顯示結(jié)果；

對于InnoDB引擎的表，一般只能使用mysqldump工具將數(shù)據(jù)導(dǎo)出，然后使用mysql命令導(dǎo)入到目標(biāo)服務(wù)器上。

從新版本向舊版本遷移數(shù)據(jù)的時候，需要特別的小心，最好使用mysqldump命令導(dǎo)出，然后導(dǎo)入目標(biāo)數(shù)據(jù)庫中；

3.不同數(shù)據(jù)庫之間的遷移

數(shù)據(jù)庫遷移可以使用一些工具，例如在Windows系統(tǒng)下，可以使用MyODBC實現(xiàn)MySQL和SQL Server之間的遷移。

MySQL官方提供的工具MySQL Migration Toolkit也可以實現(xiàn)在不同數(shù)據(jù)庫間進行數(shù)據(jù)遷移；

4.表的導(dǎo)出和導(dǎo)入

MySQL數(shù)據(jù)庫中的數(shù)據(jù)可以導(dǎo)出成SQL文本文件、xml文件或者HTML文件。

1.使用SELECT…INTO OUTFILE導(dǎo)出文本文件

MySQL數(shù)據(jù)庫導(dǎo)出數(shù)據(jù)的時候，允許使用包含導(dǎo)出定義的SELECT語句進行數(shù)據(jù)導(dǎo)出操作。該文件被創(chuàng)建到服務(wù)器主機上，因此必須擁有文件寫入權(quán)限（FILE權(quán)限），才能使用此語法。

語法格式：

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不能是一個已經(jīng)存在的文件；

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

FIELDS TERMINATED BY ‘value’：

設(shè)置字段之間的分隔字符，可以為單個或者多個字符，默認情況下為制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

設(shè)置字段的包圍字符，只能為單個字符，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字符數(shù)據(jù)字段被包括；

FIELDS ESCAPED BY ‘value’：

設(shè)置如何寫入或者讀取特殊字符，只能為單個字符，即設(shè)置轉(zhuǎn)義字符，默認值為“\”

LINES STARTING BY ‘value’：

設(shè)置每行數(shù)據(jù)開始字符，可以為單個或者多個，默認不使用任何字符

LINES TERMINATED BY ‘value’：

設(shè)置每行數(shù)據(jù)結(jié)尾的字符 可以為單個或者多個字符，默認值為‘\n’;

注意：FIELDS和LINES兩個子句是可選的，如果同時指定，F(xiàn)IELDS必須位于LINES的前面；

2.使用mysqldump命令導(dǎo)出文本文件

mysqldump工具不僅可以將數(shù)據(jù)導(dǎo)出為包含CREATE、INSERT的SQL文件，也可以導(dǎo)出為純文本文件；

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參數(shù)才可以導(dǎo)出為純文本文件；path表示導(dǎo)出數(shù)據(jù)的目錄；tables為指定要導(dǎo)出表的名稱；如果不指定，將導(dǎo)出數(shù)據(jù)庫dbname中的所有的表；

[options]取值：

●　--fields-terminated-by=value：

設(shè)置字段之間的分隔字符，可以為單個或者多個字符，默認情況下為制表符‘\t’

●　--fields-enclosed-by=value：

設(shè)置字段的包圍字符；

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

設(shè)置字段的包圍字符，只能為單個字符，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字符數(shù)據(jù)字段被包括；

●　--fields-escaped-by=value：

控制如何寫入或者讀取特殊字符，只能為單個字符，及設(shè)置轉(zhuǎn)義字符，默認為反斜線“\”;

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

設(shè)置每行數(shù)據(jù)結(jié)尾的字符，可以為單個或者多個字符，默認值為‘\n’

3.使用MySQL命令導(dǎo)出文本文件

mysql是一個功能豐富的工具命令，使用MySQL還可以在命令行模式下執(zhí)行SQL指令將查詢結(jié)果導(dǎo)入到文本文件中。相比mysqldump，MySQL工具導(dǎo)出的結(jié)果可讀性更強。

如果MySQL服務(wù)器是一個單獨的機器，用戶是在一個client上進行操作，用戶要把數(shù)據(jù)導(dǎo)入到client機器上，可以使用mysql -e語句；

使用MySQL導(dǎo)出數(shù)據(jù)文本文件語句的基本格式如下：

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

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

如果某行記錄字段很多，可能一行不能完全顯示，可以使用--vartical參數(shù)，將每條記錄分為多行顯示；

【將查詢結(jié)果導(dǎo)出到HTML文件中】

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

【將查詢結(jié)果導(dǎo)出到xml文件中】

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

4.使用LOAD DATA INFILE方式導(dǎo)入文本文件

LOAD DATA INFILE 語句用于高速的從一個文本文件中讀取行，并裝入一個表中。文件名稱必須為文字字符串。

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

注意：如果導(dǎo)出的.txt文件中指定了一些特殊的字符，因此還原語句中也要指定這些字符，以確保還原之后數(shù)據(jù)的完整性和正確性；

--OPTIONS選項

●　FIELDS TERMINATED BY ‘value’

●　FIELDS [OPTIONALLY] ENCLOSED BY ‘value’

●　FIELDS ESCAPED BY ‘value’

●　LINES STARTING BY ‘value’

●　LINES TERMINATED BY ‘value’

可以看到LOAD DATA 語句中，關(guān)鍵字INFILE后面的filename文件為導(dǎo)入數(shù)據(jù)的來源；

tablename表示待導(dǎo)入的數(shù)據(jù)表名稱；

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

FIELDS TERMINATED BY ‘value’：

設(shè)置字段之間的分隔字符，可以為單個或者多個字符，默認情況下為制表符‘\t’

FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：

設(shè)置字段的包圍字符，只能為單個字符，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字符數(shù)據(jù)字段被包括；

FIELDS ESCAPED BY ‘value’：

設(shè)置如何寫入或者讀取特殊字符，只能為單個字符，即設(shè)置轉(zhuǎn)義字符，默認值為“\”

LINES STARTING BY ‘value’：

設(shè)置每行數(shù)據(jù)開始字符，可以為單個或者多個，默認不使用任何字符

LINES TERMINATED BY ‘value’：

設(shè)置每行數(shù)據(jù)結(jié)尾的字符 可以為單個或者多個字符，默認值為‘\n’;

[IGNORE number LINES]

選項表示忽略文件開始處的行數(shù)，number表示忽略的行數(shù)。執(zhí)行LOAD DATA語句需要FILE權(quán)限；

5.使用mysqlimport命令導(dǎo)入文本文件

使用mysqlimport命令可以導(dǎo)入文本文件，并且不需要登錄MySQL客戶端。

使用mysqlimport語句需要指定所需的選項、導(dǎo)入的數(shù)據(jù)庫名稱以及導(dǎo)入的數(shù)據(jù)文件的路徑和名稱。

mysqlimport命令的基本語法如下：

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

[options]取值：

●　--fields-terminated-by=value：

設(shè)置字段之間的分隔字符，可以為單個或者多個字符，默認情況下為制表符‘\t’

●　--fields-enclosed-by=value：

設(shè)置字段的包圍字符；

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

設(shè)置字段的包圍字符，只能為單個字符，如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字符數(shù)據(jù)字段被包括；

●　--fields-escaped-by=value：

控制如何寫入或者讀取特殊字符，只能為單個字符，及設(shè)置轉(zhuǎn)義字符，默認為反斜線“\”;

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

設(shè)置每行數(shù)據(jù)結(jié)尾的字符，可以為單個或者多個字符，默認值為‘\n’

●　--ignore-lines=n

忽略數(shù)據(jù)文件的前n行；

注意：mysqlimport命令不能指定導(dǎo)入數(shù)據(jù)庫的表名稱，數(shù)據(jù)表的名稱由導(dǎo)入文件名稱決定，即文件名作為表名，導(dǎo)入數(shù)據(jù)之前該表必須存在。

感謝各位的閱讀！關(guān)于mysql備份還原庫命令有哪些就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！