MySQL利用mysqldump實現備份與恢復的方法

這篇文章運用簡單易懂的例子給大家介紹MySQL利用mysqldump實現備份與恢復的方法，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

MySQL 備份

冷備份：
停止服務進行備份，即停止數據庫的寫入

熱備份：
不停止服務進行備份（在線）

mysql 的 MyIsam 引擎只支持冷備份，InnoDB 支持熱備份，原因：

InnoDB引擎是事務性存儲引擎，每一條語句都會寫日志，并且每一條語句在日志里面都有時間點，那么在備份的時候，mysql可以根據這個日志來進行redo和undo，將備份的時候沒有提交的事務進行回滾，已經提交了的進行重做。但是MyIsam不行，MyIsam是沒有日志的，為了保證一致性，只能停機或者鎖表進行備份。

InnoDB不支持直接復制整個數據庫目錄和使用mysqlhotcopy工具進行物理備份：

1、直接復制整個數據庫目錄
因為MYSQL表保存為文件方式，所以可以直接復制MYSQL數據庫的存儲目錄以及文件進行備份。MYSQL的數據庫目錄位置不一定相同，在Windows平臺下，MYSQL5.6存放數據庫的目錄通常默認為~\MySQL\MYSQL Server 5.6\data，或其他用戶自定義的目錄。這種方法對INNODB存儲引擎的表不適用。使用這種方法備份的數據最好還原到相同版本的服務器中，不同的版本可能不兼容。在恢復的時候，可以直接復制備份文件到MYSQL數據目錄下實現還原。通過這種方式還原時，必須保證備份數據的數據庫和待還原的數據庫服務器的主版本號相同。而且這種方式只對MYISAM引擎有效，對于InnoDB引擎的表不可用。執行還原以前關閉mysql服務，將備份的文件或目錄覆蓋mysql的data目錄，啟動mysql服務。

2、使用mysqlhotcopy工具快速備份
mysqlhotcopy是一個perl腳本，最初由Tim Bunce編寫并提供。他使用LOCK TABLES 、FLUSH TABLES和cp或scp來快速備份數據庫。他是備份數據庫或單個表的最快途徑，但他只能運行在數據庫目錄所在機器上，并且只能備份myisam類型的表。

mysqldump 備份簡述

mysqldump可產生兩種類型的輸出文件，取決于是否選用- -tab=dir_name選項。

不使用- -tab=dir_name選項，mysqldump產生的數據文件是純文本的SQL文件，又CREATE（數據庫、表、存儲路徑等）語句和INSERT（記錄）語句組成。輸出結果以一個文件保存，可以用mysql命令去恢復備份文件。

使用- -tab=dir_name選項，mysqldump對于每一個需備份的數據表產生兩個輸出文件：一個是帶分隔符的文本文件，備份的數據表中的每行存儲為文本中的一行，以“表名.txt”保存；另一個輸出文件為數據表的CREATE TABLE語句，以“表名.sql”保存。

mysqldump 語法和選項

【命令】shell> mysqldump -help

- -all-databases 表示備份系統中所有數據庫，使用- -databases參數之后，必須指定至少一個數據庫的名稱，多個數據庫名稱之間用空格隔開

常用的選項

- -add-drop-table
這個選項將會在每一個表的前面加上DROP TABLE IF EXISTS語句，這樣可以保證導回MySQL數據庫的時候不會出錯，因為每次導回的時候，都會首先檢查表是否存在，存在就刪除

- -add-locks
這個選項會在INSERT語句中捆上一個LOCK TABLE和UNLOCK TABLE語句。這就防止在這些記錄被再次導入數據庫時其他用戶對表進行的操作

- -tab
這個選項將會創建兩個文件，一個是帶分隔符的文本文件，備份的數據表中的每行存儲為文本中的一行，以“表名.txt”保存；另一個輸出文件為數據表的CREATE TABLE語句，以“表名.sql”保存。

- -quick或者- -opt
如果你未使用- -quick或者- -opt選項，那么mysqldump將在轉儲結果之前把全部內容載入到內存中。這在你轉儲大數據量的數據庫時將會有些問題。該選項默認是打開的，但可以使用- -skip-opt來關閉它。

- -skip-comments
使用–skip-comments可以去掉導出文件中的注釋語句

- -compact
使用- -compact選項可以只輸出最重要的語句，而不輸出注釋及刪除表語句等等

以SQL格式備份數據

如果備份文件名.sql沒有指定所放置的路徑，則默認放在~\MySQL\MySQL Server 5.6\bin目錄下。

指定數據備份路徑:
mysqldump –h 主機名 –u 用戶名 –p - -all-databases > C:\備份文件名.sql

調用mysqldump帶有- -all-databases選項備份所有的數據庫
mysqldump –h 主機名 –u 用戶名 –p - -all-databases > C:\備份文件名.sql

調用mysqldump帶有- -databases選項備份指定的數據庫
mysqldump –u 用戶名 –p - -databases db1 db2 db3 … > C:\備份文件名.sql

調用mysqldump備份一個指定的數據庫：
mysqldump –u 用戶名 –p - -databases db > 備份文件名.sql

用mysqldump不帶有- -databases選項備份指定的一個數據庫，生成的備份文件中是沒有CREATE DATABASE和USE語句的：
mysqldump –u 用戶名 –p db > 備份文件名.sql

注意： 當對一個數據庫進行備份時- -databases允許省略，但是省略后導致的是備份文件名.sql中沒有CREATE DATABASE 和USE語句，那么恢復備份文件時，必須指定一個默認的數據庫名，由此服務器才知道備份文件恢復到哪個數據庫中；由此可以導致你可以使用一個和原始數據庫名稱不同的數據庫名。

調用mysqldump備份某個數據庫中的某幾張表：
mysqldump –u用戶名 –p 數據庫名 表名1 表名2 表名3… > 備份文件名.sql

恢復SQL格式的備份文件

通過mysqldump備份的文件，如果用了- -all-databases或- -databases選項，則在備份文件中包含CREATE DATABASE和USE語句，故并不需要指定一個數據庫名去恢復備份文件。

在Shell命令下：

shell> mysql –u 用戶名 –p < 備份文件.sql

在mysql命令下，用source命令導入備份文件：

mysql> source備份文件.sql; //已登錄mysql，用source命令

如果通過mysqldump備份的是單個數據庫，且沒有使用- -databases選項，則備份文件中不包含CREATE DATABASE和USE語句，那么在恢復的時候必須先創建數據庫。

在shell命令下：

shell> mysqladmin –u 用戶名 –p create 數據庫名 //創建數據庫
shell> mysql –u 用戶名 –p數據庫名 < 備份文件.sql

在mysql命令下：

mysql> CREATE DATABASE IF NOT EXIST 數據庫名;
mysql> USE 數據庫名;
mysql> source備份文件.sql;

注意： 只能在cmd界面下執行source命令，不能在mysql工具里面執行source命令，會報錯，因為cmd是直接調用mysql.exe來執行命令的。

以帶分隔符的文本文件格式備份數據

調用mysqldump帶有- -tab=dir_name選項去備份數據庫，則dir_name表示輸出文件的目錄，在這個目錄中，需備份的每個表將會產生兩個文件。如對于一個名為t1的表，包含兩個文件：t1.sql和t1.txt。.sql文件中包含CREATE TABLE語句，.txt文件中一行為數據表中的一條記錄，列值與列值之間以‘tab'分隔。

注意： 使用帶- -tab=dir_name選項的mysqldump最好只被用于本地服務器上。因為如果用在遠程服務器上，- -tab產生的目錄將會既存在本地主機也會存在于遠程主機上，.txt文件將會被服務器寫在遠程主機的目錄中，而.sql文件將會被寫在本地主機目錄中。

調用mysqldump帶有- -tab=dir_name選項備份數據庫
mysqldump –u 用戶名 –p - -tab=dir_name 數據庫名

【例子】用mysqldump帶有- -tab=dir_name選項備份數據庫test，放在D盤下：

數據庫test中的表：

執行備份命令：

所輸出的結果：

恢復帶分隔符的文本文件格式的備份文件 用mysql命令處理.sql文件去還原表結構，然后處理.txt文件去載入記錄。

shell> mysql –u 用戶名 –p 數據庫名 < 表名.sql //還原表結構
shell> mysqlimport –u 用戶名 –p 數據庫名 表名.txt //還原記錄

或者：可用LOAD DATA INFILE 去代替mysqlimport命令，不過此時得在mysql命令下：

mysql> use 數據庫名; //選中數據庫
mysql> LOAD DATA INFILE ‘表名.txt' INTO TABLE表名; //還原記錄

【例子】 恢復數據庫test里面的數據表stucou表： 查看test數據庫里面的表，沒有stucou表：

用stucou.sql文件恢復stucou表結構：

stucou數據表恢復成功：

stucou數據表中沒有任何記錄：

用stucou.txt文件恢復stucou表記錄：

用mysql命令將查詢的中間結果導出

將查詢結果導入到文本文件中

mysql是一個功能豐富的工具命令，使用mysql還可以在命令行模式下執行SQL指令，將查詢結果導入到文本文件中。相比mysqldump，mysql工具導出的結果可讀性更強。如果mysql服務器是單獨的機器，用戶是在一個client上進行操作，用戶要把數據結果導入到client機器上，可以使用mysql -e語句。

【命令】：
shell> mysql -u root -p --execute="SELECT 語句" dbname > filename.txt

該命令使用–execute 選項，表示執行該選項后面的語句并退出，后面的語句必須用雙引號括起來

dbname為要導出的數據庫名稱，導出的文件中不同列之間使用制表符分隔，第一行包含了字段名稱

【例子】使用mysql命令，導出test庫的person表記錄到文本文件：
shell> mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt

person3.txt的內容如下

ID     Name    Age    job

1       green    29      lawer

2       suse    26      dancer

3       evans    27      sports man

4       mary    26      singer

可以看到，person3.txt文件中包含了每個字段的名稱和各條記錄，如果某行記錄字段很多，可能一行不能完全顯示，可以使用 - -vertical參數，將每條記錄分為多行顯示

【例子】使用mysql命令導出test庫的person表使用–vertical參數顯示：
shell> mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt

*************************** 1. row ***************************

ID: 1

Name: green

Age: 29

job: lawer

*************************** 2. row ***************************

ID: 2

Name: suse

Age: 26

job: dancer

*************************** 3. row ***************************

ID: 3

Name: evans

Age: 27

job: sports man

*************************** 4. row ***************************

ID: 4

Name: mary

Age: 26

job: singer

如果person表中記錄內容太長，這樣顯示將會更加容易閱讀

將查詢結果導入到html文件中

使用mysql命令導出test庫的person表記錄到html文件，輸入語句如下

shell> mysql -u root -p --html --execute="SELECT * FROM PERSON;" test > C:\person5.html

將查詢結果導入到xml文件中

如果要導出為xml文件，那么使用–xml選項
使用mysql命令導出test庫的person表的中記錄到xml文件

shell> mysql -u root -p --xml --execute="SELECT * FROM PERSON;" test > C:\person6.xml

<&#63;xml version="1.0"&#63;>

<resultset statement="SELECT * FROM PERSON" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <row>

  <field name="ID">1</field>

  <field name="Name">green</field>

  <field name="Age">29</field>

  <field name="job">lawer</field>

 </row>

 <row>

  <field name="ID">2</field>

  <field name="Name">suse</field>

  <field name="Age">26</field>

  <field name="job">dancer</field>

 </row>

 <row>

  <field name="ID">3</field>

  <field name="Name">evans</field>

  <field name="Age">27</field>

  <field name="job">sports man</field>

 </row>

 <row>

  <field name="ID">4</field>

  <field name="Name">mary</field>

  <field name="Age">26</field>

  <field name="job">singer</field>

 </row>

</resultset>

關于MySQL利用mysqldump實現備份與恢復的方法就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。