centos7安装部署proxysql
主机：
192.168.208.39（主）
192.168.208.40（从）
下载地址：
https://repo.proxysql.com/ProxySQL/
参考文献：
https://proxysql.com/documentation/ProxySQL-Configuration/
主机proxysql安装部署
若需要部署高可用，可使用keepalive+proxysql架构
##安装步骤
cd /usr/local/src/
yum -y install proxysql-2.2.0-1-centos7.x86_64.rpm
##启动proxysql并设置开机启动
systemctl start proxysql
systemctl enable proxysql
mysql5.6安装半同步主从复制
mysql安装过程略
主机安装半同步插件
mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
mysql > select * from mysql.plugin;
+----------------------+--------------------+
| name | dl |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#等待多长时间(单位毫秒)，如果还不能成功推送到从库，则自动调整为异步模式，事务正常返回给客户端
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 3000;
##创建主从同步复制用户
mysql> grant replication slave on *.* to 'sla'@'%' identified by '!QAZ@wsx' with grant option;
主机修改配置文件/etc/my.cnf（需写在[mysqld]模块下）
server-id = 1
log-bin=mysql-bin
#二进制日志的格式，有row、statement和mixed几种类型；需要注意的是：当设置隔离级别为读提交READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致
binlog_format=row
#开启GTID模式
gtid_mode=ON
# slave更新是否记入日志
log-slave-updates=1
#强制gtid一致性
enforce-gtid-consistency=1
#以上是必选项
#以下是可选项
#以表形式（而不是文件形式）记录master-info和relay-log-info
master-info-repository=TABLE
relay-log-info-repository=TABLE
#当设置了master_info_repository = TABLE，那么如果sync_master_info的值大于0，从站会在sync_master_info的每个事件之后，更新它自己的master info表
sync-master-info=1
#设置当该服务器作为从服务器时，应用接收到的复制事件的并发线程数。设置为0将会关闭多线程从服务器功能。如果该机器拥有多核，并且使用了多个数据库，可以增加该值以更好地利用多线程功能
slave-parallel-workers=4
#用于启用所有的复制校验
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
#使用基于行的复制时启用二进制日志中的信息日志事件（尤其是原始SQL查询），简化故障排除
binlog-rows-query-log_events=1
#提供复制报告端口
report-port=3306
##这两个参数需要先安装半同步插件，否则重启数据库会报错
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000
#binlog日志过期时间
expire_logs_days = 30
主机重启数据库
systemctl restart mysqld
主机导出数据库
/usr/local/mysql/bin/mysqldump -u root -pcndatacom --all-databases --triggers --routines --events > /tmp/all.sql
从机修改配置文件/etc/my.cnf（需写在[mysqld]模块下）
### 主从配置 ###
log-bin=mysql-bin
binlog_format=row
#innodb_autoinc_lock_mode = 2
expire_logs_days = 30
### Mysql5.6 主从配置：基于事务的复制###
gtid_mode=ON
log-slave-updates=1
enforce-gtid-consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=8
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
### semi ###
#rpl_semi_sync_slave_enabled = 1
### slave ###
server-id = 2
relay_log_recovery = 1
#以下5个为可选项
#replicate-do-db=db1
#replicate-do-db=db2
#replicate-ignore-db = mysql
##replicate-ignore-table=dbname.tablename
##read-only = 1
从机重启数据库
systemctl restart mysqld
从机导入数据库
/usr/local/mysql/bin/mysql -uroot -pcndatacom < /tmp/all.sql &
#若出现ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
#则在导出文件时加入参数--set-gtid-purged=off
从机安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
再次修改配置配置文件并重启
#以下配置去掉注释
rpl_semi_sync_slave_enabled = 1
read-only = 1
#重启数据库
systemctl restart mysqld
从机开启主从同步
mysql > CHANGE MASTER TO MASTER_HOST='192.168.208.39',MASTER_PORT=3306,MASTER_USER='sla',MASTER_PASSWORD='!QAZ2wsx',MASTER_AUTO_POSITION=1;
mysql > start slave;
mysql > show slave status\G
主机查看是否为半同步复制
mysql > show global status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 7591 |
| Rpl_semi_sync_master_net_wait_time | 15182 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 7567 |
| Rpl_semi_sync_master_tx_wait_time | 7567 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
##查看Rpl_semi_sync_master_clients是否不为0，Rpl_semi_sync_master_status是否为ON
proxysql配置
##6032为proxysql管理端口，只允许本地访问，6033为转发端口
mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'ProxySQL Admin> '
#通过检查mysql_servers、mysql_replication_hostgroups和mysql_query_rules表中没有条目来验证配置是否为空。
ProxySQL Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)
添加后端mysql服务器
##添加后端mysql服务器，即39和40
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.208.39',3306);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.208.40',3306);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> SELECT * FROM mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.208.39 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.208.40 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
##配置生效
ProxySQL Admin> load mysql servers to runtime;
ProxySQL Admin> save mysql servers to disk;
配置监控
##需要先在后端服务器创建用作监控的服务器
mysql> grant select on *.* to monitor@'192.168.208.%' identified by 'Id1#Bi6HbQ';
##proxysql添加监控帐密信息
ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
ProxySQL Admin> UPDATE global_variables SET variable_value='Id1#Bi6HbQ' WHERE variable_name='mysql-monitor_password';
##配置各种监控间隔
ProxySQL Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
ProxySQL Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+--------------------------------------------------------------+----------------+
| variable_name | variable_value |
+--------------------------------------------------------------+----------------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_count | 1 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-monitor_username | monitor |
| mysql-monitor_password | Id1#Bi6HbQ |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 2000 |
| mysql-monitor_ping_interval | 2000 |
| mysql-monitor_read_only_interval | 2000 |
| mysql-monitor_read_only_timeout | 500 |
+--------------------------------------------------------------+----------------+
31 rows in set (0.00 sec)
##保存配置信息
ProxySQL Admin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQL Admin> SAVE MYSQL VARIABLES TO DISK;
后台健康检查
##查看涉及到监控的几个表
ProxySQL Admin> SHOW TABLES FROM monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.00 sec)
##查看连接成功日志表
ProxySQL Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC limit 10;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.208.39 | 3306 | 1626852388933700 | 967 | NULL |
| 192.168.208.40 | 3306 | 1626852388898454 | 1841 | NULL |
| 192.168.208.39 | 3306 | 1626852386920268 | 910 | NULL |
| 192.168.208.40 | 3306 | 1626852386898514 | 1764 | NULL |
| 192.168.208.40 | 3306 | 1626852384937959 | 1625 | NULL |
| 192.168.208.39 | 3306 | 1626852384897957 | 498 | NULL |
| 192.168.208.39 | 3306 | 1626852382918612 | 745 | NULL |
| 192.168.208.40 | 3306 | 1626852382898057 | 1815 | NULL |
| 192.168.208.40 | 3306 | 1626852380925378 | 1874 | NULL |
| 192.168.208.39 | 3306 | 1626852380898444 | 1049 | NULL |
+----------------+------+------------------+-------------------------+---------------+
设置MYSQL复制主机组
#ProxySQL 通过监视read_only配置在主机组中的服务器上的值来了解复制拓扑mysql_replication_hostgroups。
ProxySQL Admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+----------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+----------+
| 1 | 2 | read_only | cluster1 |
+------------------+------------------+------------+----------+
##现在，所有在主机组 1 或 2 中配置的 MySQL 后端服务器将根据它们的 read_only 值放置到各自的主机组中：
#如果有read_only=0，它们将被移动到主机组 1
#如果有read_only=1，它们将被移动到主机组 2
ProxySQL Admin> LOAD MYSQL SERVERS TO RUNTIME;
##监控各节点是否为只读的日志monitor.mysql_server_read_only_log
ProxySQL Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
+----------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+-------+
| 192.168.208.40 | 3306 | 1626853525013946 | 754 | 1 | NULL |
| 192.168.208.39 | 3306 | 1626853524973692 | 412 | 0 | NULL |
| 192.168.208.39 | 3306 | 1626853523001607 | 208 | 0 | NULL |
+----------------+------+------------------+-----------------+-----------+-------+
##查看主机是否通过readonly的值分配到写组和读组
ProxySQL Admin> SELECT * FROM mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.208.39 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.208.40 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
##保存配置
ProxySQL Admin> SAVE MYSQL SERVERS TO DISK;
ProxySQL Admin> SAVE MYSQL VARIABLES TO DISK;
配置MYSQL用户
ProxySQL Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','cndatacom',1);
ProxySQL Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('yjq','!QAZ2wsx',1);
ProxySQL Admin> SELECT * FROM mysql_users;
+----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| root | cndatacom | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | |
| yjq | !QAZ2wsx | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | |
+----------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
#通过定义default_hostgroup我们指定用户应该默认连接到哪些后端服务器（即这将是来自特定用户的流量的默认路由，可以配置额外的规则来重新路由，但是在它们缺席的情况下，所有查询都将转到特定的主机组）。
#生效配置
ProxySQL Admin> LOAD MYSQL USERS TO RUNTIME;
ProxySQL Admin> SAVE MYSQL USERS TO DISK;
#ProxySQL 现在已准备好在端口6603上提供流量
[updater@localhost ~]$ mysql -uyjq -p'!QAZ2wsx' -h 192.168.208.39 -P6033 -e"SELECT @@port"
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3306 |
+--------+
查询状态日志
##使用mysqlslap工具测试读写
##说明:测试100个并发线程，测试次数1次，自动生成SQL测试脚本，读、写、更新混合测试，自增长字段，测试引擎为innodb，共运行5000次查询
mysqlslap -h192.168.208.39 -uyjq -p'!QAZ2wsx' -P6033 --concurrency=100 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 15.971 seconds ##100个客户端（并发）同时运行这些SQL语句平均要花15.971秒
Minimum number of seconds to run all queries: 15.971 seconds
Maximum number of seconds to run all queries: 15.971 seconds
Number of clients running queries: 100 #总共100个客户端（并发）运行这些sql查询
Average number of queries per client: 50 #每个客户端（并发）平均运行50次查询（对应--concurrency=100，--number-of-queries=5000；5000/100=50）
##stats库下的表
ProxySQL Admin> SHOW TABLES FROM stats;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_errors |
| stats_mysql_errors_reset |
| stats_mysql_free_connections |
| stats_mysql_global |
| stats_mysql_gtid_executed |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+--------------------------------------+
19 rows in set (0.00 sec)
##查看刚刚mysqlslap的查询在哪台机执行，由于没有设置路由规则，所以全都是在39执行
ProxySQL Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 1 | 192.168.208.39 | 3306 | ONLINE | 0 | 100 | 101 | 0 | 101 | 5106 | 0 | 578780 | 325857 | 283 |
| 2 | 192.168.208.40 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 592 |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
##查看查询类型统计
ProxySQL Admin> select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters;
+--------------------+---------------+-----------+
| Command | Total_Time_us | Total_cnt |
+--------------------+---------------+-----------+
| ALTER_TABLE | 0 | 0 |
| ALTER_VIEW | 0 | 0 |
| ANALYZE_TABLE | 0 | 0 |
| BEGIN | 0 | 0 |
| CALL | 0 | 0 |
| CHANGE_MASTER | 0 | 0 |
| COMMIT | 0 | 0 |
| CREATE_DATABASE | 2253 | 1 |
| CREATE_INDEX | 0 | 0 |
| CREATE_TABLE | 11522 | 1 |
| CREATE_TEMPORARY | 0 | 0 |
| CREATE_TRIGGER | 0 | 0 |
| CREATE_USER | 0 | 0 |
| CREATE_VIEW | 0 | 0 |
| DEALLOCATE | 0 | 0 |
| DELETE | 0 | 0 |
| DESCRIBE | 0 | 0 |
| DROP_DATABASE | 0 | 0 |
| DROP_INDEX | 0 | 0 |
| DROP_TABLE | 0 | 0 |
| DROP_TRIGGER | 0 | 0 |
| DROP_USER | 0 | 0 |
| DROP_VIEW | 0 | 0 |
| GRANT | 0 | 0 |
| EXECUTE | 0 | 0 |
| EXPLAIN | 0 | 0 |
| FLUSH | 0 | 0 |
| INSERT | 1560551997 | 2799 |
| KILL | 0 | 0 |
| LOAD | 0 | 0 |
| LOCK_TABLE | 0 | 0 |
| OPTIMIZE | 0 | 0 |
| PREPARE | 0 | 0 |
| PURGE | 0 | 0 |
| RELEASE_SAVEPOINT | 0 | 0 |
| RENAME_TABLE | 0 | 0 |
| RESET_MASTER | 0 | 0 |
| RESET_SLAVE | 0 | 0 |
| REPLACE | 0 | 0 |
| REVOKE | 0 | 0 |
| ROLLBACK | 0 | 0 |
| ROLLBACK_SAVEPOINT | 0 | 0 |
| SAVEPOINT | 0 | 0 |
| SELECT | 4811469 | 2303 |
| SELECT_FOR_UPDATE | 0 | 0 |
| SET | 988 | 1 |
| SHOW_TABLE_STATUS | 0 | 0 |
| START_TRANSACTION | 0 | 0 |
| TRUNCATE_TABLE | 0 | 0 |
| UNLOCK_TABLES | 0 | 0 |
| UPDATE | 0 | 0 |
| USE | 0 | 0 |
| SHOW | 0 | 0 |
| UNKNOWN | 84112 | 2 |
+--------------------+---------------+-----------+
#查询信息在stats_mysql_query_digest提供每个后端的查询计数、每个查询的响应时间以及实际查询文本以及查询摘要中进行跟踪，查询摘要是每种查询类型的唯一标识符：
ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+------------+------------+------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+------------+------------+------------------------------------------------------------------+
| 1 | 1560551997 | 2799 | INSERT INTO t1 VALUES (NULL,?,?) |
| 1 | 4809611 | 2300 | SELECT intcol1,charcol1 FROM t1 WHERE id = ? |
| 1 | 80356 | 1 | DROP SCHEMA IF EXISTS `mysqlslap` |
| 1 | 11522 | 1 | CREATE TABLE `t1` (id serial,intcol1 INT(?),charcol1 VARCHAR(?)) |
| 1 | 3756 | 1 | DROP SCHEMA IF EXISTS `mysqlslap` |
| 1 | 2253 | 1 | CREATE SCHEMA `mysqlslap` |
| 1 | 1303 | 1 | SELECT @@port |
| 1 | 988 | 1 | set storage_engine=`innodb` |
| 1 | 555 | 1 | SELECT id from t1 |
| 1 | 0 | 1 | select @@version_comment limit ? |
+----+------------+------------+------------------------------------------------------------------+
查询路由规则配置
ProxySQL Admin> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
ProxySQL Admin> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
ProxySQL Admin> load mysql query rules to runtime;
ProxySQL Admin> save mysql query rules to disk;
ProxySQL Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1;
+----------------------+-----------------------+
| match_digest | destination_hostgroup |
+----------------------+-----------------------+
| ^SELECT.*FOR UPDATE$ | 1 |
| ^SELECT | 2 |
+----------------------+-----------------------+
测试读写分离
##在任意节点执行以下插入sql
mysql -h192.168.208.39 -uyjq -p'!QAZ2wsx' -P6033 -e "insert into testdb.student values(6,'suse',32);"
##查看stats_mysql_query_digest
ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+------------------------------------------+
| 1 | 7292 | 1 | insert into testdb.student values(?,?,?) |
| 1 | 0 | 1 | select @@version_comment limit ? |
+----+----------+------------+------------------------------------------+
##在任意节点执行以下查询sql
mysql -h192.168.208.39 -uyjq -p'!QAZ2wsx' -P6033 -e "select * from testdb.student;"
##查看stats_mysql_query_digest
ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+------------------------------------------+
| 1 | 7292 | 1 | insert into testdb.student values(?,?,?) |
| 2 | 669 | 1 | select * from testdb.student |
| 1 | 0 | 2 | select @@version_comment limit ? |
+----+----------+------------+------------------------------------------+
##可以看到insert和select语句在不同的组执行
扫一扫
279
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
