資料備份與還原-16(共22講)

目錄：

一、資料備份

二、資料還原

三、資料庫遷移

四、導出和導入文本檔案

一、資料備份：

1,mysqldump 

工作原理：它先查出需要備份的表的結構，再在文本檔案中生成一個create語句，然後将表中的所有記錄轉換成一條insert語句。生成

的.sql檔案其實是包含了create語句來恢複表結構、insert語句恢複表資料

使用方法：可以實作備份一個庫、多個庫、所有庫------

mysqldump -u root -p‘123’ dbname1 table1  table2 ... > /home/wuxy/table1.sql

mysqldump -u root -p‘123’ --databases dbname1 dbname2  > /home/wuxy/dbbacpup.sql

mysqldump -u root -p'123' --all-databases > /home/wuxy/all.sql

2,直接複制mysql的資料檔案（需停止mysql服務，否則造成資料不一緻，但實際情況一般是不允許停止mysql服務的），對Innodb存儲引擎

的表不适用，對MyISAM存儲引擎的表，這樣的備份和還原還是很友善的。但是還原時最好還是相同版本的Mysql資料庫，否則可能會存在文

件類型不同的情況。

3.mysqlhotcopy

如果備份時不能停止mysql伺服器，可以采用mysqlhotcopy工具，這種備份方式比mysqldump指令快。

工作原理：mysqlhotcopy是一個perl腳本，主要在linux系統下使用，mysqlhotcopy工具使用LOCK TABLES、FLUSH TABLES 和cp來進行快速

備份。其工作原理是：先将需要備份的資料庫加上一個讀操作鎖，然後用FLUSH TABLES将記憶體中的資料庫寫回到硬碟中的資料庫中，最後把

需要備份的資料庫檔案複制到目标目錄

使用方法：

mysqlhotcopy [options] dbname1 dbname2  backDir/

可以同時備份多個資料庫

help 檢視參數

二、資料還原：

1、

如果使用mysqldump指令将資料庫中的資料備份成一個文本檔案，通常這個檔案的字尾是sql，需要還原的時候，可以使用mysql指令來還原

備份的資料。

工作原理：備份檔案中通常包含create語句和insert語句。mysql指令可以執行備份檔案中的create語句和insert語句。通過create語句來

建立資料庫和表。通過insert語句來插入備份的資料

使用方法：mysql -u root -p'123' [daname] < backup.sql

2、如果使用的是直接複制資料的備份方法，那麼可以直接将備份資料複制到mysql的資料目錄下。通過這種方式還原時，必須保證兩個

Mysql資料庫的主版本号相同，因為隻有Mysql資料庫的主版本号相同時，才能保證這兩個Mysql資料庫的檔案類型是相同的。而且這種方式

對MyISAM類型的表比較有效，對應InnoDB類型的表則不可用，因為InnoDB表的表空間不能直接複制。

linux作業系統下，資料庫目錄通常在/var/lib/mysql、/usr/local/mysql/data或者/usr/local/mysql/var/這3個目錄下。上述位置隻是數

據庫目錄最常用的位置，具體位置根據安裝時設定的位置而定。

三、資料庫遷移：

資料庫遷移就是指将資料庫從一個系統移動到另一個系統上。

大緻可以分為3類：

1、相同版本的Mysql資料庫之間遷移

2、遷移到其他版本的Mysql資料庫中

3、遷移到其他類型的資料庫中

詳細而言：

1、相同版本的Mysql資料庫之間的遷移就是在主版本相同的Mysql資料庫之間進行資料庫移動。這種遷移的方式最容易實作。

遷移的原因比如有：換了新的伺服器、或者是裝了新的作業系統。

因為遷移後的Mysql資料庫的主版本号相同，是以可以通過複制資料庫目錄來實作資料庫遷移（需停止Mysql服務，否則會造成資料不一緻）

，但是隻有資料庫表都是MyISAM類型的才能使用這種方式。也可以通過mysqldump備份，然後通過mysql指令還原。

2，不同版本的Mysql資料庫之間進行資料遷移通常是因為Mysql更新的原因，例如：5.0版本推出以後，改進了4.0版本的很多缺陷，是以需

要更新。高版本的mysql資料庫通常會相容低版本，是以可以從低版本的mysql資料庫遷移到高版本的mysql資料庫。但是高版本的mysql資料

庫很難遷移到低版本的mysql資料庫，因為高版本的mysql資料庫可能有一些新的特性，這些新的特性是低版本mysql資料庫所不具有的。

MyISAM類型的表可以直接複制，也可以使用mysqlhotcopy工具

但是InnoDB類型的表不可以使用這兩種方法。最常用的辦法是使用mysqldump指令進行備份，然後通過mysql指令将備份檔案還原到目标

MySQL資料庫。

資料庫遷移時要特别小心，最好是使用mysqldump指令來進行備份，避免遷移時造成資料丢失。

3，不同資料庫之間遷移是指從其他類型的資料庫遷移到Mysql資料庫，或者從mysql資料庫遷移到其他類型的資料庫。例如：某個網站原來

使用的是oracle資料庫，因為營運成本太高等原因，希望該使用者mysql資料庫。或者某個管理系統原來使用的是mysql資料庫，希望改用

oracle資料庫，這樣的不同資料庫之間的遷移也經常會發生，但是這種遷移沒有普通使用的解決方法。

   mysql以外的資料庫也有類似于mysqldump這樣的備份工具，可以将mysql資料庫中的檔案備份成sql檔案或者文本。但是，因為不同資料

庫廠商沒有完全按照SQL标準設計資料庫，這就造成了不同資料庫使用的sql語句的差異。例如：微軟的sql server軟體使用的是T-SQL語言

，T-SQL中包含了非标準的SQL語句，這就造成了SQL server和mysql的sql語句不能相容。

注意：在不同資料庫之間的遷移可能會使用一些其他的一些工具，具體的可以百度。

四、表的導出和導入：

在資料庫的日常維護中，經常需要進行表的導出和導入操作。mysql資料庫中的表可以導出成文本檔案、xml檔案、或者html檔案。相應的文

本檔案也可以導入mysql資料庫中。

1，用select ... into outfile 導出文本檔案

使用方法：select [列名] from table [where語句] into outfile '目标檔案' [option];

option常見如下：

fields terminated by '字元串'   ##設定每個字段之間用什麼隔開，預設是制表符

fields enclosed by '字元串'     ##用什麼字元來括上字段的值，預設是不使用任何字元的

fields optionally enclosed by '字元串'  ##字元型資料（char、varchar、txt類型的字段）用什麼引起來，預設是不使用任何字元的

fields escaped by '字元串'      ##設定轉移字元，預設的是斜杠

lines starting by '字元串'  ##設定每一行的起始字元，預設是沒有任何字元的

lines terminated by '字元串' ##設定每一行的結尾字元，比如：'\r\n'（換行回車），預設的情況下是'\n'(回車)

示例：

select * from test.student into outfile '/home/data/student.txt' fields terminated by '\.' fields optionally enclosed by 

'\"' lines starting by '\>' lines terminated by '\r\n';

2，用mysqldump指令導出文本檔案

mysqldump指令可以備份資料庫中的資料，但是備份時是在備份檔案中儲存了create語句和insert語句。

*2.1不僅如此，mysqldump指令還可以導出文本檔案。

使用方法如下：

mysqldump -u root -p'123' -T 目标目錄 dbname table [option];

option

--fields-terminated-by=字元串   ##設定字元串作為字段的分隔符，預設是制表符（\t）

--fileds-enclosed-by=字元       ##設定用什麼字元來括上字段的值

--fields-optionally-enclosed-by=字元  ##字元型資料（char、varchar、txt類型的字段）用什麼引起來，預設是不使用任何字元的

--fields-escaped-by=字元        ##設定轉移字元，預設的是斜杠

--lines=terminated-by=字元串    ##設定每一行的結尾字元，比如：'\r\n'（換行回車），預設的情況下是'\n'(回車)

mysqldump -u root -p'123' -T /home/data/ test student "--fields-terminater-by=","--fields-optionally-enclosed-by='"'

*2.2 mysqldump指令導出xml檔案

mysqldump -u root -p'123' --xml test student > /home/data/student.xml

##注意這個語句不能以分号結束

3 用mysql指令導出文本檔案

mysql指令可以用來登入mysql伺服器，也可以用來還原備份檔案，同時，mysql指令也可以導出文本檔案。

文法形式如下：

mysql -u root -p'123' -e "select 語句" dbname > /home/data/name.txt

mysql -u root -p'123' -e "select * from student" tets > /home/data/student2.txt ###與select 查詢的資料一模一樣

同樣用mysql指令也可以導出xml檔案和html檔案

mysql -u root -p'123' --html -e "select * from department" test > /home/data/department.html

4 用load data infile 方式導入文本檔案（這個指令是在資料庫中執行的）

mysql中，可以使用load data infile 指令将文本檔案導入到mysql資料庫中。

基本文法形式如下：

load data [local] infile filename into table tablename [option]

load data infile '/home/data/student2.txt' into table student fields terminated by ',' optionally enclosed by '"';

注意：

如果文本檔案中字段之間是用逗号分隔的，那麼導入資料的時候也要指定文本檔案的分隔符

同理，如果字元型資料（cahr、varchar、txt）用雙引号括起來了，那麼導入的時候也要指定

5 用mysqlimport指令導入文本檔案

mysql中可以使用mysqlimport指令将文本檔案導入到mysql資料庫中。

mysqlimport -u root -p'123' [--local] dbname file [option]

option選項和mysqldump的選項一樣

mysqlimport -u root -p'123' test '/home/data/student.txt' "--fields-terminated-by-," "00fields-optionally-enclosed-by-''"

常見問題及解答：

1.如何選擇備份資料庫的方法？

答：根據資料庫的存儲引擎的類型不同備份表的方法也不一樣。對應MyISAM類型的表，可以直接複制Mysql資料檔案夾或者使用mysqlhotcopy指令進行備份。複制mysql資料檔案夾需要将mysql服務停止，否則可能會出現異常。而mysqlhotcopy指令則不需要停止mysql服務。mysqldump指令是最安全的備份方法，它既适合于MyISAM類型的表，也适用于InnoDB類型的表

2.如何更新mysql資料庫？

答：

（1）先使用mysqldump指令備份mysql資料庫中的資料，這樣做的目的是為了避免誤操作引起mysql資料庫中的資料丢失。

（2）停止mysql服務，可以直接終止mysql服務的程序，但是最好還是使用安全的方法停止mysql服務，這樣可以避免緩沖中的資料丢失。

（3）解除安裝舊版本的mysql資料庫，通常情況下，解除安裝mysql資料庫軟體時，系統會繼續保留mysql資料庫中的資料檔案

（4）安裝新版本的mysql資料庫，并進行相應的配置。

（5）啟動mysql服務，登入mysql資料庫查詢資料是否完整，如果資料不完整，使用之前備份的資料進行恢複。

小結：本節介紹了備份資料庫、還原資料庫、資料庫遷移、導出表和導入表的内容。備份資料庫和還原資料庫是本節的重點内容。在實際應用中，通常使用mysqldump指令備份資料庫，使用mysql指令還原資料庫。資料庫遷移、導出表和導入表是本節的難點，資料庫遷移需要考慮資料庫的相容性問題，最好是在相同版本的mysql資料庫之間遷移。導出表和導入表的方法比較多，希望熟悉且多加練習。

追加内容：

一、MyISAM引擎備份和InnoDB引擎備份

由于MyISAM引擎為表級鎖，是以在備份時需要防止在備份期間資料寫入而導緻不一緻，是以，在備份時使用--lock-all-tables加上讀鎖。

mysqldump -A -F -B --lock-all-tables　| gzip /data/backiup/$(date +%F).tar.gz

由于InnoDB引擎為行級鎖，是以備份時可以不對資料庫加鎖的操作，可以加選項--single-transaction進行備份（Option automatically turns off --lock-tables，是以不會鎖表），此參數僅對innodb有效，可以獲得一緻性備份。

mysqldump -A -F -B --single-transaction | gzip /data/backup/$(date +%F).tar.gz

備注：

1，--single-transaction這個參數僅适用于InnoDB引擎 --master-data=2

2，--single-transaction and --lock-all-tables是互斥的，不可以同時使用

      本文轉自Tenderrain 51CTO部落格，原文連結：http://blog.51cto.com/tenderrain/1602091，如需轉載請自行聯系原作者