文中多數(shù)參考網(wǎng)友喘沿，恢復未測試其他均已測試暑劝。
如需完整版內(nèi)容留下郵箱我給你發(fā)
Mysql數(shù)據(jù)庫異地備份
一铜邮、 簡介
1.1 增量備份簡介
增量備份是指在一次全備份或上一次增量備份后，以后每次的備份只需備份與前一次相比增加或者被修改的文件酱虎。這就意味著，第一次增量備份的對象是進行全備后所產(chǎn)生的增加和修改的文件擂涛；第二次增量備份的對象是進行第一次增量備份后所產(chǎn)生的增加和修改的文件读串，如此類推聊记。這種備份方式最顯著的優(yōu)點就是：沒有重復的備份數(shù)據(jù)，因此備份的數(shù)據(jù)量不大恢暖，備份所需的時間很短排监。但增量備份的數(shù)據(jù)恢復是比較麻煩的。您必須具有上一次全備份和所有增量備份磁帶（一旦丟失或損壞其中的一個增量杰捂，就會造成恢復的失斢叽病），并且它們必須沿著從全備份到依次增量備份的時間順序逐個反推恢復嫁佳，因此這就極大地延長了恢復時間挨队。
假如我們有一個數(shù)據(jù)庫，有20G的數(shù)據(jù)蒿往，每天會增加10M的數(shù)據(jù)盛垦，數(shù)據(jù)庫每天都要全量備份一次，這樣的話服務器的壓力比較大瓤漏，因此我們只需要備份增加的這部分數(shù)據(jù)腾夯，這樣減少服務器的負擔。
1.2 binlog簡介
binlog日志由配置文件的log-bin選項負責啟用蔬充，Mysql服務器將在數(shù)據(jù)根目錄創(chuàng)建兩個新文 件XXX-bin.001和xxx-bin.index蝶俱，若配置選項沒有給出文件名，Mysql將使用主機名稱命名這兩個文件饥漫，其中.index文件包含一份全體日志文件的清單榨呆。 Mysql會把用戶對所有數(shù)據(jù)庫的內(nèi)容和結(jié)構(gòu)的修改情況記入XXX-bin.n文件，而不會記錄 SELECT和沒有實際更新的UPDATE語句趾浅。
當MySQL數(shù)據(jù)庫停止或重啟時愕提，服務器會把日志文件記入下一個日志文件，Mysql會在重啟時生成一個新的binlog日志文件皿哨，文件序號遞增浅侨，此外，如果日志文件超過max_binlog_size系統(tǒng)變量配置的上限時证膨，也會生成新的日志文件如输。
二、 mysql的binlog操作
2.1開啟
修改mysql的my.cnf(Linux)或者my.ini(Windows)文件
圖 1 mysqld配置
[mysqld]
skip-grant-tables
lower_case_table_names = 1 #set case insensitivity
log-bin = logbin.log #log-bin若不顯示制定存儲目錄,則默認存儲在mysql的data目錄下
log-bin-index = logindex
expire_logs_days = 7#日志保留天數(shù)為七天
server-id=1 #在設置log-bin的同時需要設置server-id,否則會報錯
binlog_format=row #row基于行的存儲,啟動后會產(chǎn)生mysql-bin.這樣的文件,每啟動一次就會增加一個
【存在的問題】在配置的過程中嘗試使用自定義路徑央勒，但是重啟mysql一直報錯不见，未找到原因，所以咱配置默認的目錄下崔步。
【補充】log-bin若不顯示指定存儲目錄稳吮，則默認存儲在mysql的data目錄下
binlog_format的幾種格式：（STATEMENT，ROW和MIXED）
STATEMENT：基于SQL語句的復制(statement-based replication, SBR)
ROW：基于行的復制(row-based replication, RBR)
MIXED：混合模式復制(mixed-based replication, MBR)
啟動后會產(chǎn)生logbin.log.這樣的文件井濒，每啟動一次灶似，就會增加一個或者多個列林。
2.2 查看binlog的開啟情況
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/logbin |
| log_bin_index | /var/lib/mysql/logindex.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------------------------------+
6 rows in set (0,01 sec)
mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+-----------------------------------------+----------------------+
20 ows in set (0,00 sec)
2.3 查看binlog中的內(nèi)容
root@yyd-Lenovo:/var/lib/mysql# mysqlbinlog logbin.000001
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 4
171026 15:41:32 server id 1 end_log_pos 123 CRC32 0x8b1b5abe Start: binlog v 4, server v 5.7.20-0ubuntu0.16.04.1-log created 171026 15:41:32 at startup
ROLLBACK/!/;
BINLOG '
rJHxWQ8BAAAAdwAAAHsAAAAAAAQANS43LjIwLTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACskfFZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Ab5aG4s=
'/!/;
at 123
171026 15:41:32 server id 1 end_log_pos 154 CRC32 0xbfc1fab3 Previous-GTIDs
[empty]
at 154
171026 15:41:39 server id 1 end_log_pos 177 CRC32 0x6d390fdc Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog / /!*/;
DELIMITER ;
End of log file
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
信息描述：
Position:位于文件中的位置，即第一行的（#at 4）和第二行的（log_pos 4）酪惭，說明該事件記錄從文件第4字節(jié)開始希痴。
Timestamp:事件發(fā)生的時間戳，即第二行的（#070813 14:16:36）
Exec_time:事件的執(zhí)行花費時間
Error_code:錯誤碼
Type 事件類型：
Master ID:創(chuàng)建二進制事件的主機服務器ID
Master Pos:事件在原始二進制文件中的位置
Flags:標志信息
2.4 數(shù)據(jù)庫的常用操作
? mysql> show master logs; #查看數(shù)據(jù)庫所有日志文件春感。
? mysql> show binlog events \g; #查看當前使用的binlog文件信息砌创。
? mysql> show binlog events in 'mysql-bin.000016'; #查看指定的binlog文件信息。
? mysql> flush logs; #將內(nèi)存中l(wèi)og日志寫磁盤鲫懒，保存在當前binlog文件中嫩实，并產(chǎn)生一個新的binlog日志文件。
? mysql> flush logs; reset master; #刪除所有二進制日志刀疙，并重新（mysql-bin.000001）開始記錄舶赔。
三、 腳本編寫
3.1 全量備份腳本
運行source databak.sh谦秧，進行全量備份竟纳，其中bucket_water_db為數(shù)據(jù)庫的名稱，/opt/mysql_log_data為數(shù)據(jù)庫的備份路徑疚鲤，’date+%Y%m%d’.sql為備份的數(shù)據(jù)庫腳本的名稱锥累。后面的兩行是定期刪除備份內(nèi)容的實現(xiàn)，實際中可以刪除集歇。
圖 2 全量備份腳本內(nèi)容
圖 3 腳本文件存放位置
3.2 增量備份腳本
運行圖 3 腳本文件存放位置中的binlogbak.sh的腳本桶略，source binlogbak.sh。其中BakDir的位置設置為本地要存儲的位置诲宇，BinDir的位置設置為數(shù)據(jù)庫數(shù)據(jù)存放的文件（logbin文件的存放路徑）际歼，LogFile為binlog.log存放的位置+文件名，BinFile為index文件的絕對路徑姑蓝。在修改時只修改這四個參數(shù)就可以鹅心。
圖 4 增量備份腳本
四、 定時器開啟
在終端輸入命令：vi /etc/crontab
在最后一行添加
00 00 * * * /home/yyd/data_backup/binlogbak.sh >/dev/null 2>&1
保證每天的0點0分執(zhí)行腳本纺荧，【00 00 * * *】分別對應分 時 周 月 年旭愧，【/home/yyd/data_backup/binlogbak.sh】為腳本存放的絕對路徑。
圖 5 定時器配置
輸入以下命令使定時器生效宙暇。
crontab /etc/crontab
查看定時器是否生效
crontab –l
五输枯、 數(shù)據(jù)庫的還原
5.1 全量數(shù)據(jù)的恢復
方法1：進入數(shù)據(jù)庫，通過source
- mysql> use backup_full;
- mysql> source /tmp/backup_full.sql;
- mysql> select * from backup_full.full;
- +-----------+----------+
- | c1 | c2 |
- +-----------+-----------+
- | 1 | full1 |
- | 2 | full2 |
- | 3 | full3 |
- | 4 | full4 |
- | 5 | full5 |
- | 6 | full6 |
- | 7 | full7 |
- | 8 | full8 |
- | 9 | full9 |
- | 10 | full10 |
- +-----------+-----------+
全量數(shù)據(jù)恢復成功占贫。
5.2 直接還原數(shù)據(jù)文件
圖 6 數(shù)據(jù)恢復
圖 7 恢復第一個文件
以此類推全部回復完成
5.3 借助position來還原
那么如何借助position來還原操作呢桃熄？
假設我們現(xiàn)在向 testdb 數(shù)據(jù)庫下的表chinesetset中插入數(shù)據(jù)段('游戲','男','2017-4-3')，然后再刪除這一段數(shù)據(jù)型奥，再借助mysqlbinlog恢復到刪除之前的狀態(tài)~
插入數(shù)據(jù)段：
可見插入新的數(shù)據(jù)段在位置戳2285到2564之間蜻拨，現(xiàn)在我們刪掉('游戲','男','2017-4-3') ：
用mysqlbinlog恢復到刪除數(shù)據(jù)段之前：
mysqlbinlog --start-position = P1 --stop-position=P2 E:/hostname.00000x >E:/filename.sql
mysql -u root -pdatabase_name <E:/filename.sql
P1,P2為E:/hostname.00000x中位置戳的開始點和結(jié)束點池充，該語句就是將P1到P2之間的操作轉(zhuǎn)存為 E:/filename.sql 桩引，注意此時要指明logbin的文件路徑缎讼。實踐走一波：
最后要說的是，mysqlbinlog還有有很多用法坑匠，比如 ：利用時間戳血崭，利用xid等
