當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL备份与恢复——基于OUTFILE /LOAD DATA 逻辑备份恢复

發布時間：2024/8/26 数据库 29 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL备份与恢复——基于OUTFILE /LOAD DATA 逻辑备份恢复小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一、說明

在OUTFILE /LOAD DATA 邏輯備份中強烈建議備份恢復均明確指定字符集CHARACTER SET charset_name，避免亂碼的出現發生。默認讀取character_set_database參數。

1.1?必須參數

使用該方式進行備份恢復，必須設置--secure-file-priv參數！！導出導入文件必須在指定目錄下。

該參數為靜態參數，重啟生效！

下表為官方文檔對該參數介紹：

Property	Value
Command-Line Format	--secure-file-priv=dir_name
System Variable	secure_file_priv
Scope	Global
Dynamic	No
Type	string
Default Value (>= 5.6.34)	platform specific
Default Value (<= 5.6.33)	empty string
Valid Values (>= 5.6.34)	empty string dirname NULL
Valid Values (<= 5.6.33)	empty string dirname

二、 OUTFILE 備份

Outfile備份是服務端命令，導出的文件只能在服務器端。

2.1命令參數

	參數	含義
1	--fields-terminated-by=str??	列與列之間分隔
2	--fields-enclosed-by=char? ?	在每一個字段的前后加上char這個字符? ? ?
3	--fields-optionally-enclosed-by=char??	在每一個非數字的字段前后加上char這個字符
4	--fields-escaped-by=char? ??	使用char去轉義特殊字符
5	--lines-terminated-by=str? ??	行與行之間分隔 ??#linux \n ??windows \r\n

注：2與3不能同時使用

2.2備份案例? ?

2.1.1?備份全表

示例：

1	select?*?from?test?into?outfile?'/home/mysql/test.txt'?CHARACTER?SET?UTF8?fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n';

2.1.2?備份其中幾列

示例：

1	select?id,name?from?test?into?outfile?'/home/mysql/test.txt'?CHARACTER?SET?UTF8?fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n';

2.1.3?備份處理之后的列

示例：

1	select?id,name,id+1?from?test?into?outfile?'/home/mysql/test.txt'?CHARACTER?SET?UTF8?fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n';

2.1.4?按照條件備份

示例：

1	SELECT?*?into?outfile?'/home/mysql/test.txt?'?fields?terminated?by?','?optionally?enclosed?by?'"'?lines?terminated?by?'\n'?FROM?trans_transreq_20180828?a?WHERE?a.id?>?999473433;

三、LOAD DATA 恢復

load data infile是MySQL中用來批量、快速導入數據的一種方式。必須在mysqld進程（服務）啟動時運行。另外需要注意編碼方式避免出現亂碼現象。

由于安全原因，當讀取位于服務器上的文件時，文件必須處于數據庫目錄或可被所有人讀取。另外，為了對服務器上文件使用load data infile，在服務器主機上必須有file的權限。

3.1關鍵字說明

關鍵字	作用
LOW_PRIORITY	等到沒有其他人讀這個表的時候，才把數據插入
LOCAL	指定local表明從客戶主機讀文件
REPLACE \| IGNORE	對唯一鍵記錄的處理,不指定直接報錯，后續忽略
PARTITION (partition_name,...)	將數據插入到指定分區
CHARACTER SET	字符集設置? 默認讀取character_set_database
FIELDS TERMINATED BY	字段值的分隔符，若不指定則默認為 '\t'
FIELDS ENCLOSED BY	字段值的包括符，若不指定則默認為 ''
FIELDS ESCAPED BY	字段值的轉義字符，若不指定則默認為'\\'
LINES TERMINATED BY	指定行分隔符，若不指定則默認為為系統的默認行分隔符（‘\r\n‘ on windows，'\n' on linux）
LINES STARTING BY	若指定該值為xxx，則MySQL會自動去掉xxx及其前面的字符，若某行不包含xxx，則該行將被忽略，若不指定默認為'?' 如： xxx"abc",1?? something xxx"def",2?? "ghi",3?? 實際： ("abc", 1), ("def", 2)
IGNORE LINES / ROWS	忽略文件開頭的指定行，比如指定為2，那么MySQL只會解析并插入第三行及后面的數據

3.2 恢復案例

導入文件均由該導出命令生成

1	select?*?from?test.test?into?outfile?'/home/mysql/test.txt'?CHARACTER?SET?UTF8?fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n';

文件內容（截取）

3.2.1?恢復全表

前提條件：列數相同，類型符合要求

1	load?data?infile?'/home/mysql/test.txt'?into?table?test?CHARACTER?SET?UTF8?fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n';

3.2.2?恢復文件中其中幾列

不需要第二列，即忽略第二列數據

1	load?data?infile?'/home/mysql/test.txt'?into?table?test??fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n'?(id,@dummy,create_time);

3.2.3?對INT類型做數學運算

1	load?data?infile?'/home/mysql/test.txt'?into?table?test??fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n'?(@dummy,name,create_time)?set?id=?@dummy*2;

3.2.4?對字符類型做拼接

1	load?data?infile?'/home/mysql/test.txt'?into?table?test??fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n'?(id,@dummy,create_time)?set?name=??concat?(@dummy,'a');

3.2.5?對時間字段做變化

1	load?data?infile?'/home/mysql/test.txt'?into?table?test??fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n'?(id,name,@dummy)?set?create_time=CURRENT_TIMESTAMP;

3.2.6?對多個變量做處理

1	load?data?infile?'/home/mysql/test.txt'?into?table?test??fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n'?(@dummy1,@dummy2,create_time)?set?id?=?@dummy1*2?,name=concat(@dummy2,'a');

3.2.7?使用查詢值作為列

1	load?data?infile?'/home/mysql/test.txt'?into?table?test??fields?terminated?by?'\|'?enclosed?by?'"'?lines?terminated?by?'\r\n'?(id,@dummy,create_time)?set?name=?(?select?1?from?dual);

3.2.8?使用查詢值作為列

關鍵字LOCAL導入本地文件。對文件需要

1	LOAD?DATA?LOCAL?INFILE?'D:\\test.txt'?INTO?TABLE?test?CHARACTER?SET?UTF8?FIELDS?TERMINATED?BY?'\|'?ENCLOSED?BY?'"'

總結

以上是生活随笔為你收集整理的MySQL备份与恢复——基于OUTFILE /LOAD DATA 逻辑备份恢复的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。