九、MySQL读写分离
·中间件：在客户端与服务端之间，用于实现一些特定的功能，比如安全认证、读写分离、反向代理等功能。
MySQL集群
使用多台服务器实现相同的功能，通过网络进行连接。
分类：
·负载均衡集群：
·高可用集群（主备集群）：主服务器宕机后，从服务器立刻转换为主服务器做写的功能
·读写分离集群：主服务器负责写，从服务器负责读，算特殊的负载均衡集群
MySQL分布式
·单体架构：所有应用、功能、运算、存储等都在一台服务中。
·开发、简单测试、个人网站及个人应用、小型企业、验证或练习
·优点：搭建简单、成本低
·缺点：抗并发能力低，没有备份服务器
·分布式架构：“拆”，将服务、应用、运算、存储“拆分”到不同服务器上实现
·适合于规模较大、复杂的服务、应用
·横向扩展（增加服务器（节点）的数量、来提升整体的性能）
·分布式存储服务器：网络版的RAID10
·分布式缓存、分布式运算
ProxySQL
1、ProxySQL是一款开源的使用C++编写的MySQL集群代理中间件；
2、用于在MySQL数据库和客户端之间进行负载均衡、查询缓存、故障转移和查询分发；
3、它可以作为中间层插入到应用程序和数据库之间；
4、特点是高效灵活，使用简单，并且性能是所有中间件中比较优秀的。
5、在搭建好mysql的主从复制后，可以利用ProxySQL实现mysql数据库的读写分离；
主要功能：
1、代理服务：代理后端MySQL服务，进行相关指标的监控
2、负载均衡：后端多节点的访问进行负载均衡
3、高可用：自动识别异常节点，并屏蔽异常节点，保障集群的稳定
4、读写分离：自动动态的识别读写节点，转发SQL至对应节点执行
5、数据分片：通过路由规则，进行SQL分发，达到数据分片的目的
搭建ProxySQL
- 搭建MySQL主从
- 安装ProxySQL
- 配置ProxySQL
·设置MySQL账户、权限
·管理监控权限、读写、只读权限
·设置分发规则
4、测试
·使用客户端连接ProxySQL
·写（create/drop/insert/update/delete） master
·读（select）负载均衡 多个slave或master+slave
实战
一、安装proxysql（CentOS7安装proxysql）
# 配置官方yum源,或直接下载rpm包（下载地址：https://github.com/sysown/proxysql/releases）
vim /etc/yum.repos.d/proxysql.repo
++++++++++++++++++++++++++++++++++++++++++++++++++
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
++++++++++++++++++++++++++++++++++++++++++++++++++
#yum安装proxysql
yum makecache
yum install proxysql
启动proxysql服务并加入开机自启
systemctl start proxysql
systemctl enable proxysql
二、通过管理界面配置ProxySQL
使用mysql客户端，并在本地端口（6032）上进行连接。
默认账号密码admin:admin
mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql> 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 |
+-----+---------------+-------------------------------------+
三、配置内容
# 在主服务器的MySQL中创建监控用户
use mysql;
# 降低密码级别
set global validate_password_policy=low;
# 创建monitor用户，密码为用户名123
create user 'monitor'@'%' identified by 'monitor123';
# 设置权限
grant usage, replication client on *.* to 'monitor'@'%';
==================================================
# 将监控用户加入到proxysql中
update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';
update global_variables set variable_value='monitor' where variable_name='mysql-monitor_password';
# 将Master和slave节点添加到mysql_servers表中
# master主节点
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values (1,'192.168.221.20',3306,1,'write group');
# slave节点
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values (2,'192.168.221.30',3306,1,'read group');
# 保存
load mysql servers to runtime;
save mysql servers to disk;
# 查看添加的MySQL集群
select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1 | 192.168.221.20 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write group |
| 2 | 192.168.221.30 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read group |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
##########################################################
# 永久降低密码级别（修改配置文件，需重启 MySQL）
# 找到 MySQL 配置文件（如vim /etc/my.cnf），在[mysqld] 段落添加：
validate_password_policy=LOW
validate_password_length=4
# 保存后重启 MySQL 服务
systemctl restart mysqld
##########################################################
==================================================
# 在MySQL主从节点上创建用户，赋予权限，并将其存储到代理服务器中
# 在master节点和slave节点上创建adm用户,设置为管理员权限。
create user 'adm'@'%' identified by '123456';
grant all privileges on *.* to 'adm'@'%';
# 在master节点和slave节点上创建用户read,并设置权限为只读。
create user 'read'@'%' identified by '123456';
grant select on *.* to 'read'@'%';
flush privileges;
==================================================
# 在proxysql上添加用户
insert into mysql_users(username,password,default_hostgroup) values ('adm','123456',1);
insert into mysql_users(username,password,default_hostgroup) values ('read','123456',2);
# 保存用户
load mysql users to runtime;
save mysql users to disk;
# 查看
select hostgroup_id, hostname, port,status from runtime_mysql_servers;
会出现
+--------------+----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+----------------+------+---------+
| 1 | 192.168.221.20 | 3306 | ONLINE |
| 2 | 192.168.221.30 | 3306 | SHUNNED |
+--------------+----------------+------+---------+
==================================================
# 在proxysql上配置读写规则
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^select.*from update$',1,1);
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (2,1,'^select',2,1);
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (3,1,'^show',2,1);
# 保存规则
load mysql query rules to runtime;
save mysql query rules to disk;
# 查看规则
select rule_id,match_digest,destination_hostgroup,apply from mysql_query_rules order by rule_id;
会出现
+---------+-----------------------+-----------------------+-------+
| rule_id | match_digest | destination_hostgroup | apply |
+---------+-----------------------+-----------------------+-------+
| 1 | ^SELECT.*FROM UPDATE$ | 1 | 1 |
| 2 | ^SELECT | 2 | 1 |
| 3 | ^SHOW | 2 | 1 |
+---------+-----------------------+-----------------------+-------+
四、测试能否正常读取
# 在ProxySQL服务器登录MySQL
mysql -uread -p123456 -h 127.0.0.1 -P6033
select @@hostname,@@port;
# 在代理服务器上执行读操作
mysql -uread -p123456 -h 127.0.0.1 -P6033
show databases;
# 执行创建操作，测试效果
mysql -uadm -p123456 -h 127.0.0.1 -P6033
create database test2;
# 再次查询
mysql -uread -p123456 -h 127.0.0.1 -P6033
show databases;
在代理服务器中查询执行记录
# 可以看到proxysql将不同的请求分别发送给了不同的服务器处理，读取发送给slave节点，写入和删除发送给Master节点。
# 在proxysql服务器上执行
mysql -uadmin -padmin -h127.0.0.1 -P6032
select hostgroup,digest_text from stats_mysql_query_digest\G;
## ProxySQL保存配置：
# 修改后重新加载服务并保存配置，防止重启服务器后配置丢失。
# 重新加载并保存服务器设置
load mysql servers to runtime;
save mysql servers to disk;
# 重新加载并保存查询设置
load mysql query rules to runtime;
save mysql query rules to disk;
# 重新加载并保存用户设置
load mysql users to runtime;
save mysql users to disk;
# 重新加载并保存变量设置
load mysql variables to runtime;
save mysql variables to disk;
重置配置方法
如果你遇到 ProxySQL 配置错误，需要删除或重置配置，通常可以按照以下步骤操作：
一. 删除错误配置
1.连接到 ProxySQL 数据库：
使用 mysql 命令行工具或其他 MySQL 客户端连接到 ProxySQL 实例。默认情况下，ProxySQL 的管理界面监听在 3306 端口（也可能是其他端口，取决于你的配置）。
mysql -u admin -p -h 127.0.0.1 -P 6032
这里 6032 是 ProxySQL 默认的管理端口，admin 是默认的管理用户。
2.查看当前配置： 在管理控制台中，你可以使用以下命令查看当前配置：
select * from mysql_servers;
select * from mysql_users;
3.删除或修改错误配置： 假设你要删除某个错误的服务器配置：
delete from mysql_servers where hostname='错误的主机名';
对于用户配置，可以用类似的方式删除：
delete from mysql_users where username='错误的用户名';
4.应用更改： 删除或修改配置后，记得应用更改到 proxysql 配置：
load mysql servers to runtime;
save mysql servers to disk;
对于用户配置：
load mysql users to runtime;
save mysql users to disk;
二. 重置 proxysql 配置
方法一：可以执行初始化命令：
proxysql --initial
方法二：也可以删除所有配置并重新加载：
1.删除所有配置：
-- 删除所有的 mysql 服务器配置
delete from mysql_servers;
-- 删除所有的 mysql 用户配置
delete from mysql_users;
-- 删除所有的 mysql 规则配置
delete from mysql_query_rules;
-- 删除所有的 mysql 监控配置
delete from stats_mysql_status;
2.重新加载配置：
-- 将删除的配置应用到运行时
load mysql servers to runtime;
load mysql users to runtime;
load mysql query rules to runtime;
-- 将运行时配置保存到磁盘
save mysql servers to disk;
save mysql users to disk;
save mysql query rules to disk;
## 退出重启服务
sudo systemctl restart proxysql
扫一扫
2505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
