MySQL：8.0.x搭建mgr集群

べ汐颜染瞳べ

已于 2023-03-27 10:48:39 修改

阅读量356

点赞数

文章标签： mysql 数据库运维

于 2023-03-27 10:46:36 首次发布

本文链接：https://blog.csdn.net/God_sign/article/details/129789549

版权

mysql版本：8.0.32
集群环境：group replication由至少3个或3个以上节点组成的数据库集群

节点	主机名
192.168.101.11	mysqltest1
192.168.101.12	mysqltest2
192.168.101.13	mysqltest3

1、添加主机名，ip

vi /etc/hosts

三个节点都执行
[root@mysqltest1 ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.101.11 mysqltest1
192.168.101.12 mysqltest2
192.168.101.13 mysqltest3

2、关闭防火墙，selinux

systemctl stop firewalld
systemctl status firewalld

三个节点都执行
[root@mysqltest1 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Wed 2023-03-22 10:02:49 CST; 1s ago
	 Docs: man:firewalld(1)
Process: 740 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)
 Main PID: 740 (code=exited, status=0/SUCCESS)

Mar 22 09:09:19 mysqltest1 systemd[1]: Starting firewalld - dynamic firewall daemon...
Mar 22 09:09:30 mysqltest1 systemd[1]: Started firewalld - dynamic firewall daemon.
Mar 22 09:09:35 mysqltest1 firewalld[740]: WARNING: AllowZoneDrifting is enabled. This is considered an ...now.
Mar 22 10:02:47 mysqltest1 systemd[1]: Stopping firewalld - dynamic firewall daemon...
Mar 22 10:02:49 mysqltest1 systemd[1]: Stopped firewalld - dynamic firewall daemon.
Hint: Some lines were ellipsized, use -l to show in full.

vi /etc/selinux/config
SELINUX状态改为disabled

三个节点都执行
[root@mysqltest1 ~]# vi /etc/selinux/config 

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

3、配置my.cnf文件

vi /etc/my.cnf

三个节点除了server-id、loose-group_replication_local_address、report_host参数不同，其他保持一致
节点1为例：
[root@mysqltest1 ~]# vi /etc/my.cnf
[client]
port=3306
socket=/home/mysql/mysql.sock
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
server-id=1
port=3306
user=mysql
socket=/home/mysql/mysql.sock
basedir=/home/mysql/mysql8
datadir=/home/mysql/data
lower-case-table-names=1
character_set_server=utf8mb4

log_bin=binlog-bin
log_slave_updates=ON
binlog_format=ROW
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE

gtid_mode=ON
enforce_gtid_consistency=true

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.101.11:33061"
loose-group_replication_group_seeds="192.168.101.11:33061,192.168.101.12:33061,192.168.101.13:33061"
loose-group_replication_bootstrap_group=OFF

report_host=192.168.101.11
report_port=3306

loose-group_replication_recovery_get_public_key=ON

配置后重启mysql服务，使其生效

4、安装组复制插件

安装mgr插件：INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;
查看插件：show plugins;

三个节点都执行
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (9.56 sec)

mysql> show plugins;
+----------------------------------+----------+--------------------+----------------------+---------+
| Name                             | Status   | Type               | Library              | License |
+----------------------------------+----------+--------------------+----------------------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL                 | GPL     |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL                 | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL                 | GPL     |
| ngram                            | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL                 | GPL     |
| mysqlx                           | ACTIVE   | DAEMON             | NULL                 | GPL     |
| group_replication                | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------------+----------+--------------------+----------------------+---------+
49 rows in set (0.00 sec)

5、创建复制用户

三个节点都执行
mysql> create user mgr@'%' identified by 'mgr';
Query OK, 0 rows affected (0.36 sec)

mysql> grant replication slave on *.* to mgr@'%';
Query OK, 0 rows affected (0.18 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置复制通道
mysql> change master to master_user='mgr',master_password='mgr' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.01 sec)

6、启动组复制

在主节点上执行（192.168.101.11）
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 (3.66 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

在其他节点上执行（192.168.101.12,192.168.101.13）
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.88 sec)

查看mgr状态
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 6158d7b6-c7be-11ed-b240-000c29058c63 | 192.168.101.13 |        3306 | ONLINE       | SECONDARY   | 8.0.32         | XCom                       |
| group_replication_applier | 9db935d4-ac23-11ed-b958-000c299e4155 | 192.168.101.11 |        3306 | ONLINE       | PRIMARY     | 8.0.32         | XCom                       |
| group_replication_applier | c7f975e0-ac25-11ed-bafa-000c29b0414d | 192.168.101.12 |        3306 | ONLINE       | SECONDARY   | 8.0.32         | XCom                       |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

7、添加测试数据

主节点添加数据
mysql> create database mgr;
Query OK, 1 row affected (0.48 sec)

mysql> use mgr;
Database changed

//mgr集群模式下，表需要有主键
mysql> create table mgr1 (id int primary key,name varchar(10)); 
Query OK, 0 rows affected (2.14 sec)

mysql> insert into mgr1 values (1,'AAA');
Query OK, 1 row affected (0.47 sec)

mysql> insert into mgr1 values (2,'BBB');
Query OK, 1 row affected (0.36 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mgr1;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
|  2 | BBB  |
+----+------+
2 rows in set (0.00 sec)

从节点查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dey                |
| information_schema |
| mgr                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.57 sec)

mysql> select * from mgr.mgr1;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
|  2 | BBB  |
+----+------+
2 rows in set (0.00 sec)

从节点添加数据
mysql> use mgr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into mgr1 values (3,'CCC');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql> 
添加失败，从节点为只读模式