來源：碼猿技術專欄

大家好，我是不才陳某~

在專案初期，我們部署了三個資料庫A、B、C，此時資料庫的規模可以滿足我們的業務需求。為了將資料做到平均分配，我們在Service服務層使用uid%3進行取模分片，從而將資料平均分配到三個資料庫中。

如圖所示：

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

後期隨著使用者量的增加，使用者產生的資料資訊被源源不斷的新增到資料庫中，最終達到資料庫的最佳儲存容量。如果此時繼續向資料庫中新增資料，會導致資料庫的CRUD等基本操作變慢，進而影響整個服務的響應速度。

這時，我們需要增加新的節點，對資料庫進行水平擴容，那麼加入新的資料庫D後，資料庫的規模由原來的3個變為4個。

如圖所示：

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

此時由於分片規則發生了變化（uid%3 變為uid%4），導致大部分的資料，無法命中原有的資料，需要重新進行分配，要做大量的資料遷移處理。

比如之前uid如果是uid=3取模3%3=0，是分配在A庫上，新加入D庫後， uid=3取模3%4=3，分配在D庫上；

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

新增一個節點，大概會有90%的資料需要遷移，這樣會面臨大量的資料壓力，並且對服務造成極大的不穩定性。

1. 五個方案

1.1 停機方案

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

釋出公告：為了進行資料的重新拆分，在停止服務之前，我們需要提前通知使用者，比如：我們的服務會在yyyy-MM-dd進行升級，給您帶來的不便敬請諒解。
停止服務：關閉Service
離線資料遷移（拆分，重新分配資料）：將舊庫中的資料按照Service層的演算法，將資料拆分，重新分配資料
資料校驗：開發定製一個程式對舊庫和新庫中的資料進行校驗，比對
更改配置：修改Service層的配置演算法，也就是將原來的uid%3變為uid%4
恢復服務：重啟Service服務
回滾預案：針對上述的每個步驟都要有資料回滾預案，一旦某個環節（如：資料遷移，恢復服務等）執行失敗，立刻進行回滾，重新再來

停止服務之後，能夠保證遷移工作的正常進行，但是服務停止，傷害使用者體驗，並造成了時間壓力，必須在指定的時間內完成遷移。

1.2 停寫方案

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

支援讀寫分離：資料庫支援讀寫分離，在擴容之前，每個資料庫都提供了讀寫功能，資料重新分配的過程中，將每個資料庫設定為只讀狀態，關閉寫的功能
升級公告：為了進行資料的重新拆分，在停寫之前，我們需要提前通知使用者，比如：我們的服務會在yyyy-MM-dd進行升級，給您帶來的不便敬請諒解。關注公眾號：碼猿技術專欄，回覆關鍵詞：1111 獲取阿里內部Java效能最佳化手冊！
中斷寫操作，隔離寫資料來源（或攔截返回統一提示）：在Service層對所有的寫請求進行攔截，統一返回提示資訊，如：服務正在升級中，只對外提供讀服務
資料同步處理：將舊庫中的資料按照Service層的演算法，將資料重新分配，遷移（複製資料）
資料校驗：開發定製一個程式對舊庫中的資料進行備份，使用備份的資料和重新分配後的資料進行校驗，比對
更改配置：透過配置中心，修改Service層的配置演算法，也就是將原來的uid%3變為uid%4，這個過程不需要重啟服務
恢復寫操作：設定資料庫恢復讀寫功能，去除Service層的攔截提示
資料清理：使用delete語句對冗餘資料進行刪除
回滾預案：針對上述的每個步驟都要有資料回滾預案，一旦某個環節（如：資料遷移等）執行失敗，立刻進行回滾，重新再來

缺點：在資料的複製過程需要消耗大量的時間，停寫時間太長，資料需要先複製，再清理冗餘資料

1.3 日誌方案

核心是透過日誌進行資料庫的同步遷移，主要操作步驟如下：

1、資料遷移之前，業務應用訪問舊的資料庫節點。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

2、日誌記錄

在升級之前，記錄“對舊資料庫上的資料修改”的日誌（這裡修改包括增、刪、改），這個日誌不需要記錄詳細的資料資訊，主要記錄：

（1）修改的庫；

（2）修改的表；

（3）修改的唯一主鍵；

（4）修改操作型別。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

日誌記錄不用關注新增了哪些資訊，修改的資料格式，只需要記錄以上資料資訊，這樣日誌格式是固定的，這樣能保證方案的通用性。

服務升級日誌記錄功能風險較小：

寫和修改介面是少數，改動點少；
升級只是增加了一些日誌，採用非同步方式實現，對業務功能沒有太多影響。

3、資料遷移：

研發定製資料遷移工具，作用是把舊庫中的資料遷移至新庫中。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

整個過程仍然採用舊庫進行對外服務。
資料同步工具實現複雜度不高。
只對舊庫進行讀取操作，如果同步出現問題，都可以對新庫進行回滾操作。
可以限速或分批遷移執行，不會有時間壓力。

資料遷移完成之後，並不能切換至新庫提供服務。

因為舊庫依然對線上提供服務，庫中的資料隨時會發生變化，但這些變化的資料並沒有同步到新庫中，舊庫和新庫資料不一致，所以不能直接進行切換，需要將資料同步完整。

4、日誌增量遷移

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

研發一個日誌遷移工具，把上面遷移資料過程中的差異資料追平，處理步驟：

讀取log日誌，獲取具體是哪個庫、表和主鍵發生了變化修改；
把舊庫中的主鍵記錄讀取出來
根據主鍵ID，把新庫中的記錄替換掉

這樣可以最大程度的保障資料的一致性。風險分析：

整個過程，仍然是舊庫對線上提供服務；
日誌遷移工具實現的複雜度較低；
任何時間發現問題，可以重新再來，有充分的容錯空間；
可以限速重放處理日誌，處理過程不會因為對線上影響造成時間壓力。

但是，日誌增量同步完成之後，還不能切換到新的資料庫。

因為日誌增量同步過程中，舊庫中可能有資料發生變化，導致資料不一致，所以需要進一步讀取日誌，追平資料記錄；日誌增量同步過程隨時可能會產生新的資料，新庫與舊庫的資料追平也會是一個無限逼近的過程。關注公z號：碼猿技術專欄，回覆關鍵詞：1111 獲取阿里內部Java效能最佳化手冊！

5、資料校驗

準備好資料校驗工具，將舊庫和新庫中的資料進行比對，直到資料完全一致。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

6、切換新庫

資料比對完成之後，將流量轉移切換至新庫，至此新庫提供服務，完成遷移。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

但是在極限情況下，即便透過上面的資料校驗處理，也有可能出現99.99%資料一致，不能保障完全一致，這個時候可以在舊庫做一個readonly只讀功能，或者將流量遮蔽降級，等待日誌增量同步工具完全追平後，再進行新庫的切換。

至此，完成日誌方案的遷移擴容處理，整個過程能夠持續對線上提供服務，只會短暫的影響服務的可用性。

這種方案的弊端，是操作繁瑣，需要適配多個同步處理工具，成本較高，需要制定個性化業務的同步處理，不具備普遍性，耗費的時間週期也較長。

1.4 雙寫方案（中小型資料）

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

雙寫方案可透過canal或mq做實現。

增加新庫，按照現有節點，增加對應的數量。
資料遷移：避免增量影響，先斷開主從，再匯入（耗時較長），同步完成並做校驗
增量同步：開啟Canal同步服務，監聽從節點資料庫，再開啟主從同步，從節點收到資料後會透過Canal服務，傳遞至新的DB節點。
切換新庫：透過Nginx，切換訪問流量至新的服務。
修復切換異常資料：在切換過程中，如果出現，Canal未同步，但已切換至新庫的請求（比如下單，修改了資金，但還未同步），可以透過定製程式，讀取檢測異常日誌，做自動修復或人工處理。
針對此種情況，最好是在凌晨使用者量小的時候，或專門停止外網訪問，進行切換，減少異常資料的產生。
資料校驗：為保障資料的完全一致，有必要對資料的數量完整性做校驗。

1.5平滑2N方案（大資料量）

線上資料庫，為了保障其高可用，一般每臺主庫會配置一臺從庫，主庫負責讀寫，從庫負責讀取。下圖所示，A，B是主庫，A0和B0是從庫。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

1、當需要擴容的時候，我們把A0和B0升級為新的主庫節點，如此由2個分庫變為4個分庫。同時在上層的分片配置，做好對映，規則如下:

把uid%4=0和uid%4=2的資料分別分配到A和A0主庫中
把uid%4=1和uid%4=3的資料分配到B和B0主庫中

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

2、因為A和A0庫的資料相同，B和B0資料相同，此時無需做資料遷移。只需調整變更一下分片配置即可，透過配置中心更新，不需要重啟。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

由於之前uid%2的資料是分配在2個庫裡面，擴容之後需要分佈到4個庫中，但由於舊資料仍存在（uid%4=0的節點,還有一半uid%4=2的資料），所以需要對冗餘資料做一次清理。

這個清理，並不會影響線上資料的一致性，可以隨時隨地進行。

3、處理完成之後，為保證資料的高可用，以及將來下一步的擴容需求。

可以為現有的主庫再次分配一個從庫。

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

2. 平滑2N擴容方案實踐

2.1 實現應用服務級別的動態擴容

擴容前部署架構：

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

2.1.1 MariaDB服務安裝

切換阿里雲映象服務（YUM安裝過慢可以切換）

yum -y install wget
## 備份CentOS-Base.repo
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak

wget -O /etc/yum.repos.d/CentOS-Base.repo 

wget -P /etc/yum.repos.d/ 

yum clean all

yum makecache

配置YUM源

vi /etc/yum.repos.d/mariadb-10.2.repo

增加以下內容：

[mariadb]
name = MariaDB
baseurl = 
gpgkey=
gpgcheck=1

執行安裝

yum -y install mariadb mariadb-server MariaDB-client  MariaDB-common

如果之前已經安裝，需要先刪除（如果之前沒有安裝，可以忽略此步驟）

停止Mariadb服務

[root@localhost yum.repos.d]# ps -ef | grep mysql
root       1954      1  0 Oct04 ?        00:05:43 /usr/sbin/mysqld --wsrep-new-cluster --user=root
root      89521  81403  0 07:40 pts/0    00:00:00 grep --color=auto mysql
[root@localhost yum.repos.d]# kill 1954

解除安裝Mariadb服務
```
yum -y remove Maria*
```

刪除資料與配置：

rm -rf /var/lib/mysql/*
rm -rf /etc/my.cnf.d/
rm -rf /etc/my.cnf

啟動MariaDB後，執行安全配置嚮導命令，可根據安全配置嚮導提高資料庫的安全性

systemctl start mariadb

mysql_secure_installation

開啟使用者遠端連線許可權

將連線使用者root開啟遠端連線許可權；

mysql -uroot -p654321

進入MySQL服務，執行以下操作：

use mysql;

delete from user;
## 配置root使用者使用密碼654321從任何主機都可以連線到mysql伺服器
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '654321' WITH GRANT OPTION;

FLUSH PRIVILEGES;

2.1.2 MariaDB雙主同步

在Server1增加配置：

在/etc/my.cnf中新增以下配置：

[mysqld]
server-id  = 1
log-bin=mysql-bin
relay-log = mysql-relay-bin
## 忽略mysql、information_schema庫下對錶的操作
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
## 預設的情況下mysql是關閉的;
log-slave-updates=on
## 複製過程中，有任何錯誤，直接跳過
slave-skip-errors=all
auto-increment-offset=1
auto-increment-increment=2
## binlog的格式：STATEMENT，ROW，MIXED
binlog_format=mixed
## 自動過期清理binlog，預設0天，即不自動清理
expire_logs_days=10

注意， Server1自增為奇數位：

auto-increment-offset=1 主鍵自增基數, 從1開始。
auto-increment-increment=2 主鍵自增偏移量，每次為2。

在Server2增加配置：

修改/etc/my.cnf:

[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

Server2自增為偶數位：

auto-increment-offset=2 主鍵自增基數, 從2開始。
auto-increment-increment=2 主鍵自增偏移量，每次為2。

配置修改完成後，重啟資料庫。

同步授權配置

在Server1建立replica用於主從同步的使用者：

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;

查詢日誌檔案與偏移量，開啟同步時需使用：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
|              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      663 |              |                  |
+------------------+----------+--------------+------------------+

同樣，在Server2建立replica用於主從同步的使用者：

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;

查詢日誌檔案與偏移量：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
|              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      663 |              |                  |
+------------------+----------+--------------+------------------+

配置主從同步資訊

在Server1中執行：

MariaDB [(none)]> change master to master_host='192.168.116.141',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000007', master_log_pos=374, master_connect_retry=30;

在Server2中執行：

MariaDB [(none)]> change master to master_host='192.168.116.140',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000015', master_log_pos=374, master_connect_retry=30;

開啟雙主同步

在Server1和Server2中分別執行：

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

在Server1查詢同步資訊：

MariaDB [(none)]>  show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.20.126
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_: mysql-bin.000001
          Read_Master_Log_Pos: 663
               Relay_Log_: mysql-relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

在Server2查詢同步資訊：

MariaDB [(none)]>  show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.20.125
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_: mysql-bin.000001
          Read_Master_Log_Pos: 663
               Relay_Log_: mysql-relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Slave_IO_Running和Slave_SQL_Running 都是Yes，說明雙主同步配置成功。

2.1.3 KeepAlived安裝與高可用配置

在Server1與Server2兩臺節點安裝keepalived：
```
yum -y install keepalived
```

關閉防火牆

systemctl stop firewalld
systemctl disable firewalld

設定主機名稱：

Server1節點：

hostnamectl set-hostname vip1

Server2節點：

hostnamectl set-hostname vip2

Server1節點配置

/etc/keepalived/keepalived.conf：

global_defs {
   router_id vip1           # 機器標識，和主機名保持一致，執行keepalived伺服器的一個標識
}
vrrp_instance VI_1 {            #vrrp例項定義
    state BACKUP               #lvs的狀態模式，MASTER代表主， BACKUP代表備份節點
    interface ens33               #繫結對外訪問的網路卡，vrrp例項繫結的網路卡
    virtual_router_id 111        #虛擬路由標示，同一個vrrp例項採用唯一標示
    priority 100               #優先順序，100代表最大優先順序， 數字越大優先順序越高
    advert_int 1              #master與backup節點同步檢查的時間間隔，單位是秒
    authentication {           #設定驗證資訊
        auth_type PASS         #有PASS和AH兩種
        auth_pass 6666         #驗證密碼，BACKUP密碼須相同
    }
    virtual_ipaddress {         #KeepAlived虛擬的IP地址
        192.168.116.150
    }
}
virtual_server 192.168.116.150 3306 {       #配置虛擬伺服器IP與訪問埠
    delay_loop 6                 #健康檢查時間
    lb_algo rr                  #負載均衡排程演算法， rr代表輪詢
    lb_kind DR                   #負載均衡轉發規則 DR/NAT/
    persistence_timeout 0        #會話保持時間，這裡要做測試， 所以設為0， 實際可根據session有效時間配置
    protocol TCP               #轉發協議型別，支援TCP和UDP
    real_server 192.168.116.140 3306 {    #配置伺服器節點VIP1    
    notify_down /usr/local/shell/mariadb.sh #當服務掛掉時， 會執行此指令碼，結束keepalived程式
    weight 1               #設定權重，越大權重越高
    TCP_CHECK {              #狀態監測設定
       connect_timeout 10       #超時配置， 單位秒
       retry 3             #重試次數
       delay_before_retry 3        #重試間隔
       connect_port 3306         #連線埠， 和上面保持一致
       }
    }

}

建立關閉指令碼mariadb.sh

/usr/local/shell/mariadb.sh：

pkill keepalived

加入執行許可權：

chmod a+x mariadb.sh

Server2節點配置：

global_defs {
   router_id vip2           # 機器標識，和主機名保持一致，執行keepalived伺服器的一個標識
}
vrrp_instance VI_1 {            #vrrp例項定義
    state BACKUP               #lvs的狀態模式，MASTER代表主， BACKUP代表備份節點
    interface ens33               #繫結對外訪問的網路卡
    virtual_router_id 111        #虛擬路由標示，同一個vrrp例項採用唯一標示
    priority 98               #優先順序，100代表最大優先順序， 數字越大優先順序越高
    advert_int 1              #master與backup節點同步檢查的時間間隔，單位是秒
    authentication {           #設定驗證資訊
        auth_type PASS         #有PASS和AH兩種
        auth_pass 6666         #驗證密碼，BACKUP密碼須相同
    }
    virtual_ipaddress {         #KeepAlived虛擬的IP地址
        192.168.116.150
    }
}
virtual_server 192.168.116.150 3306 {       #配置虛擬伺服器IP與訪問埠
    delay_loop 6                 #健康檢查時間
    lb_algo rr                  #負載均衡排程演算法， rr代表輪詢, 可以關閉
    lb_kind DR                   #負載均衡轉發規則, 可以關閉
    persistence_timeout 0        #會話保持時間，這裡要做測試， 所以設為0， 實際可根據session有效時間配置
    protocol TCP               #轉發協議型別，支援TCP和UDP
    real_server 192.168.116.141 3306{    #配置伺服器節點VIP2
    notify_down /usr/local/shell/mariadb.sh #當服務掛掉時， 會執行此指令碼，結束keepalived程式
    weight 1               #設定權重，越大權重越高
    TCP_CHECK {              #r狀態監測設定
       connect_timeout 10       #超時配置， 單位秒
       retry 3             #重試次數
       delay_before_retry 3        #重試間隔
       connect_port 3306         #連線埠， 和上面保持一致
       }
    }

}

和Server1的差異項：

router_id vip2   # 機器標識，和主機名保持一致
priority 98               #優先順序，100代表最大優先順序， 數字越大優先順序越高
real_server 10.10.20.126 3306  #配置伺服器節點VIP2

注意，兩臺節點都設為BACKUP

virtual_router_id 111        #同一個vrrp例項採用唯一標示
state BACKUP

如果不想重啟後，爭奪備用節點的VIP，可以設定此項

nopreempt #不主動搶佔資源

注意：這個配置只能設定在backup主機上，而且這個主機優先順序要比另外一臺高

驗證高可用
停止主節點MariaDB服務，驗證是否自動切換。

2.1.4 搭建應用服務工程

ShardingJDBC的介紹
是ShardingSphere 下的一個產品
定位為輕量級 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 標準的資料庫

ShardingJDBC初始化流程

1）配置ShardingRuleConfiguration物件

2）配置表分片規則TableRuleConfiguration物件，設定分庫、分表策略

3）透過Factory物件將Rule物件與DataSource物件裝配

4）ShardingJDBC使用DataSource物件進行分庫

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

ShardingJDBC整合配置
1）maven依賴
2）規則配置application.yml
3）建立DataSource

驗證應用服務動態擴容

// 動態資料來源配置實現擴容
Properties properties = loadProperties("datasource1.properties");
try {
    log.info("load datasource config url: " + properties.get("url"));
    DruidDataSource druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
    druidDataSource.setRemoveAbandoned(true);
    druidDataSource.setRemoveAbandonedTimeout(600);
    druidDataSource.setLogAbandoned(true);
    // 設定資料來源錯誤重連時間
    druidDataSource.setTimeBetweenConnectErrorMillis(60000);
    druidDataSource.init();
    OrchestrationShardingDataSource dataSource = SpringContextUtil.getBean("tradeSystemDataSource", OrchestrationShardingDataSource.class);
    Map<String, DataSource> dataSourceMap = dataSource.getDataSource().getDataSourceMap();
    dataSourceMap.put(DatasourceEnum.DATASOURCE_2.getValue(), druidDataSource);

    Map<String, DataSourceConfiguration> dataSourceConfigMap = new HashMap<String, DataSourceConfiguration>();
    for(String key : dataSourceMap.keySet()) {
        dataSourceConfigMap.put(key, DataSourceConfiguration.getDataSourceConfiguration(dataSourceMap.get(key)));
    }
    String result = SHARDING_RULE_TABLE_ORDER.replace(SHARDING_RULE_DATASOURCE, newRule);
    replaceActualDataNodes(result);
    SHARDING_RULE_DATASOURCE = newRule;

    dataSource.renew(new DataSourceChangedEvent(
        "/" + DruidSystemDataSourceConfiguration.DYNAMIC_SHARDING + "/config/schema/logic_db/datasource",
        dataSourceConfigMap));
    return;


} catch (Exception e) {
    log.error(e.getMessage(), e);
}

配置兩個資料來源，分別指向Server1和Server2
分片只配置一個資料來源
動態增加另一個資料來源

注意事項
Sharding JDBC, Mycat, Drds 等產品都是分散式資料庫中介軟體, 相比直接的資料來源操作, 會存在一些限制, Sharding JDBC在使用時, 要注意以下問題:

有限支援子查詢
不支援HAVING
不支援OR，UNION 和 UNION ALL
不支援特殊INSERT
每條INSERT語句只能插入一條資料，不支援VALUES後有多行資料的語句
不支援DISTINCT聚合
不支援dual虛擬表查詢
不支援SELECT LAST_INSERT_ID(), 不支援自增序列
不支援CASE WHEN

2.2 實現資料庫的秒級平滑2N擴容

擴容部署架構：

5大主流方案對比：MySQL千億級資料線上平滑擴容實戰

2.2.1 新增資料庫VIP

在Server2節點，增加VIP

修改/etc/keepalived/keepalived.conf

global_defs {
   router_id vip2
}
vrrp_instance VI_1 {            #vrrp例項定義
    state BACKUP               #lvs的狀態模式，MASTER代表主， BACKUP代表備份節點
    interface ens33               #繫結對外訪問的網路卡
    virtual_router_id 112        #虛擬路由標示，同一個vrrp例項採用唯一標示
    priority 100               #優先順序，100代表最大優先順序， 數字越大優先順序越高
    advert_int 1              #master與backup節點同步檢查的時間間隔，單位是秒
    authentication {           #設定驗證資訊
        auth_type PASS         #有PASS和AH兩種
        auth_pass 6666         #驗證密碼，BACKUP密碼須相同
    }
    virtual_ipaddress {         #KeepAlived虛擬的IP地址
        192.168.116.151
    }
}
virtual_server 192.168.116.151 3306 {       #配置虛擬伺服器IP與訪問埠
    delay_loop 6                 #健康檢查時間
    persistence_timeout 0        #會話保持時間，這裡要做測試， 所以設為0， 實際可根據session有效時間配置
    protocol TCP               #轉發協議型別，支援TCP和UDP
    real_server 192.168.116.141 3306{    #配置伺服器節點VIP1
    notify_down /usr/local/shell/mariadb.sh
    weight 1               #設定權重，越大權重越高
    TCP_CHECK {              #r狀態監測設定
       connect_timeout 10       #超時配置， 單位秒
       retry 3             #重試次數
       delay_before_retry 3        #重試間隔
       connect_port 3306         #連線埠， 和上面保持一致
       }
    }

}

注意配置項：

virtual_router_id 112        #虛擬路由標示，同一個vrrp例項採用唯一標示
priority 100               #優先順序，100代表最大優先順序， 數字越大優先順序越高

2.2.2 應用服務增加動態資料來源

修改應用服務配置，增加新的資料來源，指向新設定的VIP： 192.168.116.151
透過應用服務介面，動態擴容調整

2.2.3 解除原雙主同步

mysql -uroot -p654321

進入Server1:
```
MariaDB [(none)]> stop slave;
```
進入Server2:
```
MariaDB [(none)]> stop slave;
```
透過應用服務介面驗證資料是否解除同步

2.2.4 安裝MariaDB擴容伺服器

新建兩臺虛擬機器，分別為Server3和Server4。
在Server3和Server4兩臺節點上安裝MariaDB服務
參考2.1.1 MariaDB服務安裝

配置Server3與Server1，實現新的雙主同步

[mysqld]
server-id = 3
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

service mariadb restart

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;

mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server1.sql

...
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_='mysql-bin.000002', MASTER_LOG_POS=17748;
...

scp server1.sql root@192.168.116.142:/usr/local/

mysql -uroot -p654321 < /usr/local/server1.sql

根據上面的master status資訊，在Server3中執行：

MariaDB [(none)]> change master to master_host='192.168.116.140',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000016', master_log_pos=1754, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

如果出現問題，復原主從同步資訊：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.20.125
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_: mysql-bin.000004
          Read_Master_Log_Pos: 11174
               Relay_Log_: mysql-relay-bin.000002
                Relay_Log_Pos: 1746
        Relay_Master_Log_: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

檢視Server3的日誌資訊：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
|              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     4781 |              |                  |
+------------------+----------+--------------+------------------+

在Server1節點，配置同步資訊：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to master_host='192.168.116.142',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=6931, master_connect_retry=30;

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

配置Server1與Server3節點的同步
檢查同步狀態資訊：
開啟主從同步：
配置主從同步資訊
將資料還原至Server3節點：
將備份的server1.sql透過scp命令複製至Server3節點。
檢視並記錄master status資訊：
在Server1節點，進行資料全量備份：
建立replica用於主從同步的使用者：
重啟Server3資料庫
Server3節點，修改/etc/my.cnf:

配置Server4與Server2的雙主同步

[mysqld]
server-id = 4
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=information_schema.%
log-slave-updates=on
slave-skip-errors=all
auto-increment-offset=2
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

service mariadb restart

MariaDB [(none)]> grant replication slave, replication client on *.* to 'replica'@'%' identified by 'replica';
mysql> flush privileges;

mysqldump -uroot -p654321 --routines --single_transaction --master-data=2 --databases smooth > server2.sql

...
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_='mysql-bin.000003', MASTER_LOG_POS=4208;

...

scp server2.sql root@192.168.116.143:/usr/local/

mysql -uroot -p654321 < /usr/local/server2.sql

根據上面的master status資訊，在Server4中執行：

MariaDB [(none)]> change master to master_host='192.168.116.141',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000007', master_log_pos=3006, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

注意，如果出現問題，復原主從同步資訊：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.20.125
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_: mysql-bin.000004
          Read_Master_Log_Pos: 11174
               Relay_Log_: mysql-relay-bin.000002
                Relay_Log_Pos: 1746
        Relay_Master_Log_: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

檢視Server4的日誌資訊：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
|              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     3696 |              |                  |
+------------------+----------+--------------+------------------+

在Server2節點，配置同步資訊：

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to master_host='192.168.116.143',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=5787, master_connect_retry=30;

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

配置Server2與Server4節點的同步
檢查同步狀態資訊：
開啟主從同步：
配置主從同步資訊
將資料還原至Server4節點：
將備份的server2.sql透過scp命令複製至Server4節點。
檢視並記錄master status資訊：
在Server2節點，進行資料全量備份：
建立replica用於主從同步的使用者：
重啟Server4資料庫
Server4節點，修改/etc/my.cnf:

2.2.5 增加KeepAlived服務實現高可用

確保新增的Server3和Server4節點安裝Keepalived服務。

修改Server3節點配置

global_defs {
   router_id vip3          # 機器標識，一般設為hostname，故障發生時，郵件通知會使用到。
}
vrrp_instance VI_1 {            #vrrp例項定義
    state BACKUP               #lvs的狀態模式，MASTER代表主， BACKUP代表備份節點
    interface ens33               #繫結對外訪問的網路卡
    virtual_router_id 111        #虛擬路由標示，同一個vrrp例項採用唯一標示
    priority 98               #優先順序，100代表最大優先順序， 數字越大優先順序越高
    advert_int 1              #master與backup節點同步檢查的時間間隔，單位是秒
    authentication {           #設定驗證資訊
        auth_type PASS         #有PASS和AH兩種
        auth_pass 6666         #驗證密碼，BACKUP密碼須相同
    }
    virtual_ipaddress {         #KeepAlived虛擬的IP地址
        192.168.116.150
    }
}
virtual_server 192.168.116.150 3306 {       #配置虛擬伺服器IP與訪問埠
    delay_loop 6                 #健康檢查時間
    persistence_timeout 0        #會話保持時間，這裡要做測試， 所以設為0， 實際可根據session有效時間配置
    protocol TCP               #轉發協議型別，支援TCP和UDP
    real_server 192.168.116.142 3306{    #配置伺服器節點VIP3
    notify_down /usr/local/shell/mariadb.sh
    weight 1               #設定權重，越大權重越高
    TCP_CHECK {              #r狀態監測設定
       connect_timeout 10       #超時配置， 單位秒
       retry 3             #重試次數
       delay_before_retry 3        #重試間隔
       connect_port 3306         #連線埠， 和上面保持一致
       }
    }

}

注意裡面IP配置正確，修改完成後重啟服務。

建立關閉指令碼mariadb.sh

/usr/local/shell/mariadb.sh：

pkill keepalived

加入執行許可權：

chmod a+x mariadb.sh

修改Server4節點配置

global_defs {
   router_id vip4          # 機器標識，一般設為hostname，故障發生時，郵件通知會使用到。
}
vrrp_instance VI_1 {            #vrrp例項定義
    state BACKUP               #lvs的狀態模式，MASTER代表主， BACKUP代表備份節點
    interface ens33               #繫結對外訪問的網路卡
    virtual_router_id 112        #虛擬路由標示，同一個vrrp例項採用唯一標示
    priority 98               #優先順序，100代表最大優先順序， 數字越大優先順序越高
    advert_int 1              #master與backup節點同步檢查的時間間隔，單位是秒
    authentication {           #設定驗證資訊
        auth_type PASS         #有PASS和AH兩種
        auth_pass 6666         #驗證密碼，BACKUP密碼須相同
    }
    virtual_ipaddress {         #KeepAlived虛擬的IP地址
        192.168.116.151
    }
}
virtual_server 192.168.116.151 3306 {       #配置虛擬伺服器IP與訪問埠
    delay_loop 6                 #健康檢查時間
    persistence_timeout 0        #會話保持時間，這裡要做測試， 所以設為0， 實際可根據session有效時間配置
    protocol TCP               #轉發協議型別，支援TCP和UDP
    real_server 192.168.116.143 3306{    #配置伺服器節點VIP4
    notify_down /usr/local/shell/mariadb.sh
    weight 1               #設定權重，越大權重越高
    TCP_CHECK {              #r狀態監測設定
       connect_timeout 10       #超時配置， 單位秒
       retry 3             #重試次數
       delay_before_retry 3        #重試間隔
       connect_port 3306         #連線埠， 和上面保持一致
       }
    }

}

建立關閉指令碼mariadb.sh

/usr/local/shell/mariadb.sh：

pkill keepalived

給所有的使用者組加入執行許可權：

chmod a+x mariadb.sh

修改完後重啟Keepalived服務。

2.2.6 清理資料並驗證

透過應用服務動態擴容介面做調整和驗證
在Server1節點清理資料
根據取模規則，保留accountNo為偶數的資料
```
delete from t_trade_order where accountNo % 2 != 0
```
在Server2節點清理資料
根據取模規則，保留accountNo為奇數的資料
```
delete from t_trade_order where accountNo % 2 != 1
```

3.keepalived高可用配置大全

在Server1（192.168.116.140）中執行：

MariaDB [(none)]> change master to master_host='192.168.116.141',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=3207, master_connect_retry=30;

在Server2（192.168.116.141）中執行：

MariaDB [(none)]> change master to master_host='192.168.116.140',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000012', master_log_pos=1951, master_connect_retry=30;

在Server3（192.168.116.142）中執行：

MariaDB [(none)]> change master to master_host='192.168.116.140',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000013', master_log_pos=2781, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)

在Server4（192.168.116.143）中執行：

MariaDB [(none)]> change master to master_host='192.168.116.141',master_user='replica', master_password='replica', master_port=3306, master_log_='mysql-bin.000005', master_log_pos=7358, master_connect_retry=30;
Query OK, 0 rows affected (0.01 sec)