MySQL MGR提供了一个高可用性、高弹性、可靠的 MySQL 服务。

部署环境： ubuntu 18.04 * 3台 + mysql 5.7.32 + VMware + NAT网络

MySQL基础服务部署

在配置MySQL MGR之前，需要先在服务器上部署好“单机版”MySQL服务。

MySQL部署快速命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
shell> apt install libaio1
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
shell> mv mysql-5.7.32-linux-glibc2.12-x86_64 mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chmod 750 mysql-files
shell> cd ..  # /usr/local
shell> chown -R mysql:mysql mysql
shell> bin/mysqld --initialize --user=mysql  # 此处输出日志有root初始密码
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &

密码修改：

1
2
mysql>  set password=password('yourpassword');
mysql>  flush privileges;

MySQL MGR配置

1.配置my.cnf和hosts

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
shell> cat /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
lower_case_table_names=1
# MGR settings
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
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
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "master:33061"
group_replication_group_seeds= "master:33061,slave1:33061,slave2:33061"
group_replication_bootstrap_group=off

shell> cat /etc/hosts
127.0.0.1 localhost
192.168.61.128 master
192.168.61.129 slave1
192.168.61.130 slave2

修改完成后，重启服务器。

2.启动MGR服务

先启动master服务：

1
2
3
4
-- master
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;

再启用slave服务：

1
2
-- slave
mysql> start group_replication;

查看服务状态：

1
2
3
4
5
6
7
8
9
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 035d4863-1a5b-11eb-b2f8-000c299b693b | master      |        3306 | ONLINE       |
| group_replication_applier | 612612e0-1a59-11eb-9ab6-000c29912f8c | slave1      |        3306 | ONLINE       |
| group_replication_applier | e781b3c8-1a57-11eb-8908-000c29396c65 | slave2      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

只有’MEMBER_STATE’全部为’ONLINE’，才说明启动正常，其它均为异常。
异常时可以去查看mysql的错误日志，默认是在/usr/local/mysql/data/hosts.err.

至此，MGR服务就已经搭建完成了！

Ps:

  1. 配置了/etc/hosts需要重启服务器以使host生效，否则启动mgr时会无法解析地址。
  2. 如果以其它配置启动过mgr服务，可能会导致mgr无法启动问题，系统提示“ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.”，此时在mysql命令行执行下“reset master”命令即可解决。
  3. 需先创建数据库，再启动mgr服务，否则会引起报错“ERROR 1290 (HY000): The MySQL server is running with the –super-read-only option so it cannot execute this statement”。
  4. mgr需要每张表都要有primary key。