我是風筝，公衆号「古時的風筝」，一個簡單的程式員鼓勵師。
文章會收錄在 JavaNewBee 中，更有 Java 後端知識圖譜，從小白到大牛要走的路都在裡面。
面試官：我看你履歷上寫的你們公司資料庫是 MySQL 讀寫分離的？
小阿花：嗯，是的。
面試官：那你說說資料庫是主從怎麼配置？
小阿花：額，都是 DBA 幫我們搞好的，我們直接用就好了。
面試官：你們主從結構遇到過什麼故障沒，比如從庫或者主庫挂掉了，怎麼解決的？
小阿花：這個也是 DBA 搞的。
面試官：（微笑）好的，今天就到這裡，回去等通知吧。
現在不用再等 DBA 了，自己配置 MySQL 主從，自己做自己的 DBA 吧。
背景說明
假設各位都已經安裝好了 MySQL 資料庫，本次操作采用的 MySQL 版本是 5.7.16。正好還有一台 Mac閑着，是以幹脆就用兩台實體機直接操作了，沒有空閑機器的可以在虛拟機操作是一樣的。
以最簡單的一主一從配置。
主伺服器：192.168.0.101
從伺服器：192.168.0.108
資料庫層的幾種模式
在系統架構中，資料庫層主要由如下幾種模式，分别是單點模式、主備模式、主從模式。
單點模式
單點模式是最簡單的模式，隻有一台資料庫伺服器，部署最簡單。但是存在單點風險，一旦這台伺服器挂掉，整個系統也就挂掉了。
主備模式
為了解決單點模式的風險，主備模式産生。目前，主備模式應該是各個線上服務系統的最低配置了，比如你在各個雲平台購買的資料庫服務一般都會開啟備份功能。一旦主節點出現問題，還可以切換到備份節點，不至于整個系統癱瘓。
主備又分為一主一備、一主多備。多個備份是為了保證更高的安全性，萬一主節點出現問題的時候，碰巧備份節點也出問題呢。
當主節點出現問題的時候要切換到備份節點，切換方式又分為手動切換和自動切換。手動切換具有一定的延時，當主節點出現問題時，隻能等運維人員發現或者收到系統通知。
主從模式
主從配置一般都是和讀寫分離相結合，主伺服器負責寫資料，從伺服器負責讀資料，并保證主伺服器的資料及時同步到從伺服器。
主從模式又分為一主一從、一主多從和多主多從，越往後部署越複雜，同時，系統穩定性更高。主從模式可以更好的分擔資料庫壓力，将插入更新操作和查詢操作分開，提高系統整體性能。
本文的目的就是介紹一下簡單的一主一從架構的配置和原理。
主從原理
主節點
1、當主節點上進行 insert、update、delete 操作時，會按照時間先後順序寫入到 binlog 中；
2、當從節點連接配接到主節點時，主節點會建立一個叫做 binlog dump 的線程；
3、一個主節點有多少個從節點，就會建立多少個 binlog dump 線程；
4、當主節點的 binlog 發生變化的時候，也就是進行了更改操作，binlog dump 線程就會通知從節點 (Push模式)，并将相應的 binlog 内容發送給從節點；
從節點
當開啟主從同步的時候，從節點會建立兩個線程用來完成資料同步的工作。
I/O線程： 此線程連接配接到主節點，主節點上的 binlog dump 線程會将 binlog 的内容發送給此線程。此線程接收到 binlog 内容後，再将内容寫入到本地的 relay log。
SQL線程： 該線程讀取 I/O 線程寫入的 relay log，并且根據 relay log 的内容對從資料庫做對應的操作。
主伺服器配置
開啟遠端連接配接
使用指令行或者用戶端工具進入 MySQL，執行指令：
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.108' identified by 'P@ssw0rd';
FLUSH PRIVILEGES;
上面語句執行完成後，在從伺服器中使用用戶端或者指令行測試是否生效。
mysql -h 192.168.0.101 -uroot -p
啟用 bin-log，并設定 server-id
需要在 MySQL 配置檔案中修改，MySQL 配置檔案預設位置在如下位置，從上下到下優先級降低：
-
/etc/my.cnf
-
/etc/mysql/my.cnf
-
/usr/local/etc/my.cnf
-
~/.my.cnf
配置内容如下：
[mysqld]
log-bin=mysql-bin
server-id=101
其他參數
除了上面兩個必要參數外，還有其他的幾個參數。
binlog_format
bin-log 日志的格式，支援下面三種，推薦使用 mixed 。
statement：會将對資料庫操作的sql語句寫入到binlog中。
row：會将每一條資料的變化寫入到binlog中。
mixed：statement 與 row 的混合。MySQL 決定什麼時候寫 statement 格式，什麼時候寫 row 格式。
binlog-do-db
配置要同步的資料庫，如果不配置預設為全部資料庫。
binlog-do-db=db1
binlog-do-db=db2
binlog-ignore-db
配置不需要同步的資料庫。
binlog-ignore-db=db3
expire-logs-days
bin-log 日志儲存天數，儲存天數越久占用空間越大。
然後重新開機 MySQL 服務
mysql.server restart
使用下面的指令可以檢視配置是否生效：
show variables like 'log_bin';
show variables like 'server_id';
使用下面的語句檢視 master 狀态。
show master status;
從伺服器配置
1、打開從伺服器的配置檔案，在其中加上如下配置：
server-id=108
2、重新開機 MySQL 服務。
mysql.server restart
3、配置主從同步
change master to master_host='192.168.0.101',master_user='root',master_password='P@ssw0rd',master_log_file='mysql-bin.000001' ,master_log_pos=154;
其中
master_host
表示主伺服器 IP，
master_user
和
master_password
分别是主伺服器的使用者名和密碼，
master_log_file
master_log_pos
在主伺服器中通過
show master status
語句可以查到。
4、開啟同步程序。
start slave
5、檢視同步狀态。
show slave status;
可能碰到的問題
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
mysql 5.6 之後引入了uuid的概念，各個複制主從結構中的 server_uuid 要保證不一樣。由于我這台新 Mac 是用系統自帶的「遷移助理」從老機器遷移過來的，是以說，所有的軟體和配置都是一模一樣的。
通過下面的語句可檢視 server_uuid 的值 和 auto.cnf 的路徑，auto.cnf 中儲存了 server_uuid 的值。
show variables like '%server_uuid%';
show variables like '%datadir%';
我查到的從伺服器上的 datadir 目錄在 /usr/local/var/mysql，修改目錄下的 auto.cnf 檔案中的 server-uuid 的值
修改 datadir 目錄下的auto.cnf 檔案中的 server-uuid 的值。
然後再重新開機服務，檢視同步狀态就不會有這個問題了。
測試一下
同步配置完成後，我在主伺服器上建立一個資料庫，建立一張表，然後新增、修改、删除資料，檢視從伺服器上是否相應的同步修改。
正常情況下，在主伺服器上操作完成，從伺服器也馬上會看到對應的資料。
主從狀态查詢
主服務狀态
使用如下語句可檢視主服務狀态：
show master status\G;
使用
show processlist
語句可檢視線程狀态，可以看到第一個線程的 State 是 「Master has sent all binlog to slave; waiting for more updates」，說明同步線程一直在運作中。
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 20
User: root
Host: 192.168.0.108:57815
db: NULL
Command: Binlog Dump
Time: 11702
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 21
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
2 rows in set (0.03 sec)
ERROR:
No query specified
從服務狀态
通過
show slave status;
可檢視從服務狀态，從中可以看到主服務的資訊以及 Slave_IO 和 Slave_SQL 線程的狀态等。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1393
Relay_Log_File: 192-relay-bin.000005
Relay_Log_Pos: 1284
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1393
Relay_Log_Space: 1930
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: 220919a2-9690-11e6-9c9b-9d406b577440
Master_Info_File: /usr/local/var/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
也可以通過
show processlist
檢視從服務的線程情況。
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 84003
State: Slave has read all relay log; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 123734
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 9
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
3 rows in set (0.00 sec)
ERROR:
No query specified
從庫挂機了怎麼恢複同步
哪兒有什麼歲月靜好，隻不過是有人幫你頂住了鍋而已。服務宕掉通常都在不經意間，一旦從庫服務挂掉了怎麼辦。
在主服務的 binlog dump 線程将指定的 binlog 資訊發給從服務時，除了日志内容，還包括本次發送内容在主服務端的 bin-log 日志檔案名稱以及位置資訊。
從服務的 I/O 線程接收到資訊後将日志内容寫入realy-log 檔案(mysql-relay-bin.xxxxxx)的末端，并将讀取到的主服務端的 bin-log 的檔案名和位置記錄到 master-info 中（通過
show slave status
中的
Master_Info_File
字段可以看到 master.info 儲存的位置），以便下一次讀取時能告訴主服務從哪裡開始同步。
從服務的 SQL 線程檢測到 realy-log 新增了内容後，解析日志檔案生成對應的 sql 語句，并應用這些 sql 到資料庫，保證主從資料一緻性。
是以，及時從庫挂掉了，因為有 master.info 記錄了上一次同步的位置，隻要同步服務再次啟動，那就可以從上次同步的位置繼續增量同步了。
那話說主庫宕了怎麼辦，這就是另一個悲傷的故事了，就沒有從庫挂掉這麼簡單了，如果馬上啟動那就是最好的解決辦法。如果由于硬體或者比較棘手的問題導緻沒辦法立即重新開機，那就要選一個從庫更新為主庫，選擇的标準是資料最接近主庫的，也就是最後一次同步時間最晚的。如果有可能（比如主服務隻是資料庫無法啟動，但機器還在）還要到主服務上拉取最新的 bin-log 進行同步。最後進行一系列設定将選中的從庫變更為主庫配置。（就不展開說了，因為再展開就露餡兒了，我也不會）
ShardingSphere-JDBC 實作讀寫分離
主從分離之後，就要做系統的讀寫分離了，寫操作走主節點，讀操作走從節點。這就需要資料庫中間件來幫忙了，現在比較流行的中間件有Atlas、Cobar、Mycat、Sharding-Sphere，具體選擇哪個或者自研，要看各個公司的标準了。
Sharding-Sphere 包括 ShardingSphere-JDBC 和 ShardingSphere-Proxy。
ShardingSphere-JDBC 定位為輕量級 Java 架構，在 Java 的 JDBC 層提供的額外服務。 它使用用戶端直連資料庫，以 jar 包形式提供服務，無需額外部署和依賴，可了解為增強版的 JDBC 驅動，完全相容 JDBC 和各種 ORM 架構。
- 适用于任何基于 JDBC 的 ORM 架構，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
- 支援任何第三方的資料庫連接配接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
- 支援任意實作 JDBC 規範的資料庫，目前支援 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标準的資料庫。
ShardingSphere 目前版本是 4.x，官網位址：https://shardingsphere.apache.org/index_zh.html
接下來簡單示範一下 ShardingSphere-JDBC + Spring Boot + MyBatis 實作簡單的讀寫分離。
版本說明
建立 Spring Boot 項目
1、到 Spring Initializr（https://start.spring.io/） 上建立項目結構，選擇 JDK 版本為 8，Spring Boot 為2.3.2（目前最新版本），并輸入項目相關的資訊。
2、引入 Spring Boot 相關依賴包，包括 spring-boot-starter-web、mybatis-spring-boot-starter、mysql-connector-java、lombok。
3、最後将建立完成後的目錄結構直接下載下傳下來導入到 IDEA，然後 maven 更新依賴包即可。
4、引入 ShardingSphere-JDBC 專門針對 Spring Boot 的依賴包。
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
5、配置主從資料庫，保證讀請求到從節點，寫請求到主節點，使用的連接配接池是 Spirng Boot 2.x 預設的 hikari。注意，如果用的是其他連接配接池，比如 Druid 的話，jdbcUrl 屬性要改成 url 。
spring:
shardingsphere:
datasource:
master:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://localhost:3306/master-slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: password
slave0:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://192.168.0.108:3306/master-slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: password
names: master,slave0
props:
sql.show: true
masterslave:
load-balance-algorithm-type: round_robin
sharding:
master-slave-rules:
master:
master-data-source-name: master
slave-data-source-names: slave0
如果有多個從節點，可以繼續在
spring.shardingsphere.datasource
下添加 slave1、slave2 等。
最後要配置上 master-slave-rules（主從規則），才能保證實作讀寫分離，如果不加這個配置，就會采用 round_robin 路由算法，将主從節點無差别對待。寫請求可能會落到從節點，導緻無法像主節點同步。
6、配置 MyBatis 相關的 mapper.xml 、dao 接口、實體等，不做過多介紹。
7、添加 Service、Controller，添加一個查詢方法，對應資料庫 select。添加一個新增方法，對應資料庫 insert 。
@RestController
@RequestMapping("user")
public class UserController {
@Autowired
private IUserService userService;
@GetMapping(value = "list")
public Object list() {
List<User> users = userService.list();
return users;
}
@PostMapping(value = "add")
public Object add(@RequestBody UserDto userDto) {
User user = new User();
BeanUtils.copyProperties(userDto, user);
return userService.addUser(user);
}
}
8、最後通過分别請求 list 接口和 add 接口，然後通過日志可以看到，select 會路由到從節點，insert 會路由到主節點。
源碼在 github 上，有需要的同學可以到 github 上擷取。
倉庫位址：https://github.com/huzhicheng/play ，其中的 sharding-jdbc-write-read-split 項目是本文的示例源碼。
總結
主從配置+讀寫分離可以很大程度上保證系統高可用性和整體性能，而且也是網際網路應用的基礎入門配置。MySQL 可以利用 bin-log 實作主從同步，master 節點采用推方式向 slave 節點推送寫入操作形成的日志，slave 節點會将日志先寫到本地的 relay-log 中，然後再寫入資料庫中，此過程中還會更新 master.info 檔案，記錄本地同步的位置，以便下次增量同步。
資料庫層配置完成，最後要在應用中進行讀寫配置，具體的配置可以公司内部自行開發或者采用比較穩定的開源架構，本文采用的是 ShardingSphere-JDBC，隻是個代理用戶端，其原理就是分析 SQL 語句判斷是讀還是寫，進而分發到不同的節點。
如果需要更複雜的操作，比如跨庫 join 等，那就需要代理中間件了，比如 ShardingSphere-Proxy 等。
壯士且慢，先給點個贊吧，總是被白嫖，身體吃不消！
公衆号「古時的風筝」，Java 開發者，全棧工程師，人稱遲到小王子，bug 殺手，擅長解決問題。
一個兼具深度與廣度的程式員鼓勵師，本打算寫詩卻寫起了代碼的田園碼農！堅持原創幹貨輸出，你可選擇現在就關注我，或者看看曆史文章再關注也不遲。長按二維碼關注，跟我一起變優秀！
人生沒有回頭路，珍惜當下。