在 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 的版本倡议保持一致。
架构图阐明
举荐采纳一主两从的架构，下层的 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 firewalld1.2 敞开 SELINUX
# vim /etc/selinux/config
SELINUX = disabled1.3 设置 Swap 分区
批改 vm.swappiness 的值为 1，示意尽量不应用 Swap。
# vi /etc/sysctl.conf
vm.swappiness = 11.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 10280561.5 创立用户
# groupadd mysql
# useradd -g mysql mysql
# passwd mysqlProxySQL 节点无需创立，以上步骤执行完之后，重启操作系统。
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 mariadb2.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 mysql2.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 = XXHASH64slave1
# 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 = 42.6 初始化实例
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql2.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 /stonedb573.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=mysql3.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.sql4.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\Gslave2 节点
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\G5、配置 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。