mysql8.0配置MGR
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql-8.0.27-el7-x86_64.tar.gz sbin share src
[root@localhost local]#
[root@localhost local]# tar -zxvf mysql-8.0.27-el7-x86_64.tar.gz
[root@localhost local]# mv mysql-8.0.27-el7-x86_64 mysql8.0
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql8.0 mysql-8.0.27-el7-x86_64.tar.gz sbin share src
---关闭虚拟机,克隆两台出来
更改ip 和主机名
192.168.220.131 mgr1
192.168.220.132 mgr2
192.168.220.133 mgr1
nmcli con modify ens160 ipv4.addresses 192.168.220.132/24
nmcli con reload
nmcli con up ens160
nmcli con modify ens160 ipv4.addresses 192.168.220.133/24
nmcli con reload
nmcli con up ens160
hostnamectl set-hostname mgr1
hostnamectl set-hostname mgr2
hostnamectl set-hostname mgr3
192.168.220.131 mgr1
192.168.220.132 mgr2
192.168.220.133 mgr3
编辑配置文件 /etc/my.cnf (所有节点)
3个节点除了server_id、loose-group_replication_local_address、report_host 三个参数不一样外,其他保持一致。
[mysqld]
port=3306
basedir=/usr/local/mysql8.0
datadir=/mysql/data_mgr_8.0/
socket=/mysql/data_mgr_8.0/mysql.sock
pid_file=/mysql/data_mgr_8.0/mysql.pid
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "192.168.220.131:33061"
loose-group_replication_group_seeds= "192.168.220.131:33061,192.168.220.132:33061,192.168.220.133:33061"
loose-group_replication_bootstrap_group=OFF
report_host=192.168.220.131
report_port=3306
------初始化数据库(所有节点)
useradd mysql
mkdir -p /mysql/data_mgr_8.0
sudo chown -R mysql.mysql /mysql/data_mgr_8.0/
/usr/local/mysql8.0/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql8.0 --datadir=/mysql/data_mgr_8.0 --user=mysql
[root@mgr2 ~]# useradd mysql
[root@mgr2 ~]# mkdir -p /mysql/data_mgr_8.0
[root@mgr2 ~]# sudo chown -R mysql.mysql /mysql/data_mgr_8.0/
[root@mgr2 ~]#
[root@mgr2 ~]# /usr/local/mysql8.0/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql8.0 --datadir=/mysql/data_mgr_8.0 --user=mysql
2022-03-04T08:28:03.507145Z 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2022-03-04T08:28:03.507173Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2022-03-04T08:28:03.507178Z 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2022-03-04T08:28:03.507185Z 0 [Warning] [MY-011069] [Server] The syntax '--transaction-write-set-extraction' is deprecated and will be removed in a future release.
2022-03-04T08:28:03.508811Z 0 [System] [MY-013169] [Server] /usr/local/mysql8.0/bin/mysqld (mysqld 8.0.27) initializing of server in progress as process 3550
2022-03-04T08:28:03.578344Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-04T08:28:04.691344Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-03-04T08:28:05.696662Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-03-04T08:28:05.696690Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-03-04T08:28:05.801787Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_start_on_boot=OFF'.
2022-03-04T08:28:05.801792Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_local_address=192.168.220.132:33061'.
2022-03-04T08:28:05.801798Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_group_seeds=192.168.220.131:33061,192.168.220.132:33061,192.168.220.133:33061'.
2022-03-04T08:28:05.801803Z 0 [Warning] [MY-000067] [Server] unknown variable 'loose-group_replication_bootstrap_group=OFF'.
2022-03-04T08:28:05.808462Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
安装mysql启停服务:(所有节点)
cp /usr/local/mysql8.0/support-files/mysql.server /etc/init.d/
chmod +x /etc/init.d/mysql.server
[root@mgr1 data_mgr_8.0]# service mysql.server start (所有节点)
Starting MySQL.Logging to '/mysql/data_mgr_8.0/mgr1.err'.
... SUCCESS!
添加环境变量(所有节点)
cd;vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql8.0/bin
source .bash_profile
----报了两个依赖包的错,因为是8.0的rhel太新了,依赖包版本高导致,处理下成功登录(所有节点)
[root@mgr1 bin]# ./mysql -uroot
./mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
[root@mgr1 bin]#
[root@mgr2 ~]# ln -s /usr/lib64/libncurses.so.6.1 /usr/lib64/libncurses.so.5
[root@mgr2 ~]# ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
[root@mgr1 ~]# mysql -uroot -S /mysql/data_mgr_8.0/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
----安装MGR插件,设置复制账号(所有节点执行)
# 安装MGR插件
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
#设置复制账号
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';
mysql> ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl'; ---更改加密方式,默认的caching_sha2_password会影响同步
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='repl' FOR CHANNEL 'group_replication_recovery';
======启动MGR单主模式
# 启动MGR,在主库(192.168.220.131)上执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
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 | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMM UNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+------------ ----------------+
| group_replication_applier | 0042bfcd-9b95-11ec-ad32-000c2948d7a1 | 192.168.220.131 | 3306 | ONLINE | PRIMARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+------------ ----------------+
1 row in set (0.07 sec)
# 其他节点加入MGR,在从库(192.168.220.132,192.168.220.133)上执行
mysql> START GROUP_REPLICATION;
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;\
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
| group_replication_applier | 0042bfcd-9b95-11ec-ad32-000c2948d7a1 | 192.168.220.131 | 3306 | ONLINE | PRIMARY | 8.0.27
| group_replication_applier | 027ac211-9b95-11ec-817f-000c29834b52 | 192.168.220.132 | 3306 | ONLINE | SECONDARY | 8.0.27
| group_replication_applier | 038c658b-9b95-11ec-b770-000c293f2bac | 192.168.220.133 | 3306 | ONLINE | SECONDARY | 8.0.27
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
3 rows in set (0.00 sec)
可以看到,3个节点状态为online,并且主节点为192.168.220.131,只有主节点可以写入,其他节点只读,MGR单主模式搭建成功。
---启动过程中有报错(防火墙导致,关闭即可)
2022-03-04T09:19:15.029782Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.220.133:33061 on local port: 33061.'
2022-03-04T09:19:15.029824Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061'
======切换到多主模式
MGR切换模式需要重新启动组复制,因些需要在所有节点上先关闭组复制,设置 group_replication_single_primary_mode=OFF 等参数,再启动组复制。
# 停止组复制(所有节点执行):
mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
# 随便选择某个节点执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他节点执行
mysql> START GROUP_REPLICATION;
# 查看组信息,所有节点的 MEMBER_ROLE 都为 PRIMARY
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;\
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
| group_replication_applier | 0042bfcd-9b95-11ec-ad32-000c2948d7a1 | 192.168.220.131 | 3306 | ONLINE | PRIMARY | 8.0.27
| group_replication_applier | 027ac211-9b95-11ec-817f-000c29834b52 | 192.168.220.132 | 3306 | ONLINE | PRIMARY | 8.0.27
| group_replication_applier | 038c658b-9b95-11ec-b770-000c293f2bac | 192.168.220.133 | 3306 | ONLINE | PRIMARY | 8.0.27
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
3 rows in set (0.00 sec)
=====切回单主模式
# 所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
# 主节点(192.168.220.131)执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点(192.168.220.132、192.168.220.133)执行
START GROUP_REPLICATION;
# 查看MGR组信息
mysql> SELECT * FROM performance_schema.replication_group_members;\
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
| group_replication_applier | 0042bfcd-9b95-11ec-ad32-000c2948d7a1 | 192.168.220.131 | 3306 | ONLINE | PRIMARY | 8.0.27
| group_replication_applier | 027ac211-9b95-11ec-817f-000c29834b52 | 192.168.220.132 | 3306 | ONLINE | SECONDARY | 8.0.27
| group_replication_applier | 038c658b-9b95-11ec-b770-000c293f2bac | 192.168.220.133 | 3306 | ONLINE | SECONDARY | 8.0.27
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+---------------
3 rows in set (0.00 sec)
----mgr模式下所有表都需要设置主键,不然会报错
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.