proxysql mysql_基于proxySQL实现MySQL读写分离

image.png

主从复制配置

master

# /etc/my.cnf

[mysqld]

log-bin

server-id=12

# create accout for replication

mysql -e "grant replication slave on *.* to repluser@'192.168.80.%' identified by 'password'"

# 给proxysql创建监控账号monitor

grant replication client on *.* to monitor@'192.168.80.%' identified by 'password';

# 给proxysql访问mysql创建账号slquser

grant all on *.* to sqluser@'192.168.80.%' identified by 'password';

slave

[mysqld]

server-id=13

read-only

MariaDB [(none)]> CHANGE MASTER TO

-> MASTER_HOST='192.168.80.12',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='password',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='mariadb-bin.000001',

-> MASTER_LOG_POS=245;

MariaDB [(none)]> start slave;

ProxySQL配置

安装

# 配置yum源

[proxysql_repo]

name= ProxySQL YUM repository

baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever

gpgcheck=1

gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

# 安装

[root@80_11 ~]# yum install proxysql mariadb

# 启动

systemctl start proxysql

配置

# 连接

mysql -uadmin -padmin -P6032 -h127.0.0.1

# 添加mysql server

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.12',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.13',3306);

load mysql servers to runtime;

save mysql servers to disk;

# 配置监控

set mysql-monitor_username='monitor';

set mysql-monitor_password='password';

load mysql variables to runtime;

save mysql variables to disk;

# 设置分组

insert into mysql_replication_hostgroups values(10,20,"test");

load mysql servers to runtime;

save mysql servers to disk;

MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;

+--------------+---------------+------+--------+--------+

| hostgroup_id | hostname | port | status | weight |

+--------------+---------------+------+--------+--------+

| 10 | 192.168.80.12 | 3306 | ONLINE | 1 |

| 20 | 192.168.80.13 | 3306 | ONLINE | 1 |

+--------------+---------------+------+--------+--------+

# 定义读写规则

# 添加访问mysql账号

insert into mysql_users(username,password,default_hostgroup) values('sqluser','password',10);

load mysql users to runtime;

save mysql users to disk;

# 添加规则

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);

load mysql query rules to runtime;

save mysql query rules to disk;

# 测试

# 测试读操作是否路由给20的读组

mysql -usqluser -ppassword -P6033 -h127.0.0.1 -e 'select @@server_id'

# 测试写操作

[root@80_11 ~]# mysql -usqluser -ppassword -P6033 -h127.0.0.1 -e 'create database db1'

[root@80_11 ~]# mysql -usqluser -ppassword -P6033 -h127.0.0.1 -e 'select @@server_id'

+-------------+

| @@server_id |

+-------------+

| 12 |

+-------------+

# 路由的信息：查询stats库中的stats_mysql_query_digest表

MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;