ProxySQL实现MySQL的读写分离

如何在Linux系统中安装，配置ProxySQL，以实现MySQL的读写分离，今天这篇文章意在实现对ProxySQL的安装与配置，以优化MySQL数据库的性能和可用性，实现高可用性和负载均衡。

设置各服务器IP地址信息

3台mgr（之前的文章），再建一台：

sudo cat >> /etc/sysconfig/network-scripts/ifcfg-ens33<<-'EOF' IPADDR=192.168.63.60 GATEWAY=192.168.63.2 NETMASK=255.255.255.0 DNS1=192.168.63.2 EOF hostnamectl set-hostname proxysql service network restart systemctl stop firewalld.service systemctl disable firewalld.service sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config 上传proxysql-2.4.4-1-centos7.x86_64.rpm yum -y localinstall proxysql-2.4.4-1-centos7.x86_64.rpm systemctl start proxysql.service tar -xvf mysql-8.0.23-1.el7.x86_64.rpm-bundle.tar rpm -ivh mysql-community-common-8.0.23-1.el7.x86_64.rpm  --nodeps --force rpm -ivh mysql-community-libs-8.0.23-1.el7.x86_64.rpm  --nodeps --force rpm -ivh mysql-community-client-8.0.23-1.el7.x86_64.rpm  --nodeps --force 添加mysql_servers mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' insert into mysql_servers(hostgroup_id,hostname,port,max_connections,comment) values(10,'192.168.63.71',3306,2000,'MGR_01'); insert into mysql_servers(hostgroup_id,hostname,port,max_connections,comment) values(10,'192.168.63.72',3306,2000,'MGR_02'); insert into mysql_servers(hostgroup_id,hostname,port,max_connections,comment) values(10,'192.168.63.73',3306,2000,'MGR_03'); select hostgroup_id,hostname,port,max_connections,comment,status from mysql_servers; 将配置加载到内存 load mysql servers to runtime; save mysql servers to disk; select hostgroup_id,hostname,port,max_connections,status from runtime_mysql_servers; select * from mysql_servers;

m1:        创建gr_member_routing_candidate_status视图，(主从故障切换关键)

MySql降低密码强度 Set global validate_password.policy=0; Set global validate_password.length=4; proxysql 的监控账户 create user 'monitor'@'%' identified by 'monitor@1122'; grant all privileges on *.* to 'monitor'@'%' with grant option; proxysql 的对外访问账户 create user 'proxysql'@'%' identified by 'proxysql@1122'; grant all privileges on *.* to 'proxysql'@'%' with grant option; flush privileges; USE sys; DELIMITER $$ CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$ 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 NOT IN ('ONLINE', 'RECOVERING')) >=     ((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) where member_id=my_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, Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats where member_id=my_id();$$

proxysql

定义服务器角色 insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100); 写入主服务器（写组） INSERT INTO mysql_users(username,password,default_hostgroup,active,transaction_persistent) VALUES ('proxysql','proxysql@1122',10,1,1); set mysql-monitor_username='monitor'; set mysql-monitor_password='monitor@1122'; load mysql servers to runtime; save mysql servers to disk; load mysql users to runtime; save mysql users to disk; load mysql variables to runtime; save mysql variables to disk; 检查和确认后端MySQL服务器的配置和状态 select hostgroup_id, hostname, port,status from runtime_mysql_servers; MySQL组日志中的最后6条记录 select hostname,port,viable_candidate, read_only,transactions_behind,error from mysql_server_group_replication_log order by time_start_us desc limit 6; 规则（读写分离） insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1); load mysql query rules to runtime; save mysql query rules to disk; load mysql servers to runtime; save mysql servers to disk; load mysql users to runtime; save mysql users to disk; load mysql variables to runtime; save mysql variables to disk;