MySQL備份與主備配置
資料備份類型
- 全量備份：備份整個資料庫
- 增量備份：備份自上一次備份以來（增量或完全）以來變化的資料
- 差異備份：備份自上一次完全備份以來變化的資料
全量備份
全量備份的方法有 2 種，一種是利用資料庫管理工具提供的備份恢複和導入導出功能。
例如：如果使用 Navicat、PHPMyAdmin 之類的可視化工具，可以直接點選轉儲 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 #要在資料庫操作 use student 之後 增量備份
增量備份的 binlog 是一個二進制格式的檔案，用于記錄使用者對資料庫更新的 SQL 語句資訊，例如更改資料庫表和更改内容的 SQL 語句都會記錄到 binlog 裡，但是對庫表等内容的查詢不會記錄。
在配置檔案中，修改配置打開 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 是一個整數，初始值是 1，每次送出事務的時候配置設定給這個事務，并加 1。
在 GTID 模式下，每個事務都會跟一個 GTID 一一對應。
這樣，每個MySQL 執行個體都維護了一個GTID 集合，用來對應“這個執行個體執行過的所有事務”。
下面來測試一下 binlog。
先導入測試資料，然後執行以下語句。
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 恢複資料
構造場景：
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 恢複這兩條資料？
通過檢視 binlog 找到了誤删的兩條資料。
mysqlbinlog --start-position=4183 --stop-position=4592 /opt/lampp/var/mysql/mysql-bin.000001 | mysql -u root -p 在上面這條語句中，首先是設定了起點為 4183、終點為 4592，并指定了 binlog 的檔案為 mysql-bin.000001。
輸入管理者密碼之後，可以重新打開資料庫看一下是不是成功。
可以看到恢複成功。
如果想要删除 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
替換。
删除所有資料以後再次打開資料庫，看到表已經是空的了。
然後打開 binlog 看一眼，找到 start position 和 stop position。
然後從起點位置到結束位置執行一次恢複。
可以看到資料已經恢複了。
binlog 的三種格式：
直接轉載 蔔算 的《使用mysql的binlog恢複誤操作(update|delete)的資料》（
https://blog.csdn.net/Aeroleo/article/details/77929917）中的内容：
MYSQL binlog複制主要有三種方式：基于SQL語句的複制(statement-based replication, SBR)，基于行的複制(row-based replication, RBR)，混合模式複制(mixed-based replication, MBR)。對應的，binlog的格式也有三種：STATEMENT，ROW，MIXED。
① STATEMENT模式（SBR）
每一條會修改資料的sql語句會記錄到binlog中。優點是并不需要記錄每一條sql語句和每一行的資料變化，減少了binlog日志量，節約IO，提高性能。缺點是在某些情況下會導緻master-slave中的資料不一緻(如sleep()函數， last_insert_id()，以及user-defined functions(udf)等會出現問題)
② ROW模式（RBR）
不記錄每條sql語句的上下文資訊，僅需記錄哪條資料被修改了，修改成什麼樣了。而且不會出現某些特定情況下的存儲過程、或function、或trigger的調用和觸發無法被正确複制的問題。缺點是會産生大量的日志，尤其是alter table的時候會讓日志暴漲。
③ MIXED模式（MBR）
以上兩種模式的混合使用，一般的複制使用STATEMENT模式儲存binlog，對于STATEMENT模式無法複制的操作使用ROW模式儲存binlog，MySQL會根據執行的SQL語句選擇日志儲存方式。
MySQL 主備配置
在主庫上建立使用者 repl，并給他權限。
CREATE USER repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456'; 檢查在主庫 my.cnf 中配置 server-id。發現已經配置了。
然後進配置檔案把所有
bind-address=127.0.0.1
的配置行注釋掉。
在主庫執行
show master status;
記錄 binlog 和 pos。
打開 Ubuntu-Server（從庫），修改配置檔案（
/opt/lampp/etc/my.cnf
）中的 server-id 為 2，重新開機 MySQL 服務。
進入資料庫，執行：
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 的狀态是 YES。
測試主備。在主庫添加一條記錄，然後在從庫看一下是不是有這條記錄。
主庫成功添加了這條記錄。
從庫也出現了這條記錄。
特别需要指出的是，從庫和主庫的同步隻能是從同步開始設定的那一刻之後的操作才能同步。
舉個例子，主庫有 100、200、300 這三個使用者，從庫有 200、300、500 這三個使用者：
- 在主庫添加 400 号使用者，則從庫自動添加 400 号使用者
- 在主庫删除 300 号使用者，則從庫自動删除 300 号使用者
- 在主庫将 200 号使用者的編号修改為 233 号，則從庫自動将 200 号使用者的編号設定為 233。
- 在主庫修改 100 号使用者的編号修改為 101，從庫沒有響應。
- 在主庫修改 100 号使用者的編号修改為 500，進一步修改姓名為張三，由于在第一步操作之後，主庫的 100 号使用者的編号已經修改為 500 了，之後如果用主鍵來判斷修改了哪一條記錄，那麼将會是“把 500 号使用者的姓名修改為張三”，于是，這一修改會展現在從庫上，從庫的 500 号使用者也被改成了張三。
是以，需要說明的是，如果從庫一開始就沒有 student 這個庫，或者沒有表，那麼，主從備份是不會起作用的。
MySQL 雙主結構
目的：A 和 B 雙向同步。
剛才的是主從備份，隻有主機的修改會被同步到從機，從機的修改不會被同步到主機。
修改兩邊的配置檔案。
在 A 和 B 重複上面主從備份的時候建立
repl
使用者的過程，并賦予權限。
create user repl;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@‘%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES; 測試是否可以連接配接。
自己連自己、自己連對方，都可以正常通路。
之後的步驟與主從備份是類似的，隻是相當于對兩台主機都做了一次主從備份，互相做對方的從機。
于是，需要先檢視 binlog 的 index 和 pos。
這是在 A 查到的結果，A 的 IP 是 192.168.23.129。
這是在 B 查到的結果， B 的 IP 是 192.168.23.128。
注意這個操作需要在 root 權限下運作，repl 運作不了。
然後在 A 執行：
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 版的資料庫出現了這一記錄。
在 Server 版修改。
UPDATE `student` SET `address` = 'Modified From Server' WHERE `student`.`id` = 11; 可以看到雙向同步了。
主備延遲
最後需要說明的是，主備之間存在一個延遲。
- 主庫 A 執行完成一個事務，寫入 binlog，我們把這個時間記為 T1。
- 之後傳給備庫 B，我們把備庫 B 接收完這個 binlog 的時刻記為 T2。
- 備庫 B 執行完成這個事務，我們把這個時刻記為 T3。
主備延遲即 T3 - T1 的差。
可以在備庫上執行
show slave status
檢視
seconds_behind_master
但是在我們的測試中，幾乎所有的主備延遲都是 0。
這是因為，主備延遲的來源有：
- 備庫的性能更差
- 備庫壓力較大
- 大事務 必須執行完才會寫入 binlog，然後傳給備庫
在試驗中并沒有遇到這樣的情況。
當然可以手動構造大量的資料來做個測試。
練習 2
嘗試配置MySQL一主一備及雙主結構。
上文已詳述。