由 dawei 腾讯云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值的密码