proxysql | 仰望·星空

1 搭建教程
2 总结
- 2.1 proxysql的一些基础知识
  - 2.1.1 端口
  - 2.1.2 配置
- 2.2 部署流程
3 附件
4 中文文档

文章目录[隐藏]

搭建教程
总结
附件
中文文档

搭建教程

使用ProxySQL实现MySQL Group Replication的故障转移、读写分离（一）
使用ProxySQL实现MySQL Group Replication的故障转移、读写分离（二）
以下仅作参考：
ProxySQL+MGR实现读写分离和主节点故障无感知切换 – 完整操作记录

总结

proxysql的一些基础知识

端口

proxysql有6032、6033、6080 端口。

6032：是proxysql 管理端口（consle方式）
6033：是proxysql 客户端口
6080：是proxysql 管理端口（web方式，默认关闭）

配置

proxysql有配置文件和SQL语句动态配置两种方式。除了固定的配置使用配置文件以外，其余建议使用SQL语句配置。
proxysql的配置文件路径：/etc/proxysql.cnf。
proxysql初次启动必须有proxysql.cnf文件，如无其他修改，默认内容如下：

proxysql.cnf

ProxySQL多层配置系统

前面我们说到ProxySQL具有“零停机时间变更”功能，它是通过3层配置来实现的，3层配置包括：Runtime、Memory、Disk & Configuration File。

Runtime层表示ProxySQL工作线程使用的内存数据结构；
Memory(也被称为main)层经由一个MySQL兼容接口露出的内存数据库，用户可以使用MySQL客户端连接到管理界面，查看、编辑ProxySQL配置表；
Disk & Configuration File。Disk层是一个存放在磁盘上的SQLite3数据库，Disk层可将内存中的配置信息保存到磁盘，以便ProxySQL重新启动后配置还可用。

3个层面的信息有什么区别呢？我个人的理解是：3个层面保存的都是ProxySQL的配置信息，如果管理员未作修改，那么3个层面的配置信息是相同的。如果管理员要修改配置信息，首先需要修改Memory层，要让修改的信息立刻生效，则需要把Memory层的变更信息推到Runtime层；要让修改的配置信息在ProxySQL重启后还能保存下来，则需要把Memory层的信息推到Disk层。Runtime层是ProxySQL正在使用的配置信息，Memory层是用户可以编辑的信息，Disk层可以把配置信息永久保存在磁盘上。

部署流程

下载MySQL和proxysql，并且安装。proxysql不能直接登录，需要借助MySQL的命令才能登陆。
编辑proxysql的配置文件：

1	vi /etc/proxysql.cnf

使用如下基础配置，启动proxysql：

datadir="/var/lib/proxysql"

admin_variables=

{

admin_credentials="admin:admin;radmin:radmin"

mysql_ifaces="0.0.0.0:6032"

}

mysql_variables=

{

threads=4

max_connections=2048

default_query_delay=0

default_query_timeout=36000000

have_compress=true

poll_timeout=2000

interfaces="0.0.0.0:6033"

default_schema="information_schema"

stacksize=1048576

server_version="5.5.30"

connect_timeout_server=3000

monitor_username="monitor"

monitor_password="monitor"

monitor_history=600000

monitor_connect_interval=60000

monitor_ping_interval=10000

monitor_read_only_interval=1500

monitor_read_only_timeout=500

ping_interval_server_msec=120000

ping_timeout_server=500

commands_stats=true

sessions_sort=true

connect_retries_on_failure=10

}

登录上proxysql admin

1	mysql -h127.0.0.1 -P6032 -uradmin -pradmin --prompt "ProxySQL Admin>"

-h：proxysql 的地址
-P：proxysql 的管理端口
-u：proxysql 的管理用户名
-p：proxysql 的管理密码
这些都在/etc/proxysql.cnf的admin_variables配置的。
配置分组信息

1	insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers) values(1,2,3,4,1,2);

这些字段含义如下：

write_hostgroup：默认情况下会将所有流量发送到这个组。具有read_only=0的节点也将分配到这个组；
backup_writer_hostgroup：如果集群有多个写节点(read_only=0)且超过了max_writers规定数量，则会把多出来的写节点放到备用写组里面；
reader_hostgroup：读取的流量应该发送到该组，只读节点(read_only=1)会被分配到该组；
offline_hostgroup：当ProxySQL监视到某个节点不正常时，会被放入该组；
active：是否启用主机组，当启用时，ProxySQL将监视主机在各族之间移动；
max_writers：最大写节点的数量，超过该值的节点应该被放入backup_write_hostgroup；
writer_is_also_reader：一个节点既做写节点也做读节点，如果该值为2，则backup_writer_hostgroup的节点做读写点，但是writer_hostgroup不会做读节点；

查看结果

ProxySQL Admin> select * from mysql_group_replication_hostgroups;

+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

| 1 | 2 | 3 | 4 | 1 | 2 | 0 | 0 | NULL |

+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

在MySQL上：
创建系统视图sys.gr_member_routing_candidate_status，该视图将为ProxySQL提供组复制相关的监控状态指标。

addition_to_sys.sql

USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)

RETURNS INT

DETERMINISTIC

RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)

RETURNS INT

DETERMINISTIC

RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))

RETURNS TEXT(10000)

DETERMINISTIC

RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))

RETURNS INT

DETERMINISTIC

BEGIN

DECLARE result BIGINT DEFAULT 0;

DECLARE colon_pos INT;

DECLARE next_dash_pos INT;

DECLARE next_colon_pos INT;

DECLARE next_comma_pos INT;

SET gtid_set = GTID_NORMALIZE(gtid_set);

SET colon_pos = LOCATE2(':', gtid_set, 1);

WHILE colon_pos != LENGTH(gtid_set) + 1 DO

SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);

SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);

SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);

IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN

SET result = result +

SUBSTR(gtid_set, next_dash_pos + 1,

LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -

SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;

ELSE

SET result = result + 1;

END IF;

SET colon_pos = next_colon_pos;

END WHILE;

RETURN result;

END$$

CREATE FUNCTION gr_applier_queue_length()

RETURNS INT

DETERMINISTIC

BEGIN

RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT

Received_transaction_set FROM performance_schema.replication_connection_status

WHERE Channel_name = 'group_replication_applier' ), (SELECT

@@global.GTID_EXECUTED) )));

END$$

CREATE FUNCTION gr_member_in_primary_partition()

RETURNS VARCHAR(3)

DETERMINISTIC

BEGIN

RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM

performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=

((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),

'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN

performance_schema.replication_group_member_stats USING(member_id));

END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT

sys.gr_member_in_primary_partition() as viable_candidate,

IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM

performance_schema.global_variables WHERE variable_name IN ('read_only',

'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,

sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

在三个mysql节点上可以查看该视图：

mysql> select * from sys.gr_member_routing_candidate_status;

+------------------+-----------+---------------------+----------------------+

+------------------+-----------+---------------------+----------------------+

| YES | NO | 0 | 0 |

+------------------+-----------+---------------------+----------------------+

在proxysql上：
MySQL服务器添加：

nsert into mysql_servers(hostgroup_id,hostname,port) values(1,'mysql_m1',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(1,'mysql_s1',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(1,'mysql_s2',3306);

select * from mysql_servers;

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

ProxySQL Admin>select * from mysql_servers;

+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| 1 | mysql_s1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| 1 | mysql_s2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

| 1 | mysql_m1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |

+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

监控用户配置

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

select variable_name,variable_value from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');

ProxySQL Admin>select variable_name,variable_value from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');

+------------------------+----------------+

| variable_name | variable_value |

+------------------------+----------------+

| mysql-monitor_password | monitor |

| mysql-monitor_username | monitor |

+------------------------+----------------+

注意：这个监控账号密码要与配置文件/etc/proxysql.cnf的mysql_variables一致。
在MySQL上
创建监控用户

1 2	create user monitor@'%' identified by 'monitor'; grant usage,replication client on . to monitor@'%';

授权视图

1 2	grant select on sys.* to monitor; flush privileges;

在proxysql上：
配置监控间隔

update global_variables set variable_value='2000' where variable_name in('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

select * from global_variables where variable_name like 'mysql-monitor%';

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;

检查监控信息是否存在异常

show tables from monitor;

select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10;

select * from mysql_server_group_replication_log order by time_start_us desc limit 10;

select * from mysql_server_ping_log order by time_start_us desc limit 10;

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;

+----------+------+------------------+-------------------------+---------------+

+----------+------+------------------+-------------------------+---------------+

| mysql_s1 | 3306 | 1622949447625669 | 1703 | NULL |

| mysql_s2 | 3306 | 1622949447604483 | 889 | NULL |

| mysql_m1 | 3306 | 1622949447583811 | 1378 | NULL |

| mysql_m1 | 3306 | 1622949445630777 | 3303 | NULL |

| mysql_s2 | 3306 | 1622949445608338 | 3511 | NULL |

| mysql_s1 | 3306 | 1622949445583752 | 2818 | NULL |

| mysql_s2 | 3306 | 1622949443627736 | 3149 | NULL |

| mysql_s1 | 3306 | 1622949443604416 | 2758 | NULL |

| mysql_m1 | 3306 | 1622949443583764 | 3256 | NULL |

| mysql_s2 | 3306 | 1622949441635272 | 1459 | NULL |

+----------+------+------------------+-------------------------+---------------+

10 rows in set (0.00 sec)

ProxySQL Admin>select * from mysql_server_group_replication_log order by time_start_us desc limit 10;

+----------+------+------------------+-----------------+------------------+-----------+---------------------+-------+

+----------+------+------------------+-----------------+------------------+-----------+---------------------+-------+

| mysql_s2 | 3306 | 1622949447763113 | 2682 | YES | NO | 0 | NULL |

| mysql_s1 | 3306 | 1622949447760830 | 2502 | YES | NO | 0 | NULL |

| mysql_m1 | 3306 | 1622949447759592 | 2412 | YES | NO | 0 | NULL |

| mysql_s2 | 3306 | 1622949442761708 | 971 | YES | NO | 0 | NULL |

| mysql_s1 | 3306 | 1622949442760263 | 1280 | YES | NO | 0 | NULL |

| mysql_m1 | 3306 | 1622949442758861 | 1280 | YES | NO | 0 | NULL |

| mysql_s2 | 3306 | 1622949437761961 | 4823 | YES | NO | 0 | NULL |

| mysql_s1 | 3306 | 1622949437761084 | 10564 | YES | NO | 0 | NULL |

| mysql_m1 | 3306 | 1622949437759079 | 6265 | YES | NO | 0 | NULL |

| mysql_s2 | 3306 | 1622949432757485 | 6521 | YES | NO | 0 | NULL |

+----------+------+------------------+-----------------+------------------+-----------+---------------------+-------+

10 rows in set (0.00 sec)

ProxySQL Admin>select * from mysql_server_ping_log order by time_start_us desc limit 10;

+----------+------+------------------+----------------------+------------+

+----------+------+------------------+----------------------+------------+

| mysql_s2 | 3306 | 1622949447649006 | 129 | NULL |

| mysql_m1 | 3306 | 1622949447630985 | 123 | NULL |

| mysql_s1 | 3306 | 1622949447613375 | 156 | NULL |

| mysql_m1 | 3306 | 1622949445653291 | 705 | NULL |

| mysql_s1 | 3306 | 1622949445630161 | 2127 | NULL |

| mysql_s2 | 3306 | 1622949445612256 | 592 | NULL |

| mysql_s1 | 3306 | 1622949443656155 | 602 | NULL |

| mysql_s2 | 3306 | 1622949443634256 | 426 | NULL |

| mysql_m1 | 3306 | 1622949443612418 | 359 | NULL |

| mysql_s2 | 3306 | 1622949441656225 | 177 | NULL |

+----------+------+------------------+----------------------+------------+

10 rows in set (0.00 sec)

要确保error全部为null
在MySQL上
创建proxysql用户

create user `sbuser`@`%` identified by 'sbpass';

grant all privileges on *.* to `sbuser`@`%`;

flush privileges;

在proxysql上：
创建proxysql用户 (要与MySQL配置的一样)

insert into mysql_users(username,password,default_hostgroup) values('sbuser','sbpass',1);

load mysql users to runtime;

save mysql users to disk;

使用proxysql用户登录测试

1	mysql -h127.0.0.1 -usbuser -psbpass -P6033

在proxysql上：
配置读写分离参数

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',1,1);

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES(2,1,'^SELECT',3,1);

select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

查看结果

1	select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest_reset;

配置scheduler
在https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker上有三个脚本，因为我这里是多写模式，所以按要求选择了proxysql_groupreplication_checker.sh脚本。

1	vi /var/lib/proxysql/proxysql_groupreplication_checker.sh

proxysql_groupreplication_checker.sh

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

#!/bin/bash

## inspired by proxysql_galera_checker.sh

# Author: Frédéric -lefred- Descamps

# version: 0.1

# 2016-08-25

# CHANGE THOSE

PROXYSQL_USERNAME="admin"

PROXYSQL_PASSWORD="admin"

PROXYSQL_HOSTNAME="127.0.0.1"

PROXYSQL_PORT="6032"

function usage()

{

echo "Usage: $0 <hostgroup_id write> [hostgroup_id read] [number writers] [writers are readers 0|1} [log_file]"

exit 0

}

if [ "$1" = '-h' -o "$1" = '--help' -o -z "$1" ]

then

usage

if [ $# -lt 1 ]

then

echo "Invalid number of arguments"

usage

HOSTGROUP_WRITER_ID="${1}"

HOSTGROUP_READER_ID="${2:--1}"

NUMBER_WRITERS="${3:-0}"

WRITER_IS_READER="${4:-1}"

ERR_FILE="${5:-/dev/null}"

#echo "Hostgroup writers $HOSTGROUP_WRITER_ID"

#echo "Hostgroup readers $HOSTGROUP_READER_ID"

#echo "Number of writers $NUMBER_WRITERS"

#echo "Writers are readers $WRITER_IS_READER"

#echo "log file $ERR_FILE"

#Timeout exists for instances where mysqld may be hung

TIMEOUT=10

PROXYSQL_CMDLINE="mysql -u$PROXYSQL_USERNAME -p$PROXYSQL_PASSWORD -h $PROXYSQL_HOSTNAME -P $PROXYSQL_PORT --protocol=tcp -Nse"

MYSQL_CREDENTIALS=$($PROXYSQL_CMDLINE "SELECT variable_value FROM global_variables WHERE variable_name IN ('mysql-monitor_username','mysql-monitor_password') ORDER BY variable_name DESC")

MYSQL_USERNAME=$(echo $MYSQL_CREDENTIALS | awk '{print $1}')

MYSQL_PASSWORD=$(echo $MYSQL_CREDENTIALS | awk '{print $2}')

#echo $MYSQL_CREDENTIALS

#echo $MYSQL_USERNAME $MYSQL_PASSWORD

MYSQL_CMDLINE="timeout $TIMEOUT mysql -u$MYSQL_USERNAME -p$MYSQL_PASSWORD "

$PROXYSQL_CMDLINE "SELECT hostgroup_id, hostname, port, status, max_replication_lag FROM mysql_servers WHERE hostgroup_id IN ($HOSTGROUP_WRITER_ID, $HOSTGROUP_READER_ID) AND status <> 'OFFLINE_HARD'" | while read hostgroup server port stat max_replication_lag

read GR_STATUS READONLY TRX_BEHIND <<<$($MYSQL_CMDLINE -h $server -P $port -Nse "SELECT viable_candidate, read_only, transactions_behind FROM sys.gr_member_routing_candidate_status" 2>>${ERR_FILE} | tail -1 2>>${ERR_FILE})

echo "`date` Check server $hostgroup:$server:$port , status $stat , GR_STATUS $GR_STATUS, READONLY $READONLY, TRX_BEHIND $TRX_BEHIND" >> ${ERR_FILE}

UPDATE_STATS_ONLINE_CMD="UPDATE mysql_servers SET status='ONLINE' WHERE hostgroup_id=$hostgroup AND hostname='$server' AND port='$port'; LOAD MYSQL SERVERS TO RUNTIME;"

UPDATE_STATS_OFFLINE_SOFT_CMD="UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostgroup_id=$hostgroup AND hostname='$server' AND port='$port'; LOAD MYSQL SERVERS TO RUNTIME;"

if [ "${GR_STATUS}" == "" -a "${READONLY}" == "" -a "${TRX_BEHIND}" == "" ] ; then

# case : mysql server can not reach

echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "${UPDATE_STATS_OFFLINE_SOFT_CMD}" 2>> ${ERR_FILE}

elif [ "${GR_STATUS}" == "YES" -a "${READONLY}" == "NO" -a "$stat" != "ONLINE" -a ${TRX_BEHIND} -le $max_replication_lag ] ; then

echo "`date` Changing server $hostgroup:$server:$port to status ONLINE" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "${UPDATE_STATS_ONLINE_CMD}" 2>> ${ERR_FILE}

elif [ "${GR_STATUS}" == "YES" -a "${READONLY}" == "YES" -a "$stat" != "ONLINE" -a "$hostgroup" == "$HOSTGROUP_READER_ID" -a ${TRX_BEHIND} -le $max_replication_lag ] ; then

echo "`date` Changing server $hostgroup:$server:$port to status ONLINE" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "${UPDATE_STATS_ONLINE_CMD}" 2>> ${ERR_FILE}

elif [ "${GR_STATUS}" == "NO" -o "${READONLY}" == "YES" -a "$stat" = "ONLINE" -a "$hostgroup" == "$HOSTGROUP_WRITER_ID" ] ; then

echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "${UPDATE_STATS_OFFLINE_SOFT_CMD}" 2>> ${ERR_FILE}

elif [ "${GR_STATUS}" == "YES" -a "${READONLY}" == "NO" -a "$stat" = "ONLINE" -a ${TRX_BEHIND} -gt $max_replication_lag ] ; then

echo "`date` Changing server $hostgroup:$server:$port to status OFFLINE_SOFT" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "${UPDATE_STATS_OFFLINE_SOFT_CMD}" 2>> ${ERR_FILE}

done

if [ $NUMBER_WRITERS -gt 0 ]

then

CONT=0

# Only check online servers

$PROXYSQL_CMDLINE "SELECT hostname, port FROM mysql_servers WHERE hostgroup_id = $HOSTGROUP_WRITER_ID AND status = 'ONLINE' order by hostname, port" | while read server port

if [ $CONT -ge $NUMBER_WRITERS ]

then

# Number of writers reached, disabling extra servers

echo "`date` Number of writers reached, disabling extra write server $HOSTGROUP_WRITER_ID:$server:$port to status OFFLINE_SOFT" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "UPDATE mysql_servers set status = 'OFFLINE_SOFT' WHERE hostgroup_id = $HOSTGROUP_WRITER_ID AND hostname = '$server' AND port = $port;" 2>> ${ERR_FILE}

CONT=$(( $CONT + 1 ))

done

if [ $WRITER_IS_READER -eq 0 ]

then

# Writer is not a read node, but only if we have another read node online

READER_NON_WRITER=$($PROXYSQL_CMDLINE "SELECT count(*) FROM mysql_servers ms1 LEFT JOIN mysql_servers ms2 ON ms1.hostname = ms2.hostname AND ms1.port = ms2.port AND ms1.hostgroup_id <> ms2.hostgroup_id WHERE ms1.hostgroup_id = $HOSTGROUP_READER_ID AND ms1.status = 'ONLINE' AND (ms2.hostgroup_id = $HOSTGROUP_WRITER_ID OR ms2.hostgroup_id IS NULL) AND (ms2.status = 'OFFLINE_SOFT' OR ms2.hostgroup_id IS NULL);" 2>>${ERR_FILE})

if [ $READER_NON_WRITER -gt 0 ]

then

$PROXYSQL_CMDLINE "SELECT hostname, port FROM mysql_servers WHERE hostgroup_id = $HOSTGROUP_WRITER_ID AND status = 'ONLINE' order by hostname, port" | while read server port

echo "`date` Disabling read for write server $HOSTGROUP_READER_ID:$server:$port to status OFFLINE_SOFT" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "UPDATE mysql_servers set status = 'OFFLINE_SOFT' WHERE hostgroup_id = $HOSTGROUP_READER_ID AND hostname = '$server' AND port = $port;" 2>> ${ERR_FILE}

done

else

echo "`date` Not enough read servers, we won't disable read in write servers" >> ${ERR_FILE}

echo "`date` Enabling config" >> ${ERR_FILE}

$PROXYSQL_CMDLINE "LOAD MYSQL SERVERS TO RUNTIME;" 2>> ${ERR_FILE}

exit 0

1	chmod a+x /var/lib/proxysql/proxysql_groupreplication_checker.sh

INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','3','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');

select * from scheduler;

LOAD SCHEDULER TO RUNTIME;

SAVE SCHEDULER TO DISK;

压测

安装sysbench

1 2	curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh \| sudo bash yum -y install sysbench

先建一个测试库

1	create database test character set utf8 collate utf8_general_ci;

预备

sysbench --threads=5 \

--max-requests=0 \

--time=36 \

--db-driver=mysql \

--mysql-user=sbuser \

--mysql-password='sbuser' \

--mysql-port=16033 \

--mysql-host=127.0.0.1 \

--mysql-db=test \

--report-interval=1 \

/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua prepare

测试

sysbench --threads=5 \

--max-requests=0 \

--time=36 \

--db-driver=mysql \

--mysql-user=sbuser \

--mysql-password='sbuser' \

--mysql-port=16033 \

--mysql-host=127.0.0.1 \

--mysql-db=test \

--report-interval=1 \

/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run

附件

proxysql_groupreplication_checker
ddition_to_sys.sql

中文文档

https://github.com/malongshuai/proxysql/wiki

文章 proxysql 转载需要注明出处

搭建教程

总结

proxysql的一些基础知识

端口

配置

部署流程

附件

中文文档

您必须 登录 才能发表评论！

您必须登录才能发表评论！