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

免费好用的MySQL高可用方案

news 来源：原创 2024/5/19 1:00:38

环境说明

地址端口角色说明
127.0.0.13306master-
127.0.0.13307slave-
127.0.0.16032/6033ProxySQL版本2.0.1
127.0.0.110001replication-manager-

一、MySQL搭建

这里省略，推荐增强半同步
复制账号：repl/repl4slave

二、ProxySQL

2.1安装

[root@izbp12nspj47ypto9t6vyez src]# rpm -ivh proxysql-2.0.1-1-centos7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:proxysql-2.0.1-1                 warning: group proxysql does not exist - using root
################################# [100%]

2.2用户配置

在master上创建连接账户及监控账户

grant select,update,delete,insert on *.* to xucl@'%' identified by 'xuclxucl';
grant replication client on *.* to monitor@'%' identified by 'monitor';

2.3server配置

w_hg : 10
ro_hg : 11

监控账号
monitor monitor

配置读写组

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(10,11);

配置MySQL实例

insert into mysql_servers(hostgroup_id,hostname,port,max_connections,max_replication_lag) values(10,'127.0.0.1',3306,100,300),(11,'127.0.0.1',3307,100,300);

配置USERS

insert into mysql_users(username,password,default_hostgroup,default_schema,max_connections) values('xucl','xuclxucl',10,'xucl',90);

配置监控账号

set mysql-monitor_username = 'monitor';
set mysql-monitor_password = 'monitor';
save mysql users to disk;
load mysql users to runtime;
save mysql servers to disk;
load mysql servers to runtime;
save mysql variables to disk;
load mysql variables to runtime;

2.4配置读写分离

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',11,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

2.5简单测试读写分离

(xucl:db1@xucl:09:29:57)[(none)]> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.05 sec)
(xucl:db1@xucl:09:30:00)[(none)]> select @@port for update;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

三、replication-manager

3.1安装

rpm -ivh replication-manager-osc-2.0.0_rc2-1.x86_64.rpm

3.2配置文件

[root@izbp12nspj47ypto9t6vyez src]# cat /etc/replication-manager/config.toml
[db3306]
title = "db3306"
db-servers-hosts = "127.0.0.1:3306,127.0.0.1:3307"
db-servers-prefered-master = "127.0.0.1:3306"
db-servers-credential = "xucl:xuclxucl"
replication-credential = "repl:repl4slave"
failover-mode = "manual"
proxysql=true
proxysql-server="127.0.0.1"
proxysql-port=6033
proxysql-admin-port=6032
proxysql-writer-hostgroup=10
proxysql-reader-hostgroup=11
proxysql-user="admin"
proxysql-password="admin"
proxysql-bootstrap=false
[Default]
monitoring-datadir = "/data/replication-manager"
monitoring-sharedir = "/data/share_replication-manager"
log-level=7
log-file = "/var/log/replication-manager.log"
replication-multi-master = false
replication-multi-tier-slave = false
failover-readonly-state = true
http-server = true
http-bind-address = "0.0.0.0"
http-port = "10001"

3.3启动replication-manager

[root@izbp12nspj47ypto9t6vyez src]# service replication-manager restart
Restarting replication-manager (via systemctl):            [  OK  ]

日志

2019/02/22 09:34:38 [db3306] INFO  - Failover in interactive mode
2019/02/22 09:34:38 [db3306] INFO  - Loading 1 proxies
2019/02/22 09:34:38 [db3306] INFO  - Loading ProxySQL...
2019/02/22 09:34:38 [db3306] DEBUG - Monitoring server loop
2019/02/22 09:34:38 [db3306] DEBUG - Server [0]: URL: 127.0.0.1:3306  State: Suspect PrevState: Suspect
2019/02/22 09:34:38 [db3306] DEBUG - Server [1]: URL: 127.0.0.1:3307  State: Suspect PrevState: Suspect
2019/02/22 09:34:38 [db3306] DEBUG - State unconnected set by non-master rule on server 127.0.0.1:3307
2019/02/22 09:34:38 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] DEBUG - GetMasterFromReplication server  3307  lookup if server 127.0.0.1:3307 is the one : 3307
2019/02/22 09:34:38 [db3306] DEBUG - Server 127.0.0.1:3306 is configured as a slave
2019/02/22 09:34:38 [db3306] DEBUG - Privilege check on 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] DEBUG - Client connection found on server 127.0.0.1:3306 with IP 127.0.0.1 for host 127.0.0.1
2019/02/22 09:34:38 [db3306] DEBUG - Server 127.0.0.1:3307 was set master as last non slave
2019/02/22 09:34:38 [db3306] DEBUG - Privilege check on 127.0.0.1:3307
2019/02/22 09:34:38 [db3306] DEBUG - Client connection found on server 127.0.0.1:3307 with IP 127.0.0.1 for host 127.0.0.1
2019/02/22 09:34:38 [db3306] DEBUG - GetMasterFromReplication server  3307  lookup if server 127.0.0.1:3307 is the one : 3307
2019/02/22 09:34:38 [db3306] DEBUG - Checking if server 127.0.0.1 is a slave of server 127.0.0.1
2019/02/22 09:34:38 [db3306] DEBUG - GetMasterFromReplication server  3307  lookup if server 127.0.0.1:3307 is the one : 3307
2019/02/22 09:34:38 [db3306] INFO  - Init Proxy Type: proxysql Host: 127.0.0.1 Port: 6032
2019/02/22 09:34:38 [db3306] STATE - OPENED WARN0048 : No semisync settings on slave 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] STATE - OPENED WARN0058 : No GTID strict mode on slave 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] STATE - OPENED WARN0060 : No semisync settings on master 127.0.0.1:3307
2019/02/22 09:34:38 [db3306] STATE - OPENED WARN0070 : No GTID strict mode on master 127.0.0.1:3307
2019/02/22 09:34:38 [db3306] STATE - OPENED ERR00021 : All cluster db servers down
2019/02/22 09:34:38 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:38 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:39 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:39 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:39 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:39 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:40 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:40 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:40 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:40 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:40 [db3306] DEBUG - Monitoring server loop
2019/02/22 09:34:40 [db3306] DEBUG - Server [0]: URL: 127.0.0.1:3306  State:  Slave PrevState:  Slave
2019/02/22 09:34:40 [db3306] DEBUG - Server [1]: URL: 127.0.0.1:3307  State: Master PrevState: StandAlone
2019/02/22 09:34:40 [db3306] DEBUG - Master [ ]: URL: 127.0.0.1:3307  State: Master PrevState: StandAlone
2019/02/22 09:34:40 [db3306] DEBUG - Slave  [0]: URL: 127.0.0.1:3306  State:  Slave PrevState:  Slave
2019/02/22 09:34:40 [db3306] DEBUG - Server 127.0.0.1:3306 is configured as a slave
2019/02/22 09:34:40 [db3306] DEBUG - Privilege check on 127.0.0.1:3306
2019/02/22 09:34:40 [db3306] DEBUG - Client connection found on server 127.0.0.1:3306 with IP 127.0.0.1 for host 127.0.0.1
2019/02/22 09:34:40 [db3306] DEBUG - Server 127.0.0.1:3307 was set master as last non slave
2019/02/22 09:34:40 [db3306] DEBUG - Privilege check on 127.0.0.1:3307
2019/02/22 09:34:40 [db3306] DEBUG - Client connection found on server 127.0.0.1:3307 with IP 127.0.0.1 for host 127.0.0.1
2019/02/22 09:34:40 [db3306] DEBUG - GetMasterFromReplication server  3307  lookup if server 127.0.0.1:3307 is the one : 3307
2019/02/22 09:34:40 [db3306] DEBUG - Checking if server 127.0.0.1 is a slave of server 127.0.0.1
2019/02/22 09:34:40 [db3306] DEBUG - GetMasterFromReplication server  3307  lookup if server 127.0.0.1:3307 is the one : 3307
2019/02/22 09:34:40 [db3306] STATE - RESOLV ERR00021 : All cluster db servers down
2019/02/22 09:34:41 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:41 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:41 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:41 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:42 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:42 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:42 [db3306] DEBUG - Monitoring server loop
2019/02/22 09:34:42 [db3306] DEBUG - Server [0]: URL: 127.0.0.1:3306  State:  Slave PrevState:  Slave
2019/02/22 09:34:42 [db3306] DEBUG - Server [1]: URL: 127.0.0.1:3307  State: Master PrevState: Master
2019/02/22 09:34:42 [db3306] DEBUG - Master [ ]: URL: 127.0.0.1:3307  State: Master PrevState: Master
2019/02/22 09:34:42 [db3306] DEBUG - Slave  [0]: URL: 127.0.0.1:3306  State:  Slave PrevState:  Slave
2019/02/22 09:34:42 [db3306] DEBUG - Server 127.0.0.1:3306 is configured as a slave
2019/02/22 09:34:42 [db3306] DEBUG - Privilege check on 127.0.0.1:3306
2019/02/22 09:34:42 [db3306] DEBUG - Client connection found on server 127.0.0.1:3306 with IP 127.0.0.1 for host 127.0.0.1
2019/02/22 09:34:42 [db3306] DEBUG - Server 127.0.0.1:3307 was set master as last non slave
2019/02/22 09:34:42 [db3306] DEBUG - Privilege check on 127.0.0.1:3307
2019/02/22 09:34:42 [db3306] DEBUG - Client connection found on server 127.0.0.1:3307 with IP 127.0.0.1 for host 127.0.0.1
2019/02/22 09:34:42 [db3306] DEBUG - GetMasterFromReplication server  3307  lookup if server 127.0.0.1:3307 is the one : 3307
2019/02/22 09:34:42 [db3306] DEBUG - Checking if server 127.0.0.1 is a slave of server 127.0.0.1
2019/02/22 09:34:42 [db3306] DEBUG - GetMasterFromReplication server  3307  lookup if server 127.0.0.1:3307 is the one : 3307
2019/02/22 09:34:43 [db3306] DEBUG - Lookup server 127.0.0.1:3306 if maxscale binlog server: 127.0.0.1:3306
2019/02/22 09:34:43 [db3306] DEBUG - Lookup server 127.0.0.1:3307 if maxscale binlog server: 127.0.0.1:3306

3.4切换

手动点击switch over即可实现切换

切换日志

2019/02/22 09:38:22 [db3306] INFO  - Rest API receive Switchover request
2019/02/22 09:38:22 [db3306] INFO  - Signaling Switchover...
2019/02/22 09:38:22 [db3306] INFO  - --------------------------
2019/02/22 09:38:22 [db3306] INFO  - Starting master switchover
2019/02/22 09:38:22 [db3306] INFO  - --------------------------
2019/02/22 09:38:22 [db3306] INFO  - Checking long running updates on master 10
2019/02/22 09:38:22 [db3306] INFO  - Flushing tables on master 127.0.0.1:3307
2019/02/22 09:38:22 [db3306] INFO  - Electing a new master
2019/02/22 09:38:22 [db3306] INFO  - Slave 127.0.0.1:3306 has been elected as a new master
2019/02/22 09:38:22 [db3306] INFO  - Terminating all threads on 127.0.0.1:3307
2019/02/22 09:38:22 [db3306] INFO  - Rejecting updates on 127.0.0.1:3307 (old master)
2019/02/22 09:38:22 [db3306] INFO  - Waiting for candidate master to apply relay log
2019/02/22 09:38:22 [db3306] INFO  - Reading all relay logs on 127.0.0.1:3306
2019/02/22 09:38:22 [db3306] INFO  - Stopping slave thread on new master
2019/02/22 09:38:22 [db3306] INFO  - Failover Proxy Type: proxysql Host: 127.0.0.1 Port: 6032
2019/02/22 09:38:22 [db3306] INFO  - Resetting slave on new master and set read/write mode on
2019/02/22 09:38:22 [db3306] ERROR - Could not set new master as read-write
2019/02/22 09:38:22 [db3306] INFO  - Inject fake transaction on new master 127.0.0.1:3306
2019/02/22 09:38:22 [db3306] INFO  - Switching old master as a slave
2019/02/22 09:38:22 [db3306] INFO  - Doing MySQL GTID switch of the old master
2019/02/22 09:38:22 [db3306] INFO  - Switching other slaves to the new master
2019/02/22 09:38:22 [db3306] INFO  - Master switch on 127.0.0.1:3306 complete
2019/02/22 09:38:22 [db3306] STATE - OPENED ERR00041 : Skip slave in election 127.0.0.1:3307 has more than 30 seconds of replication delay (35484)
2019/02/22 09:38:22 [db3306] STATE - OPENED ERR00039 : Skip slave in election 127.0.0.1:3307 repl not electable
2019/02/22 09:38:22 [db3306] STATE - OPENED ERR00032 : No candidates found in slaves list
2019/02/22 09:38:34 [db3306] STATE - RESOLV ERR00041 : Skip slave in election 127.0.0.1:3307 has more than 30 seconds of replication delay (35481)
2019/02/22 09:38:34 [db3306] STATE - RESOLV ERR00039 : Skip slave in election 127.0.0.1:3307 repl not electable
2019/02/22 09:38:34 [db3306] STATE - RESOLV ERR00032 : No candidates found in slaves list

四、replication切换逻辑

在线切换逻辑

failover切换逻辑

总结

  • 总得来说这是一套简单又非常高效的MySQL高可用方案，并且是可上生产的方案，并且replication manager又自带web界面，操作起来非常简单

  • 这个方案前面有ProxySQL，可以实现读写分离，应用程序连接ProxySQL即可

  • replication manager负责数据库高可用切换，切换完成后，ProxySQL通过read only感知读写节点，对应用程序来说可能会有短暂的影响，确保程序重连机制

  • 看到replication manager的在线切换和failover切换逻辑，涉及到多个步骤的freeze，具体整个实现逻辑与过程将在下篇文章进行剖析

QQ群号：763628645

QQ群二维码如下， 添加请注明：姓名+地区+职位，否则不予通过

相关文章：

  • 数据说话：推荐MySQL类的一些图书
  • MySQL数据库升级的一些坑
  • 如何做一个聪明的老人，如何让自己更幸福？我有4个小建议
  • 技术分享 | MySQL binlog 日志解析
  • 工作中的半成品
  • 基于Maxwell的MySQL数据传输服务整体设计
  • 迁移到MySQL的语法转换工具初步设计
  • MySQL空间报警后的一揽子解决方案
  • 超12万人申请北京积分落户，顺带分析了一波近年的数据
  • MySQL中insert阻塞问题的分析
  • MySQL数据延迟跳动的问题分析
  • 彻底取代Redis+数据库架构，京东618稳了！
  • 一个扭蛋的故事
  • 做一次完美的数据迁移
  • 招贤纳士-第16期，来自北京和成都的职位
  • #Java异常处理
  • [deviceone开发]-do_Webview的基本示例
  • 【腾讯Bugly干货分享】从0到1打造直播 App
  • ➹使用webpack配置多页面应用(MPA)
  • Brief introduction of how to 'Call, Apply and Bind'
  • HTTP那些事
  • javascript从右向左截取指定位数字符的3种方法
  • Java知识点总结（JDBC-连接步骤及CRUD）
  • jQuery(一)
  • miniui datagrid 的客户端分页解决方案 - CS结合
  • Netty 框架总结「ChannelHandler 及 EventLoop」
  • React 快速上手 - 06 容器组件、展示组件、操作组件
  • 笨办法学C 练习34：动态数组
  • 程序员最讨厌的9句话，你可有补充？
  • 工作踩坑系列——https访问遇到“已阻止载入混合活动内容”
  • 名企6年Java程序员的工作总结，写给在迷茫中的你！
  • 区块链技术特点之去中心化特性
  • 数据科学 第 3 章 11 字符串处理
  • 腾讯优测优分享 | 你是否体验过Android手机插入耳机后仍外放的尴尬？
  • RDS-Mysql 物理备份恢复到本地数据库上
  • 阿里云IoT边缘计算助力企业零改造实现远程运维 ...
  • ​html.parser --- 简单的 HTML 和 XHTML 解析器​
  • ​TypeScript都不会用，也敢说会前端？
  • # Python csv、xlsx、json、二进制(MP3) 文件读写基本使用
  • #pragma once与条件编译
  • $GOPATH/go.mod exists but should not goland
  • （2021|NIPS，扩散，无条件分数估计，条件分数估计）无分类器引导扩散
  • （3）STL算法之搜索
  • (C#)Windows Shell 外壳编程系列4 - 上下文菜单(iContextMenu)（二）嵌入菜单和执行命令...
  • (C++20) consteval立即函数
  • （八）c52学习之旅-中断实验
  • （二）基于wpr_simulation 的Ros机器人运动控制，gazebo仿真
  • （附源码）php投票系统 毕业设计 121500
  • （附源码）ssm基于jsp的在线点餐系统 毕业设计 111016
  • （论文阅读22/100）Learning a Deep Compact Image Representation for Visual Tracking
  • （学习日记）2024.02.29：UCOSIII第二节
  • *** 2003
  • .net MySql
  • .NET（C#、VB）APP开发——Smobiler平台控件介绍：Bluetooth组件
  • .NET/C# 使用 ConditionalWeakTable 附加字段（CLR 版本的附加属性，也可用用来当作弱引用字典 WeakDictionary）