cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqld chkconfig mysqld offsystemctl start mysqld

创建用户，所有节点都需要配置

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_members\G

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 gnutls

rpm -ivh /tmp/proxysql-2.3.2-1-centos7.x86_64.rpm

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'@'%';

主库执行即可，用于检测节点状态，官方脚本在mgr8上存在问题，在其他地方找的脚本
addition_to_sys_v2.sql

USE 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; selectperformance_schema.replication_group_member_stats.COUNT_TRANSACTIONS_IN_QUEUE into transactions_to_certFROM 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 ASSELECT 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 ;

编辑配置文件

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;

当前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_hostgroups\G *************************** 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_servers\G proxysql> select * from runtime_mysql_servers\G proxysql>select * from runtime_mysql_servers\G *************************** 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_users\G *************************** 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;

yum install keepalived -y

检测proxysql状态，检测不到proxysql则关闭keepalived

vim /etc/keepalived/chk_proxysql.sh if [ `ps -C proxysql --no-header |wc -l` -eq 0 ]; then systemctl stop keepalived fi

priority 配置不同数值

! Configuration File for keepalivedglobal_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 } }

在客户端执行如下脚本，测试高可用及负载均衡
每次查询间隔1s

for i in {1..12}; do sleep 1; mysql -uproxysql -p123456 -h192.168.124.15 -P6033 -e 'select @@hostname' -s -N; done

关闭mgr写节点，查询节点只剩一个，还可持续对外服务

随机关闭proxysql，短暂时间无法连接，之后恢复正常

【#|MySQL 8.0 高可用之MGR(组复制)搭配proxysql与keepalived】随机关闭keepalived，短暂时间无法连接，之后恢复正常

推荐阅读

• 

  2019圣诞节祝福语送朋友
• 

  管理信息系统 分析题
• 

  三炮台茶叶是什么茶叶
• 

  画画英语怎么读painting 画画的英语怎么写
• 

  初学安装套装门视频 包门套的门如何安装视频教程，门套安装方法
• 

  如何设置网易云音乐启动后自动播放音乐？网易云音乐自动播放设置方法
• 

  羊肉补肾阴虚还是肾阳虚
• 

  薛涛笺是什么意思 薛涛笺的解释
• 

  专硕考管理学综合的专业 管理学专硕有哪些专业
• 

  小天鹅TG100洗衣机声音很大维修价格
• 

  石上莲的功效与作用
• 

  生化危机2重制版里关暴君触发条件介绍 暴君什么时候出 绝对安全区域_网
• 

  低风险了为什么行程码还带星号
• 

  如何能让富婆死心塌地给钱花？
• 

  梨树春季修剪技术 梨树的夏季修剪技术
• 

  服务器数据怎么修改 服务器存档里怎么修改
• 

  这个世界有没有最美的女人和男人？
• 

  茭白冰箱冷藏保鲜多长时间
• 

  华为985处理器相当于高通骁龙多少？
• 

  40吨水家庭能用多久

• MySQL|数据库mysql增删查改操作指令
• 数据库学习|【数据库学习】——【汇总】MySQL数据库下载与安装（Msi文件安装和免安装版本）、Navicat下载与安装
• 微服务|Go 微服务十七 存储过程 + 事件 实现数据备份
• #|08-MySQL事务
• java|MYSQL优化自学笔记
• 在WordPress页面中从MySQL插入和查询数据
• #|LeetCode203. 移除链表元素
• SQL数据库|SQL数据库实操 第五波 完整性约束和触发器
• 数据库|Oracle数据库