基于proxySQL实现mysql的读写分离试验

主服务器配置

配置服务器选项

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin

systemctl restart mairadb

创建主从复制账号

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

创建monitor用户，用于监控后端节点的用户，为了查看read-only是否为off

grant replication client on *.* to monitor@'192.168.160.%' identified by 'centos';

创建sqluser用户，前端调度器可通过用户连接主从服务器

grant all on *.* to sqluser@'192.168.160.%' identified by 'centos';

从节点配置

配置服务器选项

vim /etc/my.cnf
[mysqld]
server-id=2
read-only


systemctl restart mairadb

复制线程

CHANGE MASTER TO
MASTER_HOST='192.168.160.129',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;

start slave;

proxy代理服务器配置

新建yum仓并通过yum安装

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[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
EOF


yum install proxysql

启动服务，并登入ProxySQL的数据库

service proxysql start
mysql -uadmin -padmin -P6032 -h127.0.0.1

查看mysql_servers的表结构

select * from sqlite_master where name='mysql_servers'\G

添加mysql节点信息，加载生效并保存

 insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.160.129',3306);
 insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.160.140',3306);
 load mysql servers to runtime;
 save mysql servers to disk;

ProxySQL上配置监控用的用户和密码

set mysql-monitor_username='monitor';
set mysql-monitor_password='centos';
load mysql variables to runtime;
save mysql variables to disk;

查看监控连接是否正常的，NULL为正常

select * from mysql_server_connect_log;

查看监控心跳（测ping）

 select * from mysql_server_ping_log;

设置分组信息，10代表写，20代表读

insert into mysql_replication_hostgroups values(10,20,"test");
load mysql servers to runtime;
save mysql servers to disk;

用户sqluser添加到mysql_users表中，当读写分离的路由规则不符合时，会访问默认组的数据库，默认组设置为写组10

insert into mysql_users(username,password,default_hostgroup) values('sqluser','centos',10);
 load mysql users to runtime;
 save mysql users to disk;

添加路由规则，只是查询的语句发送给20的读组，其他语句发送给10组

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;