软件信息将proxysql与keepalived绑定，proxysql进程停止会关闭keepalived
当前vip在192.168.124.17，当proxysql或keepalived出现异常，vip会切换至其他节点
| 软件 | 版本 |
|---|---|
| Database | 8.0.29 MySQL Community Server - GPL |
| System | Red Hat Enterprise Linux Server release 7.9 (Maipo) |
| MySQL-Proxy | ProxySQL version 2.3.2-10-g8cd66cf, codename Truls |
| High Availability | Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2 |
- InnoDB存储引擎
- 每个表必须有显式主键
- 网络性能要求低延时、高带宽
- server_id要唯一
- 开启GTID
- 不建议使用外键级联约束
- 组成员最大数目为9
selinux会影响后期mgr及keepalived安装配置
vim /etc/selinux/config SELINUX=disabled2. 配置hosts
vim /etc/hosts 192.168.62.16 mgr01 192.168.62.17 mgr02 192.168.62.18 mgr033. 配置yum源
# mount -o loop /dev/sr0 /mnt/ # more redhat-base.repo [local] name=Red Hat Enterprise Linux 7.9 baseurl=file:///mnt enabled=1 gpgcheck=0 gpgkey=file:///mnt/RPM-GPG-KEY-redhat-release数据库安装 1. 上传安装包
# ls -lrt -rw-r--r-- 1 root root 833555286 Apr 25 23:26 mysql-8.0.28-el7-x86_64.tar.gz2. 卸载mariadb-libs
rpm -qa |grep mariadb rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_643. 添加用户
useradd mysql id mysql4. 安装Mysql软件
cd /usr/local/ tar -zxvf /tmp/mysql-8.0.28-el7-x86_64.tar.gz ln -s mysql-8.0.28-el7-x86_64/ mysql mkdir -p /data/3306/ mkdir -p /data/binlog/ chown -R mysql.mysql /usr/local/mysql /usr/local/mysql-8.0.28-el7-x86_64 /data/5. 配置环境变量
vim /etc/profile export PATH=/usr/local/mysql/bin:$PATH source /etc/profile mysql -V6. 初始化Mysql数据库
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/33067. 配置文件
slave节点配置文件只需要把loose-group_replication_local_address和server_id改成对应的即可
可以使用uuidgen获取一个随机uuid作为复制组的名称。
cat > /etc/my.cnf <8. 将Mysql加入系统服务 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld chkconfig --add mysqld chkconfig mysqld off systemctl start mysqld9. 开启分布式复制创建用户，所有节点都需要配置
set sql_log_bin=0; create user repl@'localhost' identified by '123456'; grant replication slave,replication client on *.* to repl@'localhost'; create user repl@'127.0.0.1' identified by '123456'; grant replication slave,replication client on *.* to repl@'127.0.0.1'; create user repl@'192.168.62.%' identified by '123456'; grant replication slave,replication client on *.* to repl@'192.168.62.%'; SET SQL_LOG_BIN=1;所有节点进行如下操作
#开启复制渠道 change master to master_user='repl',master_password='123456' for channel 'group_replication_applier'; #加载GR插件 install plugin group_replication soname 'group_replication.so'; show plugins;第一个节点进行如下配置
#启动复制程序 set global group_replication_bootstrap_group=ON; start group_replication; set global group_replication_bootstrap_group=OFF;其他节点进行如下配置
#启动复制程序 start group_replication;查看集群状态
select * from performance_schema.replication_group_membersGProxySQL安装 1. 安装依赖yum install -y perl.x86_64 yum install -y libaio.x86_64 yum install -y net-tools.x86_64 yum install -y perl-DBD-MySQL yum install -y gnutls2. 安装ProxySQLrpm -ivh /tmp/proxysql-2.3.2-1-centos7.x86_64.rpm3. 创建monitor用户monitor用于ProxySQL，proxysql用于后期测试
create user 'monitor'@'%' identified by '123456'; create user 'proxysql'@'%' identified by '123456'; GRANT USAGE,process,replication slave,replication client ON *.* TO 'monitor'@'%'; GRANT SELECt, INSERT, UPDATE, DELETE ON *.* TO 'proxysql'@'%'; grant all on *.* to 'monitor'@'%'; grant all on *.* to 'proxysql'@'%';4. 创建视图主库执行即可，用于检测节点状态，官方脚本在mgr8上存在问题，在其他地方找的脚本
addition_to_sys_v2.sqlUSE sys; DROP VIEW IF EXISTS gr_member_routing_candidate_status; DROP FUNCTION IF EXISTS IFZERO; DROP FUNCTION IF EXISTS LOCATE2; DROP FUNCTION IF EXISTS GTID_NORMALIZE; DROP FUNCTION IF EXISTS GTID_COUNT; DROP FUNCTION IF EXISTS gr_applier_queue_length; DROP FUNCTION IF EXISTS gr_member_in_primary_partition; DROP FUNCTION IF EXISTS gr_transactions_to_cert; 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(20) CHARSET utf8mb4 READS SQL DATA DETERMINISTIC BEGIN DECLARE myReturn VARCHAr(20); DECLARE myError INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR 1242 SET myError = 1242; (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' ) into myReturn FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats rgms USING(member_id) WHERe rgms.MEMBER_ID=@@SERVER_UUID ) ; IF myError > 0 THEN GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT; select @p2 into myReturn; RETURN myReturn; END IF; IF myReturn IS NULL Then RETURN 'NO'; END IF; RETURN myReturn; END$$ CREATE FUNCTION `gr_transactions_to_cert`() RETURNS int DETERMINISTIC BEGIN DECLARE transactions_to_cert INT DEFAULT 0; select performance_schema.replication_group_member_stats.COUNT_TRANSACTIONS_IN_QUEUE into transactions_to_cert FROM performance_schema.replication_group_member_stats where MEMBER_ID=@@SERVER_UUID; IF transactions_to_cert IS NULL THEN RETURN 0; END IF; RETURN transactions_to_cert; END$$ CREATE VIEW gr_member_routing_candidate_status AS SELECt sys.gr_member_in_primary_partition() AS viable_candidate, IF((SELECT ((SELECT GROUP_CONCAt(performance_schema.global_variables.VARIABLE_VALUE SEPARATOR ',') FROM performance_schema.global_variables WHERe (performance_schema.global_variables.VARIABLE_NAME IN ('read_only' , 'super_read_only'))) <> 'OFF,OFF') ), 'YES', 'NO') AS read_only, sys.gr_applier_queue_length() AS transactions_behind, sys.gr_transactions_to_cert() AS transactions_to_cert;$$ DELIMITER ;5. 初始化ProxySQL编辑配置文件
vim /etc/proxysql.cnf datadir="/var/lib/proxysql" admin_variables = { admin_credentials="admin:admin;cluster_demo:123456" mysql_ifaces="0.0.0.0:6032" cluster_username="cluster_demo" cluster_password="123456" 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 } proxysql_servers = ( { hostname="192.168.124.16" port=6032 comment="proxysql16" }, { hostname="192.168.124.17" port=6032 comment="proxysql17" }, { hostname="192.168.124.18" port=6032 comment="proxysql18" } ) 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;/tmp/proxysql.sock" interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.7.28" connect_timeout_server=3000 # make sure to configure monitor username and password # https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password monitor_username="monitor" monitor_password="123456" 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
mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql>'初始化配置
delete from scheduler ; delete from mysql_servers; delete from mysql_users; delete from mysql_query_rules; delete from mysql_group_replication_hostgroups ; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;检查配置
proxysql> select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password'); select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6; select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;如果配置文件没配置可以做如下操作
proxysql> set mysql-monitor_username='monitor'; proxysql> set mysql-monitor_password='123456'; proxysql> load mysql variables to runtime; proxysql> save mysql variables to disk;6. ProxySQL配置当前proxysql版本可以配置mysql_replication、mysql_group_replication、mysql_galera、mysql_aws_aurora
proxysql>show tables like 'mysql%hostgroups'; +------------------------------------+ | tables | +------------------------------------+ | mysql_replication_hostgroups | | mysql_group_replication_hostgroups | | mysql_galera_hostgroups | | mysql_aws_aurora_hostgroups | +------------------------------------+ 4 rows in set (0.00 sec)高可用使用mgr8，对应分组选择mysql_group_replication_hostgroups
proxysql>insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup) values (1,2,3,4); proxysql> load mysql servers to runtime; proxysql> save mysql servers to disk; proxysql>select * from mysql_group_replication_hostgroupsG *************************** 1. row *************************** writer_hostgroup: 1 backup_writer_hostgroup: 2 reader_hostgroup: 3 offline_hostgroup: 4 active: 1 max_writers: 1 writer_is_also_reader: 0 max_transactions_behind: 0 comment: NULL 1 row in set (0.00 sec)添加服务器，proxysql会自动根据节点状态分组
proxysql> select * from mysql_servers; 当前应是空值 proxysql> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.168.124.16',3306),(1,'192.168.124.17',3306),(1,'192.168.124.18',3306); proxysql> load mysql servers to runtime; proxysql> save mysql servers to disk; proxysql> select * from mysql_serversG proxysql> select * from runtime_mysql_serversG proxysql>select * from runtime_mysql_serversG *************************** 1. row *************************** hostgroup_id: 3 hostname: 192.168.124.17 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 2. row *************************** hostgroup_id: 1 hostname: 192.168.124.18 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 3. row *************************** hostgroup_id: 3 hostname: 192.168.124.16 port: 3306 gtid_port: 0 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: 3 rows in set (0.04 sec)配置对外访问用户
proxysql> insert into mysql_users(username,password,default_hostgroup) values('proxysql','123456',1); proxysql> load mysql users to runtime; proxysql> save mysql users to disk; proxysql> select * from mysql_usersG *************************** 1. row *************************** username: proxysql password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 active: 1 use_ssl: 0 default_hostgroup: 1 default_schema: schema_locked: 0 transaction_persistent: 1 fast_forward: 0 backend: 0 frontend: 1 max_connections: 10000 attributes: comment: *************************** 2. row *************************** username: proxysql password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 active: 1 use_ssl: 0 default_hostgroup: 1 default_schema: schema_locked: 0 transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 0 max_connections: 10000 attributes: comment: 2 rows in set (0.02 sec)配置路由规则，用于读写分离
proxysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',1,1),(1,'^select',3,1); proxysql> load mysql query rules to runtime; proxysql> save mysql query rules to disk; proxysql> select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules; +---------+--------+----------------------+-----------------------+-------+ | rule_id | active | match_pattern | destination_hostgroup | apply | +---------+--------+----------------------+-----------------------+-------+ | 5 | 1 | ^select.*for update$ | 1 | 1 | | 6 | 1 | ^select | 3 | 1 | +---------+--------+----------------------+-----------------------+-------+ 2 rows in set (0.10 sec)查看SQL记录
proxysql> select * from stats_mysql_query_digest;Keepalived安装 1. 安装keepalivedyum install keepalived -y2. 配置检测脚本检测proxysql状态，检测不到proxysql则关闭keepalived
vim /etc/keepalived/chk_proxysql.sh if [ `ps -C proxysql --no-header |wc -l` -eq 0 ];then systemctl stop keepalived fi3. 配置keepalivedpriority 配置不同数值
节点 priority 192.168.124.16 99 192.168.124.17 98 192.168.124.18 97 ! Configuration File for keepalived global_defs { script_user root enable_script_security } vrrp_script chk_proxysql { script "/etc/keepalived/chk_proxysql.sh" interval 2 #脚本检测频率 weight -5 #脚本执行成功与否，权重怎么计算 fall 2 #如果连续两次检测失败，认为节点服务不可用 rise 1 #如果连续2次检查成功则认为节点正常 } vrrp_instance VI_1 { nopreempt interface ens33 virtual_router_id 51 priority 99 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.124.15/24 } track_script { chk_proxysql } }测试在客户端执行如下脚本，测试高可用及负载均衡
每次查询间隔1sfor i in {1..12}; do sleep 1; mysql -uproxysql -p123456 -h192.168.124.15 -P6033 -e 'select @@hostname' -s -N; done1. 测试MGR2. 测试PROXYSQL关闭mgr写节点，查询节点只剩一个，还可持续对外服务
3.测试KEEPALIVED随机关闭proxysql，短暂时间无法连接，之后恢复正常
随机关闭keepalived，短暂时间无法连接，之后恢复正常
