數(shù)據(jù)備份類型
- 全量備份：備份整個數(shù)據(jù)庫
- 增量備份：備份自上一次備份以來（增量或完全）以來變化的數(shù)據(jù)
- 差異備份：備份自上一次完全備份以來變化的數(shù)據(jù)
全量備份
全量備份的方法有 2 種摇邦，一種是利用數(shù)據(jù)庫管理工具提供的備份恢復和導入導出功能恤煞。
例如：如果使用 Navicat、PHPMyAdmin 之類的可視化工具施籍，可以直接點擊轉(zhuǎn)儲 SQL 文件居扒，或者導出 SQL 文件之類的功能。
另一種是利用 mysqldump丑慎。
導出：
sudo mysqldump -u root -p student > dir/student_backup.sql
導入：
sudo mysqldump-u root -p student < dir/student_backup.sql
source student_backup.sql #要在數(shù)據(jù)庫操作 use student 之后
增量備份
增量備份的 binlog 是一個二進制格式的文件喜喂，用于記錄用戶對數(shù)據(jù)庫更新的 SQL 語句信息，例如更改數(shù)據(jù)庫表和更改內(nèi)容的 SQL 語句都會記錄到 binlog 里竿裂，但是對庫表等內(nèi)容的查詢不會記錄玉吁。
在配置文件中，修改配置打開 binlog腻异。通過 show variables like '%log_bin%'; 查看 binlog 是否打開进副。
可以看到默認是沒有打開的。
默認的配置文件可能在 /etc/mysql/my.cnf悔常，如果是使用 XAMPP 等一鍵安裝的影斑，也可能在 /opt/lampp/etc/my.cnf 等位置。
在配置文件找到 log_bin 所在的位置机打，取消這一行的注釋矫户。
重啟服務以后，可以看到啟用了 binlog残邀。
binlog 的使用格式
show binary logs;
show binlog events in 'mysql-bin.000001';
GTID 的全稱是Global Transaction Identifier皆辽，也就是全局事務ID，是一個事務在提交的時候生成的罐旗，是這個事務的唯一標識膳汪。它由兩部分組成唯蝶，格式是：``gtid=server-uuid:gno`九秀。
server_uuid 是一個實例第一次啟動時自動生成的，是一個全局唯一的值粘我。
gno 是一個整數(shù)鼓蜒，初始值是 1，每次提交事務的時候分配給這個事務征字，并加 1都弹。
在 GTID 模式下，每個事務都會跟一個 GTID 一一對應匙姜。
這樣畅厢，每個MySQL 實例都維護了一個GTID 集合，用來對應“這個實例執(zhí)行過的所有事務”氮昧。
下面來測試一下 binlog框杜。
先導入測試數(shù)據(jù)浦楣，然后執(zhí)行以下語句。
update student set birth = 2019 where id = '100';
insert into student values(200, 'jxtxzzw', '男', 2019, '計算機系', '上海');
delete from student where id = 200;
查看 binlog咪辱。
通過以下兩條語句可以生成新的 binlog振劳。
flush logs;
show binary logs;
除了 flush logs;，重啟 MySQL 服務以及 mysqlbinlog 也可以生成新的 binlog油狂。
通過 binlog 恢復數(shù)據(jù)
構(gòu)造場景：
insert into student values(907,'李七','男',1991,'計算機系','上海');
insert into student values(908,'李八','男',1992,'音樂系','上海');
delete from student where id=907;//誤刪
delete from student where id=908;//誤刪
如何通過 binlog 恢復這兩條數(shù)據(jù)历恐？
通過查看 binlog 找到了誤刪的兩條數(shù)據(jù)。
mysqlbinlog --start-position=4183 --stop-position=4592 /opt/lampp/var/mysql/mysql-bin.000001 | mysql -u root -p
在上面這條語句中专筷，首先是設置了起點為 4183弱贼、終點為 4592，并指定了 binlog 的文件為 mysql-bin.000001磷蛹。
輸入管理員密碼之后哮洽，可以重新打開數(shù)據(jù)庫看一下是不是成功。
可以看到恢復成功弦聂。
如果想要刪除 binlog鸟辅，刪除 binlog 的方法是：
- 關閉 MYSQL 主從，關閉 binlog莺葫。
- 開啟 MYSQL 主從匪凉，設置 expire_logs_days。
- 手動清除 binlog 文件捺檬，PURGE MASTER LOGS to ‘mysqld-bin.00001’;(before ‘date’)再层。
- reset master。
練習 1
- 刪掉 student 庫堡纬，通過全量備份和 binlog 對其進行恢復聂受。
- 嘗試了解 binlog 的三種格式。
刪掉 student 庫的過程非常簡單烤镐，而通過全量備份恢復只需要導入即可蛋济，從略。
下面重點說一下從 binlog 恢復的過程炮叶。
需要特別說明的是碗旅，命令行下可能不允許使用 delete，這時候可以用 drop table 替換镜悉。
刪除所有數(shù)據(jù)以后再次打開數(shù)據(jù)庫祟辟，看到表已經(jīng)是空的了。
然后打開 binlog 看一眼侣肄，找到 start position 和 stop position旧困。
然后從起點位置到結(jié)束位置執(zhí)行一次恢復。
可以看到數(shù)據(jù)已經(jīng)恢復了。
binlog 的三種格式：
直接轉(zhuǎn)載 卜算 的《使用mysql的binlog恢復誤操作(update|delete)的數(shù)據(jù)》（https://blog.csdn.net/Aeroleo/article/details/77929917）中的內(nèi)容：
MYSQL binlog復制主要有三種方式：基于SQL語句的復制(statement-based replication, SBR)吼具，基于行的復制(row-based replication, RBR)被芳，混合模式復制(mixed-based replication, MBR)。對應的馍悟，binlog的格式也有三種：STATEMENT畔濒，ROW，MIXED锣咒。
① STATEMENT模式（SBR）
每一條會修改數(shù)據(jù)的sql語句會記錄到binlog中侵状。優(yōu)點是并不需要記錄每一條sql語句和每一行的數(shù)據(jù)變化，減少了binlog日志量毅整，節(jié)約IO趣兄，提高性能。缺點是在某些情況下會導致master-slave中的數(shù)據(jù)不一致(如sleep()函數(shù)悼嫉， last_insert_id()艇潭，以及user-defined functions(udf)等會出現(xiàn)問題)
② ROW模式（RBR）
不記錄每條sql語句的上下文信息，僅需記錄哪條數(shù)據(jù)被修改了戏蔑，修改成什么樣了蹋凝。而且不會出現(xiàn)某些特定情況下的存儲過程、或function总棵、或trigger的調(diào)用和觸發(fā)無法被正確復制的問題鳍寂。缺點是會產(chǎn)生大量的日志，尤其是alter table的時候會讓日志暴漲情龄。
③ MIXED模式（MBR）
以上兩種模式的混合使用迄汛，一般的復制使用STATEMENT模式保存binlog，對于STATEMENT模式無法復制的操作使用ROW模式保存binlog骤视，MySQL會根據(jù)執(zhí)行的SQL語句選擇日志保存方式鞍爱。
MySQL 主備配置
在主庫上創(chuàng)建用戶 repl，并給他權(quán)限专酗。
CREATE USER repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';
檢查在主庫 my.cnf 中配置 server-id睹逃。發(fā)現(xiàn)已經(jīng)配置了。
然后進配置文件把所有 bind-address=127.0.0.1 的配置行注釋掉笼裳。
在主庫執(zhí)行 show master status; 記錄 binlog 和 pos唯卖。
打開 Ubuntu-Server（從庫）粱玲，修改配置文件（/opt/lampp/etc/my.cnf）中的 server-id 為 2躬柬，重啟 MySQL 服務。
進入數(shù)據(jù)庫抽减，執(zhí)行：
change master to master_host='主庫IP（這里是192.168.23.129）', master_user='主庫用戶（這里是repl）', master_password='主庫用戶密碼（這里是123456）', master_log_file="主庫的binlog（這里是mysql-bin.000003）", master_log_pos=主庫的binlog的pos（這里是327）;
start slave;
show slave status\G;
然后在 status 可以看到 slave 的狀態(tài)是 YES允青。
測試主備。在主庫添加一條記錄卵沉，然后在從庫看一下是不是有這條記錄颠锉。
主庫成功添加了這條記錄法牲。
從庫也出現(xiàn)了這條記錄。
特別需要指出的是琼掠，從庫和主庫的同步只能是從同步開始設置的那一刻之后的操作才能同步拒垃。
舉個例子，主庫有 100瓷蛙、200悼瓮、300 這三個用戶，從庫有 200艰猬、300横堡、500 這三個用戶：
- 在主庫添加 400 號用戶，則從庫自動添加 400 號用戶
- 在主庫刪除 300 號用戶冠桃，則從庫自動刪除 300 號用戶
- 在主庫將 200 號用戶的編號修改為 233 號命贴，則從庫自動將 200 號用戶的編號設置為 233。
- 在主庫修改 100 號用戶的編號修改為 101食听，從庫沒有響應胸蛛。
- 在主庫修改 100 號用戶的編號修改為 500，進一步修改姓名為張三樱报，由于在第一步操作之后胚泌，主庫的 100 號用戶的編號已經(jīng)修改為 500 了，之后如果用主鍵來判斷修改了哪一條記錄肃弟，那么將會是“把 500 號用戶的姓名修改為張三”玷室，于是，這一修改會體現(xiàn)在從庫上笤受，從庫的 500 號用戶也被改成了張三穷缤。
因此，需要說明的是箩兽，如果從庫一開始就沒有 student 這個庫津肛，或者沒有表，那么，主從備份是不會起作用的。
MySQL 雙主結(jié)構(gòu)
目的：A 和 B 雙向同步芒篷。
剛才的是主從備份岸裙，只有主機的修改會被同步到從機，從機的修改不會被同步到主機窍帝。
修改兩邊的配置文件。
在 A 和 B 重復上面主從備份的時候創(chuàng)建 repl 用戶的過程，并賦予權(quán)限涯鲁。
create user repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@‘%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
測試是否可以連接。
自己連自己、自己連對方抹腿，都可以正常訪問岛请。
之后的步驟與主從備份是類似的，只是相當于對兩臺主機都做了一次主從備份警绩，互相做對方的從機崇败。
于是，需要先查看 binlog 的 index 和 pos肩祥。
這是在 A 查到的結(jié)果僚匆，A 的 IP 是 192.168.23.129。
這是在 B 查到的結(jié)果搭幻， B 的 IP 是 192.168.23.128咧擂。
注意這個操作需要在 root 權(quán)限下運行，repl 運行不了檀蹋。
然后在 A 執(zhí)行：
change master to master_host='192.168.23.128', master_user='repl', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=313;
類似的松申，在 B 運行：
change master to master_host='192.168.23.129', master_user='repl', master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=327;
之后分別在 A 和 B 上通過 start slave 來運行。
通過 show slave status 可以看到 IO 和 SQL 都是 Running 的俯逾。
測試贸桶。
INSERT INTO `student` (`id`, `name`, `sex`, `birth`, `department`, `address`) VALUES ('11', 'Added From Desktop', NULL, NULL, NULL, NULL);
之后可以看到 Server 版的數(shù)據(jù)庫出現(xiàn)了這一記錄。
在 Server 版修改桌肴。
UPDATE `student` SET `address` = 'Modified From Server' WHERE `student`.`id` = 11;
可以看到雙向同步了皇筛。
主備延遲
最后需要說明的是，主備之間存在一個延遲坠七。
- 主庫 A 執(zhí)行完成一個事務水醋，寫入 binlog，我們把這個時間記為 T1彪置。
- 之后傳給備庫 B拄踪，我們把備庫 B 接收完這個 binlog 的時刻記為 T2。
- 備庫 B 執(zhí)行完成這個事務拳魁，我們把這個時刻記為 T3惶桐。
主備延遲即 T3 - T1 的差。
可以在備庫上執(zhí)行 show slave status 查看 seconds_behind_master潘懊。
但是在我們的測試中姚糊，幾乎所有的主備延遲都是 0。
這是因為授舟，主備延遲的來源有：
- 備庫的性能更差
- 備庫壓力較大
- 大事務 必須執(zhí)行完才會寫入 binlog救恨，然后傳給備庫
在試驗中并沒有遇到這樣的情況。
當然可以手動構(gòu)造大量的數(shù)據(jù)來做個測試岂却。
練習 2
嘗試配置MySQL一主一備及雙主結(jié)構(gòu)忿薇。
上文已詳述裙椭。
