ProxySQL实现mysql读写分离

凌奢

已于 2023-11-20 15:54:16 修改

阅读量1.1k

点赞数 1

分类专栏：运维文章标签：数据库

于 2018-12-09 00:15:18 首次发布

本文链接：https://blog.csdn.net/qq_43094192/article/details/84900922

版权

环境说明

IP	角色	应用	系统平台
192.168.118.222	读写分离解析主机	proxysql	rhel7.4
192.168.118.112	master	mariadb	rhel7.4
192.168.118.100	slave	mariadb	rhel7.4

关闭防火墙,关闭selinux

[root@server01 ~]# systemctl stop firewalld
[root@server01 ~]# setenforce 0
[root@server01 ~]# systemctl disable firewalld

安装mysql并配置主从
安装proxysql

#安装mariadb
[root@localhost ~]# yum -y install mariadb mariadb-server
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# ss -antl
State       Recv-Q Send-Q   Local Address:Port     Peer Address:Port 
LISTEN      0      100          127.0.0.1:25                  *:*     
LISTEN      0      50                   *:3306                *:*     
LISTEN      0      128                  *:22                  *:*     
LISTEN      0      100                ::1:25                 :::*     
LISTEN      0      32                  :::21                 :::*     
LISTEN      0      128                 :::22                 :::*

#配置proxysql的yum源
安装proxysql
[root@localhost ~]# cat  /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
[root@localhost ~]# yum -y install proxysql
[root@localhost ~]# systemctl start proxysql
[root@localhost ~]# ss -antl
State       Recv-Q Send-Q   Local Address:Port     Peer Address:Port 
LISTEN      0      100          127.0.0.1:25                  *:*     
LISTEN      0      50                   *:3306                *:*     
LISTEN      0      128                  *:6032                *:*     
LISTEN      0      128                  *:6033                *:*     
LISTEN      0      128                  *:6033                *:*     
LISTEN      0      128                  *:6033                *:*     
LISTEN      0      128                  *:6033                *:*     
LISTEN      0      128                  *:22                  *:*     
LISTEN      0      100                ::1:25                 :::*     
LISTEN      0      32                  :::21                 :::*     
LISTEN      0      128                 :::22                 :::*

在192.168.118.112（主库）上添加proxysql可以增删查改的账号

MariaDB [(none)]> grant all on *.* to 'proxysql'@'192.168.118.222' identified by 'pwproxysql';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

登录proxysql管理端

[root@localhost ~]# export MYSQL_PS1="(\u@\h:\p) [\d]> "
[root@localhost ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Ora