StoneDB讀寫分離實踐方案

語言: CN / TW / HK

在 StoneDB 1.0 版本中,InnoDB 引擎處理 OLTP 的事務型業務,Tianmu 引擎處理 OLAP 的分析型業務。因此,需要在主從複製環境的基礎上做讀寫分離,所有的寫操作和部分讀操作走 InnoDB 引擎,所有的分析類查詢走 Tianmu 引擎。讀寫分離方案既可以使用第三方中介軟體,也可以在業務前端實現。本文介紹的是較為常用的中介軟體 ProxySQL。

伺服器配置說明

IP Memory CPU OS version
192.168.30.40 8G 8C CentOS Linux release 7.9
192.168.30.41 8G 8C CentOS Linux release 7.9
192.168.30.42 8G 8C CentOS Linux release 7.9
192.168.30.46 16G 16C CentOS Linux release 7.9

注:主從環境中的各個伺服器的配置一般情況下建議是一致的,但由於 StoneDB 不管重放 binlog,還是用於 OLAP 場景的查詢,都是較消耗系統資源的,建議 StoneDB 配置略高於 MySQL。

主從環境說明

IP DATABASE ROLE DB version
192.168.30.40 MySQL master MySQL 5.7
192.168.30.41 / ProxySQL /
192.168.30.42 MySQL slave MySQL 5.7
192.168.30.46 StoneDB slave StoneDB 5.7

注:MySQL 與 StoneDB 的版本建議保持一致。

架構圖說明 file 推薦採用一主兩從的架構,上層的 ProxySQL 用於讀寫分離:

1)master(192.168.30.40)使用 InnoDB 引擎,可讀寫,提供 OLTP 場景的讀寫業務; 2)slave1(192.168.30.42)使用 InnoDB 引擎,只讀,同時作為 standby,當 master 發生宕機時,可切換至 slave1,保證業務正常執行; 3)slave2(192.168.30.46)使用 Tianmu 引擎,只讀,提供 OLAP 場景的讀業務。

1、作業系統環境檢查

作業系統環境檢查的步驟在四個節點均需要執行。

1.1 關閉防火牆

# systemctl stop firewalld 
# systemctl disable firewalld

1.2 關閉SELINUX

# vim /etc/selinux/config
SELINUX = disabled

1.3 設定Swap分割槽

修改vm.swappiness的值為1,表示儘量不使用Swap。

# vi /etc/sysctl.conf
vm.swappiness = 1

1.4 修改作業系統的限制

# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1031433
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65535
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 1024
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

修改作業系統的軟硬限制
# vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
mysql soft nproc 1028056
mysql hard nproc 1028056

1.5 建立使用者

# groupadd mysql
# useradd -g mysql mysql
# passwd mysql

ProxySQL 節點無需建立,以上步驟執行完之後,重啟作業系統。

2、部署MySQL

在 master 節點和 slave1 節點安裝 MySQL。

2.1 下載安裝包

https://downloads.mysql.com/archives/community/ 從官網下載 MySQL 5.7 的安裝包。

2.2 解除安裝mariadb

# rpm -qa|grep mariadb
mariadb-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
# yum remove mariadb*
# rpm -qa|grep mariadb

2.3 上傳tar包並解壓

# tar -zxvf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# cd /usr/local/
# mv mysql-5.7.36-linux-glibc2.12-x86_64 mysql

2.4 建立目錄

# mkdir -p /mysql/data/
# mkdir -p /mysql/log
# chown -R mysql:mysql /mysql/

2.5 配置引數檔案 my.cnf

master

# vim /etc/my.cnf
[client]
port    = 3306
socket  = /mysql/data/mysql.sock

[mysqld]
port      = 3306
basedir   = /usr/local/mysql
datadir   = /mysql/data
socket    = /mysql/data/mysql.sock
pid_file  = /mysql/data/mysqld.pid
log_error = /mysql/log/mysqld.log
log_bin   = /mysql/log/mybinlog
server_id = 40
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
max_connections = 1000
binlog_format = row
default_storage_engine = innodb
read_only=0

innodb_buffer_pool_size = 4096000000
innodb_log_file_size = 1024000000
innodb_log_files_in_group = 3
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

#開啟GTID模式
gtid_mode = on
enforce_gtid_consistency = 1

#並行複製
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64

slave1

# vim /etc/my.cnf
[client]
port    = 3306
socket  = /mysql/data/mysql.sock

[mysqld]
port      = 3306
basedir   = /usr/local/mysql
datadir   = /mysql/data
socket    = /mysql/data/mysql.sock
pid_file  = /mysql/data/mysqld.pid
log_error = /mysql/log/mysqld.log
log_bin   = /mysql/log/mybinlog
server_id = 42
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
max_connections = 1000
binlog_format = row
default_storage_engine = innodb
read_only=1

innodb_buffer_pool_size = 4096000000
innodb_log_file_size = 1024000000
innodb_log_files_in_group = 3
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

#開啟GTID模式
gtid_mode = on
enforce_gtid_consistency = 1

#並行複製
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4

2.6 初始化例項

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

2.7 啟動例項

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

注:管理員使用者的臨時密碼在 mysqld.log 中,第一次登陸後需要修改管理員使用者的密碼。

3、部署StoneDB

3.1 下載安裝包

https://stonedb.io/zh/docs/download/ 從官網下載 StoneDB 5.7 的安裝包。

3.2 上傳tar包並解壓

# cd /
# tar -zxvf stonedb-ce-5.7-v1.0.0.el7.x86_64.tar.gz

使用者可根據安裝規範將安裝包上傳至伺服器,解壓出來的目錄是 stonedb57,示例中的安裝路徑是 /stonedb57。

3.3 檢查依賴檔案

# cd /stonedb57/install/bin
# ldd mysqld
# ldd mysql

如果檢查返回有關鍵字"not found",說明缺少檔案,需要安裝對應的依賴包。例如:

libsnappy.so.1 => not found

在 Ubuntu 上使用命令 "sudo apt search libsnappy" 檢查,說明需要安裝 libsnappy-dev。在 RedHat 或者 CentOS 上使用命令 "yum search all snappy" 檢查,說明需要安裝 snappy-devel、snappy。

3.4 建立目錄

mkdir -p /stonedb57/install/data
mkdir -p /stonedb57/install/binlog
mkdir -p /stonedb57/install/log
mkdir -p /stonedb57/install/tmp
mkdir -p /stonedb57/install/redolog
mkdir -p /stonedb57/install/undolog
chown -R mysql:mysql /stonedb57

3.5 配置引數檔案 my.cnf

# vim /stonedb57/install/my.cnf
[client]
port    = 3306
socket  = /stonedb57/install/tmp/mysql.sock

[mysqld]
port      = 3306
basedir   = /stonedb57/install/
datadir   = /stonedb57/install/data
socket    = /stonedb57/install/tmp/mysql.sock
pid_file  = /stonedb57/install/data/mysqld.pid
log_error = /stonedb57/install/log/mysqld.log
log_bin   = /stonedb57/install/binlog/binlog
server_id = 46
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
max_connections = 1000
binlog_format = row
default_storage_engine = tianmu
read_only=1

innodb_buffer_pool_size = 2048000000
innodb_log_file_size = 1024000000
innodb_log_files_in_group = 3
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_log_group_home_dir   = /stonedb57/install/redolog/
innodb_undo_directory       = /stonedb57/install/undolog/
innodb_undo_log_truncate    = 1
innodb_undo_tablespaces     = 3
innodb_undo_logs            = 128

#開啟GTID模式
gtid_mode = on
enforce_gtid_consistency = 1

#並行複製
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8  

3.6 初始化例項

/stonedb57/install/bin/mysqld --defaults-file=/stonedb57/install/my.cnf --initialize --user=mysql

3.7 啟動例項

/stonedb57/install/bin/mysqld_safe --defaults-file=/stonedb57/install/my.cnf --user=mysql &

注:管理員使用者的臨時密碼在 mysqld.log 中,第一次登陸後需要修改管理員使用者的密碼。

4、配置主從

4.1 建立複製使用者

create user 'repl'@'%' identified by 'mysql123';
grant replication slave on *.* to 'repl'@'%';

4.2 備份主庫

/usr/local/mysql/bin/mysqldump -uroot -pmysql123 --single-transaction --set-gtid-purged=on -B aa > /tmp/aa.sql

4.3 傳輸備份檔案

scp /tmp/aa.sql [email protected]:/tmp
scp /tmp/aa.sql [email protected]:/tmp

注:如果資料較大,建議使用 mydumper.

4.4 slave1節點

/usr/local/mysql/bin/mysql -uroot -pmysql123 -S /mysqldb/data/mysql.sock
source /tmp/aa.sql

注:恢復前需要確保 gtid_executed 為空。

4.5 slave2節點

在恢復前,需要修改儲存引擎,註釋鎖表語句。

sed -i 's/UNLOCK TABLES/-- UNLOCK TABLES/g' /tmp/aa.sql
sed -i 's/LOCK TABLES `/-- LOCK TABLES `/g' /tmp/aa.sql
sed -i 's/ENGINE=InnoDB/ENGINE=tianmu/g' /tmp/aa.sql

/stonedb57/install/bin/mysql -uroot -pmysql123 -S /stonedb57/install/tmp/mysql.sock
source /tmp/aa.sql

注:恢復前需要確保 gtid_executed 為空。

4.6 建立主從複製

slave1節點

CHANGE MASTER TO
MASTER_HOST='192.168.30.40',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='mysql123',
MASTER_AUTO_POSITION = 1;

start slave;
show slave status\G

slave2節點

CHANGE MASTER TO
MASTER_HOST='192.168.30.40',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='mysql123',
MASTER_AUTO_POSITION = 1;

start slave;
show slave status\G

5、配置ProxySQL

5.1 安裝ProxySQL

# mkdir -p /home/ProxySQL
# cd /home/ProxySQL
# yum install proxysql-2.2.0-1-centos7.x86_64.rpm
# rpm -qa|grep proxysql
# rpm -ql proxysql

注:/etc/proxysql.cnf 為 ProxySQL 的配置檔案。

5.2 啟動ProxySQL

# systemctl start proxysql
# netstat -lntp|grep proxysql
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      17957/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      17957/proxysql 

注:6032是管理埠,6033是服務埠。

5.3 管理員登入ProxySQL

# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='ProxySQL>'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

ProxySQL>show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

注:ProxySQL 的使用者名稱和密碼預設都是 admin,加引數 prompt 是為了便於區分環境。

5.4 建立ProxySQL所需使用者

在 master 上建立 ProxySQL 的監控使用者和對外訪問使用者,其中監控使用者只需要有 replication client 許可權即可。

###監控使用者
create user 'monitor'@'%' identified by 'MySQL_123';
grant replication client on *.* to 'monitor'@'%';

###對外訪問使用者(用於連線ProxySQL)
create user 'proxysql'@'%' identified by 'MySQL_123';
grant select,delete,update,insert on *.* to 'proxysql'@'%';

5.5 配置ProxySQL主從分組資訊

1)建立分組

表 mysql_replication_hostgroups 的欄位 writer_hostgroup、reader_hostgroup 分別代表寫組和讀組,都要大於0且不能相同,該環境中定義寫組為10,讀組為20。

ProxySQL 會根據 read_only 的取值將 server 進行分組,read_only=0為 master,被分到編號為10的寫組,read_only=1為 slave,被分到編號為20的讀組。

###建立分組
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;

###檢視三層配置系統是否都寫入資料
ProxySQL>select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

ProxySQL>select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

ProxySQL>select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

2)新增主從節點

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.30.40',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.30.46',3306);
load mysql servers to runtime;
save mysql servers to disk;

###檢視狀態
ProxySQL>select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 20           | 192.168.30.40 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.30.46 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

3)為ProxySQL建立監控使用者

###建立監控使用者(在ProxySQL建立)
set mysql-monitor_username='monitor';
set mysql-monitor_password='MySQL_123';
load mysql variables to runtime;
save mysql variables to disk;

###對連線使用者監控
ProxySQL>select * from monitor.mysql_server_connect_log;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 192.168.30.40 | 3306 | 1664183920198998 | 0                       | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183920926981 | 0                       | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.46 | 3306 | 1664183970671663 | 1616                    | NULL                                                                   |
| 192.168.30.40 | 3306 | 1664183971672625 | 2089                    | NULL                                                                   |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

注:在沒有建立監控使用者前,會有很多的connect_error,這是因為沒有配置監控資訊時的錯誤,配置後如果connect_error的結果為NULL,則表示正常。

###對心跳資訊的監控
ProxySQL>select * from mysql_server_ping_log limit 10;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                             |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 192.168.30.40 | 3306 | 1664183880229349 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183880427787 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183890229405 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183890336793 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183900229529 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183900357491 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.46 | 3306 | 1664183910229710 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183910406115 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183920229740 | 0                    | Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183920346638 | 0                    | Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
10 rows in set (0.00 sec)

###對read_only值監控
ProxySQL>select * from mysql_server_read_only_log limit 10;
+---------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------------------------+
| hostname      | port | time_start_us    | success_time_us | read_only | error                                                                                                      |
+---------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------------------------+
| 192.168.30.46 | 3306 | 1664183876942878 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183876961694 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183878441697 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183878461063 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183879941587 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183879961993 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183881441750 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
| 192.168.30.40 | 3306 | 1664183881461890 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.40 | 3306 | 1664183882942044 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'192.168.30.41' (using password: YES) |
| 192.168.30.46 | 3306 | 1664183882958866 | 0               | NULL      | timeout on creating new connection: Access denied for user 'monitor'@'HAMI02' (using password: YES)        |
+---------------+------+------------------+-----------------+-----------+------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

注:monitor就會開始監控後端的read_only值,然後按照read_only的值將某些節點自動移到讀寫組。

4)為ProxySQL配置對外訪問使用者

insert into mysql_users(username,password,default_hostgroup) values('proxysql','MySQL_123',10);
insert into mysql_users(username,password,default_hostgroup) values('proxysql2','*0815E74A768849A6CCF0E9C1C5B940FB4D9F839E',20);
load mysql users to runtime;
save mysql users to disk;

ProxySQL>select * from mysql_users\G
*************************** 1. row ***************************
              username: proxysql
              password: MySQL_123
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
            attributes: 
               comment: 
*************************** 2. row ***************************
              username: proxysql2
              password: *0815E74A768849A6CCF0E9C1C5B940FB4D9F839E
                active: 1
               use_ssl: 0
     default_hostgroup: 20
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
            attributes: 
               comment: 
2 rows in set (0.00 sec)

mysql_users 表最主要的三個欄位 username、password、default_hostgroup 解釋。

username:前端連線 ProxySQL 的資料庫使用者

password:使用者對應的密碼,即可以是明文密碼,也可以是 hash 密碼,如果想使用 hash 密碼,可以先在某個節點上執行 select password('password'),然後將加密結果複製到該欄位。

default_hostgroup:使用者預設的路由目標,例如:若使用者 proxysql2 是個只讀使用者,則該欄位值可以設定為20,表示所有的SQL語句預設情況下將路由到 hostgroup_id=20 的組;若使用者不是隻讀使用者,則該欄位必須設定為10,表示 DML 語句會路由到 hostgroup_id=10 的組,查詢語句即會路由到 hostgroup_id=10 的組,也會路由到 hostgroup_id=20 的組。

5.6 配置讀寫分離策略

ProxySQL 的路由規則配置比較靈活,可以基於使用者級別,資料庫級別等。由於 StoneDB 提供的是 OLAP 分析型查詢業務場景,建議將聚合類查詢、即席查詢、複雜查詢等分發到 StoneDB。由於只是測試,因此只配置了幾個簡單的路由規則。

與查詢規則有關的表有兩個:mysql_query_rules 和 mysql_query_rules_fast_routing,表mysql_query_rules_fast_routing 是 mysql_query_rules 的擴充套件,並在以後評估快速路由策略和屬性(僅在ProxySQL 1.4.7+中可用)。

mysql_query_rules 表的幾個欄位解釋。

active:是否啟用這個規則,1表示啟用,0表示禁用

match_pattern:設定規則

destination_hostgroup:預設指定的分組

apply:真正執行應用規則

###建立規則
這裡我建立兩個規則:
1)把所有以select開頭的語句分配到編號為20的讀組中;
2)把select...for update語句分配到編號為10的寫組中,其他所有操作都會預設路由到寫組。
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',20,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(3,1,'^select*sum',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

###檢視規則
ProxySQL>select * from mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^select.*for update$
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 10
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
           attributes: 
              comment: NULL
*************************** 2. row ***************************
              rule_id: 2
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^select
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 20
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
           attributes: 
              comment: NULL
*************************** 3. row ***************************
              rule_id: 3
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^select*sum
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 20
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
           attributes: 
              comment: NULL
3 rows in set (0.00 sec)

注:select...for update 規則的 rule_id 必須要小於普通的 select 規則的 rule_id,因為 ProxySQL 是根據 rule_id的順序進行規則匹配的。

5.7 讀寫分離測試

1)讀操作

# mysql -uproxysql -pMySQL_123 -h127.0.0.1 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected] 17:21:  [(none)]> select sum(money) from aa.ttt;
+------------+
| sum(money) |
+------------+
|    88888.8 |
+------------+
1 row in set (0.01 sec)

[email protected] 17:21:  [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          46 |
+-------------+
1 row in set (0.00 sec)

2)寫操作

# mysql -uproxysql -pMySQL_123 -h127.0.0.1 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[email protected] 19:53:  [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)

[email protected] 19:54:  [(none)]> insert into aa.t1 values(7);
Query OK, 1 row affected (0.00 sec)

[email protected] 19:54:  [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          40 |
+-------------+
1 row in set (0.00 sec)

6、建議項

1)建議設定為 GTID 模式,好處是從庫發生 crash 後,不需要去找位點,系統會自動拉起復制執行緒;

2)在從庫的並行 worker 相同的情況下,從庫 StoneDB 相比從庫 InnoDB 會消耗更多的CPU資源,如果未來上生產環境,建議 StoneDB 的配置略比 InnoDB 的配置高;

3)若從庫 StoneDB 的延遲較高,可臨時關閉引數 tianmu_enable_rowstore,待追上主庫後再開啟引數。關閉該引數帶來的風險是如果在同步過程中 StoneDB 發生重啟,重啟後會出現丟資料的情況;

4)建議將聚合類查詢、即席查詢、複雜查詢等分發到 StoneDB。