加入收藏 | 设为首页 | 会员中心 | 我要投稿 PHP编程网 - 黄冈站长网 (http://www.0713zz.com/)- 数据应用、建站、人体识别、智能机器人、语音技术!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

数据库中间件ProxySQL读写自动分离实行

发布时间:2022-08-24 11:13:19 所属栏目: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值的密码

(编辑:PHP编程网 - 黄冈站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读