MySQL備份與主備配置數據備份類型全量備份：備份整個資料庫增量備份：備份自上一次備份以來（增量或完全）以來變化的數據差異備份：備份自上一次完全備份以來變化的數據全量備份全量備份的方法有 2 種，一種是利用資料庫管理工具提供的備份恢復和導入導出功能。例如：如果使用 Navicat、PHP ...

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