当前位置：首页 > article >正文

proxysql读写分离的部署

article 2025/1/24 8:29:16

关闭防火墙和selinux

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

设置主机名称

hostnamectl set-hostname zhangyijia-host71.database.com && bash
hostnamectl set-hostname zhangyijia-host72.database.com && bash

两台主机安装mysql

mkdir packages
cd packages
rz mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
tar -xf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
yum remove mariadb-server-5.5.68-1.el7.x86_64 -y
rm -rf mysql-community-server-minimal-5.7.22-1.el7.x86_64.rpm
yum install ./*.rpm -y
systemctl start mysqld
cat /var/log/mysqld.log | grep pass
2024-12-14T12:58:27.564569Z 1 [Note] A temporary password is generated for root@localhost: qohD0Qa&dsST
mysqladmin -u root -p"qohD0Qa&dsST" password aaA...111
systemctl restart mysqld

主从配置

主节点

vim /etc/my.cnf

[client]
#客户端字符集
default-character-set=UTF8MB4
[mysqld]
#设置server_id，同一局域网中需要唯一
server_id=1
#服务端字符集
character-set-server=UTF8MB4
#开启二进制日志功能
log-bin=master-mysql-bin  
#指定不需要同步的数据库名称
binlog-ignore-db=mysql
#设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
#设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
#二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

从节点

vim /etc/my.cnf

[client]
#客户端字符集
default-character-set=UTF8MB4
[mysqld]
#设置server_id，同一局域网中需要唯一
server_id=2
#服务端字符集
character-set-server=UTF8MB4
#指定不需要同步的数据库名称
binlog-ignore-db=mysql
#开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=slave-1011-mysql-bin
#relay_log配置中继日志
relay_log=slave-1011-mysql-relay-bin
#log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
#slave设置为只读（具有super权限的用户除外）
read_only=1
#设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
#设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
#二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7
#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

重启两台mysql服务

service mysqld restart

主数据库

mysql -uroot -paaA...111

CREATE USER 'slaveusr'@'%' IDENTIFIED with mysql_native_password BY 'slavepwD...123';
GRANT REPLICATION SLAVE ON *.* TO 'slaveusr'@'%';

show master status;

在这里插入图片描述

从数据指向主数据库

mysql -uroot -paaA...111

CHANGE MASTER TO MASTER_HOST="192.168.3.71",MASTER_USER="slaveusr",MASTER_PASSWORD="slavepwD...123", MASTER_LOG_FILE="master-mysql-bin.000001",MASTER_LOG_POS=154;

查看从节点状态

start slave;
show slave status\G

在这里插入图片描述

主从复制验证

create database mytestdb;

从库查看

show databases;

使用proxysql 2.6软件，实现读写分离

cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/\$releasever
gpgcheck=0
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF

安装ProxySQL

yum -y install proxysql-2.6.6

在这里插入图片描述

启动ProxySQL

systemctl enable --now proxysql

查看占用的端口

ss -antl

在这里插入图片描述

连接proxysql

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --ssl-mode=DISABLED

在这里插入图片描述

添加 mysql 主机master 到 mysql_servers 表中

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) 
values(10,'192.168.3.71',3306,1,'Write Group');

添加 mysql 从机slave 到 mysql_servers 表中

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) 
values(20,'192.168.3.72',3306,1,'Read Group');

其中：hostgroup_id 10 表示写组，20表示读组

select * from mysql_servers \G

在这里插入图片描述

保存配置
修改后，需要加载到RUNTIME，并保存到disk

load mysql servers to runtime;
save mysql servers to disk;

在这里插入图片描述

在原有的MySQL主机和从机上创建proxysql的账号密码

mysql -uroot -paaA...111

CREATE USER 'proxysql'@'%' IDENTIFIED BY 'aaA...111';
alter user 'proxysql'@'%' identified with mysql_native_password by 'aaA...111';
grant all on *.* to 'proxysql'@'%';
flush privileges;

在这里插入图片描述

在proxysql主机的mysql_users表中添加刚才在写库上创建的账号
proxysql，proxysql客户端需要使用这个账号来访问数据库
default_hostgroup 默认组设置为写组，也就是10;
当读写分离的路由规则不符合时，会访问默认组的数据库;

登录admin

mysql -uadmin -padmin -h127.0.0.1 -P6032 --ssl-mode=DISABLED
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)
values('proxysql','aaA...111',10,1);
select * from mysql_users \G
# 保存配置
load mysql users to runtime;
save mysql users to disk;

在这里插入图片描述

添加健康检测的帐号

在mysql的读端添加属于proxysql的只读账号

CREATE USER 'monitor'@'%' IDENTIFIED BY 'aaA...111';
alter user 'monitor'@'%' identified with mysql_native_password by 'aaA...111';
GRANT SELECT ON *.* TO 'monitor'@'%';
flush privileges;

在这里插入图片描述

在proxysql主机端修改变量设置健康检测的账号

mysql -uadmin -padmin -h127.0.0.1 -P6032 --ssl-mode=DISABLED

set mysql-monitor_username='monitor';
set mysql-monitor_password='aaA...111';
load mysql variables to runtime;
save mysql variables to disk;

在这里插入图片描述

在proxysql主机端添加读写分离的路由规则需求：
1）将 select 查询语句全部路由至 hostgroup_id=20 的组(也就是读组)
2）但是 select * from tb for update 这样的语句是会修改数据的，所以需要单独定义，将它路由至 hostgroup_id=10 的组(也就是写组)
3）其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users 表中的 default_hostgroup)

登录admin

mysql -uadmin -padmin -h127.0.0.1 -P6032 --ssl-mode=DISABLED

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
values(1,1,'^SELECT.*FOR UPDATE$',10,1);
 
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
values(2,1,'^SELECT',20,1);
 
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
values(3,1,'^SHOW',20,1);

select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;   #查看规则

rule_id：规则的唯一标识符，这里是1。
active：规则是否激活，1表示激活。
match_digest：用于匹配查询的正则表达式。这里’^SELECT.*FOR UPDATE$'表示匹配所有以SELECT开头，后面跟着任意字符，并以FOR UPDATE结尾的查询。
destination_hostgroup：查询应该被路由到的主机组的ID，这里是10。
apply：是否应用此规则，1表示应用。
持久化，保存规则到disk

在这里插入图片描述

load mysql query rules to runtime;
load admin variables to runtime;
save mysql query rules to disk;
save admin variables to disk;

验证读写分离

验证读写分离是否成功
proxysql有个类似审计的功能，可以查看各类SQL的执行情况，其需要在proxysql管理端执行

mysql -uproxysql -paaA...111 -h127.0.0.1 -P6033 --ssl-mode=DISABLED

SHOW DATABASES;

在proxysql主机的proxysql账户下尝试对数据库进行操作

create database xiaowang;         #写操作
create database xiaozhou;          #写操作
select user,host from mysql.user;     #读操作

验证读写分离是否成功

proxysql有个类似审计的功能，可以查看各类SQL的执行情况，其需要在proxysql管理端执登录admin

mysql -uadmin -padmin -h127.0.0.1 -P6032 --ssl-mode=DISABLED
select * from stats_mysql_query_digest \G

在这里插入图片描述

注意事项

1、本环境系统使用的为CentOS7。

2、集群搭建要求的版本软件，否则无法正常使用。

3、MySQL同步故障：“ Slave_SQL_Running:No“ 两种解决办法。

解决办法一

Slave_SQL_Running: No
（1）程序可能在slave上进行了写操作

（2）也可能是slave机器重起后，[事务回滚]造成的

一般是事务回滚造成的：
解决办法：

mysql>stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>start slave ;

解决办法二

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值

进入master

mysql> show master status;然后到slave服务器上执行手动同步：

mysql>stop slave ;

mysql>change master to master_host='192.168.219.142',master_port=3306,

master_log_file='master-mysql-bin.000001',master_log_pos=536,

master_connect_retry=30,master_user='root',master_password='123456';

mysql>start slave ;

决办法二

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值

进入master

mysql> show master status;然后到slave服务器上执行手动同步：

mysql>stop slave ;

mysql>change master to master_host='192.168.219.142',master_port=3306,

master_log_file='master-mysql-bin.000001',master_log_pos=536,

master_connect_retry=30,master_user='root',master_password='123456';

mysql>start slave ;

查看全文

http://www.kler.cn/a/516058.html