首页 > 数据库 >mysql mgr demo

mysql mgr demo

时间：2023-01-16 10:46:23浏览次数：47  
标签：group mgr demo loose replication mysqld mysql 10.11 

docker compose 配置
services:
  db:
    container_name: mgr-db-0
    image: mysql:8.0
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: '123456'
    expose:
      # Opens port 3306 on the container
      - '3306'
      # Where our data will be persisted
    volumes:
      - /root/my.cnf:/etc/my.cnf
    hostname: s1
    networks:
      mgr-cc:
        ipv4_address: 10.11.0.10
    extra_hosts:
      - "s2:10.11.0.11"
      - "s3:10.11.0.12"
  db1:
    container_name: mgr-db-1
    image: mysql:8.0
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: '123456'
    expose:
      # Opens port 3306 on the container
      - '3306'
      # Where our data will be persisted
    volumes:
      - /root/my1.cnf:/etc/my.cnf
    hostname: s2
    networks:
      mgr-cc:
        ipv4_address: 10.11.0.11
    extra_hosts:
      - "s1:10.11.0.10"
      - "s3:10.11.0.12"
  db2:
    container_name: mgr-db-2
    image: mysql:8.0
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: '123456'
    expose:
      # Opens port 3306 on the container
      - '3306'
      # Where our data will be persisted
    volumes:
      - /root/my2.cnf:/etc/my.cnf
    hostname: s3
    networks:
      mgr-cc:
        ipv4_address: 10.11.0.12
    extra_hosts:
      - "s2:10.11.0.11"
      - "s1:10.11.0.10"
# Names our volume
volumes:
  my-db:
networks:
  mgr-cc:
    ipam:
      driver: default
      config:
        - subnet: "10.11.0.0/16"
#my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
#secure-file-priv=/var/lib/mysql-files
user=mysql
server_id=1013306
pid-file=/var/run/mysqld/mysqld.pid
gtid-mode=on
enforce-gtid-consistency=true
log_slave_updates=1
relay_log_info_repository=TABLE
master_info_repository=TABLE
relay_log_recovery=on
default-time_zone ="+8:00"
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name    ="a876d35e-9110-11e6-a365-842b2b5909d6"
loose-group_replication_start_on_boot    =off
loose-group_replication_local_address    ="10.11.0.10:34901"
loose-group_replication_group_seeds    ="10.11.0.10:34901,10.11.0.11:34902,10.11.0.12:34903"
loose-group_replication_bootstrap_group    =off
loose-group-replication-ip-whitelist="10.11.0.10,10.11.0.11,10.11.0.12"
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE


[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
# my1.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
server_id=1023306
pid-file=/var/run/mysqld/mysqld.pid
gtid-mode=on
enforce-gtid-consistency=true
log_slave_updates=1
relay_log_info_repository=TABLE
master_info_repository=TABLE
relay_log_recovery=on
default-time_zone ="+8:00"
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name    ="a876d35e-9110-11e6-a365-842b2b5909d6"
loose-group_replication_start_on_boot    =off
loose-group_replication_local_address    ="10.11.0.11:34902"
loose-group_replication_group_seeds    ="10.11.0.10:34901,10.11.0.11:34902,10.11.0.12:34903"
loose-group_replication_bootstrap_group    =off
loose-group-replication-ip-whitelist="10.11.0.10,10.11.0.11,10.11.0.12"
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE


[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
#my2.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
server_id=1033306
pid-file=/var/run/mysqld/mysqld.pid
gtid-mode=on
enforce-gtid-consistency=true
log_slave_updates=1
relay_log_info_repository=TABLE
master_info_repository=TABLE
relay_log_recovery=on
default-time_zone ="+8:00"
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name    ="a876d35e-9110-11e6-a365-842b2b5909d6"
loose-group_replication_start_on_boot    =off
loose-group_replication_local_address    ="10.11.0.12:34903"
loose-group_replication_group_seeds    ="10.11.0.10:34901,10.11.0.11:34902,10.11.0.12:34903"
loose-group_replication_bootstrap_group    =off
loose-group-replication-ip-whitelist="10.11.0.10,10.11.0.11,10.11.0.12"
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE


[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

操作

1、安装组复制插件

　　INSTALL PLUGIN group_replication SONAME 'group_replication.so';

　　show plugins;

2、在db-0上创建复制用户
　　SET SQL_LOG_BIN=0;
　　CREATE USER repl@'%' IDENTIFIED WITH mysql_native_password BY 'repl';

　　GRANT REPLICATION SLAVE ON *.* TO repl@'%';

　　GRANT BACKUP_ADMIN ON *.* TO repl@'%';

　　FLUSH PRIVILEGES;

　　SET SQL_LOG_BIN=1;
      启动并引导组复制 在单主模式中我们需要默认的选择一个节点作为主节点，并且使这个节点成为引导节点。

 　 在 mysql 中运行以下的命令

　　 SET GLOBAL group_replication_bootstrap_group=ON;

　　START GROUP_REPLICATION;

　　SET GLOBAL group_replication_bootstrap_group=OFF;

3、依次在另外两个节点
　　CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

　　START GROUP_REPLICATION;

 

!!!!遇到的问题

1)用户安全验证错误 方案一:使用复制用户请求服务器公钥：(原因是mysql8 密码验证增强) mysql -u repl -p123456 -hs1  --get-server-public-key    2) Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: e0c97c95-f571-11e9-933b-001b785baebb:1 > Group transactions: c2840554-f56b-11e9-8d9b-6cb3113192fc:1, 执行=> reset master;

标签：group,mgr,demo,loose,replication,mysqld,mysql,10.11
From： https://www.cnblogs.com/a-flydog/p/17054850.html

相关文章

  • Mysql：explain
    explainmysql提供了一种方式，目的是为了让我们知道sql的执行顺序、索引使用情况、执行效率的情况tabletable是展示执行计划过程中，会使用到的表。执行explain出现的每条......
  • 【学习日志】MySQL分表与索引的关系
    什么情况下需要分表呢？分表又能解决什么问题呢？一般情况下分表的直接原因是数据量太大了，比如一张表一共只有1w条数据，确实没必要分表。为什么数据量大了就需要分表呢？首先得看......
  • MySQL判断数据是不是存在的方法
        在mysql中，可以利用count()函数判断数据是否存在，该函数的作用就是用来统计表中记录数据，语法为“selectCOUNT(字段值)as字段名from表名where字段条件;”，......
  • mysql--时间
    mysql时间相关类型：date//年月日2023-1-15time//时分秒10:10:10datetime//年月日时分秒2023-1-1510:10:10timestamp//时间戳，插入，更新时自动写入当前时间注......
  • 解决mysql输入密码无法登录的问题
    ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:YES)当我输入root登录输入密码就会出现这种错误，不输入密码就可以登录，不输入密码里面......
  • 开启wamp依旧使用已下载的mysql
    1.打开D:\Environment\wamp64\bin\mysql\mysql8.0.31，修改my.ini添加本机mysql密码，并且将3个port=3306改为port=33162.打开D:\Environment\wamp64\apps\phpmyadmin5.2.0，......
  • Windows 安装 MySQL
    转：Win10安装MySQL教程下载安装包https://dev.mysql.com/downloads/installer/安装选Custom模式：从可安装的产品（Products）中选择MySQLServer：点击绿色按钮，将......
  • 基于ceph-deploy部署ceph 16.2.x 单节点mon和mgr环境
      ceph的部署方法和要求  部署方式  ceph-ansible：https://github.com/ceph/ceph-ansible #python  ceph-salt：https://github.com/ceph/ceph-salt #python ......
  • MySQL 5.X升级到8.X
    1.备份数据打开SQLyog，备份数据库为sql文件，以防升级过程中造成数据丢失。2.卸载MySQL5.X2.1停止服务按win键输入cmd，右键选择以管理员身份打开，输入netstopmysql停......
  • nodemon
         ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99