网站做弹窗广告,郑州网站建设天强科技,大型网站开发流程和步骤,计算机前端培训一、读写分离工作原理
读写分离的工作原理#xff1a;在大型网站业务中#xff0c;当单台数据库无法满足并发需求时#xff0c;通过主从同步方式同步数据。设置一台主服务器负责增、删、改#xff0c;多台从服务器负责查询#xff0c;从服务器从主服务器同步数据以保持一…一、读写分离工作原理
读写分离的工作原理在大型网站业务中当单台数据库无法满足并发需求时通过主从同步方式同步数据。设置一台主服务器负责增、删、改多台从服务器负责查询从服务器从主服务器同步数据以保持一致性从而提高数据库的并发和负载能力。 简单来说读写分离就是将数据库操作分为“读”和“写”两部分分别由不同的服务器处理。主服务器通常是单台主要负责处理写操作如插入、更新、删除而从服务器通常是多台则主要负责处理读操作如查询。主从服务器之间通过主从同步机制保持数据的一致性。通过这种方式可以显著提高数据库的并发处理能力和负载能力从而减轻单台服务器的压力。
二、通过ProxySQL对读写分离进行浅层面的理解和运用
1、实验环境
机器名称IP配置服务角色备注proxy192.168.20.149proxysql控制器用于监控管理master192.168.20.150数据库主服务器slave1192.168.20.146数据库从服务器slave2192.168.20.148数据库从服务器 2、实现数据库主从复制
基于GTID实现mysql8.0主从同步配置过程略。 基本命令 开启gtid并设置server_id值 gtid_modeON enforce-gtid-consistencyON 建立主从同步 mysql CHANGE MASTER TO MASTER_HOST host, MASTER_PORT port, MASTER_USER user, MASTER_PASSWORD password, MASTER_AUTO_POSITION 1; mysql START SLAVE; mysql show slave status \G ........ Slave_IO_Running: Yes Slave_SQL_Running: Yes .......... 查看slave双yes就代表成功 3、安装ProxySQL
本人博客另外一篇文章可以直接拿
yum install -y proxysql
启动 ProxySQL
[rootproxy ~]# systemctl enable --now proxysql
#先启服务只需要mysql客户端直接下mariadb就行了
# 管理员登录
[rootproxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032 成功登录后可以查看一下库看看是否正常 4、配置 ProxySQL 所需账户
在 Master (192.168.20.150) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户
create user monitor192.168.%.% identified with mysql_native_password by Monitor123.com;
grant all privileges on *.* to monitor192.168.%.% with grant option;#proxysql 的对外访问账户
create user proxysql192.168.%.% identified with mysql_native_password by 123456;
grant all privileges on *.* to proxysql192.168.%.% with grant option;
5、配置proxySQL
创建组(定义写为1读为0)
MySQL [(none)] insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,proxy);
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)
注意ProxySQL会根据server的read_only的取值将服务器进行分组。read_only0的servermaster被分到编号为1的写组read_only1的serverslave则分到编号为0的读组
所以创建完成之后需要在两个从服务器配置文件/etc/my.cnf添加read_noly1。
MySQL [(none)] select * from mysql_replication_hostgroups;
---------------------------------------------------------
| writer_hostgroup | reader_hostgroup | check_type | comment |
---------------------------------------------------------
| 1 | 0 | read_only | proxy |
---------------------------------------------------------
1 row in set (0.00 sec)
通过查询我们可以清晰的看到我们所分的组
添加主从服务器节点
在proxySQL端添加主从服务器的节点并保存
MySQL [(none)] insert into mysql_servers(hostgroup_id,hostname,port) values (1,192.168.20.150,3306);
Query OK, 1 row affected (0.00 sec)MySQL [(none)] insert into mysql_servers(hostgroup_id,hostname,port) values (0,192.168.20.146,3306);
Query OK, 1 row affected (0.00 sec)MySQL [(none)] insert into mysql_servers(hostgroup_id,hostname,port) values (0,192.168.20.148,3306);
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.00 sec) 重要的信息是要保证主从服务器都是online状态 为ProxySQL监控MySQL后端节点
MySQL [(none)] use monitor
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MySQL [monitor] set mysql-monitor_usernamemonitor;
Query OK, 1 row affected (0.00 sec)MySQL [monitor] set mysql-monitor_passwordMonitor123.com;
Query OK, 1 row affected (0.00 sec)修改后保存到runtime和disk
MySQL [monitor] load mysql variables to runtime;
MySQL [monitor] save mysql variables to disk;查看监控账号【ProxySQL】
SELECT * FROM global_variables WHERE variable_name LIKE mysql-monitor_%;
//也可以这样快速定位
MySQL [(none)] select mysql-monitor_username;
--------------------------
| mysql-monitor_username |
--------------------------
| monitor |
--------------------------
1 row in set (0.00 sec)
MySQL [(none)] select mysql-monitor_password;
--------------------------
| mysql-monitor_password |
--------------------------
| Monitor123.com |
--------------------------
1 row in set (0.00 sec)
验证监控信息
ProxySQL 监控模块的指标都保存在monitor库的log表中 以下是连接是否正常的监控对connect指标的监控 在前面可能会有很多connect_error这是因为没有配置监控信息时的错误配置后如果connect_error的结果为NULL则表示正常 心跳信息的监控 查看read_only日志监控
MySQL [(none)] select * from mysql_server_read_only_log;Monitor 模块就会开始监控后端的read_only值当监控到read_only值就会按照read_only的值将某些节点自动移到读写组 一些监控的状态斗志在log相关都在monitor库下面的 global_variables 变量。 ProxySQL配置对外访问账号
前面已经配置配置ProxySQL 账户我创建的对外访问账户是用户proxysql密码123456
将对外访问账号添加到mysql_users表中
MySQL [monitor] insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values (proxysql,123456,1,1);
Query OK, 1 row affected (0.000 sec)MySQL [monitor] load mysql users to runtime;
Query OK, 0 rows affected (0.000 sec)MySQL [monitor] save mysql users to disk;
Query OK, 0 rows affected (0.007 sec)MySQL [monitor] select * from mysql_users\G
*************************** 1. row ***************************username: proxysqlpassword: 123456active: 1use_ssl: 0default_hostgroup: 1default_schema: NULLschema_locked: 0
transaction_persistent: 1fast_forward: 0backend: 1frontend: 1max_connections: 10000attributes: comment:
1 row in set (0.000 sec)注transaction_persistent 如果为1则一个完整的SQL只可能路由到一个节点这点非常重要主要解决这种情况一个事务有混合的读操作和写操作组成事务未提交前如果事务中的读操作和写操作路由到不同节点那么读取到的结果必然是脏数据。所以一般情况下该值应该设置为1尤其是业务中使用到事务机制的情况默认为0 6、测试主从同步
[rootslave1 ~]# mysql -h192.168.20.149 -uproxysql -p123456 -P 6033
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 3
Server version: 5.5.30 (ProxySQL)Copyright (c) 2000, 2022, Oracle and/or its affiliates.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.mysql show databases;
--------------------
| Database |
--------------------
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
5 rows in set (0.01 sec)mysql select server_id;
-------------
| server_id |
-------------
| 21 |
-------------
1 row in set (0.00 sec)#通过proxysql用户创建一个keme库
mysql create database keme;
Query OK, 1 row affected (0.00 sec)
在slave2192.168.20.148上去验证一下是否同步过去keme这个库 7、添加简单的读写分离规则
MySQL [monitor] insertintomysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,^select .* for update$,1,1);
l query rules to runtime;
save mysql query rulQuery OK, 1 row affected (0.000 sec)es to disk;MySQL
[monitor]
MySQL [monitor] insert into values(2,1,^select,0,1);mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
Query OK, 1 row affected (0.000 sec)MySQL [monitor] load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)MySQL [monitor] save mysql query rules to disk;
Query OK, 0 rows affected (0.006 sec)8.测试读写分离
读操作 写操作 简单的读写分离实验就结束了。
