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是支持读。