單機MGR搭建
mkdir -p /app/mysql/data/{3310,3311,3312}
mkdir -p /app/mysql/log/{3310,3311,3312}
mkdir -p /app/mysql/app
mkdir -p /app/mysql/backup
cd /app/mysql/log
chown -R mysql.mysql 331*
cd /app/mysql/data
chown -R mysql.mysql 331*
chown mysql.mysql -R backup
cat >>/app/mysql/data/my3310.cnf <<EOF
[client]
socket = /app/mysql/data/mysql3310.sock
port = 3310
[mysqld]
# log_bin
basedir = /app/mysql/app/mysql8027
datadir = /app/mysql/data/3310
port = 3310
socket = /app/mysql/data/mysql3310.sock
log-error =/app/mysql/log/3310/mysqld.log
pid-file = /app/mysql/data/mysqld3310.pid
slow_query_log = ON
slow_query_log_file = /app/mysql/log/3310/mysql_slow.log
long_query_time = 1
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Group Replication
server_id = 3310
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
sync_binlog = 1
expire_logs_days = 3
#plugin
mysqlx=0
#default_authentication_plugin = mysql_native_password
plugin-load-add=mysql_clone.so
plugin-load-add=group_replication.so
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 ='172.30.135.119:33101'
loose-group_replication_group_seeds ='172.30.135.119:33101,172.30.135.119:33111,172.30.135.119:33121'
loose-group_replication_bootstrap_group = off
#---------------replicate--------------#
relay-log-index = relay.index
relay-log = relay
log-slave-updates
EOF
分別將server_id改為3310、3311、3312
loose-group_replication_local_address改為3310、3311、3312埠:
cat >/app/mysql/data/my3311.cnf <<EOF
[client]
socket = /app/mysql/data/mysql3311.sock
port = 3311
[mysqld]
# log_bin
basedir = /app/mysql/app/mysql8027
datadir = /app/mysql/data/3311
port = 3311
socket = /app/mysql/data/mysql3311.sock
log-error =/app/mysql/log/3311/mysqld.log
pid-file = /app/mysql/data/mysqld3311.pid
slow_query_log = ON
slow_query_log_file = /app/mysql/log/3311/mysql_slow.log
long_query_time = 1
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Group Replication
server_id = 3311
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
sync_binlog = 1
expire_logs_days = 3
#plugin
mysqlx=0
#default_authentication_plugin = mysql_native_password
plugin-load-add=mysql_clone.so
plugin-load-add=group_replication.so
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 ='172.30.135.119:33111'
loose-group_replication_group_seeds ='172.30.135.119:33101,172.30.135.119:33111,172.30.135.119:33121'
loose-group_replication_bootstrap_group = off
#---------------replicate--------------#
relay-log-index = relay.index
relay-log = relay
log-slave-updates
EOF
cat >/app/mysql/data/my3312.cnf <<EOF
[client]
socket = /app/mysql/data/mysql3312.sock
port = 3312
[mysqld]
# log_bin
basedir = /app/mysql/app/mysql8027
datadir = /app/mysql/data/3312
port = 3312
socket = /app/mysql/data/mysql3312.sock
log-error =/app/mysql/log/3312/mysqld.log
pid-file = /app/mysql/data/mysqld3312.pid
slow_query_log = ON
slow_query_log_file = /app/mysql/log/3312/mysql_slow.log
long_query_time = 1
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Group Replication
server_id = 3312
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
sync_binlog = 1
expire_logs_days = 3
#plugin
mysqlx=0
#default_authentication_plugin = mysql_native_password
plugin-load-add=mysql_clone.so
plugin-load-add=group_replication.so
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 ='172.30.135.119:33121'
loose-group_replication_group_seeds ='172.30.135.119:33101,172.30.135.119:33111,172.30.135.119:33121'
loose-group_replication_bootstrap_group = off
#---------------replicate--------------#
relay-log-index = relay.index
relay-log = relay
log-slave-updates
EOF
chown -R mysql.mysql /app/mysql/data/my*.cnf
/app/mysql/app/mysql8027/bin/mysqld --initialize --user=mysql --basedir=/app/mysql/app/mysql8027 --datadir=/app/mysql/data/3310
/app/mysql/app/mysql8027/bin/mysqld --initialize --user=mysql --basedir=/app/mysql/app/mysql8027 --datadir=/app/mysql/data/3311
/app/mysql/app/mysql8027/bin/mysqld --initialize --user=mysql --basedir=/app/mysql/app/mysql8027 --datadir=/app/mysql/data/3312
初始root密碼如下
3310:Nhnxa8tlWi%8
3311:wD#P8B_!N8lO
3312:Fyf_fo*F&0ug
/app/mysql/app/mysql8027/bin/mysqld --defaults-file=/app/mysql/data/my3310.cnf --user=mysql --datadir=/app/mysql/data/3310 --plugin-dir=/app/mysql/app/mysql8027/lib/plugin --basedir=/app/mysql/app/mysql8027 --pid-file=/app/mysql/data/mysql3310.pid --socket=/app/mysql/data/mysql3310.sock --port=3310 &
/app/mysql/app/mysql8027/bin/mysql -uroot -P 3310 -h127.0.0.1 -p
alter user root@`localhost` identified with mysql_native_password by 'root123';
set sql_log_bin=0;
create user rpl_user@'%' identified with mysql_native_password by 'rpl_pass';
grant replication slave on *.* to rpl_user@'%' ;
flush privileges;
set sql_log_bin=1;
change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';
報錯：
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
ERROR 1126 (HY000): Can't open shared library '/root/mysql8025/lib/plugin/group_replication.so' (errno: 0 /root/mysql8025/lib/plugin/group_replication.so: cannot open shared object file: Permission denied)
使用軟連結儲存mysql安裝檔案時總有這個報錯。換成實際地址存放後解決。
mysql> show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
……
| clone | ACTIVE | CLONE | mysql_clone.so | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
47 rows in set (0.00 sec)
@3310庫：
CREATE USER 'clone_user' IDENTIFIED BY 'password';
GRANT BACKUP_ADMIN on *.* to 'clone_user';
--裝外掛
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
select * from information_schema.plugins where plugin_name='clone';
##############################
@目標庫：
/app/mysql/app/mysql8027/bin/mysqld --defaults-file=/app/mysql/data/my3311.cnf --user=mysql --datadir=/app/mysql/data/3311 --plugin-dir=/app/mysql/app/mysql8027/lib/plugin --basedir=/app/mysql/app/mysql8027 --pid-file=/app/mysql/data/mysql3311.pid --socket=/app/mysql/data/mysql3311.sock --port=3311 &
/app/mysql/app/mysql8027/bin/mysql -uroot -P 3311 -h127.0.0.1 -p
/app/mysql/app/mysql8027/bin/mysqld --defaults-file=/app/mysql/data/my3312.cnf --user=mysql --datadir=/app/mysql/data/3312 --plugin-dir=/app/mysql/app/mysql8027/lib/plugin --basedir=/app/mysql/app/mysql8027 --pid-file=/app/mysql/data/mysql3312.pid --socket=/app/mysql/data/mysql3312.sock --port=3312 &
/app/mysql/app/mysql8027/bin/mysql -uroot -P 3312 -h127.0.0.1 -p
alter user root@`localhost` identified with mysql_native_password by 'root123';
set sql_log_bin=0;
create user rpl_user@'%' identified with mysql_native_password by 'rpl_pass';
grant replication slave on *.* to rpl_user@'%' ;
flush privileges;
set sql_log_bin=1;
--裝外掛
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
--設引數
SET GLOBAL clone_valid_donor_list = '172.30.135.119:3310';
--執行克隆操作
clone instance from 'clone_user'@`172.30.135.119`:3310 identified by 'password';
克隆結束後會嘗試自動重啟例項，由於當前環境需要用mysql命令重啟（未設定systemctl方式重啟），報錯：
ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).
這個是正常現象。
無需修改，啟動例項，命令略
在原庫和目標庫分別執行命令 show master status;
由於操作期間原庫39.51沒有資料變化，可以看到其Executed_Gtid_Set一樣
在第一個節點開啟引導功能，立即開啟該節點的組複製功能來建立組，然後立即關閉組引導功能
@3310：
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
select * from performance_schema.replication_group_members;
@3311、3312
可能用到的操作：
關閉 -》 /app/mysql/app/mysql8027/bin/mysqladmin -uroot -P3310 -p -h 127.0.0.1 shutdown
############################################################################################
一、配置
mysql-router 8.0+MGR單主叢集
-------------------------------------
寫在前面的總結：
1、MGR單主模式，不太合適，因為MGR單主模式下主庫failover之後，下一個主庫是投票出來的，不知道漂去哪裡，VIP+keepalived的方式沒用；
2、mysql-router配置檔案裡寫的read-write的主機，只要前一個能用，後一個就不會被用（“首個可用”演算法）；
3、MGR單主模式下，主以外其他的機器都是read-only，一旦主機發生failover，其他的庫無法通過代理寫入；
4、mysql-router需要分別給寫端（read-write）和讀端（read-only）繫結不同的埠，可能需要應用端來做讀寫分離，相比之下Atlas代理則無需繫結埠；
所以，Mysql-router適合的模式應該是：普通的主從叢集，多個主庫互為主從且互相之間有VIP，或者傳統的MHA叢集等。
--------------------------------------
二、對mysql-router的一些說明:
讀寫分離：read-write模式：採用“首個可用”演算法，優先使用第一個server，當第一個server不可達時，將會Failover到第二個server，依次進行。如果都不可達，那麼請求將會被中斷，且此時Router將不可用。
負載均衡：read-only模式.Mysql-router可以實現對不同的後端資料進行資料負載均衡，read-only模式將採用“輪詢”演算法，依次選擇server新建連線，如果某個Server不可達，將會重試下一個Server，如果所有的Server都不可達，那麼此埠上的請求將中斷，即READ操作將不可用。同時Router將會持續與每個Server保持心跳探測，當恢復後重新加入Active列表，此後那些新建的連線請求可以分發給此Server。
三、配置與測試過程
各伺服器的主機名、IP、角色分配：
mysql-router:172.30.135.119
mgr1:172.30.135.119:3310 Primary
mgr2:172.30.135.119:3311 Secondary
mgr3:172.30.135.119:3312 Secondary
1、下載安裝：
xz -d mysql-router-8.0.27-linux-glibc2.17-x86_64-minimal.tar.xz
tar xvf mysql-router-8.0.27-linux-glibc2.17-x86_64-minimal.tar
cp -rf mysql-router-8.0.27-linux-glibc2.17-x86_64-minimal /app/mysql/app/mysql-router
mkdir /app/mysql/app/mysql-router/conf/
2、編輯配置檔案：
vi /app/mysql/app/mysql-router/conf/mysqlrouter.ini
---------
[DEFAULT]
logging_folder=/app/mysql/app/mysql-router/log
runtime_folder=/app/mysql/app/mysql-router/run
data_folder=/app/mysql/app/mysql-router/data
connect_timeout=30
read_timeout=30
[logger]
level = INFO
[routing:failover]
bind_address = 0.0.0.0
bind_port = 7001
max_connections = 1024
mode = read-write
destinations =172.30.135.119:3310 #可用的支援寫操作的主庫，或者主庫共用的VIP,多個地址用逗號分隔
[routing:balancing]
bind_address = 0.0.0.0
bind_port = 7002 #埠7002
max_connections = 1024
mode = read-only
destinations = 172.30.135.119:3311,172.30.135.119:3312 #參與讀負載均衡的從庫們
---------
改一下環境變數：
echo "export PATH=$PATH:/app/mysql/app/mysql-router/bin/" >> /etc/profile
source /etc/profile
驗證安裝配置是否成功：
# mysqlrouter -V
MySQL Router Ver 8.0.27 for Linux on x86_64 (MySQL Community - GPL)
chown -R mysql.mysql /app/mysql/app/mysql-router
3、啟動
/app/mysql/app/mysql-router/bin/mysqlrouter -c /app/mysql/app/mysql-router/conf/mysqlrouter.ini &
netstat -ntpl|grep mysqlrouter可以看到7001和7002埠已經被使用；
# netstat -ntpl|grep mysqlrouter
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 3107/mysqlrouter
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 3107/mysqlrouter
4、登入測試及負載均衡測試：
資料庫裡建立賬號並授權允許遠端登入，此處使用rpl_user使用者
注意，一定要確保賬號可以遠端登入。自己實驗時由於root沒有遠端許可權，試了半天才解決。
先測試寫埠7001：
# /app/mysql/app/mysql8027/bin/mysql -urpl_user -P7001 -h172.30.135.119 -prpl_pass -e "select @@hostname;select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| centos84 |
+------------+
+--------+
| @@port |
+--------+
| 3310 |
+--------+
如果有兩個read-write主機的話，首先訪問第一個，只有第一個故障才會訪問第二個；
再測試讀：
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysql -urpl_user -P7002 -h172.30.135.119 -prpl_pass -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3311 |
+--------+
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysql -urpl_user -P7002 -h172.30.135.119 -prpl_pass -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3312 |
+--------+
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysql -urpl_user -P7002 -h172.30.135.119 -prpl_pass -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3311 |
+--------+
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysql -urpl_user -P7002 -h172.30.135.119 -prpl_pass -e "select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3312 |
+--------+
輪詢顯示3311和3312。
####################################################
主節點shutdown後重新加入叢集：
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysqladmin -uroot -P3310 -p -h 127.0.0.1 shutdown
Enter password:
[root@centos84 app]#
[root@centos84 app]#
[root@centos84 app]#
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysqld --defaults-file=/app/mysql/data/my3310.cnf --user=mysql --datadir=/app/mysql/data/3310 --plugin-dir=/app/mysql/app/mysql8027/lib/plugin --basedir=/app/mysql/app/mysql8027 --pid-file=/app/mysql/data/mysql3310.pid --socket=/app/mysql/data/mysql3310.sock --port=3310 &
[5] 3318
[1] Done /app/mysql/app/mysql8027/bin/mysqld --defaults-file=/app/mysql/data/my3310.cnf --user=mysql --datadir=/app/mysql/data/3310 --plugin-dir=/app/mysql/app/mysql8027/lib/plugin --basedir=/app/mysql/app/mysql8027 --pid-file=/app/mysql/data/mysql3310.pid --socket=/app/mysql/data/mysql3310.sock --port=3310 (wd: /app/mysql/data/3310)
(wd now: /app/mysql/app)
[root@centos84 app]#
[root@centos84 app]#
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysql -uroot -P 3310 -h127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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>
mysql>
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 | | | NULL | OFFLINE | | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 152 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> clone instance from 'clone_user'@`172.30.135.119`:3311 identified by 'password';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>
mysql>
mysql> show global variables like 'super%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| super_read_only | ON |
+-----------------+-------+
1 row in set (0.01 sec)
mysql> set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)
mysql> clone instance from 'clone_user'@`172.30.135.119`:3311 identified by 'password';
ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).
mysql>
mysql>
mysql> exit
Bye
[5]+ Done /app/mysql/app/mysql8027/bin/mysqld --defaults-file=/app/mysql/data/my3310.cnf --user=mysql --datadir=/app/mysql/data/3310 --plugin-dir=/app/mysql/app/mysql8027/lib/plugin --basedir=/app/mysql/app/mysql8027 --pid-file=/app/mysql/data/mysql3310.pid --socket=/app/mysql/data/mysql3310.sock --port=3310
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysqld --defaults-file=/app/mysql/data/my3310.cnf --user=mysql --datadir=/app/mysql/data/3310 --plugin-dir=/app/mysql/app/mysql8027/lib/plugin --basedir=/app/mysql/app/mysql8027 --pid-file=/app/mysql/data/mysql3310.pid --socket=/app/mysql/data/mysql3310.sock --port=3310 &
[5] 3425
[root@centos84 app]#
[root@centos84 app]# /app/mysql/app/mysql8027/bin/mysql -uroot -P 3310 -h127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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>
mysql>
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| binlog.000002 | 152 | | | 12498f62-5be7-11ec-a94e-00163e012cf2:1-3,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-12 |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (10.74 sec)
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 | 12498f62-5be7-11ec-a94e-00163e012cf2 | centos84 | 3310 | ONLINE | SECONDARY | 8.0.27 | XCom |
| group_replication_applier | a8fae19c-5bea-11ec-b0f7-00163e012cf2 | centos84 | 3311 | ONLINE | PRIMARY | 8.0.27 | XCom |
| group_replication_applier | b2c1d08a-5beb-11ec-b90e-00163e012cf2 | centos84 | 3312 | ONLINE | SECONDARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
切換主節點：
mysql> SELECT group_replication_set_as_primary('b2c1d08a-5beb-11ec-b90e-00163e012cf2');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('b2c1d08a-5beb-11ec-b90e-00163e012cf2') |
+--------------------------------------------------------------------------+
| Primary server switched to: b2c1d08a-5beb-11ec-b90e-00163e012cf2 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT event_name, work_completed, work_estimated FROM performance_schema.events_stages_current WHERE event_name LIKE "%stage/group_rpl%";
Empty set (0.00 sec)
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 | 12498f62-5be7-11ec-a94e-00163e012cf2 | centos84 | 3310 | ONLINE | SECONDARY | 8.0.27 | XCom |
| group_replication_applier | a8fae19c-5bea-11ec-b0f7-00163e012cf2 | centos84 | 3311 | ONLINE | SECONDARY | 8.0.27 | XCom |
| group_replication_applier | b2c1d08a-5beb-11ec-b90e-00163e012cf2 | centos84 | 3312 | ONLINE | PRIMARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.01 sec)
虛擬機器關機，該叢集隨之關閉。
重新開啟並啟動三個例項後，查詢：
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 | | | NULL | OFFLINE | | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| binlog.000004 | 192 | | | 12498f62-5be7-11ec-a94e-00163e012cf2:1-3,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-13 |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like 'super%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| super_read_only | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
三個節點無法相互發現，gtid一致，都不是隻讀模式。
將其中一個節點作為主節點，執行：
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 | | | NULL | OFFLINE | | | |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start group_replication;
Query OK, 0 rows affected (1.08 sec)
mysql>
mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql>
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 | 12498f62-5be7-11ec-a94e-00163e012cf2 | centos84 | 3310 | ONLINE | PRIMARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
再通過以下操作將另外兩個節點加入：
mysql> change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 4 warnings (0.01 sec)
mysql> start group_replication;
Query OK, 0 rows affected (1.65 sec)
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 | 12498f62-5be7-11ec-a94e-00163e012cf2 | centos84 | 3310 | ONLINE | PRIMARY | 8.0.27 | XCom |
| group_replication_applier | b2c1d08a-5beb-11ec-b90e-00163e012cf2 | centos84 | 3312 | ONLINE | SECONDARY | 8.0.27 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-2847550/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL MGR 叢集搭建（單主模式）MySql模式
- MySQL MGR單主模式詳細搭建過程MySql模式
- MySQL MGR 叢集搭建MySql
- mysql8.0.31 mgr搭建MySql
- MySQL之MGR白名單MySql
- MGR(MySQL Group Replication)部署搭建測試MySql
- MySQL組複製(MGR)全解析 Part 5 MGR單主模式部署指南MySql模式
- mysql MGR搭建和模擬切換演練MySql
- 單機搭建Data Guard
- MySQL組複製(MGR)全解析 Part 4 MGR單主模式部署前準備MySql模式
- MySQL8.0的MGR多主搭建與測試MySql
- 【MGR】MGR-imeout on wait for view after joining groupAIView
- 搭建ELK日誌平臺（單機）
- 單機版搭建kubernetes(K8s)K8S
- Storm 系列（三）—— Storm 單機版本環境搭建ORM
- 深入理解 ceph mgr
- 高可用 proxysql + mysql MGRMySql
- 技術｜如何為MySQL MGR實現簡單的負載均衡代理？MySql負載
- 使用 Docker 快速搭建單機版的 Kubernetes 叢集Docker
- 單機Linux下搭建MongoDB副本集-三節點LinuxMongoDB
- 基於docker 搭建redis環境—redis單機版DockerRedis
- 【教程】手把手教你如何搭建Hadoop單機偽叢集Hadoop
- Windows Server 2012配置MySQL 8.0.20 MGRWindowsServerMySql
- MySQL組複製(MGR)全解析 Part 3 組複製機制細節MySql
- 在Docker中安裝MySQL 8.0.19之MGR（單主模式&多主模式）+新增節點DockerMySql模式
- 量化機器人開發,自動掛單交易軟體搭建機器人
- ZooKeeper 系列（二）—— Zookeeper單機環境和叢集環境搭建
- 探究MySQL MGR的讀寫分離MySql
- 沃趣微講堂 | PXC、MGC&MGR原理與實踐對比（五）：MGR新增節點流程GC
- Flink 入門篇之 在 Linux 上搭建 Flink 的單機環境Linux
- tableau必知必會之VMware 搭建 Tableau Server for Linux 單機環境ServerLinux
- 沃趣微講堂 | PXC、MGC&MGR原理與實踐對比（八）：PXC、MGC和MGR crash recoveryGC
- 沃趣微講堂 | PXC、MGC&MGR原理與實踐對比（七）：MGR叢集初始化GC
- Gin 框架的簡單搭建框架
- Mysql MGR監控及最佳化點MySql
- MySQL:MGR 學習(1):寫集合(Write set)MySql
- MySQL組複製MGR（一）-- 技術概述MySql
- MySQL高可用架構-MMM、MHA、MGR、PXCMySql架構