利用 8.0.25-17 GreatSQL 部署 MGR 集群，并完成添加新节点 、切主（超详细）--
测试环境，单机-多实例（centos 7.6)
| GreatSQL-01 | 3306 | /data/GreatSQL/mgr01/ | GreatSQL-02 | 3307 | /data/GreatSQL/mgr02/ | GreatSQL-03 | 3308 | /data/GreatSQL/mgr03/
1 安装
yum install libaio groupadd mysql useradd -r -g mysql -s /bin/false mysql tar xvJf GreatSQL-8.0.25-17-Linux-glibc2.17-x86_64.tar.xz -C /usr/local/ cd /usr/local ln -s GreatSQL-8.0.25-17-Linux-glibc2.17-x86_64 mysql
#添加环境变量 #cat >>/etc/profile<< EOF export PATH=/usr/local/mysql/bin:$PATH EOF #source /etc/profile
配置文件
#vim /etc/my.cnf [mysql] prompt = "\u@mysqldb \R:\m:\s [\d]> " no_auto_rehash loose-skip-binary-as-hex [mysqld] basedir=/usr/local/mysql log_timestamps=SYSTEM user = mysql log_error_verbosity = 3 log-bin=binlog binlog-format=row log_slave_updates=ON binlog_checksum=CRC32 master-info-repository=TABLE relay-log-info-repository=TABLE gtid-mode=on enforce-gtid-consistency=true binlog_transaction_dependency_tracking=writeset transaction_write_set_extraction=XXHASH64 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers=4 #可以设置为逻辑CPU数量的2-4倍 sql_require_primary_key=1 slave_preserve_commit_order=1 slave_checkpoint_period=2 #mgr loose-plugin_load_add='mysql_clone.so' loose-plugin_load_add='group_replication.so' loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1" loose-group_replication_group_seeds= "127.0.0.1:33061,127.0.0.1:33071,127.0.0.1:33081" loose-group_replication_start_on_boot=off loose-group_replication_bootstrap_group=off loose-group_replication_exit_state_action=READ_ONLY loose-group_replication_flow_control_mode = "DISABLED" loose-group_replication_single_primary_mode=ON [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld log = /data/GreatSQL/mysqld_multi.log mysqladmin = /usr/local/mysql/bin/mysqladmin user=root [mysqld3306] datadir=/data/GreatSQL/mgr01 socket=/data/GreatSQL/mgr01/mysql.sock port=3306 server_id=3306 log-error=/data/GreatSQL/mgr01/error.log loose-group_replication_local_address= "127.0.0.1:33061" [mysqld3307] datadir=/data/GreatSQL/mgr02 socket=/data/GreatSQL/mgr02/mysql.sock port=3307 server_id=3307 log-error=/data/GreatSQL/mgr02/error.log loose-group_replication_local_address= "127.0.0.1:33071" [mysqld3308] datadir=/data/GreatSQL/mgr03 socket=/data/GreatSQL/mgr03/mysql.sock port=3308 server_id=3308 log-error=/data/GreatSQL/mgr03/error.log loose-group_replication_local_address= "127.0.0.1:33081"
创建目录，权限
mkdir -p /data/GreatSQL/{mgr01,mgr02,mgr03}
chown -R mysql.mysql /data/GreatSQL
#初始化 /usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr01 --initialize-insecure --user=mysql /usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr02 --initialize-insecure --user=mysql /usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr03 --initialize-insecure --user=mysql # 启动3个节点 /usr/local/mysql/bin/mysqld_multi start 3306 /usr/local/mysql/bin/mysqld_multi start 3307 /usr/local/mysql/bin/mysqld_multi start 3308 示例： [root@localhost /root]#/usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr01 --initialize-insecure --user=mysql 2023-05-10T07:27:57.398912Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25-17) initializing of server in progress as process 16452 2023-05-10T07:27:57.407728Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-05-10T07:27:59.437333Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2023-05-10T07:28:02.095226Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2 创建用户等
# 这里初始化没有使用密码，直接回车登录 [root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3306 -S /data/GreatSQL/mgr01/mysql.sock root@mysqldb 15:38: [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq'; root@mysqldb 15:40: [(none)]> flush privileges; #另外2个节点一样 [root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3307 -S /data/GreatSQL/mgr02/mysql.sock root@mysqldb 15:38: [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq'; root@mysqldb 15:40: [(none)]> flush privileges; [root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3308 -S /data/GreatSQL/mgr03/mysql.sock root@mysqldb 15:38: [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq'; root@mysqldb 15:40: [(none)]> flush privileges; root@mysqldb 15:40: [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) root@mysqldb 15:40: [(none)]> show master status; +---------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------+ | binlog.000001 | 642 | | | 32218282-ef04-11ed-b942-005056a63376:1-2 | +---------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) root@mysqldb 15:40: [(none)]> show plugins; | clone | ACTIVE | CLONE | mysql_clone.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ root@mysqldb 15:41: [(none)]> reset master; reset slave all; Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) root@mysqldb 15:44: [(none)]> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.01 sec) root@mysqldb 15:45: [(none)]> CREATE USER repl@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'repl.abc'; Query OK, 0 rows affected (0.02 sec) root@mysqldb 15:45: [(none)]> GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO `repl`@`%`; Query OK, 0 rows affected (0.01 sec)
节点2，3
root@mysqldb 15:43: [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq'; Query OK, 0 rows affected (0.02 sec) root@mysqldb 15:44: [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) root@mysqldb 15:44: [(none)]> show master status; +---------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+------------------------------------------+ | binlog.000001 | 643 | | | 627f6c88-ef04-11ed-818c-005056a63376:1-2 | +---------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) root@mysqldb 15:44: [(none)]> reset master; reset slave all; Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) root@mysqldb 15:44: [(none)]> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000001 | 156 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
root@mysqldb 15:45: [(none)]> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) root@mysqldb 15:47: [(none)]> CREATE USER repl@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'repl.abc'; Query OK, 0 rows affected (0.02 sec) root@mysqldb 15:47: [(none)]> GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO `repl`@`%`; Query OK, 0 rows affected (0.01 sec) root@mysqldb 15:47: [(none)]> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)
2 启动 mgr，选择3306为主节点
==节点1 root@mysqldb 15:45: [(none)]> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec) root@mysqldb 15:46: [(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl.abc' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 5 warnings (0.05 sec) root@mysqldb 15:48: [(none)]> set global group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec) root@mysqldb 15:48: [(none)]> start group_replication; Query OK, 0 rows affected (2.23 sec) root@mysqldb 15:48: [(none)]> show master status; +---------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+----------------------------------------+ | binlog.000001 | 483 | | | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1 | +---------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) root@mysqldb 15:48: [(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | PRIMARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ 1 row in set (0.01 sec) root@mysqldb 15:48: [(none)]> set global group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec) ==节点2 root@mysqldb 15:47: [(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl.abc' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 5 warnings (0.05 sec) root@mysqldb 15:49: [(none)]> start group_replication; Query OK, 0 rows affected (3.27 sec) ==节点3 root@mysqldb 15:47: [(none)]> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl.abc' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 5 warnings (0.04 sec) root@mysqldb 15:49: [(none)]> start group_replication; Query OK, 0 rows affected (2.66 sec)
插入数据
== 节点1 root@mysqldb 15:49: [(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) root@mysqldb 15:50: [(none)]> create database greatsql; Query OK, 1 row affected (0.01 sec) root@mysqldb 15:50: [(none)]> use greatsql; Database changed root@mysqldb 15:50: [greatsql]> create table t1(id int primary key); Query OK, 0 rows affected (0.06 sec) root@mysqldb 15:50: [greatsql]> insert into t1 values (rand()*1024), (rand()*1024), (rand()*1024); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 root@mysqldb 15:50: [greatsql]> select * from t1; +-----+ | id | +-----+ | 24 | | 71 | | 283 | +-----+ 3 rows in set (0.00 sec) ==节点2 root@mysqldb 15:49: [(none)]> select * from greatsql.t1; +-----+ | id | +-----+ | 24 | | 71 | | 283 | +-----+ 3 rows in set (0.00 sec) == 节点3 root@mysqldb 15:49: [(none)]> select * from greatsql.t1; +-----+ | id | +-----+ | 24 | | 71 | | 283 | +-----+ 3 rows in set (0.00 sec) root@mysqldb 15:50: [greatsql]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) root@mysqldb 15:51: [greatsql]> select * from performance_schema.replication_connection_status where channel_name = 'group_replication_applier'\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1 SOURCE_UUID: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1 THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000 RECEIVED_TRANSACTION_SET: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-6 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:3 LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2023-05-10 15:49:58.254268 LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2023-05-10 15:49:58.254312 QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000 1 row in set (0.00 sec) root@mysqldb 15:51: [greatsql]> select * from performance_schema.replication_group_member_stats\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 16837049152821789:3 MEMBER_ID: 32218282-ef04-11ed-b942-005056a63376 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 3 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 3 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-6 LAST_CONFLICT_FREE_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:6 COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 2 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 3 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 16837049152821789:3 MEMBER_ID: 627f6c88-ef04-11ed-818c-005056a63376 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 3 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 3 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-6 LAST_CONFLICT_FREE_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:6 COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 4 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 16837049152821789:3 MEMBER_ID: 69b2b717-ef04-11ed-b80f-005056a63376 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 3 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 3 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-6 LAST_CONFLICT_FREE_TRANSACTION: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:6 COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 3 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 3 rows in set (0.01 sec)
3 增加节点
[root@localhost /root]#mkdir -p /data/GreatSQL/mgr04 [root@localhost /root]#chown -R mysql.mysql /data/GreatSQL/mgr04 [root@localhost /root]#vim /etc/my.cnf #需改 loose-group_replication_group_seeds= "127.0.0.1:33061,127.0.0.1:33071,127.0.0.1:33081,127.0.0.1:33091" [mysqld3309] datadir=/data/GreatSQL/mgr04 socket=/data/GreatSQL/mgr04/mysql.sock port=3309 server_id=3309 log-error=/data/GreatSQL/mgr04/error.log loose-group_replication_local_address= "127.0.0.1:33091"
#初始化 [root@localhost /root]#/usr/local/mysql/bin/mysqld --no-defaults --datadir=/data/GreatSQL/mgr04 --initialize-insecure --user=mysql 2023-05-10T08:05:14.695741Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25-17) initializing of server in progress as process 17190 2023-05-10T08:05:14.704384Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-05-10T08:05:16.726689Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2023-05-10T08:05:19.117933Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. [root@localhost /root]#/usr/local/mysql/bin/mysqld_multi start 3309 [root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3309 -S /data/GreatSQL/mgr04/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.25-17 GreatSQL, Release 17, Revision 4733775f703 Copyright (c) 2021-2021 GreatDB Software Co., Ltd Copyright (c) 2009-2021 Percona LLC and/or its affiliates 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. root@mysqldb 16:05: [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc.123.yhq'; Query OK, 0 rows affected (0.01 sec) root@mysqldb 16:06: [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) root@mysqldb 16:06: [(none)]> reset master; reset slave all; Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) # 尽量从SECONDARY节点复制数据，不从PRIMARY节点复制 root@mysqldb 16:06: [(none)]> set global clone_valid_donor_list='127.0.0.1:3307'; Query OK, 0 rows affected (0.00 sec) root@mysqldb 16:07: [(none)]> clone instance from repl@127.0.0.1:3307 identified by 'repl.abc'; ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process). root@mysqldb 16:07: [(none)]> exit Bye #clone结束后，会自动重启mysqld实例 #但因为该实例没有用systemd服务管理起来，所以需要手动启动进程 [root@localhost /root]#ps -ef|grep mysql mysql 16743 1 2 15:33 pts/0 00:01:01 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr01 --socket=/data/GreatSQL/mgr01/mysql.sock --port=3306 --server_id=3306 --log-error=/data/GreatSQL/mgr01/error.log --loose-group_replication_local_address=127.0.0.1:33061 mysql 16800 1 2 15:35 pts/0 00:00:57 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr02 --socket=/data/GreatSQL/mgr02/mysql.sock --port=3307 --server_id=3307 --log-error=/data/GreatSQL/mgr02/error.log --loose-group_replication_local_address=127.0.0.1:33071 mysql 16855 1 3 15:35 pts/0 00:01:00 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr03 --socket=/data/GreatSQL/mgr03/mysql.sock --port=3308 --server_id=3308 --log-error=/data/GreatSQL/mgr03/error.log --loose-group_replication_local_address=127.0.0.1:33081 root 16943 8307 0 15:38 pts/0 00:00:00 /usr/local/mysql/bin/mysql -uroot -p -P3306 -S /data/GreatSQL/mgr01/mysql.sock root 16950 16120 0 15:43 pts/1 00:00:00 /usr/local/mysql/bin/mysql -uroot -p -P3307 -S /data/GreatSQL/mgr02/mysql.sock root 17313 16954 0 16:07 pts/2 00:00:00 grep --color=auto mysql [root@localhost /root]#/usr/local/mysql/bin/mysqld_multi start 3309 [root@localhost /root]#/usr/local/mysql/bin/mysql -uroot -p -P3309 -S /data/GreatSQL/mgr04/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.25-17 GreatSQL, Release 17, Revision 4733775f703 Copyright (c) 2021-2021 GreatDB Software Co., Ltd Copyright (c) 2009-2021 Percona LLC and/or its affiliates 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. root@mysqldb 16:08: [(none)]> select * from greatsql.t1; +-----+ | id | +-----+ | 24 | | 71 | | 283 | +-----+ 3 rows in set (0.01 sec) root@mysqldb 16:08: [(none)]> start group_replication; Query OK, 0 rows affected (2.91 sec) root@mysqldb 16:08: [(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 676c9de0-ef09-11ed-8ba8-005056a63376 | localhost.localdomain | 3309 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ 4 rows in set (0.00 sec)
4 切换，关闭3306主节点[root@localhost /root]#ps -ef|grep mysqlmysql 16743 1 3 15:33 pts/0 00:01:27 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr01 --socket=/data/GreatSQL/mgr01/mysql.sock --port=3306 --server_id=3306 --log-error=/data/GreatSQL/mgr01/error.log --loose-group_replication_local_address=127.0.0.1:33061
mysql 16800 1 3 15:35 pts/0 00:01:22 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr02 --socket=/data/GreatSQL/mgr02/mysql.sock --port=3307 --server_id=3307 --log-error=/data/GreatSQL/mgr02/error.log --loose-group_replication_local_address=127.0.0.1:33071 mysql 16855 1 3 15:35 pts/0 00:01:25 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr03 --socket=/data/GreatSQL/mgr03/mysql.sock --port=3308 --server_id=3308 --log-error=/data/GreatSQL/mgr03/error.log --loose-group_replication_local_address=127.0.0.1:33081 root 16950 16120 0 15:43 pts/1 00:00:00 /usr/local/mysql/bin/mysql -uroot -p -P3307 -S /data/GreatSQL/mgr02/mysql.sock mysql 17327 1 5 16:07 pts/2 00:00:27 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr04 --socket=/data/GreatSQL/mgr04/mysql.sock --port=3309 --server_id=3309 --log-error=/data/GreatSQL/mgr04/error.log --loose-group_replication_local_address=127.0.0.1:33091 root 17481 16954 0 16:16 pts/2 00:00:00 grep --color=auto mysql [root@localhost /root]#kill 16743 #杀掉3306进程 [root@localhost /root]#ps -ef|grep mysql mysql 16800 1 3 15:35 pts/0 00:01:27 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr02 --socket=/data/GreatSQL/mgr02/mysql.sock --port=3307 --server_id=3307 --log-error=/data/GreatSQL/mgr02/error.log --loose-group_replication_local_address=127.0.0.1:33071 mysql 16855 1 3 15:35 pts/0 00:01:29 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr03 --socket=/data/GreatSQL/mgr03/mysql.sock --port=3308 --server_id=3308 --log-error=/data/GreatSQL/mgr03/error.log --loose-group_replication_local_address=127.0.0.1:33081 root 16950 16120 0 15:43 pts/1 00:00:00 /usr/local/mysql/bin/mysql -uroot -p -P3307 -S /data/GreatSQL/mgr02/mysql.sock mysql 17327 1 5 16:07 pts/2 00:00:31 /usr/local/mysql/bin/mysqld --datadir=/data/GreatSQL/mgr04 --socket=/data/GreatSQL/mgr04/mysql.sock --port=3309 --server_id=3309 --log-error=/data/GreatSQL/mgr04/error.log --loose-group_replication_local_address=127.0.0.1:33091 root 17560 16954 0 16:18 pts/2 00:00:00 grep --color=auto mysql #发现已经踢出了33.6节点，另外的3个节点组成一个mgr集群
#自动选择了 mgr02 节点作为新的PRIMARY节点（未设定各节点权重值时，则按照 MEMBER_ID 的顺序依次选主）
root@mysqldb 16:16: [(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 676c9de0-ef09-11ed-8ba8-005056a63376 | localhost.localdomain | 3309 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
在新主节点写入数据
root@mysqldb 16:18: [(none)]> use greatsql; Database changed root@mysqldb 16:19: [greatsql]> insert into t1 values (rand()*1024), (rand()*1024), (rand()*1024); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 root@mysqldb 16:19: [greatsql]> select * from t1; +------+ | id | +------+ | 24 | | 71 | | 283 | | 328 | | 668 | | 1004 | +------+ 6 rows in set (0.00 sec)
启动旧的3306节点
[root@localhost /root]#/usr/local/mysql/bin/mysqld_multi start 3306 [root@localhost /data/GreatSQL/mgr01]#/usr/local/mysql/bin/mysql -uroot -p -P3306 -S /data/GreatSQL/mgr01/mysql.sock root@mysqldb 16:21: [(none)]> start group_replication; Query OK, 0 rows affected (2.97 sec) root@mysqldb 16:22: [(none)]> select * from greatsql.t1; +------+ | id | +------+ | 24 | | 71 | | 283 | | 328 | | 668 | | 1004 | +------+ 6 rows in set (0.00 sec) root@mysqldb 16:22: [greatsql]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 676c9de0-ef09-11ed-8ba8-005056a63376 | localhost.localdomain | 3309 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
手动切换选主
手动切换PRIMARY节点 root@mysqldb 16:59: [(none)]> select group_replication_set_as_primary('32218282-ef04-11ed-b942-005056a63376'); +--------------------------------------------------------------------------+ | group_replication_set_as_primary('32218282-ef04-11ed-b942-005056a63376') | +--------------------------------------------------------------------------+ | Primary server switched to: 32218282-ef04-11ed-b942-005056a63376 | +--------------------------------------------------------------------------+ 1 row in set (1.01 sec) root@mysqldb 16:59: [(none)]> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 32218282-ef04-11ed-b942-005056a63376 | localhost.localdomain | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 627f6c88-ef04-11ed-818c-005056a63376 | localhost.localdomain | 3307 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 676c9de0-ef09-11ed-8ba8-005056a63376 | localhost.localdomain | 3309 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 69b2b717-ef04-11ed-b80f-005056a63376 | localhost.localdomain | 3308 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
