MySQL5.7 mysqldump備份與恢復(fù)的實(shí)現(xiàn)

MySQL 備份

冷備份：
停止服務(wù)進(jìn)行備份，即停止數(shù)據(jù)庫的寫入

熱備份：
不停止服務(wù)進(jìn)行備份（在線）

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

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

InnoDB不支持直接復(fù)制整個(gè)數(shù)據(jù)庫目錄和使用mysqlhotcopy工具進(jìn)行物理備份：

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

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

mysqldump 備份簡述

mysqldump可產(chǎn)生兩種類型的輸出文件，取決于是否選用- -tab=dir_name選項(xiàng)。

不使用- -tab=dir_name選項(xiàng)，mysqldump產(chǎn)生的數(shù)據(jù)文件是純文本的SQL文件，又CREATE（數(shù)據(jù)庫、表、存儲路徑等）語句和INSERT（記錄）語句組成。輸出結(jié)果以一個(gè)文件保存，可以用mysql命令去恢復(fù)備份文件。

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

mysqldump 語法和選項(xiàng)

【命令】shell> mysqldump -help

- -all-databases 表示備份系統(tǒng)中所有數(shù)據(jù)庫，使用- -databases參數(shù)之后，必須指定至少一個(gè)數(shù)據(jù)庫的名稱，多個(gè)數(shù)據(jù)庫名稱之間用空格隔開

常用的選項(xiàng)

- -add-drop-table
這個(gè)選項(xiàng)將會在每一個(gè)表的前面加上DROP TABLE IF EXISTS語句，這樣可以保證導(dǎo)回MySQL數(shù)據(jù)庫的時(shí)候不會出錯，因?yàn)槊看螌?dǎo)回的時(shí)候，都會首先檢查表是否存在，存在就刪除

- -add-locks
這個(gè)選項(xiàng)會在INSERT語句中捆上一個(gè)LOCK TABLE和UNLOCK TABLE語句。這就防止在這些記錄被再次導(dǎo)入數(shù)據(jù)庫時(shí)其他用戶對表進(jìn)行的操作

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

- -quick或者- -opt
如果你未使用- -quick或者- -opt選項(xiàng)，那么mysqldump將在轉(zhuǎn)儲結(jié)果之前把全部內(nèi)容載入到內(nèi)存中。這在你轉(zhuǎn)儲大數(shù)據(jù)量的數(shù)據(jù)庫時(shí)將會有些問題。該選項(xiàng)默認(rèn)是打開的，但可以使用- -skip-opt來關(guān)閉它。

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

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

以SQL格式備份數(shù)據(jù)

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

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

調(diào)用mysqldump帶有- -all-databases選項(xiàng)備份所有的數(shù)據(jù)庫
mysqldump –h 主機(jī)名 –u 用戶名 –p - -all-databases > C:\備份文件名.sql

調(diào)用mysqldump帶有- -databases選項(xiàng)備份指定的數(shù)據(jù)庫
mysqldump –u 用戶名 –p - -databases db1 db2 db3 … > C:\備份文件名.sql

調(diào)用mysqldump備份一個(gè)指定的數(shù)據(jù)庫：
mysqldump –u 用戶名 –p - -databases db > 備份文件名.sql

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

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

調(diào)用mysqldump備份某個(gè)數(shù)據(jù)庫中的某幾張表：
mysqldump –u用戶名 –p 數(shù)據(jù)庫名 表名1 表名2 表名3… > 備份文件名.sql

恢復(fù)SQL格式的備份文件

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

在Shell命令下：

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

在mysql命令下，用source命令導(dǎo)入備份文件：

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

如果通過mysqldump備份的是單個(gè)數(shù)據(jù)庫，且沒有使用- -databases選項(xiàng)，則備份文件中不包含CREATE DATABASE和USE語句，那么在恢復(fù)的時(shí)候必須先創(chuàng)建數(shù)據(jù)庫。

在shell命令下：

shell> mysqladmin –u 用戶名 –p create 數(shù)據(jù)庫名 //創(chuàng)建數(shù)據(jù)庫
shell> mysql –u 用戶名 –p數(shù)據(jù)庫名  備份文件.sql

在mysql命令下：

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

注意： 只能在cmd界面下執(zhí)行source命令，不能在mysql工具里面執(zhí)行source命令，會報(bào)錯，因?yàn)閏md是直接調(diào)用mysql.exe來執(zhí)行命令的。

以帶分隔符的文本文件格式備份數(shù)據(jù)

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

注意： 使用帶- -tab=dir_name選項(xiàng)的mysqldump最好只被用于本地服務(wù)器上。因?yàn)槿绻迷谶h(yuǎn)程服務(wù)器上，- -tab產(chǎn)生的目錄將會既存在本地主機(jī)也會存在于遠(yuǎn)程主機(jī)上，.txt文件將會被服務(wù)器寫在遠(yuǎn)程主機(jī)的目錄中，而.sql文件將會被寫在本地主機(jī)目錄中。

調(diào)用mysqldump帶有- -tab=dir_name選項(xiàng)備份數(shù)據(jù)庫
mysqldump –u 用戶名 –p - -tab=dir_name 數(shù)據(jù)庫名

【例子】用mysqldump帶有- -tab=dir_name選項(xiàng)備份數(shù)據(jù)庫test，放在D盤下：

數(shù)據(jù)庫test中的表：

執(zhí)行備份命令：

所輸出的結(jié)果：

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

shell> mysql –u 用戶名 –p 數(shù)據(jù)庫名  表名.sql //還原表結(jié)構(gòu)
shell> mysqlimport –u 用戶名 –p 數(shù)據(jù)庫名 表名.txt //還原記錄

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

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

【例子】 恢復(fù)數(shù)據(jù)庫test里面的數(shù)據(jù)表stucou表： 查看test數(shù)據(jù)庫里面的表，沒有stucou表：

用stucou.sql文件恢復(fù)stucou表結(jié)構(gòu)：

stucou數(shù)據(jù)表恢復(fù)成功：

stucou數(shù)據(jù)表中沒有任何記錄：

用stucou.txt文件恢復(fù)stucou表記錄：

用mysql命令將查詢的中間結(jié)果導(dǎo)出

將查詢結(jié)果導(dǎo)入到文本文件中

mysql是一個(gè)功能豐富的工具命令，使用mysql還可以在命令行模式下執(zhí)行SQL指令，將查詢結(jié)果導(dǎo)入到文本文件中。相比mysqldump，mysql工具導(dǎo)出的結(jié)果可讀性更強(qiáng)。如果mysql服務(wù)器是單獨(dú)的機(jī)器，用戶是在一個(gè)client上進(jìn)行操作，用戶要把數(shù)據(jù)結(jié)果導(dǎo)入到client機(jī)器上，可以使用mysql -e語句。

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

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

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

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

person3.txt的內(nèi)容如下

ID     Name    Age    job

1       green    29      lawer

2       suse    26      dancer

3       evans    27      sports man

4       mary    26      singer

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

【例子】使用mysql命令導(dǎo)出test庫的person表使用–vertical參數(shù)顯示：
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表中記錄內(nèi)容太長，這樣顯示將會更加容易閱讀

將查詢結(jié)果導(dǎo)入到html文件中

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

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

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

如果要導(dǎo)出為xml文件，那么使用–xml選項(xiàng)
使用mysql命令導(dǎo)出test庫的person表的中記錄到xml文件

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

?xml version="1.0"?>

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>

到此這篇關(guān)于MySQL5.7 mysqldump備份與恢復(fù)的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL mysqldump備份恢復(fù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！