詳解Mysql資料庫平滑擴容解決高並行和巨量資料量問題

2022-05-24 14:02:36

1 停機方案

釋出公告
停止服務
離線資料遷移（拆分，重新分配資料）
資料校驗
更改設定
恢復服務
回滾預案

2 停寫方案

支援讀寫分離
升級公告
中斷寫操作，隔離寫資料來源（或攔截返回統一提示）
資料同步處理
資料校驗
更改設定
恢復寫操作
資料清理
回滾預案

3 平滑擴容之雙寫方案（中小型資料）

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

增加新庫
資料遷移：避免增量影響，先斷開主從，再匯入（耗時較長），同步完成並做校驗
增量同步：監聽Canal，並開啟主從同步
切換新庫
修復切換異常資料（canal未同步但已切換至新庫的請求），通過客製化程式讀取異常紀錄檔做處理

為什麼不通過MariaDB Galera叢集直接擴充新的伺服器節點去實現擴容？

Galera叢集是多主同步，擴充的節點提升的是並行吞吐能力，資料瓶頸問題未解決。
通過路由中介軟體MyCat，Sharding JDBC等，直接擴充新的節點是否可性？
原資料需要重新分配，比如取模分片。

總結:
這種方案就是水平擴容,提高了並行量,但是實際巨量資料量的瓶頸沒有解決.

4 平滑擴容之2N方案巨量資料量問題解決

4.1 擴容問題

專案初期，部署了資料庫A及其從機A0，B及其從節點B0，為將資料平均分配，在service層，使用uid%2取模分片(可採用sharding JDBC)，將資料分配到對應的資料庫。如下圖

隨著使用者量的增大，使用者產生的資料量最終達到資料庫的最佳儲存容量，需要進行擴容，否則CRUD操作會變慢，影響服務的響應速度。

如何平滑的擴容，保證業務的連續性和高可用，對業務影響最小？

4.2 解決方案

先把A0和B0升級為新的主庫節點，如此，由2個分庫變為4個分庫。同時在上層的分片設定，做好對映，規則如下：

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

由於A和A0庫的資料相同，B和B0庫的資料相同，此時無需資料遷移，只需調整分片設定即可，可通過設定中心更新，不需要重啟。

3. 擴容之後舊資料有冗餘，需要對冗餘資料做清理

-- 針對A，去除%4!=0的
delete from user.userinfo where uid % 4 != 0;
-- 針對A0，去除%4!=2的
delete from user.userinfo where uid % 4 != 2;
-- 針對B，去除%4!=1的
delete from user.userinfo where uid % 4 != 1;
-- 針對B0，去除%4!=3的
delete from user.userinfo where uid % 4 != 3;

處理完成後，為保證資料高可用，需要再次為現有主庫增加一個從庫

以下為雙主架構進行水平擴容,下圖為現在的架構

4.3 雙主架構思想

1.兩臺mysql都可讀寫，互為主備，預設只使用一臺（masterA）負責資料的寫入，另一臺（masterB）備用；

2.masterA是masterB的主庫，masterB又是masterA的主庫，它們互為主從；

3.兩臺主庫之間做高可用,可以採用keepalived等方案（使用VIP對外提供服務）；

4.所有提供服務的從伺服器與masterB進行主從同步（雙主多從）;

5.建議採用高可用策略的時候，masterA或masterB均不因宕機恢復後而搶佔VIP（非搶佔模式）；

這樣做可以在一定程度上保證主庫的高可用,在一臺主庫down掉之後,可以在極短的時間內切換到另一臺主庫上（儘可能減少主庫宕機對業務造成的影響），減少了主從同步給線上主庫帶來的壓力；

但是也有幾個不足的地方:

1.masterB可能會一直處於空閒狀態（可以用它當從庫，負責部分查詢）；

2.主庫後面提供服務的從庫要等masterB先同步完了資料後才能去masterB上去同步資料，這樣可能會造成一定程度的同步延時；

4.4 環境部署

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

yum -y install wget
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
http://mirrors.aliyun.com/repo/Centos-7.repo
wget -P /etc/yum.repos.d/ http://mirrors.aliyun.com/repo/epel-7.repo
yum clean all
yum makecache

設定YUM源

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

增加以下內容：

[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
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

初始化設定

systemctl  start mariadb
mysql_secure_installation

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

mysql -uroot -p654321

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

use mysql;
delete from user;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '654321' WITH GRANT
OPTION;
FLUSH PRIVILEGES;

4.4.2 MariaDB雙主同步

在Server1增加設定：

在/etc/my.cnf中新增以下設定：

[mysqld]
server-id  = 1
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=1
auto-increment-increment=2
binlog_format=mixed
expire_logs_days=10

注意， Server1自增為奇數位：
auto-increment-offset=1 主鍵自增基數, 從1開始。
auto-increment-increment=2 主鍵自增偏移量，每次為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。
設定修改完成後，重啟資料庫。
3. 同步授權設定
在Server1建立replica用於主從同步的使用者：

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

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

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File       | 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;
+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    663 |       |         |
+------------------+----------+--------------+------------------+

設定主從同步資訊
在Server1中執行：

MariaDB [(none)]> change master to
master_host='10.10.20.126',master_user='replica', master_password='replica',
master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=663,
master_connect_retry=30;

在Server2中執行：

MariaDB [(none)]> change master to
master_host='10.10.20.125',master_user='replica', master_password='replica',
master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=663,
master_connect_retry=30;

開啟雙主同步
在Server1和Server2中分別執行：

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

在Server1查詢同步資訊：

MariaDB [(none)]> show slave statusG;
*************************** 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_File: mysql-bin.000001
    Read_Master_Log_Pos: 663
       Relay_Log_File: mysql-relay-bin.000002
       Relay_Log_Pos: 555
   Relay_Master_Log_File: mysql-bin.000001
      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
...

在Server2查詢同步資訊：

MariaDB [(none)]> show slave statusG;
*************************** 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_File: mysql-bin.000001
    Read_Master_Log_Pos: 663
       Relay_Log_File: mysql-relay-bin.000002
       Relay_Log_Pos: 555
   Relay_Master_Log_File: mysql-bin.000001
      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
...

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

4.4.3 KeepAlived安裝與高可用設定

在Server1與Server2兩臺節點安裝keepalived：

yum -y install keepalived

2.關閉防火牆

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      # 機器標識，和主機名保持一致
}
vrrp_instance VI_1 {       #vrrp範例定義
 state BACKUP        #lvs的狀態模式，MASTER代表主， BACKUP代表備份節點
 interface ens33        #繫結對外存取的網路卡
 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地址
    10.10.20.130
 }
}
virtual_server 10.10.20.130 3306 {    #設定虛擬伺服器IP與存取埠
 delay_loop 6         #健康檢查時間
 lb_algo rr          #負載均衡排程演演算法， rr代表輪詢
 lb_kind DR          #負載均衡轉發規則
 persistence_timeout 0     #對談保持時間，這裡要做測試， 所以設為0， 實際可根
據session有效時間設定
 protocol TCP        #轉發協定型別，支援TCP和UDP
 real_server 10.10.20.125 3306 {   #設定伺服器節點VIP1  
 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     #連線埠， 和上面保持一致
   }
 }
}

建立關閉指令碼mariadb.sh
/usr/local/shell/mariadb.sh：pkill keepalived
加入執行許可權：chmod a+x mariadb.sh

5.Server2節點設定：

global_defs {
 router_id vip2      # 機器標識，和主機名保持一致
}
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地址
    10.10.20.130
 }
}
virtual_server 10.10.20.130 3306 {    #設定虛擬伺服器IP與存取埠
 delay_loop 6         #健康檢查時間
 lb_algo rr          #負載均衡排程演演算法， rr代表輪詢, 可以關閉
 lb_kind DR          #負載均衡轉發規則, 可以關閉
 persistence_timeout 0     #對談保持時間，這裡要做測試， 所以設為0， 實際可根
據session有效時間設定
 protocol TCP        #轉發協定型別，支援TCP和UDP
 real_server 10.10.20.126 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 #不主動搶佔資源

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

4.4.4 搭建應用服務工程

ShardingJDBC的整合設定
ShardingJDBC資料來源的動態切換實現
驗證應用服務動態擴容
- 設定兩個資料來源，分別指向Server1和Server2
- 分片只設定一個資料來源
- 動態增加另一個資料來源
- 動態擴容的驗證
- 動態擴容的復原驗證（模擬節點異常，實現復原單節點資料來源）
- 採用動態資料來源設定實現擴容（擴容實踐中改造）

// 動態資料來源設定實現擴容
Properties properties = loadPropertiesFile("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);
}

注意事項
Sharding JDBC, Mycat, Drds 等產品都是分散式資料庫中介軟體, 相比直接的資料來源操作, 會存在一些
限制, Sharding JDBC在使用時, 要注意以下問題:
有限支援子查詢
不支援HAVING
不支援OR，UNION 和 UNION ALL
不支援特殊INSERT
每條INSERT語句只能插入一條資料，不支援VALUES後有多行資料的語句
不支援DISTINCT聚合
不支援dual虛擬表查詢
不支援SELECT LAST_INSERT_ID(), 不支援自增序列
不支援CASE WHEN

5 資料庫秒級平滑2N擴容實踐

擴容部署架構：

5.1 新增資料庫VIP

在Server2節點，增加VIP
修改/etc/keepalived/keepalived.conf，追加：

...
vrrp_instance VI_2 {       #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地址
    10.10.20.131
 }
}
virtual_server 10.10.20.131 3306 {    #設定虛擬伺服器IP與存取埠
 delay_loop 6         #健康檢查時間
 persistence_timeout 0     #對談保持時間，這裡要做測試， 所以設為0， 實際可根
據session有效時間設定
 protocol TCP        #轉發協定型別，支援TCP和UDP
 real_server 10.10.20.126 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代表最大優先順序， 數位越大優先順序越高

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

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

5.3 解除原雙主同步

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

5.4 安裝MariaDB擴容伺服器

新建兩臺虛擬機器器，分別為Server3和Server4。
在Server3和Server4兩臺節點上安裝MariaDB服務
- 參考 MariaDB服務安裝
設定Server3與Server1，實現新的雙主同步

Server3節點，修改/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

重啟Server3資料庫

service mariadb restart

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

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

在Server1節點，進行資料全量備份：

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

檢視並記錄master status資訊：

...
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=17748;
...

將備份的server1.sql通過scp命令拷貝至Server3節點。scp server1.sql root@10.10.20.127:/usr/local/將資料還原至Server3節點：mysql -uroot -p654321 < /usr/local/server1.sql設定主從同步資訊

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

MariaDB [(none)]> change master to
master_host='10.10.20.125',master_user='replica', master_password='replica',
master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=17748,
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_File: mysql-bin.000004
    Read_Master_Log_Pos: 11174
       Relay_Log_File: mysql-relay-bin.000002
       Relay_Log_Pos: 1746
   Relay_Master_Log_File: mysql-bin.000004
      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

設定Server1與Server3節點的同步
檢視Server3的紀錄檔資訊：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File       | 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='10.10.20.127',master_user='replica', master_password='replica',
master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=4781,
master_connect_retry=30;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

設定Server4與Server2的雙主同步

Server4節點，修改/etc/my.cnf:

[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

重啟Server4資料庫

service mariadb restart

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

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

在Server2節點，進行資料全量備份：

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

檢視並記錄master status資訊：

...
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=4208;
...

將備份的server2.sql通過scp命令拷貝至Server4節點。scp server2.sql root@10.10.20.128:/usr/local/

將資料還原至Server4節點：mysql -uroot -p654321 < /usr/local/server2.sql

設定主從同步資訊
根據上面的master status資訊，在Server4中執行：

MariaDB [(none)]> change master to
master_host='10.10.20.126',master_user='replica', master_password='replica',
master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=4208,
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_File: mysql-bin.000004
    Read_Master_Log_Pos: 11174
       Relay_Log_File: mysql-relay-bin.000002
       Relay_Log_Pos: 1746
   Relay_Master_Log_File: mysql-bin.000004
      Slave_IO_Running: Yes
     Slave_SQL_Running: Yes

設定Server2與Server4節點的同步
檢視Server4的紀錄檔資訊：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File       | 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='10.10.20.128',master_user='replica', master_password='replica',
master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=3696,
master_connect_retry=30;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

5.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地址
    10.10.20.130
 }
}
virtual_server 10.10.20.130 3306 {    #設定虛擬伺服器IP與存取埠
 delay_loop 6         #健康檢查時間
 persistence_timeout 0     #對談保持時間，這裡要做測試， 所以設為0， 實際可根
據session有效時間設定
 protocol TCP        #轉發協定型別，支援TCP和UDP
 real_server 10.10.20.127 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

3. 修改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地址
    10.10.20.131
 }
}
virtual_server 10.10.20.131 3306 {    #設定虛擬伺服器IP與存取埠
 delay_loop 6         #健康檢查時間
  persistence_timeout 0     #對談保持時間，這裡要做測試， 所以設為0， 實際可根
據session有效時間設定
 protocol TCP        #轉發協定型別，支援TCP和UDP
 real_server 10.10.20.128 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

4. 修改Server2節點的keepAlived設定:

global_defs {
 router_id vip2      # 機器標識，一般設為hostname，故障發生時，郵件通知會使用
到。
}
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地址
    10.10.20.131
 }
}
virtual_server 10.10.20.131 3306 {    #設定虛擬伺服器IP與存取埠
 delay_loop 6         #健康檢查時間
 persistence_timeout 0     #對談保持時間，這裡要做測試， 所以設為0， 實際可根
據session有效時間設定
 protocol TCP        #轉發協定型別，支援TCP和UDP
 real_server 10.10.20.126 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     #連線埠， 和上面保持一致
   }
 }
}

修改完後重啟Keepalived服務。

5.6 清理資料並驗證

通過應用服務動態擴容介面做調整和驗證

在Server1節點清理資料
根據取模規則，保留accountNo為偶數的資料

delete from t_trade_order where accountNo % 2 != 0

在Server2節點清理資料
根據取模規則，保留accountNo為奇數的資料

delete from t_trade_order where accountNo % 2 != 1

到此這篇關於詳解Mysql資料庫平滑擴容解決高並行和巨量資料量問題的文章就介紹到這了,更多相關Mysql資料庫平滑擴容內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！