mysql資料庫備份有多麼重要已不需過多贅述了，廢話不多說！以下總結了mysql資料庫的幾種備份方案：
一、binlog二進位制日誌通常作為備份的重要資源，所以再說備份方案之前先總結一下binlog日誌~~
1.binlog日誌內容
1）引起mysql伺服器改變的任何操作。
2）複製功能依賴於此日誌。
3）slave伺服器通過複製master伺服器的二進位制日誌完成主從複製，在執行之前儲存於中繼日誌(relay log)中。
4）slave伺服器通常可以關閉二進位制日誌以提升效能。
2.binlog日誌檔案的檔案表現形式
1）預設在安裝目錄下，存在mysql-bin.00001, mysql-bin.00002的二進位制檔案（binlog日誌檔名依據my.cnf配置中的log-bin引數後面的設定為準）
2）還有mysql-bin.index用來記錄被mysql管理的二進位制檔案列表
3）如果需要刪除二進位制日誌時，切勿直接刪除二進位制檔案，這樣會使得mysql管理混亂。
3.binlog日誌檔案檢視相關mysql命令
1）SHOW MASTER STATUS ; 檢視正在使用的二進位制檔案
MariaDB [(none)]> SHOW MASTER STATUS ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 245 | | |
+------------------+----------+--------------+------------------+
2）FLUSH LOGS; 手動滾動二進位制日誌
MariaDB [(none)]> FLUSH LOGS;
MariaDB [(none)]> SHOW MASTER STATUS ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 245 | | |
+------------------+----------+--------------+------------------+
滾動以後，mysql重新建立一個新的日誌mysql-bin.000004
3）SHOW BINARY LOGS 顯示使用過的二進位制日誌檔案
MariaDB [(none)]> SHOW BINARY LOGS ;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30373 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 288 |
| mysql-bin.000004 | 245 |
4）SHOW BINLOG EVENTS 以表的形式檢視二進位制檔案
命令格式：SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' \G;
*************************** 99. row ***************************
Log_name: mysql-bin.000001
Pos: 30225
Event_type: Query
Server_id: 1
End_log_pos: 30354
Info: use `mysql`; DROP TEMPORARY TABLE `tmp_proxies_priv` /* generated by server */
4.MySQL二進位制檔案讀取工具mysqlbinlog
命令格式：mysqlbinlog [引數] log-files
有以下四種引數選擇：
--start-datetime
--stop-datetime
--start-position
--stop-position
[root@test-huanqiu ~]# mysqlbinlog --start-position 30225 --stop-position 30254 mysql-bin.000001
擷取一下結果：
# at 30225
#151130 12:43:35 server id 1 end_log_pos 30354 Querythread_id=1exec_time=0error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1448858615/*!*/;
SET @@session.pseudo_thread_id=1/*!*/
根據以上擷取結果第二行，進行解釋二進位制日誌內容
1）時間點： 151130 12:43:35
2）伺服器ID： server id 1
伺服器ID主要用於標記日誌產生的伺服器，主要用於雙主模型中，互為主從，確保二進位制檔案不會被相互迴圈複製
3）記錄型別： Query
4) 執行緒號： thread_id = 1
5) 語句的時間戳和寫入二進位制日誌檔案的時間差； exec_time=0
6) 事件內容
7）事件位置 #at 30225
8) 錯誤程式碼 error_code=0
9) 事件結束位置 end_log_pos也就是下一事件開始的位置
5.二進位制日誌格式
由bin_log_format={statement|row|mixed}定義
1）statement: 基於語句，記錄生成資料的語句
缺點在於如果當時插入資訊為函式生成，有可能不同時間點執行結果不一樣，
例如： INSERT INTO t1 VALUE (CURRENT_DATE());
2）row: 基於行資料
缺點在於，有時候資料量會過大
3）mixed： 混合模式，又mysql自行決定何時使用statement, 何時使用row 模式
6.二進位制相關引數總結
1）log_bin = {ON|OFF}
還可以是個檔案路徑，自定義binlog日誌檔名，使用“log_bin=“或“log-bin=“都可以，主要用於控制全域性binlog的存放位置和是否開啟binlog日誌功能。
比如：log_bin=mysql-bin 或者 log-bin=mysql-bin，這樣binlog日誌預設會和mysql資料放在同一目錄下。
2） log_bin_trust_function_creators
是否記錄在
3） sql_log_bin = {ON|OFF}
會話級別是否關閉binlog， 如果關閉當前會話內的操作將不會記錄
4） sync_binlog 是否馬上同步事務類操作到二進位制日誌中
5） binlog_format = {statement|row|mixed} 二進位制日誌的格式，上面單獨提到了
6） max_binlog_cache_size =
二進位制日誌緩衝空間大小，僅用於緩衝事務類的語句；
7） max_binlog_stmt_cache_size =
語句緩衝，非事務類和事務類共用的空間大小
8） max_binlog_size =
二進位制日誌檔案上限，超過上限後則滾動
9) 刪除二進位制日誌
命令格式：PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
MariaDB> PURGE BINARY LOGS TO 'mysql-bin.010';
MariaDB> PURGE BINARY LOGS BEFORE '2016-11-02 22:46:26';
建議：切勿將二進位制日誌與資料檔案放在一同裝置；可以將binlog日誌實時備份到遠端裝置上，以防出現機器故障進行資料恢復；
二、接下來說下binlog二進位制日誌備份和恢復
1.為什麼做備份：
（1）災難恢復
（2）審計，資料庫在過去某一個時間點是什麼樣的
（3）測試
2.備份的目的：
（1）用於恢復資料
（2）備份結束後，需要週期性的做恢復測試
3.備份型別：
（1）根據備份時，伺服器是否線上
1）冷備(cold backup)： 伺服器離線，讀寫操作都不能進行
2）溫備份： 全域性施加共享鎖，只能讀不能寫
3）熱備(hot backup)：資料庫線上，讀寫照樣進行
（2）根據備份時的資料集分類
1）完全備份(full backup)
2）部分備份(partial backup)
（3）根據備份時的介面
1）物理備份（physical backup）：直接複製資料檔案 ，打包歸檔
特點：
不需要額外工具，直接歸檔命令即可，但是跨平臺能力比較差；如果資料量超過幾十個G，則適用於物理備份
2）邏輯備份(logical backup)： 把資料抽取出來儲存在sql指令碼中
特點：
可以使用文字編輯器編輯；匯入方便，直接讀取sql語句即可；邏輯備份恢復時間慢，佔據空間大；無法保證浮點數的精度；恢復完資料庫後需要重建索引。
（4）根據備份整個資料還是變化資料
1） 全量備份 full backup
2） 增量備份 incremental backup
在不同時間點起始備份一段資料，比較節約空間；針對的是上一次備份後有變化的資料，備份資料少，備份快，恢復慢
3） 差異備份 differential backup
備份從每個時間點到上一次全部備份之間的資料，隨著時間增多二增多；比較容易恢復；對於很大的資料庫，可以考慮主從模型，備份從伺服器的內容。針對的是上一次全量備份後有變化的資料，備份資料多，備份慢，恢復快。
（5）備份策略，需要考慮因素如下
備份方式
備份實踐
備份成本
鎖時間
時長
效能開銷
恢復成本
恢復時長
所能夠容忍丟失的資料量
（6）備份內容
1）資料庫中的資料
2）配置檔案
3）mysql中的程式碼： 儲存過程，儲存函式，觸發器
4）OS 相關的配置檔案，chrontab 中的備份策略指令碼
5）如果是主從複製的場景中： 跟複製相關的資訊
6）二進位制日誌檔案需要定期備份，一旦發現二進位制檔案出現問題，需馬上對資料進行完全備份
(7)Mysql最常用的三種備份工具：
1）mysqldump：
通常為小資料情況下的備份
innodb： 熱備，溫備
MyISAM, Aria: 溫備
單執行緒備份恢復比較慢
2）Xtrabackup（通常用innobackupex工具）：
備份mysql大資料
InnoDB熱備，增量備份；
MyISAM溫備，不支援增量，只有完全備份
屬於物理備份，速度快；
3）lvm-snapshot：
接近於熱備的工具：因為要先請求全域性鎖，而後建立快照，並在建立快照完成後釋放全域性鎖；
使用cp、tar等工具進行物理備份；
備份和恢復速度較快；
很難實現增量備份，並且請求全域性需要等待一段時間，在繁忙的伺服器上尤其如此；
除此之外，還有其他的幾個備份工具：
-->mysqldumper: 多執行緒的mysqldump
-->SELECT clause INTO OUTFILE '/path/to/somefile' LOAD DATA INFILE '/path/from/somefile'
部分備份工具， 不會備份關係定義，僅備份表中的資料；
邏輯備份工具，快於mysqldump，因為不備份表格式資訊
-->mysqlhotcopy: 接近冷備，基本沒用
mysqldump工具基本使用
1. mysqldump [OPTIONS] database [tables…]
還原時庫必須存在，不存在需要手動建立
--all-databases: 備份所有庫
--databases db1 db2 ...: 備份指定的多個庫，如果使用此命令，恢復時將不用手動建立庫。或者是-B db1 db2 db3 ....
--lock-all-tables：請求鎖定所有表之後再備份，對MyISAM、InnoDB、Aria做溫備
--lock-table: 對正在備份的表加鎖，但是不建議使用，如果其它表被修改，則備份後表與表之間將不同步
--single-transaction: 能夠對InnoDB儲存引擎實現熱備；
啟動一個很大的大事物，基於MOCC可以保證在事物內的表版本一致
自動加鎖不需要，再加--lock-table, 可以實現熱備
備份程式碼：
--events: 備份事件排程器程式碼
--routines: 備份儲存過程和儲存函式
--triggers：備份觸發器
備份時滾動日誌：
--flush-logs: 備份前、請求到鎖之後滾動日誌；
方恢復備份時間點以後的內容
複製時的同步位置標記：主從架構中的，主伺服器資料。效果相當於標記一個時間點。
--master-data=[0|1|2]
0: 不記錄
1：記錄為CHANGE MASTER語句
2：記錄為註釋的CHANGE MASTER語句
2. 使用mysqldump備份大體過程：
1) 請求鎖：--lock-all-tables或使用–singe-transaction進行innodb熱備；
2) 滾動日誌：--flush-logs
3) 選定要備份的庫：--databases
4) 記錄二進位制日誌檔案及位置：--master-data=
FLUSH TABLES5 WITH READ LOCK;
3. 恢復：
恢復過程無需寫到二進位制日誌中
建議：關閉二進位制日誌，關閉其它使用者連線；
4. 備份策略：基於mysqldump
備份：mysqldump+二進位制日誌檔案；（“mysqldump >”）
週日做一次完全備份：備份的同時滾動日誌
週一至週六：備份二進位制日誌；
恢復：（“mysql < ”）或在mysql資料庫中直接執行“source sql備份檔案;”進行恢復。如果sql執行語句比較多，可以將sql語句放在一個檔案內，將檔名命名為.sql結尾，然後在mysql資料庫中使用"source 檔案.sql;"命令進行執行即可！
完全備份+各二進位制日誌檔案中至此刻的事件
5. 例項說明：
參考：Mysql備份系列（2）--mysqldump備份（全量+增量）方案操作記錄
lvm-snapshot：基於LVM快照的備份
1.關於快照：
1）事務日誌跟資料檔案必須在同一個捲上；
2）剛剛創立的快照卷，裡面沒有任何資料，所有資料均來源於原卷
3）一旦原卷資料發生修改，修改的資料將複製到快照卷中，此時訪問資料一部分來自於快照卷，一部分來自於原卷
4）當快照使用過程中，如果修改的資料量大於快照卷容量，則會導致快照卷崩潰。
5）快照卷本身不是備份，只是提供一個時間一致性的訪問目錄。
2.基於快照備份幾乎為熱備：
1）建立快照卷之前，要請求MySQL的全域性鎖；在快照建立完成之後釋放鎖；
2）如果是Inoodb引擎， 當flush tables 後會有一部分儲存在事務日誌中，卻不在檔案中。 因此恢復時候，需要事務日誌和資料檔案
但釋放鎖以後，事務日誌的內容會同步資料檔案中，因此備份內容並不絕對是鎖釋放時刻的內容，由於有些為完成的事務已經完成，但在備份資料中因為沒完成而回滾。 因此需要藉助二進位制日誌往後走一段
3.基於快照備份注意事項：
1）事務日誌跟資料檔案必須在同一個捲上；
2）建立快照卷之前，要請求MySQL的全域性鎖；在快照建立完成之後釋放鎖；
3）請求全域性鎖完成之後，做一次日誌滾動；做二進位制日誌檔案及位置標記(手動進行)；
4.為什麼基於MySQL快照的備份很好？
原因如下幾點：
1）幾乎是熱備 在大多數情況下，可以在應用程式仍在執行的時候執行備份。無需關機，只需設定為只讀或者類似只讀的限制。
2）支援所有基於本地磁碟的儲存引擎 它支援MyISAM, Innodb, BDB，還支援 Solid, PrimeXT 和 Falcon。
3）快速備份 只需拷貝二進位制格式的檔案，在速度方面無以匹敵。
4）低開銷 只是檔案拷貝，因此對伺服器的開銷很細微。
5）容易保持完整性 想要壓縮備份檔案嗎？把它們備份到磁帶上，FTP或者網路備份軟體 -- 十分簡單，因為只需要拷貝檔案即可。
6）快速恢復 恢復的時間和標準的MySQL崩潰恢復或資料拷貝回去那麼快，甚至可能更快，將來會更快。
7）免費 無需額外的商業軟體，只需Innodb熱備工具來執行備份。
快照備份mysql的缺點：
1）需要相容快照 -- 這是明顯的。
2）需要超級使用者(root) 在某些組織，DBA和系統管理員來自不同部門不同的人，因此許可權各不一樣。
3）停工時間無法預計，這個方法通常指熱備，但是誰也無法預料到底是不是熱備 -- FLUSH TABLES WITH READ LOCK 可能會需要執行很長時間才能完成。
4）多捲上的資料問題 如果你把日誌放在獨立的裝置上或者你的資料庫分佈在多個捲上，這就比較麻煩了，因為無法得到全部資料庫的一致性快照。不過有些系統可能能自動做到多卷快照。
5.備份與恢復的大體步驟
備份：
1）請求全域性鎖，並滾動日誌
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;
2）做二進位制日誌檔案及位置標記(手動進行)；
[root@test-huanqiu ~]# mysql -e 'show master status' > /path/to/orignal_volume
3）建立快照卷
[root@test-huanqiu ~]# lvcreate -L -s -n -p r /path/to/some_lv
4）釋放全域性鎖
5）掛載快照卷並備份
6）備份完成之後，刪除快照卷
恢復：
1）二進位制日誌儲存好；
提取備份之後的所有事件至某sql指令碼中；
2）還原資料，修改許可權及屬主屬組等，並啟動mysql
3）做即時點還原
4）生產環境下， 一次大型恢復後，需要馬上進行一次完全備份。
備份與恢復例項說明：
環境， 實現建立了一個test_vg卷組，裡面有個mylv1用來裝mysql資料，掛載到/data/mysqldata
備份例項：
1. 建立備份專用的使用者，授予許可權FLUSH LOGS 和 LOCK TABLES
MariaDB > GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'192.168.1.%' IDENTIFIED BY 'lvm';
MariaDB > FLUSH PRIVILEGES;
2. 記錄備份點
[root@test-huanqiu ~]# mysql -ulvm -h192.168.1.10 -plvm -e 'SHOW MASTER STATUS' > /tmp/backup_point.txt
3. 建立快照卷並掛載快照卷
[root@test-huanqiu ~]# lvcreate -L 1G -s -n lvmbackup -p r /dev/test_vg/mylv1
[root@test-huanqiu ~]# mount -t ext4 /dev/test_vg/lvmbackup /mnt/
4. 釋放鎖
[root@test-huanqiu ~]# mysql -ulvm -h192.168.98.10 -plvm -e 'UNLOCK TABLES'
做一些模擬寫入工作
MariaDB [test]> create database testdb2
5. 複製檔案
[root@test-huanqiu ~]# cp /data/mysqldata /tmp/backup_mysqldata -r
6. 備份完成解除安裝，刪除快照卷
[root@test-huanqiu ~]# umount /mnt
[root@test-huanqiu ~]# lvmremove /dev/test_vg/lvmbackup
還原例項：
假如整個mysql伺服器崩潰，並且目錄全部被刪除
1. 資料檔案複製回源目錄
[root@test-huanqiu ~]# cp -r /tmp/backup_mysqldata/* /data/mysqldata/
MariaDB [test]> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| mysqldata |
| openstack |
| performance_schema |
| test |
+--------------------+
此時還沒有testdb2， 因為這個是備份之後建立的，因此需要通過之前記錄的二進位制日誌
2. 檢視之前記錄的記錄點。向後還原
[root@test-huanqiu ~]# cat /tmp/backup_point.txt
FilePositionBinlog_Do_DBBinlog_Ignore_DB
mysql-bin.000001245
[root@test-huanqiu ~]# mysqlbinlog /data/binlog/mysql-bin.000001 --start-position 245 > tmp.sql
MariaDB [test]> source /data/mysqldata/tmp.sql
MariaDB [test]> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| mysqldata |
| openstack |
| performance_schema |
| test |
| testdb2 |
+--------------------+
8 rows in set (0.00 sec)
testdb2 已經被還原回來。
具體例項說明，參考：Mysql備份系列（4）--lvm-snapshot備份mysql資料(全量+增量）操作記錄
使用Xtrabackup進行MySQL備份：
參考：Mysql備份系列（3）--innobackupex備份mysql大資料(全量+增量）操作記錄
--------------------------------------------------------------------------------------
關於備份和恢復的幾點經驗之談
備份注意：
1. 將資料和備份放在不同的磁碟裝置上；異機或異地備份儲存較為理想；
2. 備份的資料應該週期性地進行還原測試；
3. 每次災難恢復後都應該立即做一次完全備份；
4. 針對不同規模或級別的資料量，要定製好備份策略；
5. 二進位制日誌應該跟資料檔案在不同磁碟上，並週期性地備份好二進位制日誌檔案；
從備份中恢復應該遵循步驟：
1. 停止MySQL伺服器；
2. 記錄伺服器的配置和檔案許可權；
3. 將資料從備份移到MySQL資料目錄；其執行方式依賴於工具；
4. 改變配置和檔案許可權；
5. 以限制訪問模式重啟伺服器；mysqld的--skip-networking選項可跳過網路功能；
方法：編輯my.cnf配置檔案，新增如下項：
skip-networking
socket=/tmp/mysql-recovery.sock
6. 載入邏輯備份（如果有）；而後檢查和重放二進位制日誌；
7. 檢查已經還原的資料；
8. 重新以完全訪問模式重啟伺服器；
註釋前面第5步中在my.cnf中新增的選項，並重啟；