在 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.

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

proxysql@HAMI02 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.

proxysql@HAMI02 19:53:  [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)

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

proxysql@HAMI02 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，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/513125.html

標籤：MySQL

上一篇：從原始碼分析 MGR 的新主選舉演算法

下一篇：day09-2視圖和用戶權限

StoneDB讀寫分離實踐方案