ITPub博客

首页 > 数据库 > MySQL > MySQL MGR 集群搭建(单主模式)

MySQL MGR 集群搭建(单主模式)

原创 MySQL 作者:梓沐 时间:2019-09-27 09:00:39 0 删除 编辑

1.背景
MySQL Group Replication(MGR)是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案,以插件形式提供,实现了分布式下数据的最终一致性,总结MGR特点如下:
* 高一致性:基于分布式paxos协议实现组复制,保证数据一致性;
* 高容错性:自动检测机制,只要不是大多数节点都宕机就可以继续工作,内置防脑裂保护机制;
* 高扩展性:节点的增加与移除会自动更新组成员信息,新节点加入后,自动从其他节点同步增量数据,直到与其他节点数据一致;
* 高灵活性:提供单主模式和多主模式,单主模式在主库宕机后能够自动选主,所有写入都在主节点进行,多主模式支持多节点写入。
2. 环境:
3台服务器搭建3节点MGR集群,MySQL版本5.7.27,操作系统版本CentOS 7.3。
* 192.168.8.71
* 192.168.8.72
* 192.168.8.73
3.安装步骤
3.1 安装MySQL5.7
MySQL官方版5.7.27,采用Linux Generic安装方式,直接下载压缩包,解压到目录/usr/local/mysql。
3.2 修改配置文件
编辑配置文件/etc/my.cnf,3个节点除了server_id、loose-group_replication_local_address、report_host 三个参数不一样外,其他保持一致。
[mysqld]
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.8.71:33061"
loose-group_replication_group_seeds= "192.168.8.71:33061,192.168.8.72:33061,192.168.8.73:33061"
loose-group_replication_bootstrap_group=OFF
report_host=192.168.8.71
report_port=3306
3.3 初始化数据库(所有节点执行)
3.4 启动数据库,安装MGR插件,设置复制账号(所有节点执行)
--安装MGR插件
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
--设置复制账号
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
3.5 启动MGR单主模式
# 启动MGR,在主库(192.168.8.71)上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
1 row in set (0.00 sec)
# 其他节点加入MGR,在从库(192.168.8.72,192.168.8.73)上执行
mysql> START GROUP_REPLICATION;
# 查看MGR组信息
mysql> SELECT channel_name,
    ->        member_id,
    ->        member_host,
    ->        member_port,
    ->        member_state,
    ->        IF(global_status.variable_name IS NOT NULL, 'PRIMARY', 'SECONDARY') AS member_role
    ->   FROM performance_schema.replication_group_members
    ->   LEFT JOIN performance_schema.global_status
    ->     ON global_status.variable_name = 'group_replication_primary_member'
    ->    AND global_status.variable_value = replication_group_members.member_id;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
| channel_name              | member_id                            | member_host  | member_port | member_state | member_role |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       | PRIMARY     |
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d402 | 192.168.8.72 |        3306 | ONLINE       | SECONDARY   |
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d403 | 192.168.8.73 |        3306 | ONLINE       | SECONDARY   |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
可以看到,3个节点状态为online,并且主节点为192.168.8.71,只有主节点可以写入,其他节点只读,MGR单主模式搭建成功
3.6 切换到多主模式
MGR切换模式需要重新启动组复制,因些需要在所有节点上先关闭组复制,设置 group_replication_single_primary_mode=OFF 等参数,再启动组复制。
# 停止组复制(所有节点执行):
mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
# 随便选择某个节点执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他节点执行
mysql> START GROUP_REPLICATION;
# 查看组信息,所有节点的 MEMBER_ROLE 都为 PRIMARY
mysql> SELECT channel_name,
    ->        member_id,
    ->        member_host,
    ->        member_port,
    ->        member_state,
    ->        IF(global_status.variable_name IS NOT NULL, 'PRIMARY', 'SECONDARY') AS member_role
    ->   FROM performance_schema.replication_group_members
    ->   LEFT JOIN performance_schema.global_status
    ->     ON global_status.variable_name = 'group_replication_primary_member'
    ->    AND global_status.variable_value = replication_group_members.member_id;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
| channel_name              | member_id                            | member_host  | member_port | member_state | member_role |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       | PRIMARY     |
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d402 | 192.168.8.72 |        3306 | ONLINE       | PRIMARY     |
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d403 | 192.168.8.73 |        3306 | ONLINE       | PRIMARY     |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)
可以看到所有节点状态都是online,角色都是PRIMARY,MGR多主模式搭建成功。
3.7 切回单主模式
# 所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
# 主节点(192.168.8.71)执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点(192.168.8.72、192.168.8.73)执行
START GROUP_REPLICATION;
# 查看MGR组信息
mysql> SELECT channel_name,
    ->        member_id,
    ->        member_host,
    ->        member_port,
    ->        member_state,
    ->        IF(global_status.variable_name IS NOT NULL, 'PRIMARY', 'SECONDARY') AS member_role
    ->   FROM performance_schema.replication_group_members
    ->   LEFT JOIN performance_schema.global_status
    ->     ON global_status.variable_name = 'group_replication_primary_member'
    ->    AND global_status.variable_value = replication_group_members.member_id;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
| channel_name              | member_id                            | member_host  | member_port | member_state | member_role |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d401 | 192.168.8.71 |        3306 | ONLINE       | PRIMARY     |
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d402 | 192.168.8.72 |        3306 | ONLINE       | SECONDARY   |
| group_replication_applier | 7777f2fe-bd6b-11e9-aad5-08002789d403 | 192.168.8.73 |        3306 | ONLINE       | SECONDARY   |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29812844/viewspace-2658453/,如需转载,请注明出处,否则将追究法律责任。

上一篇: MySQL8.0之XtraBackup
下一篇: 没有了~
请登录后发表评论 登录
全部评论
擅长PLS/QL开发,SQL调优和改写,数据库设计

注册时间:2014-08-18

  • 博文量
    161
  • 访问量
    1087713