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

目录

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.部署架构图

2.组件简介

MySQL：关系型数据库，按照数据结构来组织、存储和管理数据的仓库

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

3. 部署前提

具备可使用的CCE集群以及CCE节点

CCE购买参考：https://support.huaweicloud.com/usermanual-cce/cce_01_0028.html

4. 部署MySQL主从

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

MySQL主my.cnf文件

CCE控制台，进入配置中心下的配置项ConfigMap，点击创建配置项，如下图所示

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，点击创建配置项，如下图所示

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控制台，工作负载下的有状态工作负载界面，点击创建有状态工作负载

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

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

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

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

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

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

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

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

4.4 创建MySQL slave工作负载

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

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

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

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

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

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

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

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

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

创建负载

4.5 MySQL master配置

登录MySQL master数据库，查看配置项是否生效

show variables like '%server%'; show variables like '%log_bin%';

新建主库的复制账号并授权

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'@'%';

主数据库 master status

登录MySQL slave数据库，查看配置项是否生效

show variables like '%server%'; show variables like '%log_bin%';

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

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;

测试数据复制

登录MySQL master执行以下命令：

CREATE databases test; USE test; CREATE table test(name char(10),age int(3));

登录MySQL slave执行以下命令：

SHOW databases; USE test; SHOW tables;

5. 部署proxySQL

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

proxysql proxysql.cnf文件

CCE控制台，进入配置中心下的配置项ConfigMap，点击创建配置项，如下图所示

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控制台，工作负载下的有状态工作负载界面，点击创建有状态工作负载

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

创建proxysql负载-容器设置-step1-我的镜像：proxysql:V2.0

说明 镜像来源 https://registry.hub.docker.com/r/percona/proxysql 推送到华为云镜像仓库

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

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

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

创建proxysql负载-工作负载访问设置-服务：负载均衡，节点级别，容器端口3306、访问端口3306

说明：负载均衡配置完成后，服务器安全组需要开放 100.125.0.0/16 网段的安全组入方向规则

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

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;

5.3.2 MySQL master创建用于proxysql健康监测的用户(用户数据会同步到从库)

CREATE USER 'monitor'@'%' IDENTIFIED with mysql_native_password BY 'monitormima'; GRANT SELECT ON *.* TO 'monitor'@'%'; flush privilrges;

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');

字段说明：

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);

字段说明

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);

字段说明

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';

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

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);

proxysql 连接管理接口，查看路由记录；

stats_mysql_query_digest：通过ProxySQL路由出去的各类查询相关统计数据

6.2 验证proxysql 负载均衡

查看Proxysql的访问方式

访问方式为 124.71.75.74:3306

使用navicat 连接查看数据库

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

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

删除一个实例

验证

删除另一个实例

验证

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 网段的安全组

MySQL 云容器引擎 CCE

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。