MySQL Group Replication 安装配置
ta/binlog log-bin-index = /mysql/data/binlog.index relay-log = /mysql/data/relaylog relay-log-index = /mysql/data/relaylog.index binlog_format= ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="da71d2b9-6087-11e7-b3e1-005056b24460" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "node2.com:33061" loose-group_replication_group_seeds= "node2.com:33061,node3.com:33061,node4.com:33061" 3.1.6 测试服务器node2.com上的mysql mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test; Database changed mysql> create table t1(c1 int primary key, c2 text not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1, 'Luis'); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) mysql> show binlog events; +---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+ | binlog.000001 | 4 | Format_desc | 182 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 | | binlog.000001 | 123 | Previous_gtids | 182 | 150 | | | binlog.000001 | 150 | Gtid | 182 | 211 | SET @@SESSION.GTID_NEXT= 'da71d2b9-6087-11e7-b3e1-005056b24460:1' | | binlog.000001 | 211 | Query | 182 | 270 | BEGIN | | binlog.000001 | 270 | View_change | 182 | 369 | view_id=14992475935923744:1 | | binlog.000001 | 369 | Query | 182 | 434 | COMMIT | | binlog.000001 | 434 | Gtid | 182 | 495 | SET @@SESSION.GTID_NEXT= 'da71d2b9-6087-11e7-b3e1-005056b24460:2' | | binlog.000001 | 495 | Query | 182 | 554 | BEGIN | | binlog.000001 | 554 | View_change | 182 | 693 | view_id=14992475935923744:2 | | binlog.000001 | 693 | Query | 182 | 758 | COMMIT | | binlog.000001 | 758 | Gtid | 182 | 819 | SET @@SESSION.GTID_NEXT= 'da71d2b9-6087-11e7-b3e1-005056b24460:3' | | binlog.000001 | 819 | Query | 182 | 878 | BEGIN | | binlog.000001 | 878 | View_change | 182 | 1017 | view_id=14992475935923744:3 | | binlog.000001 | 1017 | Query | 182 | 1082 | COMMIT 3.2 复制组添加新实例node3.com 3.2.1 修改/etc/my.cnf 配置文件,方法和之前相同 # Group Replication server_id = 182 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log-bin = /mysql/data/binlog log-bin-index = /mysql/data/binlog.index relay-log = /mysql/data/relaylog relay-log-index = /mysql/data/relaylog.index binlog_format= ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="da71d2b9-6087-11e7-b3e1-005056b24460" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "node3.com:33061" loose-group_replication_group_seeds= "node2.com:33061,node3.com:33061,node4.com:33061" 3.2.5 在新回的实例上查看数据库发现test库和t1表已经同步 ta/binlog log-bin-index = /mysql/data/binlog.index relay-log = /mysql/data/relaylog relay-log-index = /mysql/data/relaylog.index binlog_format= ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="da71d2b9-6087-11e7-b3e1-005056b24460" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "node4.com:33061" loose-group_replication_group_seeds= "node2.com:33061,node3.com:33061,node4.com:33061"
发布日期:2021-06-28 20:28:03
浏览次数:2
分类:技术文章
本文共 12725 字,大约阅读时间需要 42 分钟。
1 介绍
MySQL Group Replication(简称MGR)是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MySQL组复制提供了高可用、高扩展、高可靠的MySQL集群服务。 高一致性,基于原生复制及paxos协议的组复制技术,并以插件的方式提供,提供一致数据安全保证; 高容错性,只要不是大多数节点坏掉就可以继续工作,有自动检测机制,当不同节点产生资源争用冲突时,不会出现错误,按照先到者优先原则进行处理,并且内置了自动化脑裂防护机制; 高扩展性,节点的新增和移除都是自动的,新节点加入后,会自动从其他节点上同步状态,直到新节点和其他节点保持一致,如果某节点被移除了,其他节点自动更新组信息,自动维护新的组信息; 高灵活性,有单主模式和多主模式,单主模式下,会自动选主,所有更新操作都在主上进行;多主模式下,所有server都可以同时处理更新操作。 MGR是MySQL数据库未来发展的一个重要方向。 2 环境准备 2.1 数据库服务器规划 1 172.19.6.45 node2.com mysql-5.7 3306 181 操作系统CentOS6.5 2 172.19.6.46 node3.com mysql-5.7 3306 182 操作系统CentOS6.5 3 172.19.6.47 node4.com mysql-5.7 3306 183 操作系统CentOS6.5 2.2 安装mysql5.7.17 安装mysql请参照 http://www.linuxidc.com/Linux/2017-06/144554.htm,在这里就不详细介绍。 2.3 设置hostname和ip映射 在三台数据库服务器上都设置: 这里写图片描述 [root@node2.com]$ vim /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 172.19.6.45 node2.com 172.19.6.46 node3.com 172.19.6.47 node4.com 3. 创建复制环境 3.1 服务器node2.com 3.1.1 配置/etc/my.cnf [mgradmin@]$ sudo vim /etc/my.cnf # Group Replication server_id = 181 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log-bin = /mysql/daloose-group_replication_ip_whitelist = '127.0.0.1/8,172.16.6.0/24'
group_replication_member_weight=100
loose-group_replication_bootstrap_group= off loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks= TRUE 3.1.2 服务器apollo.mgr.com上建立复制账号: mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec) 3.1.3 在mysql服务器node2.com上安装group replication插件 -- 安装插件 mysql> install PLUGIN group_replication SONAME 'group_replication.so'; -- 查看group replication组件 mysql> show plugins; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 3.1.4 启动服务器apollo.mgr.com上mysql的group replication -- 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置。 mysql> set global group_replication_bootstrap_group=ON; -- 作为首个节点启动mgr集群 mysql> start group_replication; mysql> set global group_replication_bootstrap_group=OFF; 3.1.5 查看mgr的状态 -- 查询表performance_schema.replication_group_members mysql> select * from performance_schema.replication_group_members;loose-group_replication_ip_whitelist = '127.0.0.1/8,172.16.6.0/24'
group_replication_member_weight=90
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE 3.2.2 用户凭证 mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec) -- 安装group replication插件 mysql> install PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.00 sec) 3.2.3 把实例添回到之前的复制组 mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; Query OK, 0 rows affected (0.00 sec) mysql> start group_replication; Query OK, 0 rows affected (6.65 sec) 3.2.4 在apollo.mgr.com上查看复制组状态 mysql> select * from performance_schema.replication_group_members;mysql> show databases;
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) mysql> select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) 3.3 以同样方法添加node3.com 详细步骤请参考3.2, 这里只给出配置文件/etc/my.cnf: # Group Replication server_id = 183 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log-bin = /mysql/daloose-group_replication_ip_whitelist = '127.0.0.1/8,172.19.6.0/24'
group_replication_member_weight=80
loose-group_replication_bootstrap_group= off loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks= TRUE 3.4 查看复制组状态 mysql> select * from performance_schema.replication_group_members;
两种工作模式在配置参数上的核心差别为: 1.loose-group_replication_single_primary_mode=FALSE 2.loose-group_replication_enforce_update_everywhere_checks=TRUE 3.(single-master mode 这俩个参数的值为TRUE和FALSE) 〇 要求和限制 仅可用于InnoDB存储引擎(需要事务的支持和行级锁) 表必须有主键(创建无主键的表不会报错,但在插入数据的时候会抛出:ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.) 必须启用GTID 必须开启二进制日志,并且其格式必须为ROW(binlog_format=row) 冲突DDl、DML只能在同一成员上执行成功 在多主结构中,不完全支持外键(单主结构中是没有问题的) 不支持serializable的事务隔离级别 只支持IPv4,并且需要低延迟,高带宽的网络环境 GR最大支持9个成员 复制信息元数据必须存在系统表(master-info-repository=TABLE、relay-log-info-repository=TABLE) 二进制日志checksums必须关闭(binlog-checksum=NONE) 不支持savepoint的使用
注意事项
mysqldump 命令mysqldump -uroot -p --set-gtid-purged=OFF disconf >/opt/soft/disconf.sql
select * from performance_schema.replication_group_member_stats\G; 查看状态 select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'; 查看主节点
转载地址:https://blog.csdn.net/yajie_12/article/details/78627629 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
感谢大佬
[***.8.128.20]2024年04月04日 00时34分49秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
三种方法拆分字符串
2019-04-29
贪心算法基本思想和典型例题
2019-04-29
坑爹的小学数学题
2019-04-29
快速找出一个数组中的两个数字,让这两个数字之和等于一个给定的值
2019-04-29
[经典排序算法][集锦]
2019-04-29
无处不在的二分查找
2019-04-29
Java集合框架List,Map,Set等全面介绍
2019-04-29
Java 泛型(二) 泛型之中的通配符(Wildcards)使用
2019-04-29
7-36 复数四则运算 (15 分)
2019-04-29
基于powershell的渗透测试工具nishang
2019-04-29
pycharm创建django项目linux部署
2019-04-29
利用Android Studio快速搭建App
2019-04-29
CompletableFuture的多线程和异步监听实现
2019-04-29
HashMap jdk1.7和1.8概述
2019-04-29
springboot多环境加载yml和logback配置
2019-04-29
几道简单的算法题(来自leetcode)
2019-04-29
推一波JAVA学习公众号
2019-04-29