mysql中间件51cto_ProxySQL实现mysql中间件读写分离-庭前雨落花开-51CTO博客

1、初始化准备，四台设备

master:192.168.2.6

slave:192.168.2.16

proxysql:192.168.2.26

Client:192.168.2.100,用于测试

2、搭建好主从服务器

master:主

[root@master ~]# yum -y install mariadb-server

[root@master ~]# vim /etc/my.cnf

[mysqld]

server_id=6

binlog_format=row

log_bin

skip_name_resolve

[root@master ~]# systemctl start mariadb

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.2.%' identified by '123456'; #创建复制账号

slave:从

[root@slave ~]# yum -y install mariadb-server

[root@slave ~]# vim /etc/my.cnf

[mysqld]

read-only

server_id=16

skip_name_resolve

binlog_format=row

[root@slave ~]# systemctl start mariadb

MariaDB [(none)]> CHANGE MASTER TO

-> MASTER_HOST='192.168.2.6',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='123456',

-> MASTER_PORT=3306,

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

-> MASTER_LOG_POS=245;

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status\G

3、测试主从同步是否正常

master:

MariaDB [(none)]> create database db1;

slave:

MariaDB [(none)]> show databases;

4、搭建proxysql yum仓库

proxysql:

[root@proxysql ~]# cat <

> [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

5、安装proxysql和mariadb客户端工具

proxysql: 6032管理端口、 6033连接端口

[root@proxysql ~]# yum -y install proxysql mariadb

[root@proxysql ~]# rpm -ql proxysql

[root@proxysql ~]# service proxysql start #老的启动方法

[root@proxysql ~]# ll /var/lib/proxysql/ #proxysql简化版的关系型数据库，用来存放proxysql配置

6、连接proxysql,在main. mysql_servers 加入主从服务器记录

proxysql:

[root@proxysql ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 #账号密码默认admin

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.2.6',3306); #组id，ip,端口

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.2.16',3306);

MySQL [(none)]> select * from mysql_servers; #查看添加的记录信息

7、主从服务器添加管理账户供proxysql机器来管理

master:

MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.2.%' identified by '123456'; #主从复制已经建立，只要在主服务器添加即可

8、设置使用monitor用户连接主从服务器 #main.global_variables表

proxysql:

MySQL [(none)]> set mysql-monitor_username='monitor';

MySQL [(none)]> set mysql-monitor_password='123456';

MySQL [(none)]> load mysql variables to runtime; #加载生效

MySQL [(none)]> save mysql variables to disk; #保存到磁盘

9、设置分组信息 #需要修改的是main库中的mysql_replication_hostgroups表

proxysql:

MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test"); #10组负责写，20负责读

MySQL [(none)]> select * from mysql_replication_hostgroups; #查看添加的记录

MySQL [(none)]> load mysql servers to runtime; #加载生效

MySQL [(none)]> save mysql servers to disk; #保存到磁盘

MySQL [(none)]> select * from mysql_servers; #再次查看记录，proxysql已经区分读写id了

10、在master节点创建访问用户，用于主从服务器去访问中间件proxysql #主从复制已经建立，只要在主服务器添加即可

master：

MariaDB [(none)]> grant all on *.* to sqluser@'192.168.2.%' identified by '123456';

11、将创建访问调度器proxysql的客户端用户sqluser加入proxysql服务器的

mysql_users表中

proxysql:

MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','123456',10); #default_hostgroup默认

组设置为写组10，

MySQL [(none)]> load mysql users to runtime; #生效

MySQL [(none)]> save mysql users to disk; #存盘

12、使用客户端测试

Client：

[root@centos6 ~]# mysql -usqluser -p123456 -h192.168.2.26 -P6033 -e 'select @@server_id';

13、设置调度规则

proxysql:

MySQL [(none)]> 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);

Query OK, 2 rows affected (0.00 sec) #读调度到20组，写调度到10组

MySQL [(none)]> load mysql query rules to runtime; #生效

MySQL [(none)]> save mysql query rules to disk; #保存

14、客户端测试

Client：

[root@centos6 ~]# mysql -usqluser -p123456 -h192.168.2.26 -P6033 -e 'select @@server_id';