数据库中间件ProxySQL读写自动分离实行
发布时间：2022-08-24 04:16:53 所属栏目：MySql教程 来源：互联网
导读：腾讯云cdb可以提供主库VIP+只读从库VIP的形式，这样开发使用时需要维护2个VIP而且不能自动读写分离，基于现状计划使用proxysql结合clb及cdb来实现读写自动分离。 架构如下： app--clb四层--proxysql--主vip+自读vip 一.ProxySQL部署 1.下载安装启动 [root@
|
腾讯云cdb可以提供主库VIP+只读从库VIP的形式，这样开发使用时需要维护2个VIP而且不能自动读写分离，基于现状计划使用proxysql结合clb及cdb来实现读写自动分离。 架构如下： app--clb四层--proxysql--主vip+自读vip 一.ProxySQL部署 1.下载安装启动 [root@VM_8_24_centos ~]# wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm [root@VM_8_24_centos ~]# rpm -ivh proxysql-2.0.8-1-centos7.x86_64.rpm [root@VM_8_24_centos ~]# systemctl start proxysql [root@VM_8_24_centos ~]# systemctl enable proxysql [root@VM_8_24_centos ~]# netstat -tulpn |grep 603 tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 26063/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 26063/proxysql 2.登陆测试 [root@VM_8_24_centos ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MySQL [(none)]> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.01 sec) MySQL [(none)]> 二.ProxySQL配置 1.架构 node1 (vip 192.168.0.9:3306) , mysql master node2 (vip 192.168.0.42:3306) , mysql slave prxoy (192.168.8.24:6033) , proxysql clb (vip 192.168.9.27:6033), clb proxysql app (192.168.0.26), mysql client 2.proxysql上添加mysql主从数据库信息 proxysql上执行，将主库master也就是做写入的节点放到group 0中，salve节点做读放到group 1中 语法如下： insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(0,'192.168.0.9',3306,1,2000,10,'TESTuser'); insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1,'192.168.0.42',3306,1,2000,10,'TESTuser'); select * from mysql_servers; MySQL [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> select host,user from mysql.user; +-----------+------------------+ | host | user | +-----------+------------------+ | % | TESTuser_data_servic | | % | TESTuser_rule_rw | | 192.168.8.24 | proxysql | | 192.168.8.24 | testuser | | 127.0.0.1 | root | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 7 rows in set (0.01 sec) 4.proxysql上添加应用操作账号 proxysql上执行，注意应用账号授权给主hostgroup，这里是0 语法如下： insert into mysql_users(username,password,active,default_hostgroup) values ('testuser','TESTuser@20191226',1,0); select * from mysql_users; 例子： MySQL [(none)]> insert into mysql_users(username,password,active,default_hostgroup) values ('testuser','TESTuser@20191226',1,0); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> select * from mysql_users; +----------+---------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +----------+---------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | testuser | TESTuser@20191226 | 1 | 0 | 0 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | +----------+---------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 1 row in set (0.00 sec) 注意：要是是用明文密码设置mysql_users，在这里可以用save命令来转换成了hash值的密码 （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐