MySQLGroupReplication与MySQLShell的使用指南

# MySQL Group Replication与MySQL Shell的使用指南 ## 一、MySQL Group Replication基础操作 ### 1.1 数据创建与复制验证 首先，我们需要创建数据库、表并插入数据。以下是具体的SQL操作： ```sql mysql> USE test; Database changed mysql> CREATE TABLE test.t1 (id INT PRIMARY KEY, message TEXT NOT NULL); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO test.t1 VALUES (1, 'Chuck is here'); Query OK, 1 row affected (0.01 sec) ``` 为了验证数据是否成功复制到从服务器，我们可以在从服务器上执行查询操作： ```bash $ mysql -uroot -h 127.0.0.1 --port=24802 ... mysql> SELECT * FROM test.t1; +----+----------------+ | id | message | +----+----------------+ | 1 | Chuck is here. | +----+----------------+ 1 row in set (0.00 sec) ``` 建议将`CREATE DATABASE`、`CREATE TABLE`、`INSERT`和`SELECT`这四个SQL命令保存到名为`sample_data.sql`的文件中，后续可用于自动化设置Group Replication。 ### 1.2 故障转移演示 #### 1.2.1 故障模拟 在单主配置中，Group Replication具备自动故障转移功能。我们可以通过停止主服务器的MySQL进程来模拟故障。在Linux系统中，可以通过查看进程ID文件来获取进程ID，然后使用`kill`命令停止进程。示例如下： ```bash $ more ./data/primary/oracle-pc.pid 18019 $ sudo kill -9 18019 ``` 在Windows系统中，可以使用任务管理器来停止进程。 #### 1.2.2 查看组健康状态 主服务器停止后，我们可以使用`check_gr.sql`文件中的查询语句来查看组的健康状态： ```bash $ mysql -uroot -h 127.0.0.1 --port=24802 -e "source check_gr.sql" ``` 查询结果示例如下： ```plaintext *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 2854aecd-4330-11e8-abb6-d4258b76e981 MEMBER_HOST: oracle-pc MEMBER_PORT: 24802 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.11 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 2ecd9f66-4330-11e8-90fe-d4258b76e981 MEMBER_HOST: oracle-pc MEMBER_PORT: 24803 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.11 *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 3525b7be-4330-11e8-80b1-d4258b76e981 MEMBER_HOST: oracle-pc MEMBER_PORT: 24804 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.11 +--------------------------------------+-------------+-------------+ | member_id | member_host | member_port | +--------------------------------------+-------------+-------------+ | 2854aecd-4330-11e8-abb6-d4258b76e981 | oracle-pc | 24802 | +--------------------------------------+-------------+-------------+ ``` 从结果可以看出，组自动选择了一个新的主服务器（端口为24802），并且写入能力没有损失。但需要注意的是，Group Replication所能容忍的故障数量是有限的，超过这个限制后，组可能无法成功进行故障转移。 ### 1.3 DevOps脚本 #### 1.3.1 启动脚本 为了方便地启动和停止Group Replication实验环境，我们可以使用脚本进行自动化操作。以下是一个适用于Linux系统的启动脚本`setup_gr.sh`： ```bash #!/bin/sh # This file contains the setup commands used to start the tests for using MEB' # with GR. Specifically, this file contains commands to start (4) mysqld # instances and establish group replication among them. # # Note: All of the files reside in a local directory such as /home/cbell/ # gr_linux. If you wish to run these commands, substitute the correct # directory in the commands. # # Note: Change the user to your user account or appropriate surrogate. # # The instances are primary (primary), secondary1, secondary2, secondary3 # (secondaries). Each is started with a corresponding config file, which is # expected to be in the base directory. Each instance uses a different # port but runs on the local machine. # # The steps include: # 1) initialize the data directories # 2) launch all mysqld instances # 3) install the GR plugin # 4) create the replication user # 5) start GR # 6) check GR # 7) create initial data echo ====== Step 1 of 7: INITIALIZE DATA DIRECTORIES ====== cd /home/cbell/gr_linux rm -rf /home/cbell/gr_linux/data mkdir /home/cbell/gr_linux/data mysqld --no-defaults --user=cbell --initialize-insecure --basedir=/home/ cbell/source/git/mysql-bug-staging/build/ --datadir=/home/cbell/gr_linux/ data/primary mysqld --no-defaults --user=cbell --initialize-insecure --basedir=/home/ cbell/source/git/mysql-bug-staging/build/ --datadir=/home/cbell ```