mysql高可用方案推荐（mysql目前稳定高可用方案）

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. 初始化所有的服务器

• 修改主机hosts

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

要永久禁止 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

• 编辑mysql配置文件my.cnf

[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

• 初始化mysql数据库

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

• 服务器192.168.20.192上建立复制账号：

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服务器192.168.20.192上安装group replication插件

-- 安装插件

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

-- 查看group replication组件

mysql> show plugins;

• 启动服务器192.168.20.192上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;

• 查看mgr的状态

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)

1. 安装配置mysql数据库（192.168.20.193）

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

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

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

• 编辑mysql配置文件my.cnf

[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

• 初始化mysql数据库

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

• 服务器192.168.20.193上建立复制账号：

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服务器192.168.20.193上安装group replication插件

-- 安装插件

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

-- 查看group replication组件

mysql> show plugins;

• 启动服务器192.168.20.193上mysql的group replication

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

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

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

mysql> start group_replication;

• 查看mgr的状态（192.168.20.192）

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)

1. 安装配置mysql数据库（192.168.20.194）

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

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

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

• 编辑mysql配置文件my.cnf

[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

• 初始化mysql数据库

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

• 服务器192.168.20.194上建立复制账号：

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';

• 安装group replication插件

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

Query OK, 0 rows affected (0.00 sec)

• 启动服务器192.168.20.194上mysql的group replication

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

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

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

mysql> start group_replication;

• 查看mgr的状态（192.168.20.192）

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