mysql集群查看主节点ip mysql查看mgr集群状态

转载

索姆拉 2024-06-08 16:14:09

文章标签 mysql集群查看主节点ip MGR集群添加低版本mysql mysql Database IP 文章分类 MySQL 数据库

MYSQL的 RAC来了! 除了无法共享内存外,基本上跟ORACLE RAC 类似!

MGR 配置非常简单,比PXC SO EASY!

MGR的限制

仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测；

必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set

COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景

目前一个MGR集群最多支持9个节点

不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚

二进制日志不支持binlog event checksum

mysql集群查看主节点ip mysql查看mgr集群状态_IP

这个MGR提交图,在事务提交前,也事务执行后各个节点都要检查一下. 写BINLOG的时候,其他节点同时写RELAY LOG. 其实这可以看作MASTER-SLAVE的完全同步版本.

事务所在的节点当做是MASTER,其他节点是SLAVE. 这样节点相互都是对方的SLAVE,也同时是自己事务的MASTER.

我们拿最开始的 5.7来说

Server version: 5.7.19-log MySQL Community Server (GPL)

我把上次配了主从库 3个通过VBOX复制了3份. 原来搭建的是主从,反向复制.

IP 地址如下 :

192.168.2.21

192.168.2.22

我们先干21

关掉MYSQL : service mysqld stop

编辑配置文件, 文件很长截取重点部分

#----binlog-----------#
log_bin=/u01/mysqllogs/mylogbin.log
log_slave_updates=ON
binlog_format=ROW  #必须是ROW
sync_binlog = 1
expire_logs_days = 15
max_binlog_cache_size = 128M
max_binlog_size = 500M
binlog_cache_size = 64k
binlog_checksum=NONE #这个也要
#----MASTER---SLAVE-----#
server_id=21
gtid_mode=ON
enforce_gtid_consistency=on
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64#以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#MGR组名字
loose-group_replication_start_on_boot=OFF
#启动时候不开启MGR
loose-group_replication_local_address= "192.168.2.21:33061"
#本节点IP
loose-group_replication_group_seeds= "192.168.2.21:33061,192.168.2.22:33061,192.168.2.23:33061"
#MGR组所有节点IP
loose-group_replication_bootstrap_group=OFF
report_host=192.168.2.21
report_port=3306
loose-group_replication_single_primary_mode = off  #关闭单主模式的参数
loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数
SERVICE MYSQLD START
....  很幸运一切正常
# 安装MGR插件
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
#这句只有server21,在第一次执行引导组的时候执行.
SET GLOBAL group_replication_bootstrap_group=ON;
#创建复制账号
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2%' IDENTIFIED BY 'repl';
#开启复制隧道
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
#启动组复制
START GROUP_REPLICATION;
#LOG没有毛病
mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | ad216d41-5c3a-11e8-8720-08002791d97c | 192.168.2.21 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
1 row in set (0.00 sec)
5.7.19 看不到节点角色
开启节点2
参数复制过去 并且修改
report_host=192.168.2.22
loose-group_replication_local_address= "192.168.2.22:33061"
server_id=22
SERVCIE MYSQLD STRAT
# 安装MGR插件
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2%' IDENTIFIED BY 'repl';
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
#加入组的问题 事务问题, 各个节点的事务状态不一致. 所有节点加入集群前最好不要修改任何数据.
set global group_replication_allow_local_disjoint_gtids_join=ON;
#启动组
START GROUP_REPLICATION;
mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 50c08571-5dff-11e8-ad74-080027cbedd8 | 192.168.2.22 |        3306 | ONLINE       |
| group_replication_applier | ad216d41-5c3a-11e8-8720-08002791d97c | 192.168.2.21 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
依样画葫芦 搞定节点3
# 安装MGR插件
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2%' IDENTIFIED BY 'repl';
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
set global group_replication_allow_local_disjoint_gtids_join=ON;
START GROUP_REPLICATION;
mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 50c08571-5dff-11e8-ad74-080027cbedd8 | 192.168.2.22 |        3306 | ONLINE       |
| group_replication_applier | ad216d41-5c3a-11e8-8720-08002791d97c | 192.168.2.21 |        3306 | ONLINE       |
| group_replication_applier | ae1c4db7-5f27-11e8-a331-0800275ee196 | 192.168.2.23 |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
节点3正在恢复中....
#测试
#23
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ilike_org          |
| ilike_worker       |
| mysql              |
| performance_schema |
| sharkdb            |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
#22
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ilike_org          |
| mysql              |
| performance_schema |
| sharkdb            |
| sys                |
+--------------------+
6 rows in set (0.03 sec)
#21
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ilike_org          |
| ilike_worker       |
| mysql              |
| performance_schema |
| sharkdb            |
| sys                |
+--------------------+
7 rows in set (0.04 sec)
#23 创建数据库
mysql> create database MGR_TEST;
#22
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MGR_TEST           |
| ilike_org          |
| mysql              |
| performance_schema |
| sharkdb            |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
#21
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MGR_TEST           |
| ilike_org          |
| ilike_worker       |
| mysql              |
| performance_schema |
| sharkdb            |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

#在节点23创建表

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 varchar(20));

在节点21插入数据

INSERT INTO t1 VALUES (1, 'lalala');

#3个节点都同步了数据

3个节点都是多主模式!

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。