MySQL備份與主備配置

jxtxzzw發表於2019-05-10

MySql

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。
這是因為，主備延遲的來源有：