MGR介绍

MySQL 是目前最流行的开源关系型数据库,国内金融行业也开始全面使用,其中 MySQL 5.7.17 提出的 MGR(MySQL Group Replication)既可以很好地保证数据一致性又可以自动切换,具备故障检测功能、支持多节点写入,MGR 是一项被普遍看好的技术。

测试安装

序号

IP地址

主机名

Mysql版本

端口号

server_id

备注

1

192.168.20.192

hdfs03

mysql-5.7.31

3306

181

CentOS 7

2

192.168.20.193

hdfs04

mysql-5.7.31

3306

182

CentOS 7

3

192.168.20.194

hdfs05

mysql-5.7.31

3306

183

CentOS 7

  1. 初始化所有的服务器

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.20.192 hdfs03

192.168.20.193 hdfs04

192.168.20.194 hdfs05

systemctl disable firewalld

systemctl stop firewalld

要永久禁止 SELinux 自动启动,编辑/etc/selinux/config 文件,

(vi /etc/selinux/config)

设置 SELINUX=disabled

  1. 安装配置mysql数据库(192.168.20.192)

数据库安装文件:mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

数据库安装目录:/data/mysql

数据库数据文件存放目录:/data/mysql/data

[root@hdfs05 mysql]# cp /etc/my.cnf /data/mysql/

[root@hdfs05 mysql]# vim my.cnf

[mysqld]

datadir=/data/mysql/data

socket=/data/mysql/socket/mysql.sock

pid-file=/data/mysql/data/mysql.pid

port = 3306

user = mysql

symbolic-links=0

log_error = /data/mysql/logs/mysql-error.log

slow_query_log_file = /data/mysql/logs/mysql-slow.log

relay-log = /data/mysql/logs/relaylog

relay-log-index = /data/mysql/logs/relaylog.index

init-connect = 'SET NAMES utf8'

character-set-server = utf8

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

# 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 = binlog

binlog_format= ROW

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = 'hdfs05:33061'

loose-group_replication_group_seeds ='hdfs03:33061,hdfs04:33061,hdfs05:33061'

loose-group_replication_bootstrap_group = off

[root@hdfs05 mysql]# bin/mysqld --defaults-file=/data/mysql/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data --initialize-insecure

mysql> set SQL_LOG_BIN=0;

mysql> create user repl@'%' identified by 'Love88me=-.,';

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='Love88me=-.,' for channel 'group_replication_recovery';

-- 安装插件

mysql> install PLUGIN group_replication SONAME 'group_replication.so';

-- 查看group replication组件

mysql> show plugins;

-- 设置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;

mysql> select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| group_replication_applier | 1c053e34-9835-11eb-8780-000c29518e9d | hdfs03 | 3306 | ONLINE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

1 row in set (0.00 sec)

mysql高可用方案推荐(mysql目前稳定高可用方案)(1)

  1. 安装配置mysql数据库(192.168.20.193)

数据库安装文件:mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

数据库安装目录:/data/mysql

数据库数据文件存放目录:/data/mysql/data

[root@hdfs04 mysql]# vim my.cnf

[mysqld]

datadir=/data/mysql/data

socket=/data/mysql/socket/mysql.sock

pid-file=/data/mysql/data/mysql.pid

port = 3306

user = mysql

symbolic-links=0

log_error = /data/mysql/logs/mysql-error.log

slow_query_log_file = /data/mysql/logs/mysql-slow.log

relay-log = /data/mysql/logs/relaylog

relay-log-index = /data/mysql/logs/relaylog.index

init-connect = 'SET NAMES utf8'

character-set-server = utf8

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

# 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 = binlog

binlog_format= ROW

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = 'hdfs04:33061'

loose-group_replication_group_seeds ='hdfs03:33061,hdfs04:33061,hdfs05:33061'

loose-group_replication_bootstrap_group = off

[root@hdfs05 mysql]# bin/mysqld --defaults-file=/data/mysql/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data --initialize-insecure

mysql> set SQL_LOG_BIN=0;

mysql> create user repl@'%' identified by 'Love88me=-.,';

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='Love88me=-.,' for channel 'group_replication_recovery';

-- 安装插件

mysql> install PLUGIN group_replication SONAME 'group_replication.so';

-- 查看group replication组件

mysql> show plugins;

-- 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置。

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

-- 作为首个节点启动mgr集群

mysql> start group_replication;

mysql> select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| group_replication_applier | 18cfeb05-9836-11eb-ae70-000c29a0bffc | hdfs04 | 3306 | ONLINE |

| group_replication_applier | 1c053e34-9835-11eb-8780-000c29518e9d | hdfs03 | 3306 | ONLINE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

2 rows in set (0.00 sec)

mysql高可用方案推荐(mysql目前稳定高可用方案)(2)

  1. 安装配置mysql数据库(192.168.20.194)

数据库安装文件:mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

数据库安装目录:/data/mysql

数据库数据文件存放目录:/data/mysql/data

[root@hdfs05 mysql]# vim my.cnf

[mysqld]

datadir=/data/mysql/data

socket=/data/mysql/socket/mysql.sock

pid-file=/data/mysql/data/mysql.pid

port = 3306

user = mysql

symbolic-links=0

log_error = /data/mysql/logs/mysql-error.log

slow_query_log_file = /data/mysql/logs/mysql-slow.log

relay-log = /data/mysql/logs/relaylog

relay-log-index = /data/mysql/logs/relaylog.index

init-connect = 'SET NAMES utf8'

character-set-server = utf8

max_connections = 1000

max_connect_errors = 6000

open_files_limit = 65535

# 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 = binlog

binlog_format= ROW

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = 'hdfs05:33061'

loose-group_replication_group_seeds ='hdfs03:33061,hdfs04:33061,hdfs05:33061'

loose-group_replication_bootstrap_group = off

[root@hdfs05 mysql]# bin/mysqld --defaults-file=/data/mysql/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data --initialize-insecure

mysql> set SQL_LOG_BIN=0;

mysql> create user repl@'%' identified by 'Love88me=-.,';

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='Love88me=-.,' for channel 'group_replication_recovery';

mysql> install PLUGIN group_replication SONAME 'group_replication.so';

Query OK, 0 rows affected (0.00 sec)

-- 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置。

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

-- 作为首个节点启动mgr集群

mysql> start group_replication;

mysql> select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

| group_replication_applier | 18cfeb05-9836-11eb-ae70-000c29a0bffc | hdfs04 | 3306 | ONLINE |

| group_replication_applier | 1c053e34-9835-11eb-8780-000c29518e9d | hdfs03 | 3306 | ONLINE |

| group_replication_applier | ac3340d4-9836-11eb-b658-000c29b0170b | hdfs05 | 3306 | ONLINE |

+---------------------------+--------------------------------------+-------------+-------------+--------------+

3 rows in set (0.01 sec)

mysql高可用方案推荐(mysql目前稳定高可用方案)(3)

切换多主模式

查看主节点:

mysql> SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');

  1. 停止组复制(在所有MGR节点上执行):

mysql> stop group_replication;

Query OK, 0 rows affected (9.08 sec)

mysql> set global group_replication_single_primary_mode=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> set global group_replication_enforce_update_everywhere_checks=ON;

Query OK, 0 rows affected (0.00 sec)

  1. 选择原来MGR主节点执行

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (2.20 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

Query OK, 0 rows affected (0.00 sec)

  1. 然后在其他的MGR节点执行

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected, 1 warning (5.89 sec)

4) 查看MGR组信息 (在任意一个MGR节点上都可以查看)

mysql> SELECT * FROM performance_schema.replication_group_members;