mysql proxysql+mgr集群 centos7系统安装配置

编译安装 wget https://codeload.github.com/sysown/proxysql/tar.gz/v1.4.4 tar xf v1.4.4 cd proxysql-1.4.4 yum install -y automake bzip2 cmake make gcc gcc-c++ git openssl openssldevel patch make make install yum 安装 wget https://github.com/sysown/proxysql/releases/download/v1.4.4/proxysql-1.4.4-1-centos7.x86\_64.rpm yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm yum install proxysql 启动登录 systemctl start proxy mysql -uadmin -padmin -P6032 -h127.0.0.1 —prompt=‘proxysql>’ 配置proxysql  mysql内 mysql添加proxysql用户 mysql>grant all privileges on *.* to ‘proxysql’@’%’ identified by ‘proxysql’; mysql>flush privileges; 配置proxysql mysql服务注册 mysql>insert into mysql_servers (hostgroup_id, hostname, port) values(1,‘192.168.15.85’, 3306); mysql>insert into mysql_servers (hostgroup_id, hostname, port) values(2,‘192.168.15.86’, 3306); mysql>insert into mysql_servers (hostgroup_id, hostname, port) values(2,‘192.168.15.87’, 3306);   hostgroup_id = 1代表write group，针对我们提出的限制，这个地方只配置了一个节 点；hostgroup_id = 2代表read group，包含了MGR的所有节点。 #即时生效 mysql>LOAD MYSQL SERVERS TO RUNTIME; #存入磁盘 mysql>SAVE MYSQL SERVERS TO DISK; 添加proxysql监控mysql用户 GRANT SELECT on sys.* to ‘monitor’@’%’ identified by ‘monitor’; mysql>LOAD MYSQL VARIABLES TO RUNTIME; mysql>SAVE MYSQL VARIABLES TO DISK;   添加后端访问用户 mysql>insert into mysql_users(username, password) values(‘proxysql’,‘proxysql’); mysql>LOAD MYSQL USERS TO RUNTIME; mysql>SAVE MYSQL USERS TO DISK; 添加mysql group replication 信息 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 (1,3,2,4,1,1,0,100);   mysql>save mysql servers to disk; mysql>load mysql servers to runtime;     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’,2,1);   load mysql query rules to runtime; save mysql query rules to disk;   #将select语句全部路由至hostgroup_id=2的组(也就是读组) #但是select * from tb for update这样的语句是修改数据的，所以需要单独定义，将它路由至hostgroup_id=1的组(也就是写组) #其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users表中的default_hostgroup)   MySQL [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules; +---------+--------+----------------------+-----------------------+-------+ | rule_id | active | match_digest         | destination_hostgroup | apply | +---------+--------+----------------------+-----------------------+-------+ | 1       | 1      | ^SELECT.*FOR UPDATE$ | 1                     | 1     | | 2       | 1      | ^SELECT              | 2                     | 1     | +---------+--------+----------------------+-----------------------+-------+ 2 rows in set (0.00 sec)   select * from stats_mysql_query_digest;   4个组含义 1:writer group 2:reader group 3:backup group 4:offline group writer_is_also_reader： 0是写不支持读，1是支持读。