docker compose mysql主从复制及orchestrator高可用使用

 1.orchestrator 功能演示:

1.1 多级级联:

1.2 主从切换:

切换成功后,原来的主库是红色的,需要在主库的配置页面点击“start replication ”,重新连接上新的主库。

1.3 主从故障,从库自动切换新主库

2.mysql主从复制 搭建

参考地址:https://www.jb51.net/server/3105779cp.htmhttps://www.jb51.net/server/3105779cp.htm

主库my.cnf配置文件如下:

[mysqld]
log-bin=mysql-bin
server-id=1
report_host=172.22.0.103
log-slave-updates=1
# 启用GTID模式
gtid_mode=ON
# 确保一致性
enforce_gtid_consistency=ON
# 使用表格存储master信息
master_info_repository=TABLE
# 使用表格存储relay log信息
relay_log_info_repository=TABLE
# 设置binlog格式为ROW
binlog_format=ROW

其他数据库的my.cnf只需要修改server-id和report_host就行了。

docker compose 配置文件如下:

version: "3"

services:

  mysql-master:
    container_name: mysql-master
    hostname: mysql-master
    image: mysql:5.7.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: test123456
    ports:
      - "3307:3306"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.103
    volumes:
      - ./mysql1/conf/my.cnf:/etc/my.cnf
      - ./mysql1/data:/var/lib/mysql
  
  mysql-slave:
    container_name: mysql-slave
    hostname: mysql-slave
    image: mysql:5.7.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: test123456
    ports:
      - "3308:3306"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.104
    volumes:
      - ./mysql2/conf/my.cnf:/etc/my.cnf
      - ./mysql2/data:/var/lib/mysql
  mysql-slave2:
    container_name: mysql-slave2
    hostname: mysql-slave2
    image: mysql:5.7.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: test123456
    ports:
      - "3309:3306"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.105
    volumes:
      - ./mysql3/conf/my.cnf:/etc/my.cnf
      - ./mysql3/data:/var/lib/mysql
networks:
  mysql-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.22.0.0/16

主从复制使用gtid,

主库需要执行的sql:

create user 'repl_user'@'%' identified by 'test123456';
grant replication slave on *.* to 'repl_user'@'%';

从库需要执行的sql:

stop slave ;

CHANGE MASTER TO MASTER_HOST='192.168.0.62', MASTER_PORT=3306, MASTER_USER='repl_user',
MASTER_PASSWORD='test123456',master_auto_position=1;

start slave;

show slave status;

--主从切换时要用到,提前创建好用户

create user 'repl_user'@'%' identified by 'test123456';
grant replication slave on *.* to 'repl_user'@'%';

检查主从同步是否配置好,在主库执行以下sql:

show slave hosts;

主从复制正常时,显示如下:

3.orchestrator 搭建

参考地址:Orchestrator实现MySQL故障切换 - 墨天轮 (modb.pro)

orchestrator.conf.json配置文件如下:

{
  "Debug": true,
  "EnableSyslog": false,
  "ListenAddress": ":3000",
  "MySQLTopologyUser": "orc_client_user",
  "MySQLTopologyPassword": "test123456",
  "MySQLTopologyCredentialsConfigFile": "",
  "MySQLTopologySSLPrivateKeyFile": "",
  "MySQLTopologySSLCertFile": "",
  "MySQLTopologySSLCAFile": "",
  "MySQLTopologySSLSkipVerify": true,
  "MySQLTopologyUseMutualTLS": false,
  "MySQLOrchestratorHost": "172.22.0.102",
  "MySQLOrchestratorPort": 3306,
  "MySQLOrchestratorDatabase": "orcdb",
  "MySQLOrchestratorUser": "orc1",
  "MySQLOrchestratorPassword": "orc123456",
  "MySQLOrchestratorCredentialsConfigFile": "",
  "MySQLOrchestratorSSLPrivateKeyFile": "",
  "MySQLOrchestratorSSLCertFile": "",
  "MySQLOrchestratorSSLCAFile": "",
  "MySQLOrchestratorSSLSkipVerify": true,
  "MySQLOrchestratorUseMutualTLS": false,
  "MySQLConnectTimeoutSeconds": 1,
  "RaftEnabled": true,
  "RaftDataDir": "/var/lib/orchestrator",
  "RaftBind": "172.22.0.91",	
  "DefaultRaftPort": 10008,
  "RaftNodes": [
    "172.22.0.91",
    "172.22.0.92",
    "172.22.0.93"
  ], 
  "DefaultInstancePort": 3306,
  "DiscoverByShowSlaveHosts": false,
  "InstancePollSeconds": 5,
  "DiscoveryIgnoreReplicaHostnameFilters": [
    "a_host_i_want_to_ignore[.]example[.]com",
    ".*[.]ignore_all_hosts_from_this_domain[.]example[.]com",
    "a_host_with_extra_port_i_want_to_ignore[.]example[.]com:3307"
  ],
  "UnseenInstanceForgetHours": 240,
  "SnapshotTopologiesIntervalHours": 0,
  "InstanceBulkOperationsWaitTimeoutSeconds": 10,
  "HostnameResolveMethod": "default",
  "MySQLHostnameResolveMethod": "@@hostname",
  "SkipBinlogServerUnresolveCheck": true,
  "ExpiryHostnameResolvesMinutes": 60,
  "RejectHostnameResolvePattern": "",
  "ReasonableReplicationLagSeconds": 10,
  "ProblemIgnoreHostnameFilters": [],
  "VerifyReplicationFilters": false,
  "ReasonableMaintenanceReplicationLagSeconds": 20,
  "CandidateInstanceExpireMinutes": 60,
  "AuditLogFile": "",
  "AuditToSyslog": false,
  "RemoveTextFromHostnameDisplay": ".mydomain.com:3306",
  "ReadOnly": false,
  "AuthenticationMethod": "",
  "HTTPAuthUser": "",
  "HTTPAuthPassword": "",
  "AuthUserHeader": "",
  "PowerAuthUsers": [
    "*"
  ],
  "ClusterNameToAlias": {
    "127.0.0.1": "test suite"
  },
  "ReplicationLagQuery": "",
  "DetectClusterAliasQuery": "SELECT SUBSTRING_INDEX(@@hostname, '.', 1)",
  "DetectClusterDomainQuery": "",
  "DetectInstanceAliasQuery": "",
  "DetectPromotionRuleQuery": "",
  "DataCenterPattern": "[.]([^.]+)[.][^.]+[.]mydomain[.]com",
  "PhysicalEnvironmentPattern": "[.]([^.]+[.][^.]+)[.]mydomain[.]com",
  "PromotionIgnoreHostnameFilters": [],
  "DetectSemiSyncEnforcedQuery": "",
  "ServeAgentsHttp": false,
  "AgentsServerPort": ":3001",
  "AgentsUseSSL": false,
  "AgentsUseMutualTLS": false,
  "AgentSSLSkipVerify": false,
  "AgentSSLPrivateKeyFile": "",
  "AgentSSLCertFile": "",
  "AgentSSLCAFile": "",
  "AgentSSLValidOUs": [],
  "UseSSL": false,
  "UseMutualTLS": false,
  "SSLSkipVerify": false,
  "SSLPrivateKeyFile": "",
  "SSLCertFile": "",
  "SSLCAFile": "",
  "SSLValidOUs": [],
  "URLPrefix": "",
  "StatusEndpoint": "/api/status",
  "StatusSimpleHealth": true,
  "StatusOUVerify": false,
  "AgentPollMinutes": 60,
  "UnseenAgentForgetHours": 6,
  "StaleSeedFailMinutes": 60,
  "SeedAcceptableBytesDiff": 8192,
  "PseudoGTIDPattern": "",
  "PseudoGTIDPatternIsFixedSubstring": false,
  "PseudoGTIDMonotonicHint": "asc:",
  "DetectPseudoGTIDQuery": "",
  "BinlogEventsChunkSize": 10000,
  "SkipBinlogEventsContaining": [],
  "ReduceReplicationAnalysisCount": true,
  "FailureDetectionPeriodBlockMinutes": 60,
  "FailMasterPromotionOnLagMinutes": 0,
  "RecoveryPeriodBlockSeconds": 60,
  "RecoveryIgnoreHostnameFilters": [],
  "RecoverMasterClusterFilters": [
    "*"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "*"
  ],
  "OnFailureDetectionProcesses": [
    "echo 'Detected {failureType} on {failureCluster}. Affected replicas: {countSlaves}' >> /tmp/recovery.log"
  ],
  "PreGracefulTakeoverProcesses": [
    "echo 'Planned takeover about to take place on {failureCluster}. Master will switch to read_only' >> /tmp/recovery.log"
  ],
  "PreFailoverProcesses": [
    "echo 'Will recover from {failureType} on {failureCluster}' >> /tmp/recovery.log"
  ],
  "PostFailoverProcesses": [
    "echo '(for all types) Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
  "PostUnsuccessfulFailoverProcesses": [],
  "PostMasterFailoverProcesses": [
    "echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Promoted: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
  "PostIntermediateMasterFailoverProcesses": [
    "echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
  "PostGracefulTakeoverProcesses": [
    "echo 'Planned takeover complete' >> /tmp/recovery.log"
  ],
  "CoMasterRecoveryMustPromoteOtherCoMaster": true,
  "DetachLostSlavesAfterMasterFailover": true,
  "ApplyMySQLPromotionAfterMasterFailover": true,
  "PreventCrossDataCenterMasterFailover": false,
  "PreventCrossRegionMasterFailover": false,
  "MasterFailoverDetachReplicaMasterHost": false,
  "MasterFailoverLostInstancesDowntimeMinutes": 0,
  "PostponeReplicaRecoveryOnLagMinutes": 0,
  "OSCIgnoreHostnameFilters": [],
  "GraphiteAddr": "",
  "GraphitePath": "",
  "GraphiteConvertHostnameDotsToUnderscores": true,
  "ConsulAddress": "",
  "ConsulAclToken": "",
  "ConsulKVStoreProvider": "consul"
}

多个orchestrator.conf.json需要修改的地方如下:


 "MySQLTopologyUser": "orc_client_user",
 "MySQLTopologyPassword": "test123456",

  ......

  "MySQLOrchestratorHost": "172.22.0.102",
  "MySQLOrchestratorPort": 3306,
  "MySQLOrchestratorDatabase": "orcdb",
  "MySQLOrchestratorUser": "orc1",
  "MySQLOrchestratorPassword": "orc123456",

   ......

  "AuthenticationMethod": "basic",

  "HTTPAuthUser": "admin",

  "HTTPAuthPassword": "uq81sgca1da",  

  "RaftEnabled":true,

  "RaftDataDir":"/usr/local/orchestrator/raftdata",

  "RaftBind": "172.22.0.91",	
  "DefaultRaftPort": 10008,
  "RaftNodes": [
    "172.22.0.91",
    "172.22.0.92",
    "172.22.0.93"
  ], 

    ......

  "RecoveryPeriodBlockSeconds": 60,

  "RecoveryIgnoreHostnameFilters": [],

  "RecoverMasterClusterFilters": [

    "*"

  ],

  "RecoverIntermediateMasterClusterFilters": [

    "*"

  ],

  ......

MySQLTopologyUser是orchestrator监听mysql主从数据库时使用的用户

MySQLOrchestratorUser是orchestrator自身需要的数据库

在主库和从库执行以下sql,创建MySQLTopologyUser:

create user 'orc_client_user'@'%' identified by 'test123456'; 
GRANT ALL PRIVILEGES ON *.* TO 'orc_client_user'@'%';

在orchestrator自身的数据库(这个数据库是独立的,不在三个mysql主从库之中)执行以下sql,创建MySQLOrchestratorUser:

CREATE USER 'orc1'@'%' IDENTIFIED BY 'orc123456';
GRANT ALL PRIVILEGES ON *.* TO 'orc1'@'%';

 成功启动后,如下图:

4.完整的docker-compose.yml文件

version: "3"

services:

  # orchestrator 监控
  orchestrator-test1:
    container_name: orchestrator-test1
    image: openarkcode/orchestrator:latest
    restart: always
    ports:
      - "3000:3000"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.91
    volumes:
      - ./orchestrator.conf.json:/etc/orchestrator.conf.json

  orchestrator-test2:
    container_name: orchestrator-test2
    image: openarkcode/orchestrator:latest
    restart: always
    ports:
      - "3002:3000"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.92
    volumes:
      - ./orchestrator2.conf.json:/etc/orchestrator.conf.json

  orchestrator-test3:
    container_name: orchestrator-test3
    image: openarkcode/orchestrator:latest
    restart: always
    ports:
      - "3003:3000"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.93
    volumes:
      - ./orchestrator3.conf.json:/etc/orchestrator.conf.json

  # orc 使用的数据库
  orc-mysql:
    container_name: orc-mysql
    image: mysql:5.7.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: test123456
      MYSQL_PASSWORD: orc123456
      MYSQL_USER: orc1
      MYSQL_DATABASE: orcdb
    ports:
      - "3306:3306"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.102
    volumes:
      - ./mysql:/var/lib/mysql

  mysql-master:
    container_name: mysql-master
    hostname: mysql-master
    image: mysql:5.7.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: test123456
    ports:
      - "3307:3306"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.103
    volumes:
      - ./mysql1/conf/my.cnf:/etc/my.cnf
      - ./mysql1/data:/var/lib/mysql
  
  mysql-slave:
    container_name: mysql-slave
    hostname: mysql-slave
    image: mysql:5.7.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: test123456
    ports:
      - "3308:3306"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.104
    volumes:
      - ./mysql2/conf/my.cnf:/etc/my.cnf
      - ./mysql2/data:/var/lib/mysql
  mysql-slave2:
    container_name: mysql-slave2
    hostname: mysql-slave2
    image: mysql:5.7.30
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: test123456
    ports:
      - "3309:3306"
    networks:
      mysql-network:
        ipv4_address: 172.22.0.105
    volumes:
      - ./mysql3/conf/my.cnf:/etc/my.cnf
      - ./mysql3/data:/var/lib/mysql
networks:
  mysql-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.22.0.0/16

5.常见问题 

5.1 拖动报Relocating m03:3306 below m02:3306 turns to be too complex; please do it manually错误

解决办法:检查mysql是否启用gtid模式

5.2 mysql无法拖动到另一个mysql的下级,报ERROR m04:3306 cannot replicate from m03:3306. Reason: instance does not have log_slave_updates enabled: m03:3306错误

解决办法:mysql需要开启log-slave-updates

5.3 主从故障后,从库上显示errant gtid found错误

解决办法1:执行reset master

解决办法2:跳过从库多余的gtid,参考https://blog.csdn.net/weixin_48154829/article/details/124200051

5.4 orchestrator无法发现mysql主从集群,后台日志报hostname无法解析错误

解决办法:需要在安装orchestrator的机器上,修改hosts文件

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/585520.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

pyqt字体选择器

pyqt字体选择器 pyqt字体选择器效果代码 pyqt字体选择器 pyqt中QFontDialog 类是一个预定义的对话框,允许用户选择一个字体并设置其样式、大小等属性。 效果 代码 from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QLabel, QPushButton, QFontD…

信息收集。

信息收集 接着使用cs进行信息收集 发现域内管理员账号。 然后查看pc信息, 查看进程。 发现域为god.org 尝试定位域控。 提权 使用cs的功能进行权限提权 成功获取管理员权限。 hash抓取 接着抓hash 成功抓到管理员账号、密码。 接着进行横向传递 成功获取AD和…

从曝光到安装:App传参安装的关键步骤与数据指标

随着移动互联网的普及,手游市场日益繁荣,手游推广方式也日新月异。在这个竞争激烈的市场中,如何有效地推广手游,吸引更多的用户,成为了开发者和广告主关注的焦点。而Xinstall作为国内专业的App全渠道统计服务商&#x…

Android Widget开发代码示例详细说明

因为AppWidgetProvider扩展自BroadcastReceiver, 所以你不能保证回调函数完成调用后,AppWidgetProvider还在继续运行。 a. AppWidgetProvider 的实现 /*** Copyright(C):教育电子有限公司 * Project Name: NineSync* Filename: SynWidgetProvider.java * Author(S…

ORACLE 11G RAC 访问SQLSERVER

平时都是单机,RAC有点不一样,其实也一样。 目录 1.操作环境信息 2.安装GATEWAY 3.配置实例信息 4.配置监听 5.配置网络别名 6.创建到SQLSERVER的DBLINK 7.测试DBLINK有效性 1.操作环境信息 HIS PACS 数据库版本 ORACLE 11.2.0.4 RAC MS SQLSE…

C++多态(全)

多态 概念 调用函数的多种形态, 多态构成条件 1)父子类完成虚函数的重写(三同:函数名,参数,返回值相同) 2)父类的指针或者引用调用虚函数 虚函数 被virtual修饰的类成员函数 …

Llama images - 记录我看到的那些羊驼

来自 : DREAM: Distributed RAG Experimentation Framework

73、栈-柱状图中最大的矩形

思路: 矩形面积:宽度*高度 高度如何确定呢?就是在宽度中最矮的元素。如何确定宽度,就是要确定左右边界。 当我们在处理直方图最大矩形面积问题时,遇到一个比栈顶柱子矮的新柱子时开始计算面积的原因关键在于如何确定…

Hotcoin Research|玩赚WEB3:Seraph零成本赚取技巧

在《Seraph》这款游戏里,要提升自己的游戏技能和体验,了解如何免费赚取游戏货币灵魂晶石并挑战游戏主线是非常重要的。你可以通过卖东西、参加虚空异界地图和混沌秘境来在游戏里赚更多的钱,并更享受游戏的乐趣。最酷的是,得到的灵…

低功耗数字IC后端设计实现典型案例| UPF Flow如何避免工具乱用Always On Buffer?

下图所示为咱们社区低功耗四核A7 Top Hierarchical Flow后端训练营中的一个案例,设计中存在若干个Power Domain,其中Power Domain2(简称PD2)为default Top Domain,Power Domain1(简称PD1)为一个需要power off的domain&…

三星电子与蔡司达成新合作 | 百能云芯

近日,三星电子会长李在镕的欧洲之行备受瞩目,他特别造访了德国光学巨擘蔡司的总部,并与其高层进行了深入的会谈。 据韩国前锋报报道,三星电子在28日宣布,李在镕在26日与蔡司执行长兰普瑞特(Karl Lamprecht&…

企业智能名片小程序:AI智能跟进功能助力精准营销新篇章

在数字化浪潮的推动下,企业营销手段不断迭代升级。如今,一款集手机号授权自动获取、智能提醒、访客AI智能跟进及客户画像与行为记录于一体的企业智能名片小程序,正以其强大的AI智能跟进功能,助力企业开启精准营销的新篇章。 通过深…

SQL提升

1. SQL TOP 子句 TOP 子句用于规定要返回的记录的数目。 对于拥有数千条记录的大型表来说,TOP 子句是非常有用的。 **注释:**并非所有的数据库系统都支持 TOP 子句。 1.1 SQL TOP 语法 SQL Server 的语法: SELECT TOP number|percent c…

期权交割对股市是好是坏?2024期权交割日一览表

期权交割是指期权买方在期权合约到期日或之前行使期权,卖方履行义务,按照约定的价格和数量与期权卖方进行标的物的买卖或现金结算的过程。 交割方式 期权交割可以分为实物交割和现金交割,具体取决于合约规定。 实物交割 实物交割是指期权买…

【Threejs】获取相交网格相交线

BVH-INTER 1 导入threejs-mesh-bvh库 import * as THREE from "three"; import { SAH, acceleratedRaycast, computeBoundsTree, disposeBoundsTree } from "three-mesh-bvh"; import { GLTFLoader } from "three/examples/jsm/loaders/GLTFLoader.j…

photoshop如何使用PS中的吸管工具吸取软件外部的颜色?

第一步,打开PS,随意新建一个画布,或打开一个图片。 第二步,将PS窗口缩小,和外部窗口叠加放置,以露出后面的其它页面。 第三步,选中吸管工具,在PS窗口内单击一点吸取颜色,…

变电站自动化控制系统应用案例分析

变电站自动化控制系统介绍 变电站自动化控制系统用于大中型企业变电站项目,这类企业变压器多,日耗电量大。把多个变压器集中到一个电器平台上,集中管理分析,优化厂区用电管理,从而达到集中控制、集中分析、集中管理的…

【网络原理】以太网协议 | 以太网数据帧格式 | DNS域名解析系统

文章目录 一、以太网协议1.以太网数据帧格式MAC地址IP地址和MAC地址各自的用途 二、DNS 一、以太网协议 通过网线、光纤来通信,使用的就是以太网协议。 以太网协议,横跨了数据链路层和物理层。 1.以太网数据帧格式 由帧头载荷(IP数据报&…

简单谈谈URL过滤在网络安全中的作用

用户花在网络上的时间越来越多,浏览他们最喜欢的网站,点击电子邮件链接,或利用各种基于网络的 SaaS 应用程序供个人和企业使用。虽然这种不受约束的网络活动对提高企业生产力非常有用,但也会使组织面临一系列安全和业务风险&#…

13.4.1 实验1:配置VTP

1、使用目的 通过本实验可以掌握 VTP三种模式的区别。VTP工作原理。VTP的配置和调试方法 2、实验拓扑 配置VTP的实验拓扑如下图所示 3、实验拓扑 3.1、实验准备 通过命令 delete nash:van.dat和erasestartup-config把3台交换机的配置清除干净,重启交换机&#…