在設定的時候有一個坑要注意，就是每次重啟服務都要重新新增資料，這是因為 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;
