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

MGR介绍

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

测试安装

1. 初始化所有的服务器

• 修改主机hosts

127.0.0.1localhostlocalhost.localdomainlocalhost4localhost4.localdomain4

::1localhostlocalhost.localdomainlocalhost6localhost6.localdomain6

192.168.20.192hdfs03

192.168.20.193hdfs04

192.168.20.194hdfs05

• 关闭防火墙

systemctldisablefirewalld

systemctlstopfirewalld

• 关闭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@hdfs05mysql]#cp/etc/my.cnf/data/mysql/

[root@hdfs05mysql]#vimmy.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='SETNAMESutf8'

character-set-server=utf8

max_connections=1000

max_connect_errors=6000

open_files_limit=65535

#GroupReplication

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@hdfs05mysql]#bin/mysqld--defaults-file=/data/mysql/my.cnf--basedir=/data/mysql--datadir=/data/mysql/data--initialize-insecure

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

mysql>setSQL_LOG_BIN=0;

mysql>createuserrepl@'%'identifiedby'Love88me=-.,';

mysql>grantreplicationslaveon*.*torepl@'%';

mysql>flushprivileges;

mysql>setSQL_LOG_BIN=1;

mysql>changemastertomaster_user='repl',master_password='Love88me=-.,'forchannel'group_replication_recovery';

• 在mysql服务器192.168.20.192上安装groupreplication插件

--安装插件

mysql>installPLUGINgroup_replicationSONAME'group_replication.so';

--查看groupreplication组件

mysql>showplugins;

• 启动服务器192.168.20.192上mysql的groupreplication

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

mysql>setglobalgroup_replication_bootstrap_group=ON;

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

mysql>startgroup_replication;

mysql>setglobalgroup_replication_bootstrap_group=OFF;

• 查看mgr的状态

mysql>select*fromperformance_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|

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

1rowinset(0.00sec)

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@hdfs04mysql]#vimmy.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='SETNAMESutf8'

character-set-server=utf8

max_connections=1000

max_connect_errors=6000

open_files_limit=65535

#GroupReplication

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@hdfs05mysql]#bin/mysqld--defaults-file=/data/mysql/my.cnf--basedir=/data/mysql--datadir=/data/mysql/data--initialize-insecure

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

mysql>setSQL_LOG_BIN=0;

mysql>createuserrepl@'%'identifiedby'Love88me=-.,';

mysql>grantreplicationslaveon*.*torepl@'%';

mysql>flushprivileges;

mysql>setSQL_LOG_BIN=1;

mysql>changemastertomaster_user='repl',master_password='Love88me=-.,'forchannel'group_replication_recovery';

• 在mysql服务器192.168.20.193上安装groupreplication插件

--安装插件

mysql>installPLUGINgroup_replicationSONAME'group_replication.so';

--查看groupreplication组件

mysql>showplugins;

• 启动服务器192.168.20.193上mysql的groupreplication

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

mysql>setglobalgroup_replication_allow_local_disjoint_gtids_join=ON;

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

mysql>startgroup_replication;

• 查看mgr的状态（192.168.20.192）

mysql>select*fromperformance_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|

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

2rowsinset(0.00sec)

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@hdfs05mysql]#vimmy.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='SETNAMESutf8'

character-set-server=utf8

max_connections=1000

max_connect_errors=6000

open_files_limit=65535

#GroupReplication

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@hdfs05mysql]#bin/mysqld--defaults-file=/data/mysql/my.cnf--basedir=/data/mysql--datadir=/data/mysql/data--initialize-insecure

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

mysql>setSQL_LOG_BIN=0;

mysql>createuserrepl@'%'identifiedby'Love88me=-.,';

mysql>grantreplicationslaveon*.*torepl@'%';

mysql>flushprivileges;

mysql>setSQL_LOG_BIN=1;

mysql>changemastertomaster_user='repl',master_password='Love88me=-.,'forchannel'group_replication_recovery';

• 安装groupreplication插件

mysql>installPLUGINgroup_replicationSONAME'group_replication.so';

QueryOK,0rowsaffected(0.00sec)

• 启动服务器192.168.20.194上mysql的groupreplication

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

mysql>setglobalgroup_replication_allow_local_disjoint_gtids_join=ON;

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

mysql>startgroup_replication;

• 查看mgr的状态（192.168.20.192）

mysql>select*fromperformance_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|

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

3rowsinset(0.01sec)

切换多主模式

查看主节点：

mysql>SELECT*FROMperformance_schema.replication_group_membersWHEREMEMBER_ID=(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAME='group_replication_primary_member');

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

mysql>stopgroup_replication;

QueryOK,0rowsaffected(9.08sec)

mysql>setglobalgroup_replication_single_primary_mode=OFF;

QueryOK,0rowsaffected(0.00sec)

mysql>setglobalgroup_replication_enforce_update_everywhere_checks=ON;

QueryOK,0rowsaffected(0.00sec)

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

mysql>SETGLOBALgroup_replication_bootstrap_group=ON;

QueryOK,0rowsaffected(0.00sec)

mysql>STARTGROUP_REPLICATION;

QueryOK,0rowsaffected(2.20sec)

mysql>SETGLOBALgroup_replication_bootstrap_group=OFF;

QueryOK,0rowsaffected(0.00sec)

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

mysql>STARTGROUP_REPLICATION;

QueryOK,0rowsaffected,1warning(5.89sec)

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

mysql>SELECT*FROMperformance_schema.replication_group_members;