面試官你好，我已經掌握了MySQL主從配置和讀寫分離，你看我還有機會嗎？
- 2020 年 7 月 30 日
- 筆記
- JAVA, MySQL主從配置, MySQL讀寫分離, ShardingSphere
我是風箏，公眾號「古時的風箏」，一個簡單的程序員鼓勵師。
文章會收錄在 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，官網地址：//shardingsphere.apache.org/index_zh.html
接下來簡單演示一下 ShardingSphere-JDBC + Spring Boot + MyBatis 實現簡單的讀寫分離。
版本說明
創建 Spring Boot 項目
1、到 Spring Initializr（//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 上獲取。
倉庫地址：//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 殺手，擅長解決問題。
一個兼具深度與廣度的程序員鼓勵師，本打算寫詩卻寫起了代碼的田園碼農！堅持原創乾貨輸出，你可選擇現在就關注我，或者看看歷史文章再關注也不遲。長按二維碼關注，跟我一起變優秀！
