目录：
1.ProxySQL介绍
2.架构介绍
3.主从复制搭建
4.读写分离实现
5.总结
1. ProxySQL介绍
1.1 版本和特点
ProxySQL做为MySQL中间件主要有以下版本
两个版本：官方版和percona版， percona版是基于官方版基础上修改，是C++语言开发，轻量级但性能优异(支持处理千亿级数据)。具有中间件所需的绝大多数功能：
多种方式的读/写分离
定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
缓存查询结果
后端节点监控
1.2 安装与使用
准备工作：
实现读写分离前，新要实现主从复制
牢记：slave节点的read_only=1一定要设置，因为这个参数是ProxySQL判断主从服务的标准
安装：使用官方yum源即可
[root@proxy_sql ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
[root@proxy_sql ~]# yum install proxysql
[root@proxy_sql ~]# rpm -ql proxysql
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
[root@proxy_sql ~]# service proxysql start
Starting ProxySQL: 2020-07-08 01:29:09 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@proxy_sql ~]# ss -ntlu
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
tcp LISTEN 0 128 *:6032 *:*
tcp LISTEN 0 128 *:6033 *:*
1.3 ProxySQL的组成
服务脚本： /etc/init.d/proxysql
配置文件： /etc/proxysql.cnf
主程序： /usr/bin/proxysql
基于SQLITE的数据库文件： /var/lib/proxysql/
启动ProxySQL： service proxysql start
启动后会监听两个默认端口
6032： ProxySQL的管理端口
6033： ProxySQL对外提供服务的端口
使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：
mysql -uadmin -padmin -P6032 -h127.0.0.1
1.4 数据库说明
main: 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，
不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效，SAVE使其存到硬盘以供下次重启加载
disk: 是持久化到硬盘的配置， sqlite数据文件
stats: 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间， 等等
monitor: 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
说明：
在main和monitor数据库中的表， runtime_开头的是运行时的配置，不能修改，只能修改非runtime_表 修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
2. 架构介绍
ProxySQL实现读写分离.jpg
3. 主从复制搭建
3.1 主服务器搭建
[root@mysql_master ~]# yum install mariadb-server -y
[root@mysql_master ~]# cat /etc/my.cnf
[mysqld]
server-id=138
log-bin
[root@mysql_master ~]# systemctl start mariadb
[root@mysql_master ~]# mysql -e "grant replication slave on *.* to stone@'192.168.177.%' identified by 'stone'"
[root@mysql_master ~]# mysql -e "select user,host,password from mysql.user"
+-------+---------------+-------------------------------------------+
| user | host | password |
+-------+---------------+-------------------------------------------+
| root | localhost | |
| root | mysql\_master | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | mysql\_master | |
| stone | 192.168.177.% | *BBE37FCE8F92DCEABE3FFE8C290F7A15F12F9DCF |
+-------+---------------+-------------------------------------------+
[root@mysql_master ~]# mysql -e "show master logs"
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 397 |
+--------------------+-----------+
3.2 从服务器搭建
[root@mysql_slave1 ~]# yum install mariadb-server -y
[root@mysql_slave1 ~]# vim /etc/my.cnf
[root@mysql_slave1 ~]# systemctl start mariadb
[root@mysql_slave1 ~]# cat /etc/my.cnf
[mysqld]
server-id=139
read-only
[root@mysql_slave1 ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.177.138',
-> MASTER_USER='stone',
-> MASTER_PASSWORD='stone',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-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: 192.168.177.138
Master_User: stone
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 397
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 683
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.3 主从同步测试
master：
[root@mysql_master ~]# mysql -e "create database db1"
slave：
[root@mysql_slave1 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
4. 读写分离的实现
4.1 验证ProxySQL安装是否成功
[root@proxy_sql ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Server version: 5.5.30 (ProxySQL Admin Module)
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.00 sec)
MySQL [(none)]> show tables; 默认看的是MAIN库中的表
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
MySQL [(none)]> show tables from monitor;
+------------------------------------+
| tables |
+------------------------------------+
| mysql_server_connect_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+------------------------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> select * from sqlite_master where name='mysql_servers'\G; 查看表的表结构
*************************** 1. row ***************************
type: table
name: mysql_servers
tbl_name: mysql_servers
rootpage: 2
sql: CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL ,
port INT NOT NULL DEFAULT 3306 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD'))
NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1 , compression INT CHECK (compression >=0 AND
compression <= 102400) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 ,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 ,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 ,
comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.00 sec)
4.2 在ProxySQL中添加MySQL节点
MySQL [(none)]> select * from mysql_servers; 表中添加节点，对应字段填充即可
Empty set (0.00 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.177.138',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.177.139',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> select * from mysql_servers;
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 192.168.177.138 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.168.177.139 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
MySQL [(none)]> load mysql servers to runtime; 加载内存生效
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save mysql servers to disk; 保存到磁盘
Query OK, 0 rows affected (0.10 sec)
4.3 添加监控后端节点用户
说明：ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组
后端Master上添加监控用户：
[root@mysql_master ~]# mysql
MariaDB [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication client on *.* to proxycopy@'192.168.177.%' identified by 'stonefu';
Query OK, 0 rows affected (0.00 sec)
创建用户proxycopy用户查看数据库是否具有read_only属性，从而区分主从此时从服务器上也应已将此用户同步
ProxySQL节点上添加监控用户：
proxySQL配置
MySQL [(none)]> set mysql-monitor_username='proxycopy'; 在proxy_sql上面配置监控,这个用户非常重要
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> set mysql-monitor_password='stonefu';
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save mysql variables to disk;
Query OK, 0 rows affected (0.01 sec)
4.4 监控模块的指标检测
查看监控连接是否正常的 (对connect指标的监控)： (如果connect_error的结果为NULL则表示正常)
MySQL [(none)]> select * from mysql_server_connect_log;
+-----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-----------------+------+------------------+-------------------------+---------------+
| 192.168.177.138 | 3306 | 1594314471165155 | 6139 | NULL |
| 192.168.177.139 | 3306 | 1594314471825125 | 5518 | NULL |
| 192.168.177.139 | 3306 | 1594314531164901 | 4206 | NULL |
| 192.168.177.138 | 3306 | 1594314531978977 | 1379 | NULL |
| 192.168.177.139 | 3306 | 1594315011169208 | 3236 | NULL |
| 192.168.177.138 | 3306 | 1594315012149357 | 4662 | NULL |
+-----------------+------+------------------+-------------------------+---------------+
查看监控心跳信息 (对ping指标的监控)
MySQL [(none)]> select * from mysql_server_ping_log;
+-----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-----------------+------+------------------+----------------------+------------+
| 192.168.177.138 | 3306 | 1594314451937927 | 1337 | NULL |
| 192.168.177.139 | 3306 | 1594314452044268 | 1444 | NULL |
| 192.168.177.138 | 3306 | 1594314541951833 | 1447 | NULL |
| 192.168.177.138 | 3306 | 1594315042002364 | 711 | NULL |
| 192.168.177.139 | 3306 | 1594315042130345 | 1851 | NULL |
+-----------------+------+------------------+----------------------+------------+
查看read_only和replication_lag的监控日志
MySQL [(none)]> select * from mysql_server_read_only_log;
+-----------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+-----------------+------+------------------+-----------------+-----------+-------+
| 192.168.177.139 | 3306 | 1594314466650399 | 4850 | 1 | NULL |
| 192.168.177.138 | 3306 | 1594314466675059 | 1815 | 0 | NULL |
| 192.168.177.139 | 3306 | 1594314694787211 | 1802 | 1 | NULL |
| 192.168.177.139 | 3306 | 1594314696263700 | 1772 | 1 | NULL |
| 192.168.177.139 | 3306 | 1594315063978481 | 489 | 1 | NULL |
| 192.168.177.138 | 3306 | 1594315065463008 | 1136 | 0 | NULL |
| 192.168.177.139 | 3306 | 1594315065484606 | 1099 | 1 | NULL |
+-----------------+------+------------------+-----------------+-----------+-------+
MySQL [(none)]> select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)
4.5 设置分组信息
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup， reader_hostgroup， comment, 指定写组的id为10，读组的id为20
MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+-----------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+-----------------+------+--------+--------+
| 10 | 192.168.177.138 | 3306 | ONLINE | 1 |
| 20 | 192.168.177.139 | 3306 | ONLINE | 1 |
+--------------+-----------------+------+--------+--------+
2 rows in set (0.00 sec)
因为138read_only为NO所以是读组，139是写组但是哪些规则是读哪些是写还需要重新定义区分
4.6 配置发送SQL语句的用户
在Master创建访问用户
MariaDB [(none)]> grant all on *.* to magedusql@'192.168.177.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)
在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('magedusql','magedu',10); 往10上发写请求
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
验证
[root@proxy_sql ~]# mysql -umagedusql -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
| 138 |
+-------------+
[root@proxy_sql ~]# mysql -umagedusql -pmagedu -P6033 -h127.0.0.1 -e 'create database testdb10'
Master:
[root@mysql_master ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| dbtest |
| mysql |
| performance_schema |
| testdb10 |
+--------------------+
Slave:
[root@mysql_slave1 ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| dbtest |
| mysql |
| performance_schema |
| testdb10 |
+--------------------+
4.7 在ProxySQL配置路由规则，实现读写分离
与规则有关的表： mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表， 1.4.7之后支持。插入路由规则：将select语句分离到20的读组， select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组
MySQL [(none)]> insert into mysql_query_rules
-> (rule_id,active,match_digest,destination_hostgroup,apply)VALUES
-> (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
MySQL [(none)]> load mysql query rules to runtime;
MySQL [(none)]> save mysql query rules to disk;
注意：因ProxySQL根据rule_id顺序进行规则匹配， select ... for update规则的rule_id必须要小于普通的select规则的rule_id
4.8 验证
[root@proxy_sql ~]# mysql -umagedusql -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
| 139 |
+-------------+
[root@proxy_sql ~]# mysql -umagedusql -pmagedu -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id'
[root@proxy_sql ~]# mysql -umagedusql -pmagedu -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
[root@proxy_sql ~]# mysql -umagedusql -pmagedu -P6033 -h127.0.0.1 -e 'select id from testdb.t'
MySQL [(none)]> SELECT hostgroup hg,sum_time, count_star, digest_text
-> FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------+
| 10 | 1003215 | 1 | create table t(id int) |
| 20 | 7539 | 5 | select @@server_id |
| 10 | 5917 | 4 | show databases |
| 10 | 4415 | 3 | start transaction |
| 10 | 2652 | 1 | show databasaes |
| 10 | 2059 | 1 | create database test3 |
| 20 | 1573 | 1 | select @@sever_id |
| 10 | 1480 | 3 | select @@server_id |
| 10 | 1164 | 3 | commit |
| 10 | 795 | 1 | create database testdb10 |
| 10 | 785 | 1 | create database testdb5 |
| 10 | 735 | 1 | create database testdb6 |
| 20 | 733 | 1 | select * from mysql_servers |
| 10 | 655 | 1 | delete help |
| 10 | 620 | 1 | delete --help |
| 10 | 547 | 1 | create table t(id int) |
+----+----------+------------+----------------------------------+
18 rows in set (0.00 sec)
