【技术方案分享】CCE proxysql+mysql 实现MySQL主从读写分离

举报
HuaweiCloudDeveloper 发表于 2022/01/18 10:03:04 2022/01/18
【摘要】 本文基于华为云CCE部署 Proxysql + Mysql，实现数据库主从读写分离，部署过程涉及到数据库的持久化存储、数据库配置文件的Configmap挂载、CCE环境变量设置、CCE的服务（ClusterIP，负载均衡）设置。详细内容可阅读文章了解

目录

1.部署架构图
2.组件简介
3.部署前提
4.部署MySQL主从

4.1 通过配置项configmap创建MySQL master的配置文件
4.2 通过配置项configmap创建MySQL slave的配置文件
4.3 创建MySQL master工作负载
4.4 创建MySQL slave工作负载
4.5 MySQL master配置

5.部署proxySQL

5.1 通过配置项configmap创建proxysql的配置文件
5.2 创建proxySQL 工作负载
5.3 proxySQL配置数据库读写分离
5.4 proxySQL另一实例配置

6.验证

6.1 验证MySQL读写分离
6.2 验证proxysql 负载均衡

7.FAQ

7.1 通过负载均衡数据库后，SQL语句执行报错
7.2 数据库连接报 1251 错误
7.3 ELB 负载均衡后连接失败

1.部署架构图

部署架构图.png

2.组件简介

MySQL：关系型数据库，按照数据结构来组织、存储和管理数据的仓库
proxySQL：proxySQL是灵活强大的MySQL代理层, 是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。默认管理连接端口6032，数据库连接端口 3306。

3. 部署前提

4. 部署MySQL主从

4.1 通过配置项configmap创建MySQL master的配置文件

  • MySQL主my.cnf文件
  • CCE控制台，进入配置中心下的配置项ConfigMap，点击创建配置项，如下图所示

image.png

  • my.cnf mysql-master配置内容如下：
[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
secure-file-priv= NULL

server-id=101  ##用于高可用区分服务的ID，MySQL主从ID不一致即可
log-bin=master-binlog

4.2 通过配置项configmap创建MySQL slave的配置文件

  • MySQL主my.cnf文件
  • CCE控制台，进入配置中心下的配置项ConfigMap，点击创建配置项，如下图所示

image.png

  • my.cnf mysql-slave配置内容如下：
[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
secure-file-priv= NULL

server-id=102  ##用于高可用区分服务的ID，MySQL主从ID不一致即可
log-bin=master-binlog
  • 说明：配置文件内的其他参数可自行设置，当前仅为演示，未进行其他配置。

4.3 创建MySQL master工作负载

  • 进入华为云CCE控制台，工作负载下的有状态工作负载界面，点击创建有状态工作负载

image.png

  • 创建MySQL master负载-工作负载基本信息

image.png

  • 创建MySQL master负载-容器设置-step1-开源镜像中心：MySQL 8.0

image.png

  • 创建MySQL master负载-容器设置-step2-环境变量：设置MySQL密码

image.png

  • 创建MySQL master负载-容器设置-step3-数据存储：mysql-master-cnf挂载

image.png

  • 创建MySQL master负载-容器设置-step4-数据存储：挂载SFS存储MySQL数据

image.png

  • 创建MySQL master负载-工作负载访问设置-实例间发现服务：访问端口3306

  • 创建MySQL master负载-工作负载访问设置-服务：节点访问，节点模式，访问端口3306，访问端口自动生成

image.png

  • 创建MySQL master负载-高级设置保持默认即可，点击创建

image.png

4.4 创建MySQL slave工作负载

  • 进入华为云CCE控制台，工作负载下的有状态工作负载界面，点击创建有状态工作负载

image.png

  • 创建MySQL slave负载-工作负载基本信息

image.png

  • 创建MySQL slave负载-容器设置-step1-开源镜像中心：MySQL 8.0

image.png

  • 创建MySQL slave负载-容器设置-step2-环境变量：设置MySQL密码

image.png

  • 创建MySQL slave负载-容器设置-step3-数据存储：mysql-slave-cnf挂载

image.png

  • 创建MySQL slave负载-容器设置-step4-数据存储：挂载SFS存储MySQL数据

image.png

  • 创建MySQL slave负载-工作负载访问设置-实例间发现服务：访问端口3306

  • 创建MySQL slave负载-工作负载访问设置-服务：节点访问，节点模式，访问端口3306，访问端口自动生成

image.png

  • 创建MySQL slave负载-高级设置:设置与MySQL master负载的反亲和性

image.png

  • 创建负载

image.png

4.5 MySQL master配置

  • 登录MySQL master数据库，查看配置项是否生效
show variables like '%server%';
show variables like '%log_bin%';

image.png

  • 新建主库的复制账号并授权
CREATE USER 'backup'@'%' IDENTIFIED BY 'backupmima'；
## 8.0数据库请使用 CREATE USER 'backup'@'%' IDENTIFIED WITH mysql_native_password BY 'backupmima'；
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%';
SHOW GRANTS FOR 'backup'@'%';

image.png

  • 主数据库 master status

image.png

  • 登录MySQL slave数据库，查看配置项是否生效
show variables like '%server%';
show variables like '%log_bin%';

image.png

  • MySQL slave增加MySQL master连接配置信息。
CHANGE master to master_host='mysql-master.default.svc.cluster.local',
master_user='backup',
master_password='backupmima',
master_port=3306,
master_log_file='master-binlog.0000003',
master_log_pos=1186,
master_connect_retry=30;

说明
master_host='mysql-master.default.svc.cluster.local' 为MySQL master服务集群内网IP

image.png

master_user='backup',master_password='backupmima' 为复制数据库的账号和密码
master_log_file='master-binlog.0000003', 为MySQL master的binlog文件
master_log_pos=1186, 为MySQL master的Posistion值
master_connect_retry=30 主节点宕机，从服务器的尝试连接时间

  • MySQL slave开启设置复制
start slave;
show slave status \G;

image.png

  • 测试数据复制
    登录MySQL master执行以下命令：
CREATE databases test;
USE test;
CREATE table test(name char(10),age int(3));

image.png

登录MySQL slave执行以下命令：

SHOW databases;
USE test;
SHOW tables;

image.png

5. 部署proxySQL

5.1 通过配置项configmap创建proxysql的配置文件

  • proxysql proxysql.cnf文件
  • CCE控制台，进入配置中心下的配置项ConfigMap，点击创建配置项，如下图所示

image.png

  • proxysql.cnf 配置内容如下：
datadir="/var/lib/proxysql"

admin_variables =
{
        admin_credentials="admin:admin"
        mysql_ifaces="0.0.0.0:6032"
        refresh_interval=2000

        cluster_username="admin"
        cluster_password="admin"
        cluster_check_interval_ms=200
        cluster_check_status_frequency=100
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=1
        cluster_mysql_servers_diffs_before_sync=1
        cluster_mysql_users_diffs_before_sync=1
        cluster_proxysql_servers_diffs_before_sync=1
}

mysql_variables=
{
        monitor_password="monitor"
        monitor_galera_healthcheck_interval=1000
        threads=2
        max_connections=2048
        default_query_delay=0
        default_query_timeout=10000
        poll_timeout=2000
        interfaces="0.0.0.0:3306;0.0.0.0:33062"
        default_schema="information_schema"
        stacksize=1048576
        connect_timeout_server=10000
        monitor_history=60000
        monitor_connect_interval=20000
        monitor_ping_interval=10000
        ping_timeout_server=200
        commands_stats=true
        sessions_sort=true
        have_ssl=false
        ssl_p2s_ca=""
        ssl_p2s_cert=""
        ssl_p2s_key=""
        ssl_p2s_cipher="ECDHE-RSA-AES128-GCM-SHA256"
}

5.2 创建proxySQL 工作负载

  • 进入华为云CCE控制台，工作负载下的有状态工作负载界面，点击创建有状态工作负载

image.png

  • 创建proxysql负载-工作负载基本信息

image.png

image.png

  • 创建proxysql负载-容器设置-step2-数据存储：挂载Configmap 配置文件

image.png

  • 创建proxysql负载-容器设置-step2-数据存储：挂载SFS存储proxysql数据

image.png

  • 创建proxysql负载-工作负载访问设置-实例间发现服务：访问端口3306

  • 创建proxysql负载-工作负载访问设置-服务：负载均衡，节点级别，容器端口3306、访问端口3306
    说明：负载均衡配置完成后，服务器安全组需要开放 100.125.0.0/16 网段的安全组入方向规则

image.png

  • 创建proxysql负载-高级设置保持默认即可，点击创建

image.png

5.3 proxySQL配置数据库读写分离

  • 5.3.1 MySQL master创建用于proxysql连接的用户(用户数据会同步到从库)
CREATE USER 'proxysql'@'%' IDENTIFIED with mysql_native_password BY 'proxysqlmima';
GRANT ALL ON  *.* TO 'proxysql'@'%';
flush privilrges;

image.png

  • 5.3.2 MySQL master创建用于proxysql健康监测的用户(用户数据会同步到从库)
CREATE USER 'monitor'@'%' IDENTIFIED with mysql_native_password BY 'monitormima';
GRANT SELECT ON  *.* TO 'monitor'@'%';
flush privilrges;

image.png

  • 5.3.3 登录proxysql 配置mysql_server 信息
    说明：分别插入mysql的节点信息，10表示master（写），20表示slave（读）
mysql -h 127.0.0.1 -P 6032 -u admin -p
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'mysql-master.default.svc.cluster.local',3306,1,'master');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'mysql-slave.default.svc.cluster.local',3306,1,'slave');

image.png

字段说明：
hostgroup_id：一个组ID，组内可包含多个MySQL地址
hostname：MySQL访问地址
port：访问端口
weight：访问权重
comment：文本，用于备注节点信息

生效配置并把配置保存到磁盘

load mysql servers to runtime;
save mysql servers to disk;
  • 5.3.4 登录proxysql 配置mysql_users 信息
    说明：proxysql 通过6032链接管理接口，默认账号密码为 admin/admin
mysql -h 127.0.0.1 -P 6032 -u admin -p
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','proxysqlmima',10,1);

image.png

字段说明
sername、password：proxysql连接MySQL的用户和密码
default_hostgroup：没有匹配到规则的SQL直接访问这个ID内的数据库
transaction_persistent：一个事务内的多条 SQL，只会路由到一个主机组中

生效配置并把配置保存到磁盘

load mysql users to runtime;
save mysql users to disk;
  • 5.3.5 登录proxysql 配置MySQL路由规则
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);

image.pngimage.png

字段说明
rule_id：规则ID
active：设置为1时，为启用规则
match_digest：SQL匹配的规则
destination_hostgroup：将匹配的规则路由到这个主机组
apply：设置为1时，在匹配和处理此规则后，将不再评估进一步的查询

load mysql query rules to runtime;
save mysql query rules to disk;
  • 5.3.6 配置数据库健康监测账号
set mysql-monitor_username='monitor';
set mysql-monitor_password='monitormima';

image.png

生效配置并把配置保存到磁盘.

load mysql variables to runtime;
save mysql variables to disk;

5.4 proxySQL另一实例配置

按照同样的方法进行另一实例proxysql的配置
说明：也可通过proxySQL集群的方式进行部署。实现各个proxysql之间的数据同步

6. 验证

6.1 验证MySQL读写分离

  • proxysql连接数据并执行以下命令
mysql -h 127.0.0.1 -P 3306 -u proxysql -p   ##使用proxysql 连接数据库
show databases;
use test;
show tables;
DESC test;
insert into test(name,age) values('caichunfu',10);

image.png

  • proxysql 连接管理接口，查看路由记录；
    stats_mysql_query_digest：通过ProxySQL路由出去的各类查询相关统计数据

image.png

image.png

6.2 验证proxysql 负载均衡

  • 查看Proxysql的访问方式

image.png

访问方式为 124.71.75.74:3306

  • 使用navicat 连接查看数据库

image.png

  • 验证负载均衡能力-step1 连接进入数据

image.png

  • 验证负载均衡能力-step2 轮流删除实例，验证Proxysql负载均衡能力

删除一个实例

image.png
验证

image.png

删除另一个实例

image.png

验证

image.png

7 FAQ

7.1 通过负载均衡数据库后，SQL语句执行报错。

[Error] 9006 - ProxySQL Error: connection is locked to ......
解决办法：登录proxysql管理端，执行以下命令

set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;

7.2 数据库连接报 1251 错误

[Error] 1251 Client does not....
解决办法：MySQL8.0的密码加密规则修改为mysql_native_password

ALTER USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password BY 'proxysqlmima';               #修改加密规则
ALTER USER 'proxysql'@'%' IDENTIFIED BY 'proxysqlmima' PASSWORD EXPIRE NEVER;                   #更新一下用户的密码
FLUSH PRIVILEGES;

7.3 ELB 负载均衡后连接失败

解决办法：安全组入方向放通 100.125.0.0/16 网段的安全组

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 云容器引擎 CCE
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入