[TOC] 前言 1.備份數據的意義 運維工作的核心簡單概括起來就是兩件事：第一個是保護公司的數據，第二個是讓網站能夠7 24小時提供服務。 雖然這兩件事情都很重要，但是相比較而言，丟失一部分數據和讓網站7 24小時提供服務，哪個更重要呢？ 對於絕大多數企業來講，失去數據就相當於失去商機，失去產品， ...
目錄
- 前言
- 1.備份數據的意義
- 2.使用mysqldump進行資料庫備份
- 3.mysqldump重要關鍵參數說明
- 4.不同引擎的mysqldump備份命令
- 5.利用SQL語句方式對錶進行導入導出
前言
1.備份數據的意義
運維工作的核心簡單概括起來就是兩件事：第一個是保護公司的數據，第二個是讓網站能夠7*24小時提供服務。
雖然這兩件事情都很重要，但是相比較而言，丟失一部分數據和讓網站7*24小時提供服務，哪個更重要呢？
對於絕大多數企業來講，失去數據就相當於失去商機，失去產品，失去客戶，甚至會造成公司倒閉，那麼，在所有的數據中，最核心的數據又是哪些呢？這恐怕要屬資料庫中的數據了。既然資料庫中的數據地位這麼高，那麼資料庫備份與恢復的重要性就不言而喻了。
2.使用mysqldump進行資料庫備份
MySQL資料庫自帶了一個很優秀的備份命令，即mysqldump。
2.1.不帶參數備份單個資料庫
mysqldump oldboy >/opt/mysql_bak.sql
2.2.加-B參數備份
mysqldump -B oldboy >/opt/mysql_bak_B.sql #B參數的作用是增加創建資料庫和連接資料庫的語句，可同時備份多個庫。
2.3.使用gzip壓縮備份資料庫
mysqldump -B oldboy|gzip>/opt/mysql_bak_B.gz
2.4.mysqldump命令工作原理
利用mysqldump命令備份數據的過程，實際上就是把數據（包括庫表）從MySQL庫里以SQL語句的形式直接輸出或者生成備份文件的過程，這種備份成SQL語句的方式稱為邏輯備份。
使用mysqldump命令可以把資料庫中的數據導出來，並通過SQL語句的形式存儲。這種備份方式稱為邏輯備份，效率不是很高。在當下的生產場景中，多用於數據量不是很大的備份情況，例如30GB以內的數據。若在資料庫數據很大的時候採用此備份方法，則所用的時候就會很長，恢復的時間也會很長，因此，當數據大於30GB後，建議選擇其他的諸如Xtrabackup的物理方式進行備份和恢復。
2.5.備份多個庫
2.5.1.備份多個庫
mysqldump -B oldboy oldboy_utf8 mysql|gzip>/opt/all.sql.gz
2.5.2.如何做分庫備份
分庫備份實際上就是每次只執行一個mysqldump備份命令語句備份一個庫，如果資料庫里有多個庫，就執行多條相同的語句來備份各個庫。
mysqldump -uroot -p'oldboy123' -B oldboy |gzip >/tmp/oldboy.sql.gz
mysql -e "show databases;" |egrep -v "_schema|atabase" |sed -r 's#^(.*)#mysqldump -B \1 |gzip >/tmp/\1.sql.gz#g' |bash #bash執行這些備份命令，就是一次分開備份多個庫了
2.6.備份單個表
當不加-B參數備份資料庫時，例如“mysqldump oldboy test”，mysqldump命令預設就會把oldboy當作庫，把test當作表，如果後面還有多個字元串，例如“mysqldump oldboy test test1“，那麼除了oldboy為庫之外，其他的test、test1都是oldboy庫的表。
mysqldump oldboy test>/tmp/oldboy_test,sql
2.7.備份多個表
分表備份的缺點：數據文件多，很碎，一旦需要全部恢復又很麻煩。
1、做一個完整備份，再做一個分庫分表備份。
2、雖然文件多、碎，但可以利用腳本批量操作多個SQL文件。
mysqldump mysql user db>/tmp/mysql.sql
egrep -v "#|\*|--|^$" /tmp/mysql.sql
mysqldump oldboy test > /tmp/oldboy_test.sql
2.8.備份資料庫表結構（不包含數據）
利用mysqldump的-d參數可以只備份表的結構，即建表的語句。
mysqldump -d oldboy >/opt/oldboy.sql
2.9.只備份資料庫表的數據（不包含表結構）
利用-t參數備份資料庫表的數據（SQL語句形式）。
mysqldump -t oldboy >/opt/oldboy1.sql
2.10.同時將數據和表結構分離導出
利用-T參數可以實現將數據和表結構同時分離備份。
vi /etc/my.cnf
secure_file_priv='' #在[mysqld]模塊下增加
/etc/init.d/mysqld restart
mysqldump oldboy test --compact -T /tmp/
1、-d參數的作用是只備份庫表結構（SQL語句形式）。
2、-t參數的作用是只備份表內的數據（SQL語句形式）。
3、-T將庫表和數據分離成不同的文件，數據是純文本，表結構是SQL語句。
2.11.刷新binlog文件參數（-F）
2.11.1.binlog是什麼
binlog是一個二進位格式的文件，用於記錄用戶對資料庫更新的SQL語句信息，例如更改資料庫庫表和更改表內容的SQL語句都會記錄到binlog里，但是對庫表等內容的查詢則不會記錄到日誌中。
2.11.2.binlog對於備份的作用
當有數據寫入到資料庫時，還會同時把更新的SQL語句寫入到對應的binlog文件里。
使用mysqldump備份時，一般是對某一時刻的數據進行全備，例如，0點進行資料庫備份。
假設是每天0點對資料庫進行備份，那麼在兩次備份之間就有24小時的數據沒有備份，在這期間如果資料庫發生故障，使用mysqldump全量恢復也只能恢復到當日0點，但是有了binlog文件，就可以將兩次完整備份間隔之間的數據還原，因為binlog文件里的數據就是寫入資料庫的數據，使用binlog文件恢複數據，我們稱之為二進位增量數據恢復。
2.11.3.為什麼要刷新binlog
刷新（切割）binlog日誌的目的就是確定全備和增量備（binlog文件）的臨界點，當全備完成後，全備時刻以前的binlog文件就無用了（全備里已有這部分數據了），但是全備以後到下一次全備之前的數據就是十分重要的，這部分數據就存在於binlog文件里，因此在進行全備時需要找到全備之後和binlog增量之間的臨界點，使得恢復時，需要的binlog文件數據一條不多（不能和全備的內容重合），一條不少（全備後的所有數據都要有）。
2.11.4.如何開啟binlog功能
binlog文件生效需要一個參數：log_bin，編輯配置文件增加log_bin參數即可。
vi /etc/my.cnf
添加log_bin=mysql-bin到mysqld模塊下
2.11.5.使用-F刷新binlog日誌
使用-F將會從備份後的時刻起重新記錄binlog日誌文件，將來增量恢復從新的binlog日誌文件開始即可。
例如，早晨10點丟失數據需要恢複數據。
1、將0點時刻備份的全備數據還原到資料庫，這個時候數據就恢復到了當日0點。
2、0點到10點丟失的數據，就要從全備後當天的所有binlog里恢復，而使用-F切割日誌，就是找到0點這個時刻全備和binlog接縫的起始binlog文件。
mysqldump -F -B oldboy|gzip >/opt/bak_$(date +%F).sql.gz
2.12.記錄binlog位置的特殊參數（--master-data）
mysqldump里提供了一個參數，使得管理員不用刷新binlog，也可以在備份的文件對應的SQL語句里會添加change mastar語句及binlog文件及位置點信息。
當“--master-data=1”時，備份結果為可執行的“change master...”語句；當“--master-data=2”時，備份結果為註釋的“--change master...” 語句，“--”在SQL語句里為註釋的意思。“--master-data“ 參數除了確定增量恢復和全備之間的臨界點之外，進行主從複製時的作用更大。
mysqldump --master-data=1 oldboy --compact|head -1
mysqldump --master-data=2 oldboy --compact|head -1
2.13.鎖定所有表備份（-x參數）
使用mysqldump命令備份期間，資料庫依然是可以寫入數據的，此時，備份的數據就不是某一個時刻的一致性備份了，因此也會在恢復時造成困擾。例如備份時使用-x鎖表備份就會取得0點時刻的完整備份，即在0點時刻停止所有寫入操作，然後導出備份數據，備份完畢，放開寫入。
如果不鎖表進行備份，就會無法得到某一個時刻的完整備份，由於是一邊備份數據一邊寫入數據，因此最後備份的數據很可能就是一個時間段的數據，而非某一個時刻的一致性全備。
2.14.innodb表特有的備份參數（--single-transaction）
當使用mysqldump的“--single-transaction”對innodb表進行備份時，會開啟一個事務，並將整個備份過程放到一個事務里，以確保執行本次dump會話時，不會看到其他連接會話已經提交了的數據，即備份開始時刻的數據是什麼樣，備份出來就是什麼樣子。這也相當於是鎖表之後備份的數據，但是這個參數是允許在備份期間寫入數據的，而不是在使用“-x”參數鎖表之後，備份期間無法寫入任何數據。
3.mysqldump重要關鍵參數說明
| mysqldump重要參數 | 參數說明 |
|---|---|
| -B，--databases | 會在備份的數據中增加建庫（create）及“use庫”的語句，可以直接接多個庫名，同時備份多個庫 |
| -A，--all-databases | 備份所有的資料庫 |
| -d，--no-data | 只備份庫表結構（SQL語句形式），沒有行數據 |
| -t，--no-create-info | 只備份表內行數據（SQL語句形式），沒有表結構 |
| -T，--tab=name | 將庫表和數據分離成不同的文件，行數據是純文本，表結構是SQL語句 |
| -F，--flush-logs | 刷新binlog日誌，生產新binlog文件，將來增量恢復從這個新binlog文件開始，當備份多個庫時，每個庫都會刷新一次binlog，如果想只刷新一次binlog，可加“--lock-all-tables”或“--master-data”參數 |
| --master-data={1|2} | 在備份結果中增加binlog日誌文件名及對應的binlog位置點（即change master...語句）。值為1時是不註釋狀態，值為2時是註釋狀態，該參數執行時會打開“--lock-all-tables”功能，除非有“--single-transaction”存在，使用該參數時會關閉“--lock-tables”功能 |
| -x，--lock-all-tables | 備份時對所有資料庫的表執行全局讀鎖，期間同時禁止“--single-transaction”和“--lock-tables”參數功能 |
| -l，--lock-tables | 鎖定所有的表為只讀 |
| --single-transaction | 在備份innodb引擎數據表時，通常會啟用該選項來獲取一個一致性的數據快照備份，它的工作原理是設定本次備份會話的隔離級別為repeatable read，並將整個備份放在一個事務里，以確保執行本次dump會話時，不會看到其他連接會話已經提交了的數據，即備份開始時刻的數據是什麼樣，備份出來就是什麼樣子！也就相當於鎖表備份數據，但是這個參數是允許在備份期間寫入數據的，而不是-x鎖表後的備份期間無法寫入任何數據，啟用該參數會關閉“--lock-tables” |
| -R，--routines | 備份存儲過程和函數數據 |
| --triggers | 備份觸發器數據 |
| --compact | 只顯示很少的有用輸出，適合學習和測試環境調試用 |
4.不同引擎的mysqldump備份命令
4.1.innodb引擎的備份命令
“--single-transaction”是innodb引擎專有的備份參數，優勢就是備份期間數據依然可以執行寫操作。
mysqldump -A -B --master-data=2 --single-transaction |gzip >/opt/all.sql.gz
4.2.適合多引擎混合的備份命令
使用“--master-data”會自動開啟-x鎖表參數功能，在備份期間會影響數據寫入。
mysqldump -A -B --master-data=2 |gzip >/opt/all_$(date +%F).sql.gz
5.利用SQL語句方式對錶進行導入導出
5.1.導出表
5.1.1.導出表SQL語句語法
| export options | 說明 |
|---|---|
| character set utf8 | 導出時設置字元為utf8，預設和庫字元集一致 |
| fields terminated by '-' | 導出時設置不同的域分隔符為“-”，預設是tab |
| field enclosed by ""; | 導出時設置對欄位內容的引用符號，這裡設置為“”，預設為空 |
| lines starting by '=' | 導出時每行行首設置等號，預設為空 |
| lines termibated by'=' | 導出時每行行尾的結束符設置為“=”，預設是回車符 |
5.1.2.導出表
use oldboy
select * from test;
select * from test into outfile "/tmp/oldboy_test1.txt"; #將test表裡的數據導出為純文本
system cat /tmp/oldboy_test.txt;
select * from test into outfile "/tmp/oldboy_test2.txt" character set utf8; #導出時設置字元集
select * from test into outfile "/tmp/oldboy_test3.txt" fields terminated by '-'; #指定分隔符導出
select * from test into outfile "/tmp/oldboy_test4.txt" fields enclosed by ""; #導出時設置對欄位內容進行引用
5.2.導入表
5.2.1.導入表命令語法
| import options | 說明 |
|---|---|
| character set utf8 | 導入時設置字元集為utf8，預設和庫字元集一致 |
| fields terminated by '-' | 不同域之間的分隔符導入，這裡是“-”，預設是tab |
| fields enclosed by ""; | 根據欄位內容的引用符號導入，這裡是引號，預設為空 |
| lines starting by '=' | 導入時在每行行首設置等號，預設為空 |
| lines terminated by '=' | 導入時將每行行尾的結束符設置為“=”，預設是回車符 |
| ignore number lines | 不導入文件的前N行 |
5.2.2.導入表
delete from test;
system cat /tmp/oldboy_test1.txt;
load data infile '/tmp/oldboy_test1.txt' into table test; #預設方式將數據導入資料庫
select * from test;
system cat /tmp/oldboy_test3.txt;
load data infile '/tmp/oldboy_test3.txt' into table test fields terminated by '-'; #指定分隔符方式導入資料庫
select * from test;
system cat /tmp/oldboy_test4.txt
load data infile '/tmp/oldboy_test4.txt' into table test fields enclosed by ""; #雙引號引用數據導入資料庫
