面試官你好，我已經掌握了MySQL主從配置和讀寫分離，你看我還有機會嗎？-有解無憂

我是風箏，公眾號「古時的風箏」，一個簡單的程式員鼓勵師，
文章會收錄在 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-JDBC Architecture

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 = "https://www.cnblogs.com/fengzheng/p/list")
    public Object list() {
        List<User> users = userService.list();
        return users;
    }

    @PostMapping(value = "https://www.cnblogs.com/fengzheng/p/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 殺手，擅長解決問題，
一個兼具深度與廣度的程式員鼓勵師，本打算寫詩卻寫起了代碼的田園碼農！堅持原創干貨輸出，你可選擇現在就關注我，或者看看歷史文章再關注也不遲，長按二維碼關注，跟我一起變優秀！

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/122637.html

標籤：Java

上一篇：Spring Security入門教程通俗易懂超詳細【內含案例】

下一篇：Es6中的常用新特性