mysql进阶1——proxysql中间件

一、基本了解

mysql实现读写分离方式：

1. 程序修改mysql操作，直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配，需要开发人员协助。
2. amoeba，直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案，自己分配账户，和后端数据库权限管理独立，权限处理不够灵活。
3. mysql-proxy，直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，效率低。
4. mycat中间件，需要开发介入。
5. proxysql中间件，推荐使用。

ProxySQL是什么？

1. ProxySQL 是一款可以实际用于生产环境的 MySQL 中间件，它有官方版和 percona 版两种。percona版是在官方版的基础上修改的，添加了几个比较实用的工具。生产环境建议用官方版。
2. ProxySQL 是用 C++ 语言开发的，虽然也是一个轻量级产品，但性能很好(据测试，能处理千亿级的数据)，功能也足够，能满足中间件所需的绝大多数功能，包括：
   • 最基本的读/写分离，且方式有多种
   • 可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由。换句话说，规则很灵活。基于schema和与语句级的规则，可以实现简单的sharding(分库分表)。
   • 可缓存查询结果。虽然ProxySQL的缓存策略比较简陋，但实现了基本的缓存功能，绝大多数时候也够用了。此外，作者已经打算实现更丰富的缓存策略。
   • 监控后端节点。ProxySQL可以监控后端节点的多个指标，包括：ProxySQL和后端的心跳信息，后端节点的read-only/read-write，slave和master的数据同步延迟性(replication lag)

ProxySQL常用端口：

• ProxySQL 启动后，将监听两个端口。admin管理接口是一个使用 MySQL 协议的接口，默认用户名和密码均为 admin。
• admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL。
• 接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口。
  

二、安装部署

• 下载proxysql安装包有三个路径，分别为官网、Percona网站和github社区
• proxysql默认配置文件为/etc/proxysql.cnf，有两个端口。6032是管理proxysql服务本身的，6033是通过proxysql管理后端mysql服务的。

1.下载安装。

wget https://github.com/sysown/proxysql/releases/download/v2.5.4/proxysql-2.5.4-1-centos7.x86_64.rpm
yum -y  localinstall proxysql-2.5.4-1-centos7.x86_64.rpm

2.启动服务。

systemctl enable --now proxysql

3.连接proxysql服务，通过mysql客户端连接，默认账号/密码：admin/admin。

yum -y install mariadb
mysql -uadmin -padmin -P6032 -h127.0.0.1

三、proxysql管理配置

• ProxySQL使用的是SQLite3数据库，通过SQLite3引 擎进行解析、操作的。与MySQL关系型数据库的语法稍有不同，但ProxySQL会对不兼容的语法自动进行调整，比如desc命令就玩不了，需要使用命令查看pragma table_info(“表名”);
• peoxysql中的数据都是以文件方式保存，文件里都是key/value形式保存数据，不能通过vim编辑器修改，只能通过特有的sql语句对其配置项修改。
• 每次修改数据后，需要load、save保存到磁盘，不然重启服务修改的数据会消失。

3.1 内置库

3.1.1 main库表

3.1.2 stats库表

3.1.3 monitor库

3.2 常用管理变量

区分admin管理接口的用户名和mysql_users中的用户名：

• admin管理接口的用户是连接到管理接口(默认端口6032)上用来管理、配置ProxySQL的。
• mysql_users表中的用户名是应用程序连接ProxySQL(默认端口6033)，以及ProxySQL连接后端MySQL Servers使用的用户。它的作用是发送、路由SQL语句，类似于MySQL Server的3306端口。所以，这个表中的用户必须已经在后端MySQL Server上存在且授权了。
• admin管理接口的用户必须不能存在于mysql_users中，这是出于安全的考虑，防止通过admin管理接口用户猜出mysql_users中的用户。

注意事项：

• 命令中的有些单词可以简写。比如disk/memory/runtime/config可以缩写，只要能识别即可。
• 例如memory可以缩写为mem，runtime可以缩写为run。

3.2.1 添加管理用户

基本了解：

• admin-admin_credentials变量控制的是admin管理接口的管理员账户。
• 默认的管理员账户和密码为admin/admin，但是这个默认的用户只能在本地使用。
• 如果想要远程连接到ProxySQL必须自定义一个管理员账户。

1.连接本地proxysql，查看当前用户密码。

mysql -uadmin -padmin -P6032 -h127.0.0.1
select @@admin-admin_credentials;

2.添加新管理员帐户qingjun/citms。

set admin-admin_credentials='admin:admin;qingjun:citms';

//刷新，保存到磁盘。
load admin variables to runtime;
save admin variables to disk;

3.其他服务器连接prosysql。

mysql -uqingjun -pcitms -P6032 -h192.168.161.129

4.查看proxysql用户表。

//根据表的字段来查看，与mysql语法相同。
select * from global_variables where variable_name='admin-admin_credentials';  


//根据proxysql语法查看。
select @@admin-admin_credentials;

3.2.2 添加普通用户

基本了解：

• admin-stats_credentials 变量控制admin管理接口的普通用户，这个变量中的用户没有超级管理员权限，只能查看monitor库和main库中关于统计的数据，其它库都是不可见的，且没有任何写权限。
• 默认的普通用户名和密码均为 stats ，与admin一样，默认只能用于本地登录，若想远程查看则要添加查看的专有用户。

1.只能查看到monitor、main库的内容。

mysql -ustats -pstats -P6032 -h127.0.0.1

2.设置远程连接用户。

set admin-stats_credentials='stats:stats;baimu:123456'; 
load admin variables to runtime;
save admin variables to disk;

3.使用远程用户在其他机器上登录proxysql。

mysql -ubaimu -p123456 -h192.168.161.129 -P6032

3.2.3 修改监听套接字

基本了解：

• admin-mysql_ifaces 变量指定admin接口的监听地址，格式为冒号分隔的hostname:port列表。默认监听在 0.0.0.0:6032

1.默认的监听套接字是0.0.0.0:6032。

select @@admin-mysql_ifaces;

2.修改监听端口。

//修改监听端口为8888，之后连接proxysql就是用8888连接。
set admin-mysql_ifaces='0.0.0.0:8888';
load admin variables to runtime;
save admin variables to disk;

3.去除-h参数登录。

//创建默认套接字存放目录。
mkdir /var/lib/mysql/
chown -R proxysql.proxysql /var/lib/mysql/


//使用管理员用户修改。
mysql -uadmin -padmin -P8888 -h127.0.0.1
set admin-mysql_ifaces='0.0.0.0:8888;/var/lib/mysql/mysql.sock'; 
load admin variables to runtime;
save admin variables to disk;

四、多层配置系统

多层匹配系统的好处：

• 可以在线修改几乎所有配置(仅有的两个需要重启才能生效的变量为 mysql-threads 和 mysql-stacksize )，并在线生效、持久化保存。

4.1 系统结构

1. 最底层是disk库和主配置文件/etc/proxysql.cnf 。ProxySQL第一次启动时，读取的是主配置文件，此时会生成proxysql.db文件。此后就是从disk 库中读取配置，并加载到内存，最终加载到runtime 生效。
2. 中间层的是 memory ，表示内存数据库，就是指main 库中不以runtime开头的表。通过管理接口修改的所有配置，都保存在内存数据库(main)中。当 ProxySQL 重启或者崩溃时，这个内存数据库中的数据会丢失，所以需要 save 到 disk 库中。
3. 最上层的是 runtime ，指main 库中以runtime开头的表，是已生效的配置。所以，修改了 main 库中的配置后，必须 load 到 runtime 数据结构中才能使其生效。

       +-------------------------+
       |         RUNTIME         |
       +-------------------------+
              /|\          |
               |           |
           [1] |       [2] |
               |          \|/
       +-------------------------+
       |         MEMORY          |
       +-------------------------+ _
              /|\          |      |\
               |           |        \
           [3] |       [4] |         \ [5]
               |          \|/         \
       +-------------------------+  +---------------+
       |          DISK           |  |  CONFIG FILE  |
       +-------------------------+  +---------------+


序号[1] ：将内存数据库中的配置加载到RUNTIME数据结构中
          LOAD XXX FROM MEMORY
          LOAD XXX TO RUNTIME  （常用）

序号[2] ：将RUNTIME数据结构中的配置持久化到内存数据库中
          SAVE XXX FROM RUNTIME
          SAVE XXX TO MEMORY
  
序号[3] ：将磁盘数据库中的配置加载到内存数据库中
          LOAD XXX FROM DISK
          LOAD XXX TO MEMORY

序号[4] ：将内存数据库中的配置持久化到磁盘数据库中
          SAVE XXX FROM MEMORY
          SAVE XXX TO DISK （常用）

序号[5] ：从传统配置文件中读取配置加载到内存数据库中
          LOAD XXX FROM CONFIG


XXX表示要加载、保存的是哪类配置，目前支持一下几类：
1.mysql users
2.mysql servers
3.mysql variables
4.mysql query rules
5.admin variables
6.scheduler
7.proxysql_servers：目前ProxySQL集群功能还处于实验阶段，所以该类配置不应该去使用

4.2 修改变量加载配置

MySQL [(none)]> show tables from disk;
+------------------------------------+
| tables                             |
+------------------------------------+
| global_variables                   |  # (1)
| mysql_collations                   |  # (N)
| mysql_group_replication_hostgroups |  # (2)
| mysql_query_rules                  |  # (3)
| mysql_query_rules_fast_routing     |  # (4)
| mysql_replication_hostgroups       |  # (5)
| mysql_servers                      |  # (6)
| mysql_users                        |  # (7)
| proxysql_servers                   |  # (8)
| scheduler                          |  # (9)
+------------------------------------+


(1)中包含两类变量，以amdin-开头的使用admin variables修改；以mysql-开头的使用mysql variables修改。
(2,5,6)使用mysql servers修改。
(3,4)使用mysql query rules修改。
(7)使用mysql users修改。
(9)使用scheduler修改。
(N)只是一张表，保存的是ProxySQL支持的字符集和排序规则，它是不用修改的
(8)是ProxySQL的集群配置表，该功能目前还处于实验阶段。如果想要配置该功能，则load/save proxysql_servers to/from ...

4.3 启动加载流程

1. 若ProxySQL 是刚安装的，或者磁盘数据库文件为空(甚至不存在)，或者启动 ProxySQL 时使用了选项 --initial，这几种情况启动 ProxySQL 时，都会从传统配置文件 config file 中读取配置加载到内存数据库，并自动 load 到 runtime 数据结构、save到磁盘数据库，这是初始化 ProxySQL 运行环境的过程。
2. 若不是第一次启动ProxySQL ，由于已经存在磁盘数据库文件，这时 ProxySQL 会从磁盘数据库中读取几乎所有的配置(即使传统配置文件中配置了某项，也不会去解析)。
3. 有3项是必须从传统配置文件中读取，分别是：
   • datadir：ProxySQL启动时，必须从配置文件中确定它的数据目录，因为磁盘数据库文件、日志以及其它一些文件是存放在数据目录下的。如果使用/etc/init.d/proxysql管理ProxySQL，则除了修改/etc/proxysql.cnf的datadir，还需要修改该脚本中的datadir。
   • restart_on_missing_heartbeats：MySQL线程丢失多少次心跳，就会杀掉这个线程并重启它。默认值为10。
   • execute_on_exit_failure：如果设置了该变量，ProxySQL父进程将在每次ProxySQL崩溃的时候执行已经定义好的脚本。建议使用它来生成一些崩溃时的警告和日志。注意，ProxySQL的重启速度可能只有几毫秒，因此很多其它的监控工具可能无法探测到ProxySQL的一次普通故障，此时可使用该变量

总结： proxysql启动时，会从配置文件/etc/proxysql.cnf加载配置项，当服务启动后会生成/var/lib/proxysql/proxysql.db文件，此后读取的大部分配置都是从/var/lib/proxysql/proxysql.db中读取。

1.proxysql服务已启动，已生成在proxysql.db文件，连接mysql服务器的端口是默认的6033。

2.将proxysql.db删除或移走，修改主配置文件，重启服务，6033变成3306

mv /var/lib/proxysql/proxysql.db  /opt/
vim /etc/proxysql.cnf 
......
interfaces="0.0.0.0:3306"    //将6033修改成3306

systemctl restart proxysql

3.此时不移走proxysql.db文件，在修改主配置文件该参数为3307，重启服务，发现依然是3306。是因为此时读取的是proxysql.db文件里的参数配置。

vim /etc/proxysql.cnf 
......
interfaces="0.0.0.0:3307"    //将3306改成3307

systemctl restart proxysql

文章来源地址https://www.toymoban.com/news/detail-606609.html

到了这里，关于mysql进阶1——proxysql中间件的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！