沈阳成创网站建设公司,seo销售话术开场白,网页系统,无人在线观看高清完整视频下载1.什么是读写分离中间件#xff1f; 就是实现当[写]的时候转发到主库#xff0c;当[读]的时候转发到从库的工具。 很类似学习过的proxy,比如nginx proxy做动静分离. 2.为什么要实现读写分离#xff1f; 1#xff09;让主库专注于写#xff0c;因为读可以有很多从库可以干…1.什么是读写分离中间件 就是实现当[写]的时候转发到主库当[读]的时候转发到从库的工具。 很类似学习过的proxy,比如nginx proxy做动静分离. 2.为什么要实现读写分离 1让主库专注于写因为读可以有很多从库可以干。 2让多个从库接收并发读请求。 好处增加读和写的并发,防止锁竞争减轻主数据库的压力。
生产场景:读写比基本都比较大,超过10/1. 3.读写分离中间件有哪些 1程序实现JAVA,PHP,PYTHON,SHELL 需要读写分离的SQL语句DML语句关键字:insert,delete,update,select 读select开头找从库否则就找主库。 优点少配置一个服务降低了连接延迟以及服务宕机问题。 程序修改mysql操作直接和数据库通信简单快捷的读写分离和随机的方式实现的负载均衡权限独立分配需要开发人员协助。 2中间件插件(对应用层透明,ip1 读,ip2写) amoeba: 直接实现读写分离和负载均衡不用修改代码有很灵活的数据解决方案自己分配账户和后端数据库权限管理独立权限处理不够灵活。 mysql-proxy: 直接实现读写分离和负载均衡不用修改代码master和slave用一样的帐号效率低 proxySQL Atlas maxscale cobar mycat proxySQL推荐使用类似学习过的proxy NGINXnginx 反向代理代理web服务请求
4. ProxySQL介绍 ProxySQL是一个高性能的MySQL代理软件可以实现数据库读写分离支持 Query路由功能支持动态指定某个SQL进行缓存支持动态加载配置信息无需重启ProxySQL 支持故障切换和SQL过滤功能。 Scale 100K connections across thousands of servers ProxySQL网站 https://www.proxysql.com/ https://github.com/sysown/proxysql/wiki
5. 基本架构逻辑原理 见架构图。 6.检查修改主从复制环境 GTID环境: 51:Master mha node/mha management 52/53:Slave mha node 7. 安装ProxySQL插件 # 下载proxySQL https://proxysql.com/ https://github.com/sysown/proxysql/releases
Scale 100K connections across thousands of servers
# 安装proxySQL # 理论选择哪个机器都可以本次选择51安装。 [rootdb03 ~]# rpm -ivh proxysql-2.0.10-1-centos7.x86_64.rpm [rootdb03 bin]# systemctl start proxysql [rootdb03 bin]# netstat -tulnp|grep 603 tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 15595/proxysql tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 15595/proxysql
注意: #6033 对外提供服务端口,用户连接 #6032 管理员管理端口
# 登录proxySQL [rootdb03 bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
db03 [(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 | --------------------------------------------------------- db03 [(none)]use main Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed db03 [main]show tables; ---------------------------------------------------- | tables | ---------------------------------------------------- | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | runtime_* ----------------------------------------------------
8. ProxySQL中管理结构自带系统库
在ProxySQL中6032端口管理共五个库 main、disk、stats 、monitor、stats_history 核心是main库 main: main 库中有如下信息 1)mysql_replication_hostgroups : 读写节点分组配置信息 db03 [main]select * from mysql_replication_hostgroups; --------------------------------------------------------- | writer_hostgroup | reader_hostgroup | check_type | comment | --------------------------------------------------------- | 10 | 20 | read_only | proxy | --------------------------------------------------------- 2)mysql_servers: 后端可以连接MySQL服务器的列表 ###nginx upstream池子 db03 [main]select * from mysql_servers; -------------------------------------------------------------------------- | hostgroup_id | hostname | port | gtid_port | status | weight |max_connections | -------------------------------------------------------------------------- | 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 |1000 | | 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 |1000 | | 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 |1000 | | 20 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 |1000 | --------------------------------------------------------------------------
3)mysql_users: 配置后端数据库的账号和监控的账号。 db03 [main]select username,password,default_hostgroup from mysql_users; --------------------------------------- | username | password | default_hostgroup | --------------------------------------- | root | 123 | 10 | --------------------------------------- 4)mysql_query_rules: 指定Query路由到后端不同服务器的规则列表。 db03 [main]select rule_id,active,match_pattern,destination_hostgroup from mysql_query_rules; -------------------------------------------------------------- | rule_id | active | match_pattern | destination_hostgroup | -------------------------------------------------------------- | 1 | 1 | ^select.*for update$ | 10 | | 2 | 1 | ^select | 20 | --------------------------------------------------------------
注 表名以 runtime_开头的表示ProxySQL当前运行的配置内容不能直接修改。 不带runtime_是下文图中Mem相关的配置。
disk 持久化的磁盘的配置 stats 统计信息的汇总 monitor 监控的收集信息比如数据库的健康状态等 stats_history: ProxySQL收集的有关其内部功能的历史指标
9. ProxySQL管理接口的多层配置关系 见架构图。
整套配置系统分为三层 顶层 RUNTIME 中间层 MEMORY主要修改的配置表 持久层 DISK和CFG FILE RUNTIME 代表ProxySQL当前正在使用的配置无法直接修改此配置必须要从下一层MEM层load进来。
MEMORY MEMORY层上面连接RUNTIME层下面连接disk持久层。这层可以在线操作ProxySQL 配置随便修改不会影响生产环境。确认正常之后在加载到RUNTIME和持久化磁盘上。 修改方法 使用SQL DML语句insert、update、delete、select。
DISK和CONFIG FILE 持久化配置信息。重启时可以从磁盘快速加载回来。 10. 在不同层次间移动配置
为了将配置持久化到磁盘或者应用到 runtime在管理接口下有一系列管理命令来实现它们。
1. user相关配置 ## MEM加载到runtime LOAD MYSQL USERS TO RUNTIME;
## runtime保存至MEM SAVE MYSQL USERS TO MEMORY;
## disk加载到MEM LOAD MYSQL USERS FROM DISK;
## MEM到disk SAVE MYSQL USERS TO DISK;
## CFG到MEM LOAD MYSQL USERS FROM CONFIG 2. server相关配置 ## MEM加载到runtime LOAD MYSQL SERVERS TO RUNTIME;
## runtime保存至MEM SAVE MYSQL SERVERS TO MEMORY;
## disk加载到 MEM LOAD MYSQL SERVERS FROM DISK;
## MEM 到disk SAVE MYSQL SERVERS TO DISK;
## CFG 到 MEM LOAD MYSQL SERVERS FROM CONFIG 3. mysql query rules配置 ## MEM 加载到runtime LOAD MYSQL QUERY RULES TO RUNTIME;
## runtime 保存至 MEM SAVE MYSQL QUERY RULES TO MEMORY;
## disk 加载到 MEM LOAD MYSQL QUERY RULES FROM DISK;
## MEM 到 disk SAVE MYSQL QUERY RULES TO DISK;
## CFG 到 MEM LOAD MYSQL QUERY RULES FROM CONFIG 4. MySQL variables配置 ## MEM 加载到runtime LOAD MYSQL VARIABLES TO RUNTIME;
## runtime 保存至 MEM SAVE MYSQL VARIABLES TO MEMORY;
## disk 加载到 MEM LOAD MYSQL VARIABLES FROM DISK;
## MEM 到 disk SAVE MYSQL VARIABLES TO DISK;
## CFG 到 MEM LOAD MYSQL VARIABLES FROM CONFIG 总结 日常配置其实大部分时间在MEM层配置 然后load到RUNTIME然后SAVE到Disk。cfg很少使用。
常见命令 使用SQL DML语句修改insert、update、delete、select。
使用LOAD,SAVE生效和保存。 load xxx to runtime; save xxx to disk; XXX就是上面配置里关键字部分
注意 所有配置保MEM或disk层时都不会发生任何警告或错误。 只有load到runtime状态时才会验证配置。当load到 runtime时如果出现错误将恢复为之前保存的状态这时可以去检查错误日志。 11. ProxySQL应用————基于SQL实现读写分离
11.1 MySQL从库要设定read_only参数
1)主从复制的从库应该禁止用户写入。
2)ProxySQL如何判断主从根据是否有read-only1 read-only1就是从否则就是主。
将52,53设置为read-only1,修改配置步骤略. 11.2 配置读写节点分组信息
在mysql_replication_hostgroup表中配置读写组编号 10编号对应就是写库的组20编号对应的就是读库的组。 [rootdb01 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 db03 [(none)]use main
insert into mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) values (10,20,proxy);
db01 [main]select * from mysql_replication_hostgroups; --------------------------------------------------------- | writer_hostgroup | reader_hostgroup | check_type | comment | --------------------------------------------------------- | 10 | 20 | read_only | proxy | --------------------------------------------------------- db01 [main]load mysql servers to runtime; db01 [main]save mysql servers to disk; db01 [main]select * from mysql_replication_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 10 reader_hostgroup: 20 check_type: read_only comment: proxy 说明 ProxySQL会根据server的read_only的取值将服务器进行分组。 read_only0的servermaster被分到编号为10的写组 read_only1的serverslave则被分到编号20的读组。 所以需要将从库设置set global read_only1; 11.3. 添加主机到ProxySQL insert into mysql_servers(hostgroup_id,hostname,port) values (10,10.0.0.51,3306); insert into mysql_servers(hostgroup_id,hostname,port) values (20,10.0.0.52,3306); insert into mysql_servers(hostgroup_id,hostname,port) values (20,10.0.0.53,3306); db01 [main]select * from mysql_servers; --------------------------------------------------------------------------- | hostgroup_id | hostname | port | gtid_port | status | weight | max_connections | --------------------------------------------------------------------------- | 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 | 1000 | | 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 | 1000 | | 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 | 1000 | --------------------------------------------------------------------------- load mysql servers to runtime; save mysql servers to disk;
11.4. 创建监控用户并开启监控
1# 51主库创建监控用户给ProxySQL连接。 create user monitor% identified with mysql_native_password by 123; grant replication client on *.* to monitor%; # proxySQL库修改variables表让proxySQL可以通过monitor用户和123密码去连接和监控数据库 set mysql-monitor_usernamemonitor; set mysql-monitor_password123;
或者 UPDATE global_variables SET variable_valuemonitor WHERE variable_namemysql-monitor_username; UPDATE global_variables SET variable_value123 WHERE variable_namemysql-monitor_password; load mysql variables to runtime; save mysql variables to disk;
2# 查询监控日志 db01 [(none)]select * from mysql_server_connect_log; db01 [(none)]select * from mysql_server_ping_log; db01 [(none)]select * from mysql_server_read_only_log; db01 [(none)]select * from mysql_server_replication_lag_log;
3配置web节点等应用连接ProxySQL的用户 设定应用连接ProxySQL的用户为root密码为123
# proxysql代理 insert into mysql_users(username,password,default_hostgroup) values(root,123,10); load mysql users to runtime; save mysql users to disk; # 51主库创建允许proxysql代理访问的用户和密码 create user root% identified with mysql_native_password by 123; grant all on *.* to root%;
注意早期版本需要开启事务持续化。今天版本不需要只是提及。 update mysql_users set transaction_persistent1 where usernameroot; load mysql users to runtime; save mysql users to disk;
让事务里的所有SQL都读写同一个库 begin; select update insert delete commit; 4 读写规则流程或原理 架构图 5配置读写规则mysql_query_rules # ProxySQL insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,^select.*for update$,10,1); insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,^select,20,1);
load mysql query rules to runtime; save mysql query rules to disk;
注 select … for update规则的rule_id必须要小于普通的select规则的rule_id ProxySQL是根据rule_id的顺序进行规则匹配。
6. 模拟web测试读写分离 成功: [rootdb01 /usr/local/bin]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e select server_id; mysql: [Warning] Using a password on the command line interface can be insecure. ------------- | server_id | ------------- | 51 | ------------- [rootdb01 /usr/local/bin]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e select server_id; mysql: [Warning] Using a password on the command line interface can be insecure. ------------- | server_id | ------------- | 52 | ------------- [rootdb01 /usr/local/bin]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e select server_id; mysql: [Warning] Using a password on the command line interface can be insecure. ------------- | server_id | ------------- | 53 | ------------- [rootdb01 ~]# mysql -uroot -p123 -P6033 -h 127.0.0.1 -e begin;select server_id;commit; 遇到问题上面语句可以访问到51,52,53原因 db01 [main]select * from mysql_servers; --------------------------------------------------------------------------- | hostgroup_id | hostname | port | gtid_port | status | weight max_connections | --------------------------------------------------------------------------- | 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 1000 | | 20 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 1000 | | 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 1000 | | 10 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 1000 | | 10 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 1000 | | 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 1000 | --------------------------------------------------------------------------- 从上面那内容可以发现所有节点都在写组10里所有节点都在读组20里。
最终罪魁祸首没有在从库配置read_only1;临时设置如下 52/53上配置 set global read_only1; ##普通用户只读。 set global super_read_only1; ##管理员用户只读。 返回proxysql查看立刻发生吧变化 db01 [main]select * from mysql_servers; ------------------------------------------------------------------- | hostgroup_id | hostname | port | gtid_port | status | weight | comment | ------------------------------------------------------------------- | 10 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 | | | 20 | 10.0.0.51 | 3306 | 0 | ONLINE | 1 | | | 20 | 10.0.0.53 | 3306 | 0 | ONLINE | 1 | | | 20 | 10.0.0.52 | 3306 | 0 | ONLINE | 1 | | ------------------------------------------------------------------- 10是写组有51,20是读组有51,52,53.
[rootdb01 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e select server_id; db01 [(none)]select * from stats_mysql_query_digest\G 给开发人员: root 123 6033 10.0.0.51 自动实现读写分离,根据语句过滤select读,其他写.
8. ProxySQL应用扩展——花式路由规则 1. 基于端口实现读写分离 ## 修改ProxySQL监听SQL流量的端口号监听多端口上。 set mysql-interfaces0.0.0.0:6033;0.0.0.0:6034; save mysql variables to disk; ## 重启生效 systemctl restart proxysql
## 设定路由规则 delete from mysql_query_rules; # 为了测试先清空已有规则
insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) values(1,1,6033,10,1), (2,1,6034,20,1);
load mysql query rules to runtime; save mysql query rules to disk;
说明 除了基于端口进行分离还可以基于监听地址(修改字段proxy_addr即可)也可以基于客户端地址(修改字段client_addr字段即可)。 给开发人员: 写: root 123 6033 10.0.0.51
读 root 123 6034 10.0.0.51 2. 基于用户实现读写分离 insert into mysql_users(username,password,default_hostgroup) values(writer,123,10),(reader,123,20); load mysql users to runtime; save mysql users to disk;
delete from mysql_query_rules; # 为了测试先清空已有规则
insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply) values(1,1,writer,10,1),(2,1,reader,20,1);
load mysql query rules to runtime; save mysql query rules to disk;
#最后在主库创建writer,reader用户和密码。
作业实践基于端口和基于用户实现读写分离。 给开发人员: 写: writer 123 6033 10.0.0.51
读: reader 123 6033 10.0.0.51
