安裝 mysql-proxy (ubuntu 22.04) | 老洪的 IT 學習系統

在設定的時候有一個坑要注意，就是每次重啟服務都要重新新增資料，這是因為 ProxySQL 只有把資料儲存在記憶體（RUNTIME），但 沒有寫入磁碟（DISK）。

所以改掉設定要記得下，不然就要重來了

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
SAVE MYSQL VARIABLES TO DISK;

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;

增加 apt repository

apt-get install -y --no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key' | apt-key add - 
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list

安裝 ProxySQL

sudo apt update
sudo apt install proxysql -y

啟用服務

sudo systemctl start proxysql
sudo systemctl enable proxysql

進入 ProxySQL 管理控制台
```
mysql -u admin -p -h 127.0.0.1 -P 6032
```
預設密碼是 admin

新增 mysql 主機

# 寫入用
INSERT INTO mysql_servers (hostgroup_id, hostname, port) 
VALUES (10, '10.0.0.1', 3306);

# Read only
INSERT INTO mysql_servers (hostgroup_id, hostname, port) 
VALUES (20, '10.0.0.2', 3306);

新增使用者到 ProxySQL

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) 
VALUES ('root', 'PASSWORD', 10, 1);

設定 ssl 證書 (若有)

SET mysql-ssl_p2s_ca = '/mysql-cert/mysql-server-ca.pem';
SET mysql-ssl_p2s_cert = '/mysql-cert/mysql-client-cert.pem';
SET mysql-ssl_p2s_key = '/mysql-cert/mysql-client-key.pem';

SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;

設定讀寫規則

# read only (destination_hostgroup: 20)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT .*', 20, 1);

# read/write (destination_hostgroup: 10)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '.*', 10, 1);

套用變更

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

檢查目前連線的 mysql
```
SELECT * FROM runtime_mysql_servers;
```

Facebook 討論區載入中...

步驟