一、配置

https://dev.mysql.com/doc/refman/8.0/en/replication-options-reference.html

MySQL 復制過濾詳解：https://developer.aliyun.com/article/59268

master

[mysqld]
server-id = 1

#log_bin = on # 8 版本默認啟用，5 版本需要手動開啟
#log_bin_basename = binlog
#log-bin = binlog
#log-bin-index = binlog.index
# 建議在 slave 端做過濾，避免影響 master 端日志文件完整性
#binlog-do-db = test_table # 寫入日志
#binlog-ignore-db = mysql,sys,performance_schema,information_schema # 不寫入日志

slave

[mysqld]
server-id = 2
super_read_only = on # 禁止手動 CURD，開啟后不影響主從同步

# 不需要同步的庫和表，顯式配置后，未配置的庫表將會被同步
# replicate-ignore-db = mysql
# replicate-wild-ignore-table = mysql.%
# replicate-ignore-db = sys
# replicate-wild-ignore-table = sys.%
# replicate-ignore-db = performance_schema
# replicate-wild-ignore-table = performance_schema.%
# replicate-ignore-db = information_schema
# replicate-wild-ignore-table = information_schema.%

# 需要同步的庫，不配置表示同步所有
replicate-wild-do-table = db_a.%
replicate-wild-do-table = db_b.%
# replicate-do-db = db_a
# replicate-do-db = db_b
# 需要同步的表
# replicate-do-table = db_a.table_a
# replicate-do-table = db_a.table_b

二、開啟同步

在此之前需要保證，不需要同步之外的庫表完全一致，否則會同步失敗

master

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-repuser.html

-- 創建同步賬戶，不推薦直接使用 root
DROP USER repl;
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 查看 master 服務器狀態，File 和 Postion 對應的值要記錄下來，下面要用到
SHOW MASTER STATUS;
-- 查看從節點
SHOW SLAVE HOSTS;

slave

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slaveinit.html

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-additionalslaves.html

-- 停止同步
STOP SLAVE;

-- 配置主節點信息，MASTER_LOG_FILE 對應 File，MASTER_LOG_POS 對應 Postion
CHANGE MASTER TO MASTER_HOST = '127.0.0.1',
MASTER_PORT = 3306,
MASTER_USER = 'repl',
MASTER_PASSWORD = 'password',
MASTER_LOG_FILE = 'binlog.000009',
MASTER_LOG_POS = 1237;

-- 開啟同步
START SLAVE;

-- 查看狀態，Slave_IO_Running 和 Slave_SQL_Running 必須為 YES
SHOW SLAVE STATUS;

查看信息

https://dev.mysql.com/doc/refman/8.0/en/replication-threads-monitor-main.html

SHOW VARIABLES LIKE '%server_id%';
SHOW VARIABLES LIKE '%log_bin%';
SHOW VARIABLES LIKE '%server_uuid%';
SHOW VARIABLES LIKE '%datadir%';

SHOW PROCESSLIST;
SHOW BINLOG EVENTS;

canal 方案：https://github.com/alibaba/canal

https://github.com/alibaba/canal/blob/master/admin/admin-web/src/main/resources/canal_manager.sql
默認 admin 123456

docker run -d -it \
-e server.port=8089 \
-e canal.adminUser=admin \
-e canal.adminPasswd=admin \
-e spring.datasource.address=10.74.2.71:3306 \
-e spring.datasource.database=canal_manager \
-e spring.datasource.username=root \
-e spring.datasource.password=root \
-p 8089:8089 \
--name=canal-admin -m 1024m canal/canal-admin

docker run -d -it \
-e canal.admin.manager=10.74.2.71:8089 \
-e canal.admin.user=admin \
-e canal.admin.passwd=4ACFE3202A5FF5CF467898FC58AAB1D615029441 \
-e canal.admin.port=11110 \
-e canal.port=11111 \
-e canal.metrics.pull.port=11112 \
-p 11110:11110 -p 11111:11111 -p 11112:11112 \
--name=canal-server -m 4096m canal/canal-server

View Code

三、主從復制原理

主從數據同步是基于 binlog 進行的。

在主從復制過程中，會基于三個線程來操作：一個主庫線程，兩個從庫線程。

1. 二進制日志轉儲線程(Binlog dump thread)是一個主庫線程。當從庫線程連接的時候， 主庫可以將二進制日志發送給從庫，當主庫讀取事件(Event)的時候，會在 Binlog 上加鎖，讀取完成之后，再將鎖釋放掉。
2. 從庫 I/O 線程會連接到主庫，向主庫發送請求更新 Binlog。這時從庫的 I/O 線程就可以讀取到主庫的二進制日志轉儲線程發送的 Binlog 更新部分，并且拷貝到本地的中繼日志(Relay log)。
3. 從庫 SQL 線程會讀取從庫中的中繼日志，并且執行日志中的事件，將從庫中的數據與主庫保持同步。

復制的最大問題是有延時。

四、日志

general query log(通用查詢日志)

通用查詢日志用來記錄用戶的所有操作 ，包括啟動和關閉 MySQL 服務、所有用戶的連接開始時間和截止時間、發給 MySQL 數據庫服務器的所有 SQL 指令等。當我們的數據發生異常時，查看通用查詢日志，還原操作時的具體場景，可以幫助定位問題。

-- 查看當前狀態
SHOW VARIABLES LIKE '%general%';
-- general_log 狀態
-- general_log_file 日志文件名稱

-- 啟動日志
-- [mysqld]
-- general_log=ON
-- general_log_file=[path[filename]] #日志文件所在目錄路徑，filename為日志文件名
-- 果不指定目錄和文件名，通用查詢日志將默認存儲在 MySQL 數據目錄中的 hostname.log 文件中，hostname 表示主機名。
SET GLOBAL general_log=on; -- 開啟通用查詢日志
SET GLOBAL general_log_file=’path/filename’; -- 設置日志文件保存位置

-- 刪除\刷新日志
-- mysqladmin -uroot -p flush-logs

View Code

error log(錯誤日志)

在 MySQL 數據庫中，錯誤日志功能是默認開啟的。而且錯誤日志無法被禁止 。默認錯誤日志存儲在 MySQL 數據庫的數據文件夾下，名稱默認為 mysqld.log(Linux) 或 hostname.err(Mac)。

SHOW VARIABLES LIKE 'log_err%';

-- 配置路徑
-- [mysqld]
-- log-error=[path/[filename]] # path為日志文件所在的目錄路徑，filename 為日志文件名

-- 刪除\刷新日志
-- https://dev.mysql.com/doc/refman/8.0/en/log-file-maintenance.html
-- mv /var/log/mysqld.log /var/log/mysqld.log.old
-- install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
-- mysqladmin -uroot -p flush-logs # 5.5.7 之前會自動備份為 filename.err_old

View Code

bin log(二進制日志)

binlog 即 binary log，二進制日志文件，也叫作變更日志(update log)。以事件形式記錄數據庫執行的所有 DDL 和 DML 等數據庫更新事件的語句，不包含沒有修改任何數據的語句(select、show 等)。binlog 主要應用于數據恢復和數據復制。

設置

SHOW VARIABLES LIKE '%log_bin%';

-- [mysqld]
-- log-bin="/var/lib/mysql/binlog/my-bin" # 啟用二進制日志
-- binlog_expire_logs_seconds=600
-- max_binlog_size=100M
-- chown -R -v mysql:mysql binlog # 新建文件夾需要使用 mysql 用戶

-- 不希望通過修改配置文件并重啟的方式設置二進制日志
-- SET GLOBAL sql_log_bin=0; -- 在 mysql8 中只有 session 級別的設置，沒有 global 級別
SET sql_log_bin=0;

View Code

當 MySQL 創建二進制日志文件時，先創建一個以 filename 為名稱，以 .index 為后綴的文件。再創建一個以 filename 為名稱，以 .000001 為后綴的文件。

MySQL 服務重新啟動一次，以 .000001 為后綴的文件就會增加一個，并且后綴名按 1 遞增。即日志文件個數與 MySQL 服務啟動的次數相同。

如果日志長度超過了 max_binlog_size 的上限(默認1GB)，就會創建一個新的日志文件。

查看

SHOW BINARY LOGS; -- 查看當前的二進制日志文件列表及大小

mysqlbinlog

# 將行事件以偽 SQL 的形式查看
mysqlbinlog -v "/var/lib/mysql/binlog/my-bin.000002"
# 不顯示 binlog 格式的語句
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/my-bin.000002"、
# 查看幫助
mysqlbinlog --no-defaults --help
# 查看最后 100 行
mysqlbinlog --no-defaults --base64-output=decode-rows -v my-bin.000002 | tail -100
# 根據 position 查找
mysqlbinlog --no-defaults --base64-output=decode-rows -v my-bin.000002 | grep -A 20 '4939002'

View Code

mysqlbinlog 讀取出 binlog 日志的全文內容比較多，不容易分辨查看到 pos 點信息。可以用 SHOW 查看。

-- SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
-- IN 'log_name'：指定要查詢的 binlog 文件名(不指定就是第一個 binlog 文件)
-- FROM pos：指定從哪個 pos起始點開始查起(不指定就是從整個文件首個 pos 點開始算)
-- LIMIT [offset]：偏移量(不指定就是 0)
-- row_count：查詢總條數(不指定就是所有行)

SHOW BINLOG EVENTS IN 'my-bin.000002';

View Code

binlog 格式

SHOW VARIABLES LIKE 'binlog_format';

Statement：每一條會修改數據的 sql 都會記錄在 binlog 中。優點：不需要記錄每一行的變化，減少了 binlog 日志量，節約了IO，提高性能。

Row：5.1.5 版本的 MySQL 才開始支持 row level 的復制，它不記錄 sql 語句上下文相關信息，僅保存哪條記錄被修改。優點：日志內容非常清楚的記錄下每一行數據修改的細節。且不會出現某些情況下的存儲過程或 function 或 trigger 的調用和觸發無法被正確復制的問題。

Mixed：從 5.1.8 版本開始，MySQL 提供了 Mixed 格式，是 Statement 與 Row 的結合。

使用 binlog 恢復數據

mysqlbinlog [option] filename | mysql –uuser -ppass;

這個命令可以這樣理解：使用 mysqlbinlog 命令來讀取 filename 中的內容，然后使用 mysql 命令將這些內容恢復到數據庫中。

• filename：日志文件名。
• option：可選項，比較重要的兩對參數：--start-date 和 --stop-date：可以指定恢復數據庫的起始時間點和結束時間點。--start-position 和 --stop-position：可以指定恢復數據的開始位置和結束位置。

注意：使用 mysqlbinlog 命令進行恢復操作時，必須是編號小的先恢復，例如 my-bin.000001 必須在 my-bin.000002 之前恢復。

通過數據庫全量備份和 binlog 中的增量信息可以完成數據庫的無損失恢復。但是，如果遇到數據量大，庫表很多(比如分庫分表的應用)的場景，用 binlog 恢復數據是很有挑戰性的，因為起止位置不容易管理。

在這種情況下，一個有效的解決辦法是配置主從，甚至是一主多從，把 binlog 的內容通過 relay log(中繼日志)，同步到從數據庫服務器中，這樣就可以有效避免數據庫故障導致的數據異常等問題。

刪除

MySQL 的二進制文件可以配置自動刪除，同時 MySQL 也提供了安全的手動刪除的方法。

RESET MASTER; -- 刪除所有二進制日志文
-- PURGE MASTER LOGS xxx 刪除指定部分之前的二進制日志文件
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名';
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期';

寫入機制

事務執行過程中，先把日志寫到 binlog cache，事務提交的時候再把 binlog cache 寫到 binlog 文件中。因為一個事務的 binlog 不能被拆開，無論這個事務多大，也要確保一次性寫入。

系統會給每個線程分配一個塊內存作為 binlog cache。

write(binlog cache 寫到 binlog (文件系統緩存page cache)文件) 和 fsync(文件系統緩存寫到磁盤) 的時機，可以由參數 sync_binlog 控制，默認是 0。

為 0 時，表示每次提交事務都只 write，由系統自行判斷什么時候執行 fsync。雖然性能得到提升，但是機器宕機，page cache 里面的 binglog 會丟失。

為安全起見，可設為 1，表示每次提交事務都執行 fsync，如同 redo log 刷盤流程一樣。還有一種折中方式，可設為 N(N>1)，表示每次提交事務都 write，但累積 N 個事務后才 fsync。

在出現 IO 瓶頸的場景里，將 sync_binlog 設置成一個比較大的值，可以提升性能。同樣的，如果機器宕機，則會丟失最近 N 個事務的 binlog 日志。

與 redo log 區別

redo log 是物理日志，記錄內容是在某個數據頁上做了什么修改，屬于 InnoDB 存儲引擎層產生的。

binlog 是邏輯日志，記錄內容是語句的原始邏輯，類似于給 ID=2 這一行的 c 字段加 1，屬于 MySQL Server 層。

兩階段提交

在執行更新語句過程，會記錄 redo log 與 binlog 兩塊日志，以事務為基本單位，redo log 在事務執行過程中可以不斷寫入，而 binlog 只有在提交事務時才寫入，所以 redo log 與 binlog 的寫入時機不一樣。

寫入時機不一樣會導致 redo log 與 binlog 之間的邏輯不一致。假如 binlog 沒寫完就異常，這時 binlog 里面就沒有對應的修改記錄。為了解決日志邏輯一致問題，InnoDB 使用了兩階段提交方案。

兩階段提交就是將 redo log 的寫入拆成了兩個步驟 prepare 和 commit。

寫入 binlog 時發生異常，MySQL 根據 redo log 日志恢復數據時，發現 redo log 還處于 prepare 階段，并且沒有對應 binlog 日志，就會回滾該事務。

redo log commit 時發生異常，它會執行框住的邏輯，雖然 redo log 是處于 prepare 階段，但是能通過事務 id 找到對應的 binlog 日志，所以 MySQL 認為是完整的，就會提交事務恢復數據。

relay log(中繼日志)

中繼日志只在主從服務器架構的從服務器上存在。從服務器為了與主服務器保持一致，要從主服務器讀取二進制日志的內容，并且把讀取到的信息寫入本地的日志文件 中，這個從服務器本地的日志文件就叫中繼日志。

然后，從服務器讀取中繼日志，并根據中繼日志的內容對從服務器的數據進行更新，完成主從服務器的數據同步 。

搭建好主從服務器之后，中繼日志默認會保存在從服務器的數據目錄下。文件名的格式是： 從服務器名 -relay-bin.序號。中繼日志還有一個索引文件：從服務器名-relaybin.index，用來定位當前正在使用的中繼日志。

https://dev.mysql.com/doc/refman/8.0/en/replication.html & https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

https://blog.jcole.us/innodb & https://github.com/jeremycole/innodb_diagrams