Ubuntu 22.04 MySQL Router 負載平衡

前言

在現代化的資料庫架構中，高可用性和負載平衡是不可或缺的重要元素。MySQL Router 是 Oracle 官方提供的輕量級中介軟體，專為 MySQL 設計，可作為應用程式與 MySQL 伺服器之間的透明路由層。透過 MySQL Router，您可以實現讀寫分離、負載平衡，以及與 InnoDB Cluster 的無縫整合。

本文將詳細介紹如何在 Ubuntu 22.04 上安裝、設定和優化 MySQL Router，以建立穩定且高效能的資料庫存取架構。

MySQL Router 概述

什麼是 MySQL Router？

MySQL Router 是 MySQL 官方提供的資料庫路由器，它位於應用程式和 MySQL 伺服器之間，負責將連線請求智慧地分配到適當的後端資料庫節點。MySQL Router 是 MySQL InnoDB Cluster 的重要組成部分，但也可以獨立使用於其他 MySQL 複製架構。

主要功能

連線路由：將客戶端連線自動路由到可用的 MySQL 伺服器
負載平衡：在多個 MySQL 節點之間分配讀取查詢
讀寫分離：自動將寫入操作導向主節點，讀取操作分散到從節點
自動故障轉移：當主節點失效時，自動切換到新的主節點
連線池管理：有效管理和重用資料庫連線

架構說明

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
                    ┌─────────────────┐
                    │   Application   │
                    └────────┬────────┘
                             │
                    ┌────────▼────────┐
                    │  MySQL Router   │
                    └────────┬────────┘
                             │
        ┌────────────────────┼────────────────────┐
        │                    │                    │
┌───────▼───────┐   ┌───────▼───────┐   ┌───────▼───────┐
│ MySQL Primary │   │ MySQL Replica │   │ MySQL Replica │
│   (Read/Write)│   │   (Read Only) │   │   (Read Only) │
└───────────────┘   └───────────────┘   └───────────────┘

MySQL Router 與 ProxySQL 比較

功能	MySQL Router	ProxySQL
官方支援	Oracle 官方	第三方開源
InnoDB Cluster 整合	原生支援	需額外設定
查詢快取	不支援	支援
查詢改寫	不支援	支援
設定複雜度	簡單	較複雜
效能開銷	極低	低

安裝與基本設定

系統需求

Ubuntu 22.04 LTS
MySQL 8.0 或更高版本
至少 1GB RAM
網路連通至 MySQL 伺服器

安裝 MySQL Router

方法一：使用 APT 套件管理器

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 更新套件清單
sudo apt update

# 安裝 MySQL APT 設定工具
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb

# 更新並安裝 MySQL Router
sudo apt update
sudo apt install mysql-router -y

方法二：直接從 MySQL 官方倉庫安裝

1
2
3
4
5
6
7
8
9
# 添加 MySQL 官方 GPG 金鑰
wget -qO - https://repo.mysql.com/RPM-GPG-KEY-mysql-2023 | sudo gpg --dearmor -o /usr/share/keyrings/mysql-archive-keyring.gpg

# 添加 MySQL 倉庫
echo "deb [signed-by=/usr/share/keyrings/mysql-archive-keyring.gpg] http://repo.mysql.com/apt/ubuntu jammy mysql-8.0" | sudo tee /etc/apt/sources.list.d/mysql.list

# 安裝 MySQL Router
sudo apt update
sudo apt install mysql-router -y

驗證安裝

1
2
3
4
5
# 檢查版本
mysqlrouter --version

# 檢查服務狀態
sudo systemctl status mysqlrouter

目錄結構

安裝完成後，MySQL Router 的主要檔案位置如下：

路徑	說明
`/etc/mysqlrouter/`	設定檔目錄
`/var/lib/mysqlrouter/`	資料目錄
`/var/log/mysqlrouter/`	日誌目錄
`/usr/bin/mysqlrouter`	執行檔
`/usr/bin/mysqlrouter_keyring`	金鑰環工具

基本設定檔

建立基本設定檔 /etc/mysqlrouter/mysqlrouter.conf：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

[routing:primary]
bind_address = 0.0.0.0
bind_port = 6446
destinations = 192.168.1.10:3306,192.168.1.11:3306,192.168.1.12:3306
routing_strategy = first-available
protocol = classic

[routing:secondary]
bind_address = 0.0.0.0
bind_port = 6447
destinations = 192.168.1.11:3306,192.168.1.12:3306,192.168.1.10:3306
routing_strategy = round-robin
protocol = classic

啟動服務

1
2
3
4
5
6
7
8
# 啟動 MySQL Router
sudo systemctl start mysqlrouter

# 設定開機自動啟動
sudo systemctl enable mysqlrouter

# 檢查服務狀態
sudo systemctl status mysqlrouter

路由模式

MySQL Router 支援多種路由策略，可根據不同的應用場景選擇適合的模式。

路由策略類型

1. first-available（首選可用）

將所有連線導向第一個可用的伺服器，只有當該伺服器不可用時才切換到下一個。適用於主從架構的寫入操作。

1
2
3
4
5
6
[routing:write]
bind_address = 0.0.0.0
bind_port = 6446
destinations = primary.mysql.local:3306,secondary1.mysql.local:3306
routing_strategy = first-available
protocol = classic

2. round-robin（輪詢）

平均分配連線到所有可用的後端伺服器。適用於讀取負載平衡。

1
2
3
4
5
6
[routing:read]
bind_address = 0.0.0.0
bind_port = 6447
destinations = secondary1.mysql.local:3306,secondary2.mysql.local:3306,secondary3.mysql.local:3306
routing_strategy = round-robin
protocol = classic

3. round-robin-with-fallback（帶故障轉移的輪詢）

在指定的伺服器之間輪詢，當所有伺服器都不可用時，回退到備用伺服器。

1
2
3
4
5
6
[routing:read_fallback]
bind_address = 0.0.0.0
bind_port = 6448
destinations = secondary1.mysql.local:3306,secondary2.mysql.local:3306
routing_strategy = round-robin-with-fallback
protocol = classic

讀寫分離設定

完整的讀寫分離設定範例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO
filename = mysqlrouter.log
timestamp_precision = second

# 寫入端口 - 連接到主節點
[routing:primary_rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = mysql-primary.local:3306
routing_strategy = first-available
protocol = classic
max_connections = 512
max_connect_errors = 100
client_connect_timeout = 9
connect_timeout = 5

# 讀取端口 - 負載平衡到所有從節點
[routing:secondary_ro]
bind_address = 0.0.0.0
bind_port = 6447
destinations = mysql-replica1.local:3306,mysql-replica2.local:3306,mysql-replica3.local:3306
routing_strategy = round-robin
protocol = classic
max_connections = 1024
max_connect_errors = 100
client_connect_timeout = 9
connect_timeout = 5

應用程式連線範例

Python 範例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
import mysql.connector
from mysql.connector import pooling

# 建立寫入連線池
write_pool = pooling.MySQLConnectionPool(
    pool_name="write_pool",
    pool_size=5,
    host="mysql-router.local",
    port=6446,
    user="app_user",
    password="secure_password",
    database="myapp"
)

# 建立讀取連線池
read_pool = pooling.MySQLConnectionPool(
    pool_name="read_pool",
    pool_size=10,
    host="mysql-router.local",
    port=6447,
    user="app_user",
    password="secure_password",
    database="myapp"
)

# 寫入操作使用 6446 端口
def insert_data(data):
    conn = write_pool.get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", data)
        conn.commit()
    finally:
        cursor.close()
        conn.close()

# 讀取操作使用 6447 端口
def get_users():
    conn = read_pool.get_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM users")
        return cursor.fetchall()
    finally:
        cursor.close()
        conn.close()

PHP 範例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<?php
// 資料庫設定
$write_config = [
    'host' => 'mysql-router.local',
    'port' => 6446,
    'user' => 'app_user',
    'password' => 'secure_password',
    'database' => 'myapp'
];

$read_config = [
    'host' => 'mysql-router.local',
    'port' => 6447,
    'user' => 'app_user',
    'password' => 'secure_password',
    'database' => 'myapp'
];

// 寫入連線
function getWriteConnection($config) {
    $mysqli = new mysqli(
        $config['host'],
        $config['user'],
        $config['password'],
        $config['database'],
        $config['port']
    );

    if ($mysqli->connect_error) {
        throw new Exception("Connection failed: " . $mysqli->connect_error);
    }

    return $mysqli;
}

// 讀取連線
function getReadConnection($config) {
    $mysqli = new mysqli(
        $config['host'],
        $config['user'],
        $config['password'],
        $config['database'],
        $config['port']
    );

    if ($mysqli->connect_error) {
        throw new Exception("Connection failed: " . $mysqli->connect_error);
    }

    return $mysqli;
}
?>

與 InnoDB Cluster 整合

MySQL Router 與 InnoDB Cluster 的整合是其最強大的功能之一。InnoDB Cluster 由 MySQL Group Replication、MySQL Shell 和 MySQL Router 組成，提供完整的高可用性解決方案。

InnoDB Cluster 架構

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
                        ┌─────────────────┐
                        │   Application   │
                        └────────┬────────┘
                                 │
                        ┌────────▼────────┐
                        │  MySQL Router   │
                        │  (Auto-config)  │
                        └────────┬────────┘
                                 │
            ┌────────────────────┼────────────────────┐
            │                    │                    │
    ┌───────▼───────┐   ┌───────▼───────┐   ┌───────▼───────┐
    │    Primary    │◄──│   Secondary   │◄──│   Secondary   │
    │  (R/W Node)   │──►│  (R/O Node)   │──►│  (R/O Node)   │
    └───────────────┘   └───────────────┘   └───────────────┘
            │                    │                    │
            └────────────────────┴────────────────────┘
                      Group Replication

建立 InnoDB Cluster

首先，使用 MySQL Shell 建立 InnoDB Cluster：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# 連接到 MySQL Shell
mysqlsh

# 連接到主節點
\connect root@mysql-node1:3306

# 設定執行個體
dba.configureInstance('root@mysql-node1:3306')
dba.configureInstance('root@mysql-node2:3306')
dba.configureInstance('root@mysql-node3:3306')

# 建立 Cluster
var cluster = dba.createCluster('myCluster')

# 添加節點
cluster.addInstance('root@mysql-node2:3306')
cluster.addInstance('root@mysql-node3:3306')

# 檢查 Cluster 狀態
cluster.status()

Router Bootstrap 自動設定

使用 Bootstrap 模式自動設定 MySQL Router 與 InnoDB Cluster 的整合：

1
2
3
4
5
6
7
8
9
# 建立 Router 使用者（在 MySQL Shell 中）
cluster.setupRouterAccount('router_user@'%'')

# 執行 Bootstrap
sudo mysqlrouter --bootstrap root@mysql-node1:3306 \
    --user=mysqlrouter \
    --directory=/var/lib/mysqlrouter \
    --conf-use-sockets \
    --conf-use-gr-notifications

Bootstrap 執行後會自動產生設定檔，包含：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
[DEFAULT]
name = system
user = mysqlrouter
logging_folder = /var/lib/mysqlrouter/log
runtime_folder = /var/lib/mysqlrouter/run
data_folder = /var/lib/mysqlrouter/data
keyring_path = /var/lib/mysqlrouter/data/keyring
master_key_path = /var/lib/mysqlrouter/mysqlrouter.key
connect_timeout = 5
read_timeout = 30
dynamic_state = /var/lib/mysqlrouter/data/state.json

[logger]
level = INFO

[metadata_cache:myCluster]
cluster_type = gr
router_id = 1
user = router_user
metadata_cluster = myCluster
ttl = 0.5
auth_cache_ttl = -1
auth_cache_refresh_interval = 2
use_gr_notifications = 1

[routing:myCluster_rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = metadata-cache://myCluster/?role=PRIMARY
routing_strategy = first-available
protocol = classic

[routing:myCluster_ro]
bind_address = 0.0.0.0
bind_port = 6447
destinations = metadata-cache://myCluster/?role=SECONDARY
routing_strategy = round-robin-with-fallback
protocol = classic

[routing:myCluster_x_rw]
bind_address = 0.0.0.0
bind_port = 6448
destinations = metadata-cache://myCluster/?role=PRIMARY
routing_strategy = first-available
protocol = x

[routing:myCluster_x_ro]
bind_address = 0.0.0.0
bind_port = 6449
destinations = metadata-cache://myCluster/?role=SECONDARY
routing_strategy = round-robin-with-fallback
protocol = x

Metadata Cache 設定詳解

Metadata Cache 是 MySQL Router 與 InnoDB Cluster 整合的核心機制：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[metadata_cache:myCluster]
# Cluster 類型：gr (Group Replication)
cluster_type = gr

# Router 唯一識別碼
router_id = 1

# 存取 metadata 的使用者
user = router_user

# Cluster 名稱
metadata_cluster = myCluster

# metadata 更新間隔（秒）
ttl = 0.5

# 認證快取 TTL
auth_cache_ttl = -1

# 認證快取更新間隔
auth_cache_refresh_interval = 2

# 啟用 Group Replication 通知
use_gr_notifications = 1

端口分配說明

端口	用途	協定
6446	讀寫連線	Classic MySQL
6447	唯讀連線	Classic MySQL
6448	讀寫連線	X Protocol
6449	唯讀連線	X Protocol

Bootstrap 自動設定

Bootstrap 是 MySQL Router 最便利的設定方式，可自動從 InnoDB Cluster 取得設定資訊。

Bootstrap 選項詳解

1
mysqlrouter --bootstrap <user>@<host>:<port> [options]

常用選項：

選項	說明
`--directory=<path>`	指定設定目錄
`--user=<user>`	執行 Router 的系統使用者
`--name=<name>`	Router 實例名稱
`--force`	強制覆蓋現有設定
`--conf-use-sockets`	同時監聽 Unix socket
`--conf-use-gr-notifications`	啟用 GR 通知
`--conf-bind-address`	指定綁定位址
`--conf-base-port=<port>`	設定基礎端口
`--account=<user>`	指定 Router 使用者
`--account-create=<mode>`	使用者建立模式

完整 Bootstrap 範例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# 建立專用目錄
sudo mkdir -p /opt/mysqlrouter
sudo chown mysqlrouter:mysqlrouter /opt/mysqlrouter

# 執行 Bootstrap
sudo mysqlrouter --bootstrap admin@mysql-primary:3306 \
    --directory=/opt/mysqlrouter \
    --user=mysqlrouter \
    --name=router-01 \
    --conf-use-sockets \
    --conf-use-gr-notifications \
    --conf-bind-address=0.0.0.0 \
    --conf-base-port=6446 \
    --account=router_admin \
    --account-create=if-not-exists \
    --force

產生的檔案結構

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
/opt/mysqlrouter/
├── data/
│   ├── keyring
│   └── state.json
├── log/
│   └── mysqlrouter.log
├── run/
│   └── mysqlrouter.pid
├── mysqlrouter.conf
├── mysqlrouter.key
├── start.sh
└── stop.sh

使用 systemd 管理

建立 systemd 服務檔案 /etc/systemd/system/mysqlrouter.service：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
[Unit]
Description=MySQL Router
After=network.target

[Service]
Type=simple
User=mysqlrouter
Group=mysqlrouter
ExecStart=/usr/bin/mysqlrouter -c /opt/mysqlrouter/mysqlrouter.conf
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

啟用服務：

1
2
3
sudo systemctl daemon-reload
sudo systemctl enable mysqlrouter
sudo systemctl start mysqlrouter

連線池與效能調校

連線相關設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
[routing:primary_rw]
bind_address = 0.0.0.0
bind_port = 6446
destinations = metadata-cache://myCluster/?role=PRIMARY
routing_strategy = first-available
protocol = classic

# 最大連線數
max_connections = 1024

# 最大連線錯誤次數（超過後暫時封鎖客戶端）
max_connect_errors = 100

# 客戶端連線逾時（秒）
client_connect_timeout = 9

# 後端連線逾時（秒）
connect_timeout = 5

# 連線空閒逾時（秒）
connection_sharing_delay = 1

效能優化參數

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[DEFAULT]
# 執行緒數量（0 = 自動偵測）
max_total_connections = 4096

# I/O 執行緒數
io_threads = 0

# 連線共享設定
connection_sharing = 1

[routing:read_pool]
bind_address = 0.0.0.0
bind_port = 6447
destinations = metadata-cache://myCluster/?role=SECONDARY
routing_strategy = round-robin

# 啟用連線共享
connection_sharing = 1

# 連線共享延遲（毫秒）
connection_sharing_delay = 1000

# 未使用連線的無效逾時
unreachable_destination_refresh_interval = 1

系統層級優化

調整系統限制

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 編輯 /etc/security/limits.conf
cat >> /etc/security/limits.conf << EOF
mysqlrouter soft nofile 65535
mysqlrouter hard nofile 65535
mysqlrouter soft nproc 65535
mysqlrouter hard nproc 65535
EOF

# 編輯 /etc/sysctl.conf
cat >> /etc/sysctl.conf << EOF
# MySQL Router 優化
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15
net.ipv4.ip_local_port_range = 1024 65535
EOF

# 套用設定
sudo sysctl -p

systemd 服務限制

編輯 /etc/systemd/system/mysqlrouter.service.d/limits.conf：

1
2
3
[Service]
LimitNOFILE=65535
LimitNPROC=65535

效能監控指標

使用 MySQL Router REST API 監控效能：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 啟用 REST API（在 mysqlrouter.conf 中添加）
cat >> /opt/mysqlrouter/mysqlrouter.conf << EOF

[http_server]
port = 8443
ssl = 1
ssl_cert = /opt/mysqlrouter/data/router-cert.pem
ssl_key = /opt/mysqlrouter/data/router-key.pem

[http_auth_realm:default_auth_realm]
backend = default_auth_backend
method = basic
name = default_realm

[rest_router]
require_realm = default_auth_realm

[rest_api]

[http_auth_backend:default_auth_backend]
backend = metadata_cache
EOF

監控與故障排除

日誌設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
[logger]
# 日誌等級：DEBUG, INFO, WARNING, ERROR, FATAL
level = INFO

# 日誌檔案名稱
filename = mysqlrouter.log

# 時間戳精度
timestamp_precision = second

# 日誌目的地：file, consolelog, syslog
sinks = file,consolelog

日誌分析

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# 即時監控日誌
sudo tail -f /var/log/mysqlrouter/mysqlrouter.log

# 搜尋錯誤訊息
sudo grep -i error /var/log/mysqlrouter/mysqlrouter.log

# 搜尋連線問題
sudo grep -i "connection\|connect" /var/log/mysqlrouter/mysqlrouter.log

# 統計錯誤類型
sudo grep -i error /var/log/mysqlrouter/mysqlrouter.log | \
    awk '{print $NF}' | sort | uniq -c | sort -rn

REST API 監控

取得路由狀態

1
2
3
4
5
6
7
8
# 取得所有路由狀態
curl -k -u admin:password https://localhost:8443/api/20190715/router/status

# 取得特定路由連線數
curl -k -u admin:password https://localhost:8443/api/20190715/routes/myCluster_rw/connections

# 取得 metadata cache 狀態
curl -k -u admin:password https://localhost:8443/api/20190715/metadata/myCluster/status

監控腳本範例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
#!/bin/bash
# mysql_router_monitor.sh

ROUTER_HOST="localhost"
ROUTER_PORT="8443"
ROUTER_USER="admin"
ROUTER_PASS="password"

# 取得 Router 狀態
get_status() {
    curl -s -k -u ${ROUTER_USER}:${ROUTER_PASS} \
        https://${ROUTER_HOST}:${ROUTER_PORT}/api/20190715/router/status
}

# 取得連線數
get_connections() {
    local route=$1
    curl -s -k -u ${ROUTER_USER}:${ROUTER_PASS} \
        https://${ROUTER_HOST}:${ROUTER_PORT}/api/20190715/routes/${route}/connections | \
        jq '.items | length'
}

# 檢查健康狀態
check_health() {
    local status=$(get_status)
    local process_id=$(echo $status | jq -r '.processId')

    if [ "$process_id" != "null" ] && [ -n "$process_id" ]; then
        echo "OK: MySQL Router is running (PID: $process_id)"
        return 0
    else
        echo "CRITICAL: MySQL Router is not responding"
        return 2
    fi
}

# 主程式
case "$1" in
    status)
        get_status | jq .
        ;;
    connections)
        echo "RW Connections: $(get_connections myCluster_rw)"
        echo "RO Connections: $(get_connections myCluster_ro)"
        ;;
    health)
        check_health
        ;;
    *)
        echo "Usage: $0 {status|connections|health}"
        exit 1
        ;;
esac

常見問題排除

問題 1：無法連線到後端資料庫

1
2
3
4
5
6
7
8
# 檢查網路連通性
telnet mysql-primary 3306

# 檢查 MySQL Router 日誌
sudo tail -100 /var/log/mysqlrouter/mysqlrouter.log | grep -i error

# 驗證 destinations 設定
mysqlrouter --config /etc/mysqlrouter/mysqlrouter.conf --help

問題 2：連線數過高

1
2
3
4
5
6
7
8
9
# 檢查當前連線數
ss -tnp | grep mysqlrouter | wc -l

# 檢查系統限制
cat /proc/$(pgrep mysqlrouter)/limits | grep "Max open files"

# 調整 max_connections
sudo sed -i 's/max_connections = 512/max_connections = 2048/' /etc/mysqlrouter/mysqlrouter.conf
sudo systemctl restart mysqlrouter

問題 3：Metadata cache 更新延遲

1
2
3
4
5
6
# 檢查 metadata cache 狀態
curl -k -u admin:password https://localhost:8443/api/20190715/metadata/myCluster/status

# 調整 TTL 設定
sudo sed -i 's/ttl = 0.5/ttl = 0.25/' /etc/mysqlrouter/mysqlrouter.conf
sudo systemctl restart mysqlrouter

問題 4：故障轉移不正常

1
2
3
4
5
6
# 檢查 Group Replication 狀態
mysql -h mysql-primary -u root -p -e "SELECT * FROM performance_schema.replication_group_members;"

# 強制更新 metadata
curl -k -u admin:password -X POST \
    https://localhost:8443/api/20190715/metadata/myCluster/refresh

效能診斷

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# 使用 mysqlslap 測試效能
mysqlslap --host=localhost --port=6446 --user=testuser --password=testpass \
    --auto-generate-sql --auto-generate-sql-load-type=mixed \
    --number-of-queries=10000 --concurrency=50

# 比較直連與透過 Router 的效能
# 直連測試
mysqlslap --host=mysql-primary --port=3306 --user=testuser --password=testpass \
    --auto-generate-sql --number-of-queries=10000 --concurrency=50

# 透過 Router 測試
mysqlslap --host=localhost --port=6446 --user=testuser --password=testpass \
    --auto-generate-sql --number-of-queries=10000 --concurrency=50

高可用架構設計

多 Router 部署架構

為了避免 MySQL Router 成為單點故障，建議部署多個 Router 實例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
                    ┌─────────────────┐
                    │  Load Balancer  │
                    │    (HAProxy)    │
                    └────────┬────────┘
                             │
            ┌────────────────┼────────────────┐
            │                │                │
    ┌───────▼───────┐┌───────▼───────┐┌───────▼───────┐
    │ MySQL Router  ││ MySQL Router  ││ MySQL Router  │
    │   (Node 1)    ││   (Node 2)    ││   (Node 3)    │
    └───────┬───────┘└───────┬───────┘└───────┬───────┘
            │                │                │
            └────────────────┼────────────────┘
                             │
                    ┌────────▼────────┐
                    │  InnoDB Cluster │
                    └─────────────────┘

HAProxy 設定範例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# /etc/haproxy/haproxy.cfg

global
    log /dev/log local0
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin
    stats timeout 30s
    user haproxy
    group haproxy
    daemon

defaults
    log global
    mode tcp
    option tcplog
    option dontlognull
    timeout connect 5000
    timeout client  50000
    timeout server  50000

# MySQL Router 讀寫端口
frontend mysql_rw_front
    bind *:3306
    default_backend mysql_rw_back

backend mysql_rw_back
    balance roundrobin
    option mysql-check user haproxy_check
    server router1 192.168.1.101:6446 check
    server router2 192.168.1.102:6446 check backup
    server router3 192.168.1.103:6446 check backup

# MySQL Router 唯讀端口
frontend mysql_ro_front
    bind *:3307
    default_backend mysql_ro_back

backend mysql_ro_back
    balance roundrobin
    option mysql-check user haproxy_check
    server router1 192.168.1.101:6447 check
    server router2 192.168.1.102:6447 check
    server router3 192.168.1.103:6447 check

Keepalived 高可用設定

在兩台 Router 節點上設定 Keepalived，提供虛擬 IP：

主節點設定（Router 1）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# /etc/keepalived/keepalived.conf

global_defs {
    router_id MYSQL_ROUTER_1
    script_user root
    enable_script_security
}

vrrp_script check_mysqlrouter {
    script "/usr/local/bin/check_mysqlrouter.sh"
    interval 2
    weight -20
    fall 3
    rise 2
}

vrrp_instance VI_MYSQL {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass mysqlrouter123
    }

    virtual_ipaddress {
        192.168.1.100/24 dev eth0
    }

    track_script {
        check_mysqlrouter
    }

    notify_master "/usr/local/bin/notify_master.sh"
    notify_backup "/usr/local/bin/notify_backup.sh"
    notify_fault "/usr/local/bin/notify_fault.sh"
}

備用節點設定（Router 2）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# /etc/keepalived/keepalived.conf

global_defs {
    router_id MYSQL_ROUTER_2
    script_user root
    enable_script_security
}

vrrp_script check_mysqlrouter {
    script "/usr/local/bin/check_mysqlrouter.sh"
    interval 2
    weight -20
    fall 3
    rise 2
}

vrrp_instance VI_MYSQL {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass mysqlrouter123
    }

    virtual_ipaddress {
        192.168.1.100/24 dev eth0
    }

    track_script {
        check_mysqlrouter
    }
}

健康檢查腳本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
#!/bin/bash
# /usr/local/bin/check_mysqlrouter.sh

# 檢查 MySQL Router 程序
if ! pgrep -x mysqlrouter > /dev/null; then
    exit 1
fi

# 檢查端口
if ! ss -tln | grep -q ":6446 "; then
    exit 1
fi

# 嘗試連線
if ! mysql -h 127.0.0.1 -P 6446 -u health_check -pcheck123 -e "SELECT 1" > /dev/null 2>&1; then
    exit 1
fi

exit 0

Kubernetes 部署

在 Kubernetes 環境中部署 MySQL Router：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
# mysql-router-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-router
  labels:
    app: mysql-router
spec:
  replicas: 3
  selector:
    matchLabels:
      app: mysql-router
  template:
    metadata:
      labels:
        app: mysql-router
    spec:
      containers:
      - name: mysql-router
        image: container-registry.oracle.com/mysql/mysql-router:8.0
        ports:
        - containerPort: 6446
          name: rw-port
        - containerPort: 6447
          name: ro-port
        - containerPort: 6448
          name: x-rw-port
        - containerPort: 6449
          name: x-ro-port
        env:
        - name: MYSQL_HOST
          value: "mysql-cluster-0.mysql-cluster"
        - name: MYSQL_PORT
          value: "3306"
        - name: MYSQL_USER
          valueFrom:
            secretKeyRef:
              name: mysql-router-secret
              key: username
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-router-secret
              key: password
        resources:
          requests:
            memory: "256Mi"
            cpu: "200m"
          limits:
            memory: "512Mi"
            cpu: "500m"
        livenessProbe:
          tcpSocket:
            port: 6446
          initialDelaySeconds: 30
          periodSeconds: 10
        readinessProbe:
          tcpSocket:
            port: 6446
          initialDelaySeconds: 5
          periodSeconds: 5
---
apiVersion: v1
kind: Service
metadata:
  name: mysql-router
spec:
  selector:
    app: mysql-router
  ports:
  - name: rw
    port: 6446
    targetPort: 6446
  - name: ro
    port: 6447
    targetPort: 6447
  type: ClusterIP

完整高可用架構檢查清單

項目	說明	狀態
多 Router 實例	部署至少 2 個 Router 實例	☐
負載平衡	使用 HAProxy/Nginx 或 Kubernetes Service	☐
虛擬 IP	設定 Keepalived VIP	☐
健康檢查	設定自動健康檢查機制	☐
監控告警	整合監控系統（Prometheus/Grafana）	☐
日誌集中	設定日誌收集（ELK/Loki）	☐
自動重啟	設定 systemd 自動重啟	☐
備份設定	定期備份設定檔	☐
文件記錄	記錄架構和設定	☐
災難恢復	制定災難恢復計劃	☐

總結

MySQL Router 是建立高可用 MySQL 架構的重要工具，透過本文的介紹，您應該已經了解：

MySQL Router 基礎概念：作為應用程式與資料庫之間的路由層，提供負載平衡和故障轉移功能
安裝與設定：在 Ubuntu 22.04 上安裝和設定 MySQL Router
路由策略：不同的路由模式（first-available、round-robin）和讀寫分離設定
InnoDB Cluster 整合：透過 Bootstrap 自動設定與 InnoDB Cluster 的整合
效能調校：連線池設定和系統層級優化
監控與故障排除：日誌分析、REST API 監控和常見問題解決
高可用架構：多 Router 部署、HAProxy 和 Keepalived 整合

透過正確的架構設計和設定，MySQL Router 可以幫助您建立穩定、高效能且具備高可用性的資料庫存取層。建議在生產環境部署前，先在測試環境中充分驗證各項設定和故障轉移機制。

前言