部署MySQL5.7 MGR集群

草莓草莓草莓

于 2024-08-05 02:18:00 发布

阅读量10

点赞数

环境配置：

主机名	IP地址	版本	系统
mysql-001	192.168.10.200	mysql-5.7.44	centos7
mysql-001	192.168.10.200	mysql-5.7.44	centos7
mysql-001	192.168.10.200	mysql-5.7.44	centos7

注：MGR是在MySQL5.7.17以后的版本才支持的，如果是yum安装的应该没有问题

默认应该大于5.7.17，如果其他安装方式推荐选择稳定且较新的版本。

三台主机安装MySQL：

# 因为我三台主机都是刚安装系统的虚拟机所以需要重新配置国内yum源
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum clean all
yum makecache
# 安装vim以及ntp同步系统时间
yum install -y vim ntp wget
#下载mysql安装包
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
# 安装
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server
yum -y remove mysql57-community-release-el7-10.noarch

#关闭防火墙及selinux
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

注：上述mysql就安装好了，在启动之前需要做时间同步，确保系统时间没有问题，三台机器。

配置时间同步：

# 时间同步
*/5 * * * * /usr/sbin/ntpdate -u ntp.aliyun.com > /dev/null 2>&1; /sbin/hwclock -w

注：这个放在定时任务中的，这个定时任务根据自己需求设置。

修改配置：

mysql-001:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Disabling symbolic-links is recommended to prevent assorted security risks



user=mysql
#skip-grant-tables 


datadir=/data/mysql

socket=/tmp/mysql.sock

log-error=/data/mysql/mysql.err

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

character-set-server=utf8

innodb_rollback_on_timeout = ON

collation-server=utf8_general_ci

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

max_connections=10000

sync_binlog=1

binlog_format=row

########basic settings########

server-id =138

character_set_server=utf8

max_allowed_packet = 16M

lower_case_table_names=1

slow_query_log=1

slow_query_log_file=/data/mysql/slow.log

########replication settings########

#####replication 复制配置###############

log-bin = /data/mysql/mysql-bin

max_binlog_size=500M

binlog_format = row

sync_binlog=1

expire_logs_days=15

###group replication###########

gtid_mode=on

enforce_gtid_consistency= ON

master_info_repository = TABLE

relay_log_info_repository = TABLE

binlog_checksum = NONE

log_slave_updates = ON

#log_slave_updates是将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中。

transaction_write_set_extraction = XXHASH64

##server必须为每个事物收集写集合，使用XXHASH64哈希算法将其编码为散列

loose-group_replication_group_name ='51837954-2d8a-11ed-bc2d-000c29f511b3'

#组的名字可以随便起,但不能用主机的GTID

loose-group_replication_start_on_boot = off  # #插件在server启动时不自动启动组复制

loose-group_replication_bootstrap_group = off #同上

loose-group_replication_ip_whitelist="192.168.10.200,192.168.10.201,192.168.10.202"

report_host=192.168.10.200

report_port=3306

loose-group_replication_local_address = '192.168.10.200:33061'

loose-group_replication_group_seeds ='192.168.10.200:33061,192.168.10.201:33061,192.168.10.202:33061'

loose-group_replication_single_primary_mode = FALSE #关闭单主模式的参数

loose-group_replication_enforce_update_everywhere_checks = TRUE #开启多主模式的参数

########innodb settings########

innodb_flush_log_at_trx_commit = 1 #改为1 是为了更安全, 值为2是性能

innodb_buffer_pool_size=128M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

secure_file_priv="/tmp"

[mysql]

socket=/tmp/mysql.sock

default-character-set=utf8

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.

mysql-002:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Disabling symbolic-links is recommended to prevent assorted security risks



user=mysql

#skip-grant-tables
datadir=/data/mysql

socket=/tmp/mysql.sock

log-error=/data/mysql/mysql.err

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

character-set-server=utf8

innodb_rollback_on_timeout = ON

collation-server=utf8_general_ci

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

max_connections=10000

sync_binlog=1

binlog_format=row

########basic settings########

server-id =139

character_set_server=utf8

max_allowed_packet = 16M

lower_case_table_names=1

slow_query_log=1

slow_query_log_file=/data/mysql/slow.log

########replication settings########

#####replication 复制配置###############

log-bin = /data/mysql/mysql-bin

max_binlog_size=500M

binlog_format = row

sync_binlog=1

expire_logs_days=15

###group replication###########

gtid_mode=on

enforce_gtid_consistency= ON

master_info_repository = TABLE

relay_log_info_repository = TABLE

binlog_checksum = NONE

log_slave_updates = ON

#log_slave_updates是将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中。

transaction_write_set_extraction = XXHASH64

##server必须为每个事物收集写集合，使用XXHASH64哈希算法将其编码为散列

loose-group_replication_group_name ='51837954-2d8a-11ed-bc2d-000c29f511b3'

#组的名字可以随便起,但不能用主机的GTID

loose-group_replication_start_on_boot = off  # #插件在server启动时不自动启动组复制

loose-group_replication_bootstrap_group = off #同上

loose-group_replication_ip_whitelist="192.168.10.200,192.168.10.201,192.168.10.202"

report_host=192.168.10.201

report_port=3306

loose-group_replication_local_address = '192.168.10.201:33061'

loose-group_replication_group_seeds ='192.168.10.200:33061,192.168.10.201:33061,192.168.10.202:33061'

loose-group_replication_single_primary_mode = FALSE #关闭单主模式的参数

loose-group_replication_enforce_update_everywhere_checks = TRUE #开启多主模式的参数

########innodb settings########

innodb_flush_log_at_trx_commit = 1 #改为1 是为了更安全, 值为2是性能

innodb_buffer_pool_size=128M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

secure_file_priv="/tmp"

[mysql]

socket=/tmp/mysql.sock

default-character-set=utf8

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.

mysql-003:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Disabling symbolic-links is recommended to prevent assorted security risks

skip-name-resolve

user=mysql

datadir=/data/mysql

socket=/tmp/mysql.sock

log-error=/data/mysql/mysql.err

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

character-set-server=utf8

innodb_rollback_on_timeout = ON

collation-server=utf8_general_ci

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

lower_case_table_names=1

max_connections=10000

sync_binlog=1

binlog_format=row

########basic settings########

server-id =140

character_set_server=utf8

max_allowed_packet = 16M

lower_case_table_names=1

slow_query_log=1

slow_query_log_file=/data/mysql/slow.log

########replication settings########

#####replication 复制配置###############

log-bin = /data/mysql/mysql-bin

max_binlog_size=500M

binlog_format = row

sync_binlog=1

expire_logs_days=15

###group replication###########

gtid_mode=on

enforce_gtid_consistency= ON

master_info_repository = TABLE

relay_log_info_repository = TABLE

binlog_checksum = NONE

log_slave_updates = ON

#log_slave_updates是将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中。

transaction_write_set_extraction = XXHASH64

##server必须为每个事物收集写集合，使用XXHASH64哈希算法将其编码为散列

loose-group_replication_group_name ='51837954-2d8a-11ed-bc2d-000c29f511b3'

#组的名字可以随便起,但不能用主机的GTID

loose-group_replication_start_on_boot = off  # #插件在server启动时不自动启动组复制

loose-group_replication_bootstrap_group = off #同上

loose-group_replication_ip_whitelist="192.168.10.200,192.168.10.201,192.168.10.202"

report_host=192.168.10.202

report_port=3306

loose-group_replication_local_address = '192.168.10.202:33061'

loose-group_replication_group_seeds ='192.168.10.200:33061,192.168.10.201:33061,192.168.10.202:33061'

loose-group_replication_single_primary_mode = FALSE #关闭单主模式的参数

loose-group_replication_enforce_update_everywhere_checks = TRUE #开启多主模式的参数

########innodb settings########

innodb_flush_log_at_trx_commit = 1 #改为1 是为了更安全, 值为2是性能

innodb_buffer_pool_size=128M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

secure_file_priv="/tmp"

[mysql]

socket=/tmp/mysql.sock

default-character-set=utf8

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.

启动MySQL，三台机器都要执行的操作：

systemctl start  mysqld.service
# yum安装的需要在日志中查找密码然后登陆，第一次登陆后需要修改初始密码才能执行其他操作
grep "password" /var/log/mysqld.log
# 查到密码后登陆
mysql -uroot -p'O,3*2/MlgJOB'
#需修改密码，这里要改成简单密码需要设置复杂度要求，这里就不做太多需改，设置一个满足复杂度的密码。
alter user 'root'@'localhost' identified by 'Admin@123！';
# 刷新权限
flush privileges;

三台机器登陆mysql中创建复制账号，三台机器都要操作：

# 创建账号不记录binlog
set sql_log_bin=0;

create user rpl_user@'%' identified by 'Admin@123!' ;

grant replication slave on *.* to rpl_user@'%' ;

flush privileges;
# 创建完开启，如果需要修改密码也记得关闭下
set sql_log_bin=1;

三台机器都要执行创建通道并且安装mgr模块,配置完，重启mysql服务：

change master to master_user='rpl_user',master_password='Admin@123!' for channel 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

注：如果和我一样虚拟机而且是克隆的要修改网卡的uuid.

mysql-001启动组复制：

set  global group_replication_bootstrap_group=ON;

set global group_replication_ip_whitelist="192.168.10.0/24";

START GROUP_REPLICATION;   

SELECT * FROM performance_schema.replication_group_members;
# 显示
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 45d51e99-5140-11ef-bc63-000c29e1aaac | 192.168.10.200 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
1 row in set (0.00 sec)



SET GLOBAL group_replication_bootstrap_group=OFF;

mysql-002启动组复制：

et global group_replication_allow_local_disjoint_gtids_join=ON;

set global group_replication_ip_whitelist="192.168.10.0/24";

START GROUP_REPLICATION;                                               

SELECT * FROM performance_schema.replication_group_members;
# 显示
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 44a8aa4b-5140-11ef-bbbc-000c29cad77d | 192.168.10.201 |        3306 | ONLINE       |
| group_replication_applier | 45d51e99-5140-11ef-bc63-000c29e1aaac | 192.168.10.200 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
2 rows in set (0.01 sec)

mysql-003启动组复制：

set global group_replication_allow_local_disjoint_gtids_join=ON;

set global group_replication_ip_whitelist="192.168.10.0/24";

START GROUP_REPLICATION;                                               

SELECT * FROM performance_schema.replication_group_members;
# 显示
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | 432755c9-5140-11ef-bcfa-000c29a620ad | 192.168.10.202 |        3306 | ONLINE       |
| group_replication_applier | 44a8aa4b-5140-11ef-bbbc-000c29cad77d | 192.168.10.201 |        3306 | ONLINE       |
| group_replication_applier | 45d51e99-5140-11ef-bc63-000c29e1aaac | 192.168.10.200 |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+

注：都为ONLINE就表示OK，第一次需要稍微等会，如果长时间状态不为ONLINE，或者error了那说明有问题，查看下错误日志。

查看集群状态的命令：

测试：

在mysql-001上创建库表写入数据：

create database mgr1;

use mgr1;

create table mgr1.t1(id int primary key, cn varchar(30));

insert into t1 values(1,'a');

在mysql-002上查询，并且添加数据

在mysql-003上查询，并且添加数据

故障处理：

报错1：

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log

解决方法：

根据提示打开group_replication_allow_local_disjoint_gtids_join选项，mysql命令行执行:

set global group_replication_allow_local_disjoint_gtids_join=ON;
# 在启动组复制
 START GROUP_REPLICATION;

注：如果虚拟机三台都关闭了，启动后发现组复制端口没有被监听我这里是33061，那么需要按照前面启动组复制的方式重新来一遍，三台都要。

连不上master，报错信息如下：

2017-04-17T16:18:14.756191+08:00 25 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
 2017-04-17T16:18:14.814193+08:00 25 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master'repl_user@host-192-168-99-156:3306' - retry-time: 60  retries: 1, Error_code: 2005
 2017-04-17T16:18:14.814219+08:00 25 [Note] Slave I/O thread for channel 'group_replication_recovery' killed while connecting to master
 2017-04-17T16:18:14.814227+08:00 25 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
 2017-04-17T16:18:14.814342+08:00 19 [ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Check group replication recovery's connection credentials.'

解决方案：

添加映射

重启下组复制

日常维护步骤：

1、如果从库某一节点关闭

2、如果所有的库都关闭后，第一个库作为主库首先执行

剩下的库直接执行即可！

3、如果主库故障，会自动从两个从库选出一个主库，主库启动后再次执行如下命令后会变成从库

mysql5.7的MGR部署完成。

原创作者: qingfeng00 转载于: https://blog.51cto.com/qingfeng00/11655635

草莓草莓草莓

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
部署MySQL5.7 MGR集群

环境配置：主机名IP地址版本系统mysql-001192.168.10.200mysql-5.7.44centos7mysql-001192.168.10.200mysql-5.7.44centos7mysql-001192.168.10.200mysql-5.7.44centos7注：MGR是在MySQL5.7.17以后...
复制链接

扫一扫