面試官：我看你簡歷上寫的你們公司數據庫是 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: 20User: rootHost: 192.168.0.108:57815db: NULL Command: Binlog DumpTime: 11702State: Master has sent all binlog to slave; waiting for more updatesInfo: NULL *************************** 2. row ***************************Id: 21User: rootHost: localhostdb: NULL Command: QueryTime: 0State: startingInfo: 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?eventMaster_Host:?192.168.0.101Master_User:?rootMaster_Port:?3306Connect_Retry:?60Master_Log_File:?mysql-bin.000001Read_Master_Log_Pos:?1393Relay_Log_File:?192-relay-bin.000005Relay_Log_Pos:?1284Relay_Master_Log_File:?mysql-bin.000001Slave_IO_Running:?YesSlave_SQL_Running:?YesReplicate_Do_DB:?Replicate_Ignore_DB:?Replicate_Do_Table:?Replicate_Ignore_Table:?Replicate_Wild_Do_Table:?Replicate_Wild_Ignore_Table:?Last_Errno:?0Last_Error:?Skip_Counter:?0Exec_Master_Log_Pos:?1393Relay_Log_Space:?1930Until_Condition:?NoneUntil_Log_File:?Until_Log_Pos:?0Master_SSL_Allowed:?NoMaster_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:?NoLast_IO_Errno:?0Last_IO_Error:?Last_SQL_Errno:?0Last_SQL_Error:?Replicate_Ignore_Server_Ids:?Master_Server_Id:?101Master_UUID:?220919a2-9690-11e6-9c9b-9d406b577440Master_Info_File:?/usr/local/var/mysql/master.infoSQL_Delay:?0SQL_Remaining_Delay:?NULLSlave_SQL_Running_State:?Slave?has?read?all?relay?log;?waiting?for?more?updatesMaster_Retry_Count:?86400Master_Bind:?Last_IO_Error_Timestamp:?Last_SQL_Error_Timestamp:?Master_SSL_Crl:?Master_SSL_Crlpath:?Retrieved_Gtid_Set:?Executed_Gtid_Set:?Auto_Position:?0Replicate_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:?1User:?system?userHost:?db:?NULL Command:?ConnectTime:?84003State:?Slave?has?read?all?relay?log;?waiting?for?more?updatesInfo:?NULL ***************************?2.?row?***************************Id:?2User:?system?userHost:?db:?NULL Command:?ConnectTime:?123734State:?Waiting?for?master?to?send?eventInfo:?NULL ***************************?3.?row?***************************Id:?9User:?rootHost:?localhostdb:?NULL Command:?QueryTime:?0State:?startingInfo:?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-JDBC Architecture

ShardingSphere 當前版本是 4.x，官網地址：https://shardingsphere.apache.org/index_zh.html

接下來簡單演示一下 ShardingSphere-JDBC + Spring Boot + MyBatis 實現簡單的讀寫分離。

版本說明

JDK:1.8

Spring Boot:2.3.2.RELEASE (當前最新版本)

mybatis-spring-boot-starter:2.1.3 (當前最新版)

ShardingSphere-JDBC:4.1.1 (當前最新版)

創建 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.HikariDataSourcedriverClassName:?com.mysql.cj.jdbc.DriverjdbcUrl:?jdbc:mysql://localhost:3306/master-slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMTusername:?rootpassword:?passwordslave0:type:?com.zaxxer.hikari.HikariDataSourcedriverClassName:?com.mysql.cj.jdbc.DriverjdbcUrl:?jdbc:mysql://192.168.0.108:3306/master-slave?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMTusername:?rootpassword:?passwordnames:?master,slave0props:sql.show:?truemasterslave:load-balance-algorithm-type:?round_robinsharding:master-slave-rules:master:master-data-source-name:?masterslave-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?{@Autowiredprivate?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之道公眾號

好文章，我在看??

總結

以上是生活随笔為你收集整理的终于学会了 MySQL 主从配置和读写分离的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。