MySQL備份與恢複

標籤：option   sys   包含   環境   load data   nes   版本相容   class   sql語句   

mysql的備份可以分為冷備份和熱備份兩種。

冷備份：停止資料庫服務進行備份

熱備份：不停止資料庫服務進行備份

mysql的儲存引擎為MyIsam時，只支援冷備份，可以直接複製mysql的data目錄下的資料庫檔案。這種方式需要注意mysql版本相容性問題，同時，為了保證一致性，必須停機或者鎖表進行備份。

在恢複時，首先關閉mysql服務，將備份的資料庫檔案複製到mysql的data目錄下，然後啟動mysql服務。

mysql的儲存引擎為InnoDB時，支援熱備份，因為InnoDB引擎是事務性儲存引擎，可以根據日誌來進行redo和undo，即將備份的時候沒有提交的事務進行復原，已經提交了的事務進行重做。

mysql提供了mysqldump命令用於儲存引擎為InnoDB時的備份。

 

一、備份

mysqldump的文法和選項可以通過命令列輸入mysqldump --help查看。

-- 運行協助命令，查看文法及完整的選項列表mysqldeump --help

這裡因為安裝mysql時配置了環境變數，所以可以直接輸入命令，否則需要進入mysql安裝目錄的bin目錄下。(例如：C:\soft\MySql\mysql-5.7.22-winx64\bin)

 

mysqldump命令備份指定資料庫。

-- 備份指定資料庫(demo)mysqldump -u root -h 127.0.0.1 -p demo >c:\Users\liufa\Desktop\2018-07-10.sql

 

mysqldump命令備份指定資料庫中的指定表。

-- 備份指定資料庫(demo)中的指定表(student)mysqldump -u root -h 127.0.0.1 -p demo student >c:\Users\liufa\Desktop\Student_2018-07-10.sql

 

mysqldump命令備份多個資料庫。

-- 備份多個資料庫(demo、sys)mysqldump -u root -h 127.0.0.1 -p --databases demo sys >c:\Users\liufa\Desktop\2018-07-10.sql

 

mysqldump命令備份所有資料庫。

-- 備份所有資料庫mysqldump -u root -h 127.0.0.1 -p --all-databases >c:\Users\liufa\Desktop\2018-07-10.sql

 

備份的sql指令檔中的一些知識點，簡單介紹。

-- 表示當mysql的版本為4.01.03以上時，這裡面的sql語句是會執行的/*!40103 SET [email protected]_TIME_ZONE */;-- 在大大量匯入時，先禁用索引，然後匯入資料完成之後再開啟索引，一次性重建索引的效率更高-- 禁用表(student)的索引alter table student disable keys;-- 啟用表(student)的索引alter table student enable keys;-- 為指定表設定讀鎖定，那麼該線程和其它線程只能從該表中讀資料，不能進行任何寫操作lock tables demo.student read;-- 釋放鎖定unlock tables;-- 為指定表設定寫鎖定，那麼該表只有擁有這個鎖的線程才能讀寫，其它的線程被阻塞lock tables demo.student write;-- 釋放鎖定unlock tables;

 

二、恢複

mysql命令恢複至指定資料庫。

-- 恢複至指定資料庫(Test)mysql -u root -h 127.0.0.1 -p Test <c:\Users\liufa\Desktop\Student_2018-07-10.sql

 

如果已經登入mysql，則可以使用這種方式恢複至指定資料庫，但只能在cmd介面下執行source命令，不能在mysql工具裡面執行source命令。

-- 如果已經登入mysql，則可以使用這種方式恢複至指定資料庫-- 只能在cmd介面下執行source命令，不能在mysql工具裡面執行source命令use Test;source c:\Users\liufa\Desktop\Student_2018-07-10.sql

 

注意：通過mysqldump備份的資料庫檔案，如果用了(--all-databases)或(--databases)選項，則在備份檔案中包含create database和use語句，故並不需要指定一個資料庫名去恢複備份檔案。

如果通過mysqldump備份的是單個資料庫，且沒有使用(--databases)選項，則備份檔案中不包含create database和use語句，那麼在恢複的時候必須先建立資料庫並指定該資料庫。

 

三、帶有--tab=dir_name選項的備份與恢複

調用mysqldump帶有--tab=dir_name選項備份資料庫，則dir_name表示輸出檔案的目錄，在這個指定的目錄中，需備份的每個表將會產生兩個檔案。

例如：對於一個名為t1的表，備份會產生兩個檔案，即t1.sql和t1.txt。

t1.sql檔案中包含create table語句，t1.txt檔案中一行對應為資料表中的一條記錄，列值與列值之間以‘tab’分隔。

-- 帶有--tab=dir_name選項的備份mysqldump -u root -h 127.0.0.1 -p test --tab=c:\Users\liufa\Desktop

 

帶有--tab=dir_name選項的備份在恢複時，需要先恢複表結構，然後恢複表中的資料。

-- 恢複表結構mysql -u root -h 127.0.0.1 -p test <c:\Users\liufa\Desktop\student.sql

-- 恢複表中的資料mysqlimport -u root -h 127.0.0.1 -p test c:\Users\liufa\Desktop\student.txt

 

四、匯出

使用select...into outfile命令匯出。

-- select ... into outfile文法select * from tablename where 1=1 into outfile ‘filename.txt‘ [OPTIONS][OPTIONS]:fields terminated by ‘string‘                -- 表示每個欄位的值用指定的字元分割，可以為單個或多個字元fields [OPTIONALLY]  ENCLOSED BY ‘char‘    -- 表示每個欄位的值用指定的字元包起來，只能為單個字元fields ESCAPED BY ‘char‘                    -- 表示將系統預設的轉移字元替換為指定字元，只能為單個字元lines STARTING by ‘string‘                    -- 表示每行資料開頭的字元，可以為單個或多個字元，預設沒有任何字元lines terminated by ‘string‘                 -- 表示每行資料結尾的字元，可以為單個或多個字元，預設沒有任何字元-- 把查詢出的資料匯出到指定的文字檔select * from demo.student into outfile ‘C:\\Users\\liufa\\Desktop\\student.txt‘ fields terminated by ‘,‘    -- 表示欄位之間用逗號分隔optionally enclosed BY ‘\‘‘    -- 表示每個欄位用單引號引起來，加上optionally關鍵字表示所有的值都放在引號之間，否則只有char和varchar等字元資料類型的欄位被引號引起來escaped BY ‘\‘‘    lines terminated by ‘\r\n‘;    -- 表示每行以斷行符號分行符號結尾，保證每一條記錄佔一行

 

使用mysql命令匯出。

-- 文法mysql -u root -h 127.0.0.1 -p --execute="select 語句" dbname >filename.txt

 

使用mysql命令匯出指定資料庫中所查詢的指定表的資料。

-- 匯出資料庫(demo)中表(student)的資料，匯出的檔案一行對應資料表中的一條記錄，列與列之間以定位字元(‘tab’)分割mysql -u root -h 127.0.0.1 -p --execute="select * from student;" demo >c:\Users\liufa\Desktop\student.txt

 

-- 匯出資料庫(demo)中表(student)的資料，每個欄位佔一行，如mysql -u root -h 127.0.0.1 -p --vertical --execute="select * from student;" demo >c:\Users\liufa\Desktop\student.txt

 

-- 匯出資料庫(demo)中表(student)的資料，產生html檔案mysql -u root -h 127.0.0.1 -p --html --execute="select * from student;" demo >c:\Users\liufa\Desktop\student.html

 

-- 匯出資料庫(demo)中表(student)的資料，產生xml檔案mysql -u root -h 127.0.0.1 -p --xml --execute="select * from student;" demo >c:\Users\liufa\Desktop\student.xml

 

五、匯入

使用load data infile命令匯入。

-- load data infile文法-- 如果指定關鍵字LOW_PRIORITY，那麼mysql將會等到沒有線程讀取當前指定表的時候才匯入資料-- 如果指定關鍵字CONCURRENT，則讀取和匯入可以同時發生-- 如果指定關鍵字LOCAL，則表示匯入本地的檔案LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘filename.txt‘ -- REPLACE和IGNORE控制對現有表的唯一鍵記錄重複的處理，當指定REPLACE時，新行將替換有相同的唯一索引值的現有行；當指定IGNORE時，跳過有唯一鍵的現有行的重複行的輸入。-- 如果你不指定任何一個選項，當找到重複鍵時，出現一個錯誤，並且文字檔的餘下部分被忽略。[REPLACE | IGNORE] INTO TABLE tablename [FIELDS     [TERMINATED BY ‘string‘]     [[OPTIONALLY] ENCLOSED BY ‘char‘]     [ESCAPED BY ‘char‘ ] ] [LINES     [STARTING BY ‘string‘]     [TERMINATED BY ‘string‘] ] [IGNORE number LINES] -- 表示從開始忽略檔案的行數，number表示忽略的行數[(col_name_or_user_var,...)] [SET col_name = expr,...]-- 匯入資料load data infile ‘c:\\Users\\liufa\\Desktop\\student.txt‘ replaceinto table test.student fields terminated by ‘,‘optionally enclosed BY ‘\‘‘escaped BY ‘\‘‘lines terminated by ‘\r\n‘;

 

MySQL備份與恢複