Proxysql实现MySQL读写分离

环境：

---

主机名	IP	应用
proxysql	172.16.78.125	proxysql
master	172.16.78.128	mariadb
slave	172.16.78.129	mariadb

配置步骤：

---

1. 在master和slave上安装mariadb

[root@master ~]# yum -y install mariadb*
[root@master ~]# systemctl enable --now mariadb  
[root@master ~]# ss -antl |grep 3306
LISTEN     0      50           *:3306                     *:*

[root@slave ~]# yum -y install mariadb*
[root@slave ~]# systemctl enable --now mariadb
[root@slave ~]# ss -antl |grep 3306
LISTEN     0      50           *:3306                     *:*

2. 在master和slave上配置主从

//在主库中创建一个同步账号给从库使用
[root@master ~]# mysql
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'172.16.78.129' identified by 'repl123';              
Query OK, 0 rows affected (0.00 sec)

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

//配置主数据库
[root@master ~]# vim /etc/my.cnf
//在mysqld段添加下面两行内容
server-id = 10 
log-bin = mysql_bin
...
[root@master ~]# systemctl restart mariadb

//查看主库的状态
[root@master ~]# mysql
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

//配置从数据库
[root@slave ~]# vim /etc/my.cnf
//在mysqld段添加下面两行内容
server-id = 20 
relay-log = myrelay_bin
...
[root@slave ~]# systemctl restart mariadb

//配置并启动主从复制
[root@slave ~]# mysql
MariaDB [(none)]> change master to
master_host='172.16.78.128',
master_user='repl',
master_password='repl123',
master_log_file='mysql_bin.000001',
master_log_pos=245;
Query OK, 0 rows affected (0.01 sec)

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

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.78.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: myrelay_bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes    //此行和下面一行必须为yes
            Slave_SQL_Running: Yes
            ......

3. 安装proxysql

[root@proxysql ~]# wget http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/7/proxysql-2.0.13-1-centos7.x86_64.rpm
[root@proxysql ~]# yum -y localinstall proxysql-2.0.13-1-centos7.x86_64.rpm 
[root@proxysql ~]# systemctl enable --now proxysql
[root@proxysql ~]# ss -antl |grep -E '6032|6033'
LISTEN     0      128          *:6032