-
MySQL中间件之ProxySQL(7):详述ProxySQL的路由规则
1.关于ProxySQL路由的简述 当ProxySQL收到前端app发送的SQL语句后,它需要将这个SQL语句(或者重写后的SQL语句)发送给后端的MySQL Server,然后收到SQL语句的MySQL Server执行查询,并将查询结果返回给ProxySQL,再由ProxySQL将结果返回给客户端(如果设置了查询缓存,则先缓存查询结果)。 ProxySQL可以实现多种方式的路由:基于ip/port、username、schema、SQL语句。其中基于SQL语句的路由是按照规则进行匹配的,匹配方式有hash高效匹配、正则匹配,还支持更复杂的链式规则匹配。 本文将简单演示基于端口、用户和schema的路由,然后再详细介绍基于SQL语句的路由规则。不过需要说明的是,本文只是入门,为后面ProxySQL的高级路由方法做铺垫。 在阅读本文之前,请确保: 已经理解ProxySQL的多层配置系统,可参考:ProxySQL的多层配置系统 会操作ProxySQL的Admin管理接口,可参考:ProxySQL的Admin管理接口 已经配置好了后端节点、mysql_users等。可参考:ProxySQL管理后端节点 如果想速成,可参考;ProxySQL初试读写分离 本文涉及到的实验环境如下: 角色 主机IP server_id 数据状态 Proxysql 192.168.100.21 null 无 Master 192.168.100.22 110 刚安装的全新MySQL实例 Slave1 192.168.100.23 120 刚安装的全新MySQL实例 Slave2 192.168.100.24 130 刚安装的全新MySQL实例 该实验环境已经在前面的文章中搭建好,本文不再赘述一大堆的内容。环境的搭建请参考前面给出的1、2、3。 2.ProxySQL基于端口的路由 我前面写了一篇通过MySQL Router实现MySQL读写分离的文章,MySQL Router实现读写分离的方式就是通过监听不同端口实现的:一个端口负责读操作,一个端口负责写操作。这样的路由逻辑非常简单,配置起来也很方便。 虽然基于端口实现读写分离配置起来非常简单,但是缺点也很明显:必须在前端ap……
SE_You 2024-03-2173 0 0 -
MySQL中间件之ProxySQL(6):管理后端节点
1.配置后端节点前的说明 为了让ProxySQL能够找到后端的MySQL节点,需要将后端的MySQL Server加入到ProxySQL中。ProxySQL的一切配置行为都是在修改main库中的对应的表,所以添加节点到ProxySQL中实际上也是通过修改相关表来实现的。 管理后端节点有几个过程: 将后端MySQL Server的主从复制结构配置好。 将后端MySQL Server节点加入到ProxySQL中。相关的表为mysql_servers。 监控后端节点。相关的表是全局变量表global_vairbles,相关的变量为mysql-monitor_开头的变量。 配置读、写组。相关的表为mysql_replication_hostgroups。 配置MySQL用户,用于发送SQL语句。相关的表为mysql_users。 几个注意点: (1).ProxySQL是通过监控后端节点的read_only值来自动调整节点所属组的,例如read_only=1的节点会移动到读组,read_only=0的节点会移动到写组。所以,在配置读、写组之前,需要先监控后端节点。ProxySQL也支持手动管理后端节点,这种模式不会根据read_only的值自动调整,在后面的文章中会介绍这种模式。 (2).对于传统的主从复制,默认的read_only=0,所以在第一步中,各slave节点的配置文件中需要加上read_only=1。对于组复制、Galera,因为会自动强制设置非写节点的read_only=1,所以无需额外配置该属性。 (3).ProxySQL支持传统主从复制结构(即异步、半同步、gtid复制)的后端,读、写组相关的表是mysql_replication_hostgroups。还支持MySQL组复制结构的后端,相关的表是mysql_group_replication_hostgroups,还支持Galera(如percona XtraDB cluster)结构的后端,不过ProxySQL是通过scheduler调度proxysql_galera_checker.sh脚本来支持Galera的,而且目前还没有mysql_galera_hostgroups(ProxySQL 2.0才新增该表)。 本文暂时只解释mysql_servers和mysql_replication_hostgroups,组复制相关的表在在后面介……
SE_You 2024-03-2066 0 0 -
MySQL中间件之ProxySQL(5):线程、线程池、连接池
1.ProxySQL的线程 ProxySQL由多个模块组成,是一个多线程的daemon类程序。每个模块都有一个或多个线程去执行任务。 例如,以下是刚启动ProxySQL时的进程情况,一个main进程,一个主线程,21个线程。 [root@s1 ~]# pstree | grep proxy |-proxysql---proxysql---21*[{proxysql}] 下面是正常运行时使用的线程列表: 1.1 Main thread 这其实是一个进程,该进程只负责引导、启动核心模块以及启动其它核心线程。 1.2 Admin thread 该线程负责以下几件事: 初始化并引导启动Admin接口。 从磁盘数据库或配置文件中加载配置,为ProxySQL的运行提供环境。 启动一个监听者,让其负责监听并接受到Admin接口的新连接,并为每个这样的连接创建一个新线程。 所以,每连接一次admin接口,就会新生成一个线程。每次退出admin接口时,减去一个线程。 1.3 MySQL workers mysql-threads线程负责处理MySQL流量,包括所有来自客户端的连接以及所有到后端服务器节点的连接。也就是:用少量线程处理任意多数量的连接。 MySQL workers线程在相同的端口上进行监听。当新客户端发起连接请求,其中一个MySQL worker线程将成功接受该连接,并创建一个MySQL会话(session):客户端和会话绑定在该worker线程上,直到连接断开。换句话说,在断开连接之前,某客户端的所有连接总是被同一个worker线程处理。 默认情况下,MySQL worker的线程数量为4。 mysql> select @@mysql-threads; +-----------------+ | @@mysql-threads | +-----------------+ | 4 | +-----------------+ mysql-threads变量修改后,必须重启ProxySQL才能生效,这是少有的需要重启的变量之一(另一个是mysql-stacksize)。例如,修改为8个mysql worker线程。 set mysql-threads=8; save mysql variables to disk; select * from ru……
SE_You 2024-03-1932 0 0 -
MySQL中间件之ProxySQL(4):多层配置系统
1.ProxySQL中的库 使用ProxySQL的Admin管理接口连上ProxySQL,可查看ProxySQL拥有的库。 mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin> ' admin> 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 | +-----+---------------+-------------------------------------+ 其中: main库是ProxySQL最主要的库,是需要修改配置时使用的库,它其实是一个内存数据库系统。所以,修改main库中的配置后,必须将其持久化到disk上才能永久保存。 disk库是磁盘数据库,该数据库结构和内存数据库完全一致。当持久化内存数据库中的配置时,其实就是写入到disk库中。磁盘数据库的默认路径为$DATADIR/proxysql.db。 stats库是统计信息库。这个库中的数据一般是在检索其内数据时临时填充的,它保存在内存中。因为没有相关的配置项,所以无需持久化。 monitor库是监控后端MySQL节点相关的库,该库中只有几个log类的表,监控模块收集到的监控信息全都存放到对应的log表中。 stats_history库是1.4.4版新增的库,用于存放历史统计数据。默认路径为$DATADIR/proxysql_stats.db。 ProxySQL内部使用的是SQLite3数据库,无论是内存数据库还是磁盘数据库,都是通过SQLite3引擎进行解析、操作的。它和MySQL……
SE_You 2024-03-1817 0 0 -
MySQL中间件之ProxySQL(3):Admin管理接口
1.ProxySQL的Admin管理接口 当ProxySQL启动后,将监听两个端口: (1).admin管理接口,默认端口为6032。该端口用于查看、配置ProxySQL。 (2).接收SQL语句的接口,默认端口为6033,这个接口类似于MySQL的3306端口。 ProxySQL的admin管理接口是一个使用MySQL协议的接口,所以,可以直接使用mysql客户端、navicat等工具去连接这个管理接口。 例如,使用mysql客户端去连接ProxySQL的管理接口。下面使用的是管理接口的默认用户名和密码admin:admin。 mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin> ' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin> 由于ProxySQL的配置全部保存在几个自带的库中,所以通过管理接口,可以非常方便地通过发送一些SQL命令去修改ProxySQL的配置。ProxySQL会解析通过该接口发送的某些对ProxySQL有效的特定命令,并将其合理转换后发送给内嵌的SQLite3数据库引擎去运行。 例如,查看库、库中的表。 admin> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | ma……
SE_You 2024-03-1520 0 0 -
MySQL中间件之ProxySQL(2):初试读写分离
1.实现一个简单的读写分离 这里通过一个简单的示例实现ProxySQL的读写分离功能,算是ProxySQL的快速入门。即使是快速入门,需要配置的内容也很多,包括:后端MySQL配置、监控配置、发送SQL语句的用户、SQL语句的路由规则。所以,想要实现一个ProxySQL+MySQL,即使只实现最基本的功能,步骤也是挺多的,不过配置的逻辑都很简单。 实验环境: 角色 主机IP server_id 数据状态 Proxysql 192.168.100.21 null 无 Master 192.168.100.22 110 刚安装的全新MySQL实例 Slave1 192.168.100.23 120 刚安装的全新MySQL实例 Slave2 192.168.100.24 130 刚安装的全新MySQL实例 为了演示完整的过程,这里把后端MySQL主从复制的基本配置步骤也列出来了。如了解配置过程,可跳过主从配置的部分。 注意点:slave节点需要设置read_only=1。如果后端是PXC/MGR/MariaDB Galera,则无需手动设置,因为会自动设置。 1.1 配置后端的主从复制 提供3个MySQL节点的配置文件。 # 以下是Master的配置文件 [mysqld] datadir=/data socket=/data/mysql.sock server-id=110 log-bin=/data/master-bin sync-binlog=1 log-error=/data/error.log pid-file=/data/mysqld.pid # 以下是slave1的配置文件 [mysqld] datadir=/data socket=/data/mysql.sock server-id=120 relay_log=/data/relay-log log-error=/data/error.log pid-file=/data/mysqld.pid read_only=1 # 以下是slave2的配置文件 [mysqld] datadir=/data socket=/data/mysql.sock server-id=130 relay_log=/data/relay-log log-error=/data/error.log pid-file=/data/mysqld.pid read_only=1 为3个MySQL节点提供数据目录/data mkdir /data chown -R mysql.mysql /dat……
SE_You 2024-03-1448 0 0 -
MySQL安全配置之基本设置
1. 安装最新的安全补丁 安全说明 确保数据库版本为最新并修复已知的安全漏洞。攻击者可能会利用已知的漏洞对MySQL服务器进行攻击。 检查方法 mysql> show variables where variable_name like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 8.0.16 | +---------------+--------+ 1 row in set (0.00 sec) 显示当前的数据库版本,查看MySQL官网,比较补丁修复情况。 配置方法 安装最新版本的补丁或升级到最新版本。 2. 删除默认安装数据库test 安全说明 MySQL安装时默认创建一个测试数据库test,完全的空数据库,没有任何表,可以删除。测试数据库可以被所有用户访问,并且可以用来消耗系统资源。删除测试数据库将减少MySQL服务器的攻击面。 检查方法 mysql> show databases like 'test'; Empty set (0.00 sec) 检查test数据库是否存在。 配置方法 mysql> drop database test; 3. 设置“allow-suspicious-udfs”为“FALSE” 安全说明 该选项控制是否可以载入主函数只有 xxx 符的用户定义函数。默认情况下,该选项被关闭,并且只能载入至少有辅助符的 UDF。关闭allow-suspicious-udfs,可以防止通过共享对象文件加载存在威胁的UDFs函数。 检查方法 查看MySQL配置文件allow-suspicious-udfs参数的设定:cat /etc/my.cnf 确认allow-suspicious-udfs不等于TRUE 配置方法 修改MySQL配置文件my.cnf,删除以下安全参数 allow-suspicious-udfs=TRUE (或者将TRUE改为FALSE,具体效果一样) 重启mysql服务。 4. 禁用“local_infile” 安全说明 local_infile参数决定是否可以通过LOAD DATA LOCAL INFILE来实现加载或或选择位于MySQL客户端计算机上的文件。禁用local_infile可以阻止黑客……
SE_Ning 2024-03-1244 0 0 -
MySQL安全配置之网络设置
1. 设置“have_ssl”为YES 安全说明 在不受信任的网络上传输时,所有网络流量都必须使用ssl/tls。受SSL/TLS协议保护的MySQL有助于防止窃听和中间人攻击。 检查方法 mysql> show variables where variable_name='have_ssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_ssl | YES | +---------------+-------+ 1 row in set (0.00 sec) 查看返回值是否为YES。 配置方法 可参考:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html 2. 设置远程用户的“ssl_type” * 安全说明 在不受信任的网络上传输时,所有网络流量都必须使用ssl/tls。对于通过网络进入系统的用户,应该基于每个用户强制执行SSL/TLS。受SSL/TLS协议保护的MySQL有助于防止窃听和中间人攻击。 检查方法 mysql> select user,host,ssl_type from mysql.user where not host in('::1','127.0.0.1','localhost'); +---------+-------------+----------+ | user | host | ssl_type | +---------+-------------+----------+ | airflow | 10.0.121.% | | | hive | 10.0.121.% | | | root | 10.0.121.% | | | airflow | 10.17.141.% | | | hive | 10.17.141.% | | | root | 10.17.141.% | | +---------+-------------+----------+ 6 rows in set (0.01 sec) 验证返回的每个用户的ssl_type是否等于ANY,X509或SPECIFIED。 配置方法 grant usage on airflow.* to 'airflow'@'10.0.121.%' require ssl;
SE_Ning 2024-03-1113 0 0 -
MySQL安全配置之认证
1. 禁止在全局配置中存储密码 安全说明 MySQL配置文件的[client]部分允许设置要使用的用户和密码。在全局配置中使用密码参数可能会造成用户密码的泄漏。 检查方法 打开MySQL配置文件my.cnf,检查[client]部分,确保未使用密码。 配置方法 使用mysql_config_editor以加密形式在.mylogin.cnf中存储身份验证凭据。 2. 设置“sql_mode”包含“NO_AUTO_CREATE_USER” 安全说明 sql_mode模式有三种,STRICT_TRANS_TABLES是其中一种模式。当执行数据更新操作(如INSERT、UPDATE),MySQL依据是否启用严格的SQL_MODE处理非法与丢失的数据。NO_AUTO_CREATE_USER是sql_mode的一个选项,可以阻止grant语句在特定情况下自动创建用户,给数据库带来安全隐患。 检查方法 mysql> select @@global.sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@global.sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select @@session.sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@session.sql_mode ……
SE_Ning 2024-03-0816 0 0 -
MySQL安全配置之审计和记录安全
1. 启用错误日志“log_error” 安全说明 错误日志包含有关事件的信息,例如mysqld启动和停止,何时需要检查和修复表,以及mysqld失败时堆栈跟踪。启用错误日志可以提高检测针对mysql和其他关键消息的恶意尝试的能力,如果没有启用错误日志,那么连接错误可能会被忽略。 检查方法 mysql> show variables like 'log_error'; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | log_error | /data/mysql/logs/mysqld.log | +---------------+-----------------------------+ 确保返回值不为空。 配置方法 打开MySQL配置文件my.cnf 将log-error选项设置为错误日志的路径。 2. 设置日志文件存储在非系统分区上 安全说明 MySQL日志文件可以在MySQL配置中设置,以存储在文件系统的任何位置。通常应将日志文件存放在非系统分区上。 检查方法 mysql> select @@global.log_bin_basename; +---------------------------+ | @@global.log_bin_basename | +---------------------------+ | /data/mysql/data/binlog | +---------------------------+ 确保返回的值不为root(‘/’)、/var或/usr。 配置方法 打开MySQL配置文件my.cnf 将log-bin选项设置为不在root(‘/’)、/var或/usr上的目录上。 3. 确保“log_error_verbosity”被设置为2或3 安全说明 log_error_verbosity系统变量决定MySQL日志中记录的内容。值为1表示仅记录错误消息,值为2表示记录错误和警告消息,值为3表示记录错误、警告和注释消息。随着级别的调整会记录更多信息,调整到2有助于通过日志追查安全问题。 检查方法 mysql> show global variables like 'log_error_verbosity'; +------------------……
SE_Ning 2024-03-0713 0 0