之前写的2篇proxysql的相关文档:
https://blog.51cto.com/lee90/2299815 # 开启web监控页面
https://blog.51cto.com/lee90/2298804 # 搭建proxysql cluster集群
pxc节点IP:
192.168.2.11
192.168.2.12
192.168.2.13
pxc版本:
5.7.27-30-57-log
proxysql版本:
proxysql-2.0.8-1-centos7.x86_64.rpm
proxysql集群节点IP:
192.168.2.11
192.168.2.12
192.168.2.13
部署pxc集群,这里就跳过了。步骤很简单,官网的文档很全面。
贴下我的mysql的配置文件(有些参数需要根据自己的环境修改,如gcachedd大小,主机名 ip之类的)
######
[mysqld]
socket = /tmp/mysql.sock
datadir=/var/lib/mysql/
port = 3306
user = mysql
character-set-server = utf8
default_storage_engine = InnoDB
transaction_isolation = READ-COMMITTED
skip_name_resolve = ON
skip_external_locking
server-id = 13096
innodb_file_per_table = ON
log-bin = mysql-bin
binlog_format = ROW
max_binlog_size = 256M
max_connections = 1000
innodb_buffer_pool_size = 1G
default-time-zone = '+08:00'
connect_timeout = 20
wait_timeout = 14400
interactive_timeout = 14400
net_write_timeout = 180
lock_wait_timeout = 120
max_allowed_packet = 256M
open_files_limit = 65535
innodb_open_files = 4000
event_scheduler = OFF
table_open_cache = 4096
innodb_use_native_aio = ON
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_autoinc_lock_mode = 2
# 多线程复制
slave_parallel_workers = 4
slave_parallel_type = 'LOGICAL_CLOCK'
slave_preserve_commit_order = ON
gtid_mode = on
enforce_gtid_consistency = ON
log_slave_updates = ON
pid-file=/var/lib/mysql/mysqld.pid
relay_log_info_repository = TABLE
relay_log_recovery = ON
master_info_repository = TABLE
log_bin_trust_function_creators = ON
binlog_rows_query_log_events = ON
sql_mode='NO_ENGINE_SUBSTITUTION'
########### PXC 参数 ##############
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluste
wsrep_cluster_address=gcomm://192.168.2.11,192.168.2.12,192.168.2.13
wsrep_node_name=archmysql-01
wsrep_node_address=192.168.2.11
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:Abcd@4321
wsrep_slave_threads = 4
wsrep_on = ON
wsrep_causal_reads = ON
wsrep_certify_nonPK = ON
wsrep_provider_options="gcache.size = 5G; gcache.name = /var/lib/mysql/galera.cache;gcache.recover=yes"
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
slow_query_log = ON
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow-query.log
log_slow_slave_statements
log_slow_admin_statements
log_error_verbosity = 2
log_error = /var/lib/mysql/error.log
open_files_limit = 65535
innodb_open_files = 65535
[client]
user = root
port = 3306
socket = /tmp/mysql.sock
[mysql]
no-auto-rehash
safe-updates
prompt = "[\\d] \\R:\\m:\\s > "
yum localinstall proxysql-2.0.8-1-centos7.x86_64.rpm -y 安装软件
cat /etc/proxysql.cnf | egrep -v '^#|^$' 这里是修改后的配置文件(生产上记得把后面我加的注释去掉)
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin;pxc1:secret1pass" # 这里我搞的是proxysql集群配置方案。分号后面是集群的账号密码
mysql_ifaces="0.0.0.0:6032"
cluster_username="pxc1" # 集群的账号
cluster_password="secret1pass" # 集群的密码
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=3
cluster_mysql_servers_diffs_before_sync=3
cluster_mysql_users_diffs_before_sync=3
cluster_proxysql_servers_diffs_before_sync=3
}
# 下面是3节点的proxysql的配置信息
proxysql_servers =
(
{
hostname="192.168.2.11"
port=6032
comment="proxysql1"
},
{
hostname="192.168.2.12"
port=6032
comment="proxysql2"
},
{
hostname="192.168.2.13"
port=6032
comment="proxysql3"
}
)
# 下面是proxysql的初始配置信息
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.7.27"
connect_timeout_server=3000
monitor_username="proxysql"
monitor_password="proxysql"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_sec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
# 下面这些内容留空, 待我们启动proxysql后进到控制台去配置即可
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
在pxc的任一节点执行,创建一个proxysql用的监控用账号
create user proxysql@'%' identified by 'proxysql';
grant PROCESS, REPLICATION SLAVE, REPLICATION CLIENT on *.* to proxysql@'%';
在pxc上任一节点执行,再创建连接用户,这里假设ProxySQL全都使用 rw 用户连接PXC,以及接受app的SQL请求。
create user 'rw'@'%' identified by '123456' ;
grant select,update,delete,insert on *.* to 'rw'@'%';
# 登录进proxysql的控制台,进行核心配置:
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
-- 添加3台pxc节点,3节点都是可写的,这里直接不区分hostgroup了
insert into mysql_servers(hostgroup_id,hostname,port) values
(10,'192.168.2.11',3306),
(10,'192.168.2.12',3306),
(10,'192.168.2.13',3306);
-- 查看配置
Admin> select hostgroup_id,hostname,port,status,weight,max_connections from mysql_servers;
+--------------+-------------+------+--------+--------+-----------------+
| hostgroup_id | hostname | port | status | weight | max_connections |
+--------------+-------------+------+--------+--------+-----------------+
| 10 | 192.168.2.11 | 3306 | ONLINE | 1 | 1000 |
| 10 | 192.168.2.12 | 3306 | ONLINE | 1 | 1000 |
| 10 | 192.168.2.13 | 3306 | ONLINE | 1 | 1000 |
+--------------+-------------+------+--------+--------+-----------------+
3 rows in set (0.00 sec)
-- 载入配置到内存生效,并持久化到文件
load mysql servers to runtime;
save mysql servers to disk;
-- 查看内存中的生效配置
Admin> select hostgroup_id,hostname,port,status,weight,max_connections from runtime_mysql_servers;
+--------------+-------------+------+--------+--------+-----------------+
| hostgroup_id | hostname | port | status | weight | max_connections |
+--------------+-------------+------+--------+--------+-----------------+
| 10 | 192.168.2.11 | 3306 | ONLINE | 1 | 1000 |
| 10 | 192.168.2.13 | 3306 | ONLINE | 1 | 1000 |
| 10 | 192.168.2.12 | 3306 | ONLINE | 1 | 1000 |
+--------------+-------------+------+--------+--------+-----------------+
3 rows in set (0.01 sec)
注意:
不要监控PXC的read\_only,也不要配置mysql\_replication\_hostgroup。
因为目前版本的ProxySQL不支持指定Galera集群中哪些节点作为读,哪些节点作为写节点。
在未来的ProxySQL 2.0版本,将会添加mysql\_galera\_hostgroups表,可以指定集群中的读写节点,让ProxySQL对Galera的支持更丰富、具体。
目前版本的ProxySQL+PXC,无法将读、写操作分离到同一个组中的不同节点上。但是可以保证同一个事务内的语句全都路由到同一个组中的同一个节点
(ProxySQL在某些情况下会自动禁用multiplexing功能)。
2019年11月15日,本人实验时候补充:
实际测试proxysql2的虽然对原生pxc有支持,但是还是比较薄弱,需要强制设置其中一个只读,以便proxysql将读流量引到只读节点去,我在测试时候发现有很多的不方便,
个人认为不具备生产部署的条件。
因此下面文章中,我还是延用了proxysql+监控脚本的方式来做pxc的监控:
-- 到ProxySQL,配置mysql_users表
insert into mysql_users
(username,password,default_hostgroup,transaction_persistent)
values('rw','123456',10,1);
-- 查看配置
Admin> select * from mysql_users where username='rw' \G
*************************** 1. row ***************************
username: rw
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema:
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 0
max_connections: 10000
comment:
*************************** 2. row ***************************
username: rw
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema:
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 0
frontend: 1
max_connections: 10000
comment:
2 rows in set (0.00 sec)
-- 载入配置到内存生效,并持久化到文件
load mysql users to runtime;
save mysql users to disk;
-- 查看内存中的生效配置
Admin> select * from runtime_mysql_users where username='rw' \G
*************************** 1. row ***************************
username: rw
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema:
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 0
frontend: 1
max_connections: 10000
comment:
*************************** 2. row ***************************
username: rw
password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema:
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 0
max_connections: 10000
comment:
2 rows in set (0.00 sec)
下面添加Galera支持(即 配置监控脚本)
注意:ProxySQL2对pxc支持还是不够完美。
要保证Galera的合理运行,只能通过任务调度模块(scheduler)来调度外部脚本做Galera的健康检查,并根据检查结果动态修改ProxySQL的配置。
ProxySQL自身已带Galera集群的健康检查脚本,路径为/usr/share/proxysql/tools/proxysql\_galera\_checker.sh。
可以自己打开这个shell脚本看看,并不复杂。
注意这个脚本,能自动配置ProxySQL标记某个MySQL节点offline\_soft,并将重新上线的节点加回ProxySQL中。
脚本核心思路是:监测到后端节点如果show global status like 'wsrep\_local\_state'; 结果不是4(即synced状态) ,就不加入到proxysql后端
-- 改pxc监测脚本(官方rpm包里面提供的脚本里面传递密码与问题)
修改3个proxysql节点的脚本 /usr/share/proxysql/tools/proxysql_galera_checker.sh 需要修改第115行,修改后的内容如下:
PROXYSQL_CMDLINE="mysql -p$PROXYSQL_PASSWORD -u$PROXYSQL_USERNAME -h $PROXYSQL_HOSTNAME -P $PROXYSQL_PORT --protocol=tcp -Nse"
-- 在ProxySQL控制台中创建一个scheduler,用于调用这个脚本:
注意: 实际测试发现,proxysql集群环境下,
这个涉及到scheduler的操作是不会同步到其它proxysql节点去,因此添加scheduler操作需要在3个proxysql节点都执行一次
INSERT INTO
schedule
(id,interval_ms,filename,arg1,arg2,arg3,arg4,arg5)
VALUES
(1,'10000','/usr/share/proxysql/tools/proxysql_galera_checker.sh',10,10,3,1,'/tmp/proxysql_galera_checker.log');
-- 查看配置
Admin> select * from scheduler \G
*************************** 1. row ***************************
id: 1
active: 1
interval_ms: 10000
filename: /usr/share/proxysql/tools/proxysql_galera_checker.sh
arg1: 10
arg2: 10
arg3: 3
arg4: 1
arg5: /tmp/proxysql_galera_checker.log
comment:
1 row in set (0.00 sec)
-- 载入配置到内存生效,并持久化到文件
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;
-- 查看内存中的生效配置
Admin> select * from runtime_scheduler \G
*************************** 1. row ***************************
id: 1
active: 1
interval_ms: 5000
filename: /usr/share/proxysql/tools/proxysql_galera_checker.sh
arg1: 10
arg2: 10
arg3: 3
arg4: 1
arg5: /tmp/proxysql_galera_checker.log
comment:
1 row in set (0.00 sec)
说明:
id:调度任务的id
interval_ms:执行脚本的时间间隔,也就是检查Galera集群的时间间隔,单位毫秒,我这里设置的是5秒
filename:该调度任务所要调度的文件名, 默认这个脚本文件在 /usr/share/proxysql/tools/proxysql_galera_checker.sh
arg1:写节点的hostgroup_id
arg2:读节点的hostgroup_id
arg3:写节点的个数
arg4:用于定义writers are readers 。1表示写节点也作为读节点
arg5:脚本日志的存放地方
上述配置完成后,可以进行读、写测试,并测试是否会按预期移除、加回故障节点。
mysql -urw -p123456 -h 192.168.2.11 --port 6033 -e 'select @@hostname;'
这个select请求会轮询调度到后端的3个pxc节点上
cat /tmp/proxysql_galera_checker.log
Fri Nov 15 13:12:45 CST 2019 ###### proxysql_galera_checker.sh SUMMARY ######
Fri Nov 15 13:12:45 CST 2019 Hostgroup writers 10
Fri Nov 15 13:12:45 CST 2019 Hostgroup readers 10
Fri Nov 15 13:12:45 CST 2019 Number of writers 3
Fri Nov 15 13:12:45 CST 2019 Writers are readers 1
Fri Nov 15 13:12:45 CST 2019 log file /tmp/proxysql_galera_checker.log
Fri Nov 15 13:12:45 CST 2019 ###### HANDLE WRITER NODES ######
Fri Nov 15 13:12:45 CST 2019 --> Checking WRITE server 10:192.168.2.11:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:12:45 CST 2019 server 10:192.168.2.11:3306 is already ONLINE: 1 of 3 write nodes
Fri Nov 15 13:12:45 CST 2019 --> Checking WRITE server 10:192.168.2.12:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:12:45 CST 2019 server 10:192.168.2.12:3306 is already ONLINE: 2 of 3 write nodes
Fri Nov 15 13:12:45 CST 2019 --> Checking WRITE server 10:192.168.2.13:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:12:45 CST 2019 server 10:192.168.2.13:3306 is already ONLINE: 3 of 3 write nodes
mysql: [Warning] Using a password on the command line interface can be insecure.
Fri Nov 15 13:12:45 CST 2019 ###### HANDLE READER NODES ######
Fri Nov 15 13:12:45 CST 2019 --> Checking READ server 10:192.168.2.11:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:12:45 CST 2019 server 10:192.168.2.11:3306 is already ONLINE
Fri Nov 15 13:12:45 CST 2019 --> Checking READ server 10:192.168.2.12:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:12:45 CST 2019 server 10:192.168.2.12:3306 is already ONLINE
Fri Nov 15 13:12:45 CST 2019 --> Checking READ server 10:192.168.2.13:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:12:45 CST 2019 server 10:192.168.2.13:3306 is already ONLINE
mysql: [Warning] Using a password on the command line interface can be insecure.
Fri Nov 15 13:12:45 CST 2019 ###### SUMMARY ######
Fri Nov 15 13:12:45 CST 2019 --> Number of writers that are 'ONLINE': 3 : hostgroup: 10
Fri Nov 15 13:12:45 CST 2019 --> Number of readers that are 'ONLINE': 3 : hostgroup: 10
Fri Nov 15 13:12:45 CST 2019 ###### Not loading mysql_servers, no change needed ######
Fri Nov 15 13:12:55 CST 2019 ###### proxysql_galera_checker.sh SUMMARY ######
Fri Nov 15 13:12:55 CST 2019 Hostgroup writers 10
Fri Nov 15 13:12:55 CST 2019 Hostgroup readers 10
Fri Nov 15 13:12:55 CST 2019 Number of writers 3
Fri Nov 15 13:12:55 CST 2019 Writers are readers 1
Fri Nov 15 13:12:55 CST 2019 log file /tmp/proxysql_galera_checker.log
然后,人工关闭pxc节点3的mysql进程, 模拟mysql进程宕掉的情况,稍等约10秒钟(这时候业务sql会出现卡顿情况),查看proxysql控制台,如下:
Admin> select * from runtime_mysql_servers ;
+--------------+-------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 192.168.2.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.168.2.13 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.168.2.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
cat /tmp/proxysql_galera_checker.log 相关日志如下:
Fri Nov 15 13:19:01 CST 2019 ###### HANDLE WRITER NODES ######
Fri Nov 15 13:19:01 CST 2019 --> Checking WRITE server 10:192.168.2.11:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:19:01 CST 2019 server 10:192.168.2.11:3306 is already ONLINE: 1 of 3 write nodes
Fri Nov 15 13:19:01 CST 2019 --> Checking WRITE server 10:192.168.2.12:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:19:01 CST 2019 server 10:192.168.2.12:3306 is already ONLINE: 2 of 3 write nodes
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.13' (111) ### 可以看到这里提示proxysql的pxc监测进程,连不上192.168.2.13这个后端pxc节点
Fri Nov 15 13:19:01 CST 2019 --> Checking WRITE server 10:192.168.2.13:3306, current status ONLINE, wsrep_local_state
Fri Nov 15 13:19:01 CST 2019 Changing server 10:192.168.2.13:3306 to status OFFLINE_SOFT. Reason: WSREP status is which is not ok
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Fri Nov 15 13:19:01 CST 2019 ###### HANDLE READER NODES ######
Fri Nov 15 13:19:01 CST 2019 --> Checking READ server 10:192.168.2.11:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:19:01 CST 2019 server 10:192.168.2.11:3306 is already ONLINE
Fri Nov 15 13:19:01 CST 2019 --> Checking READ server 10:192.168.2.12:3306, current status ONLINE, wsrep_local_state 4
Fri Nov 15 13:19:01 CST 2019 server 10:192.168.2.12:3306 is already ONLINE
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.13' (111)
Fri Nov 15 13:19:01 CST 2019 --> Checking READ server 10:192.168.2.13:3306, current status OFFLINE_SOFT, wsrep_local_state
Fri Nov 15 13:19:01 CST 2019 server 10:192.168.2.13:3306 is already OFFLINE_SOFT, WSREP status is which is not ok # 192.168.2.13健康检查失败,被置为OFFLINE_SOFT状态
mysql: [Warning] Using a password on the command line interface can be insecure.
到这里,我们可以在3台proxysql前面加一个SLB(我这里用的是阿里云的slb 虚拟服务器组)。
然后,就可以继续得测试起来了。
测完后,我们可以考虑接入其它业务的数据库账号,以便业务连接接到 proxysql 上。
insert into mysql_users
(username,password,default_hostgroup,transaction_persistent)
values('opsmanage','123456',10,1);
load mysql users to runtime;
save mysql users to disk;
select * from runtime_mysql_users ;
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)
几个比较常用的是:
select * from stats_mysql_global ;
select * from stats_mysql_processlist ; 或者 show full processlist;
select * from stats_proxysql_servers_checksums ;
select * from stats_proxysql_servers_metrics ;
select * from stats_mysql_users ;
select hostgroup,schemaname,username,digest_text from stats_mysql_query_digest order by hostgroup ;
select
hostgroup,schemaname,username,substr(digest_text,120,-120),count_star
from
stats_mysql_query_digest
where
schemaname not in ("information_schema","test") ;
select * from stats_mysql_commands_counters ;
官网地址: https://github.com/percona/proxysql_exporter
先要开启proxysql的http端口
Admin> set admin-web_enabled='true';
Query OK, 1 row affected (0.00 sec)
Admin> show variables like '%admin%web%' ;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| admin-web_enabled | true |
| admin-web_port | 6080 |
+-------------------+-------+
2 rows in set (0.00 sec)
Admin> load admin variables to runtime;
Admin> save admin variables to disk;
ss -lnt | grep 6080
web页面的用户名和密码都是 stats
我自己编译的文件: https://pan.baidu.com/s/18CDQqWw50vfMiW8uyaQRHw
这个bin文件是我自己在centos7下编译的
启动:
./proxysql_exporter
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。