MySQL 8.0.31 单机部署3节点mgr并使用mysqlsh接管

原创柚子身上依云和恩墨交付战队 2022-10-23

1741

适用范围

mysql8.0版本数据库

部署步骤

使用mysqld_multi管理多个mysql实例

1.实例规划

IP	HOSTNAME	MySQL PORT	Mgr PORT
192.168.11.88	mgrhost	3306	33061
		3307	33071
		3308	33081

2.MySQL环境安装

2.1.软件安装

下载路径: https://dev.mysql.com/downloads/mysql/

# tar -xf mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz -C /usr/local
# cd /usr/local
# mv mysql-8.0.31-linux-glibc2.12-x86_64 mysql

2.2.配置参数文件

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/data/mysql/multi.log
password=root

[mysqld3306]
port=3306
server-id=101
user=mysql
datadir=/data/mysql/mysql3306
socket=/data/mysql/mysql3306/mysql.sock
log_error=/data/mysql/mysql3306/mysqld.err
gtid_mode=on
enforce_gtid_consistency=on
mysqlx_port=33060
mysqlx_socket=/data/mysql/mysql3306/mysqlx.sock
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
group_replication_local_address= "192.168.11.88:33061"
group_replication_group_seeds= "192.168.11.88:33061,192.168.11.88:33071,192.168.11.88:33081"
report_host=192.168.11.88
group_replication_start_on_boot=off

[mysqld3307]
port=3307
server-id=102
user=mysql
datadir=/data/mysql/mysql3307
socket=/data/mysql/mysql3307/mysql.sock
log_error=/data/mysql/mysql3307/mysqld.err
gtid_mode=on
enforce_gtid_consistency=on
mysqlx_port=33070
mysqlx_socket=/data/mysql/mysql3307/mysqlx.sock
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
group_replication_local_address= "192.168.11.88:33071"
group_replication_group_seeds= "192.168.11.88:33061,192.168.11.88:33071,192.168.11.88:33081"
report_host=192.168.11.88
group_replication_start_on_boot=off

[mysqld3308]
port=3308
server-id=103
user=mysql
datadir=/data/mysql/mysql3308
socket=/data/mysql/mysql3308/mysql.sock
log_error=/data/mysql/mysql3308/mysqld.err
gtid_mode=on
enforce_gtid_consistency=on
mysqlx_port=33080
mysqlx_socket=/data/mysql/mysql3308/mysqlx.sock
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
group_replication_local_address= "192.168.11.88:33081"
group_replication_group_seeds= "192.168.11.88:33061,192.168.11.88:33071,192.168.11.88:33081"
report_host=192.168.11.88
group_replication_start_on_boot=off

2.3.创建用户及授权

# mkidr -p /data/mysql/mysql3306
# mkidr -p /data/mysql/mysql3307
# mkidr -p /data/mysql/mysql3308
# useradd -s /bin/false mysql
# chown -R /usr/local/mysql 
# chown -R /data/mysql

2.4.初始化mysql数据目录

# mysqld --initialize --user=mysql --datadir=/data/mysql/mysql3306  --记录初始化密码
# mysqld --initialize --user=mysql --datadir=/data/mysql/mysql3307  --记录初始化密码
# mysqld --initialize --user=mysql --datadir=/data/mysql/mysql3308  --记录初始化密码

2.5.启动mysql实例

# mysqld_multi start 3306-3308

2.6.修改密码

# mysql -uroot -P3306 -S/data/mysql/mysql3306/mysql.sock -p
mysql> alter user root@'localhost' identified by 'root@123';
# mysql -uroot -P3307 -S/data/mysql/mysql3307/mysql.sock -p
mysql> alter user root@'localhost' identified by 'root@123';
# mysql -uroot -P3308 -S/data/mysql/mysql3308/mysql.sock -p
mysql> alter user root@'localhost' identified by 'root@123';

3.配置mgr集群

Step 3.1-3.3 登录分别登录3306-3308 mysql实例执行

3.1.检查组复制插件

配置文件已经配置下面参数,正常启动已经加载完毕:
plugin_load_add=‘group_replication.so’

mysql>  select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,LOAD_OPTION from information_schema.plugins where PLUGIN_NAME in('clone','group_replication');
+-------------------+----------------+---------------+-------------+
| PLUGIN_NAME       | PLUGIN_VERSION | PLUGIN_STATUS | LOAD_OPTION |
+-------------------+----------------+---------------+-------------+
| group_replication | 1.1            | ACTIVE        | ON          |
+-------------------+----------------+---------------+-------------+
--如果加载有问题,需要排查日志进行解决,或者手工加载,手工加载方式如下:
mysql> INSTALL PLUGIN group_replication SONAME'group_replication.so';

3.2.创建复制用户

mysql> create user repl@'%' identified 'repl';
mysql> grant replication slave, backup_admin ON *.* TO `repl`@`%`;
mysql> reset master;reset slave all;

3.3.创建复制通道

mysql> change replication source to source_user='repl',source_password='repl' for channel 'group_replication_recovery';

3.4.引导主节点

引导3306实例为主节点

mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
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 | 198b4ccd-5051-11ed-abfb-0050568aa1b4 | 192.168.11.88 |        3306 | ONLINE       | PRIMARY     | 8.0.31         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

3.5.启动备节点

3307、3308节点执行

mysql> start group_replication;

3.6.检查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 | 198b4ccd-5051-11ed-abfb-0050568aa1b4 | 192.168.11.88 |        3306 | ONLINE       | PRIMARY     | 8.0.31         | XCom                       |
| group_replication_applier | 366f4d9e-5051-11ed-b4e6-0050568aa1b4 | 192.168.11.88 |        3307 | ONLINE       | SECONDARY   | 8.0.31         | XCom                       |
| group_replication_applier | 3bf41a79-5051-11ed-b8e6-0050568aa1b4 | 192.168.11.88 |        3308 | ONLINE       | SECONDARY   | 8.0.31         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

4.0.使用mysqlsh接管

4.1.安装mysql shell

rpm包方式安装mysql shell

# rpm -ivh mysql-shell-8.0.31-1.el7.x86_64.rpm

4.2.接管mgr并检查运行状态

# mysqlsh --uri=repl@192.168.11.88:3306
 MySQL  192.168.11.88:3306 ssl  JS >  var c=dba.createCluster('MGR1');
A new InnoDB cluster will be created on instance '192.168.11.88:3306'.

You are connected to an instance that belongs to an unmanaged replication group.
Do you want to setup an InnoDB cluster based on this replication group? [Y/n]: Y
Creating InnoDB cluster 'MGR1' on '192.168.11.88:3306'...

Adding Seed Instance...
Adding Instance '192.168.11.88:3306'...
Adding Instance '192.168.11.88:3307'...
Adding Instance '192.168.11.88:3308'...
Cluster successfully created based on existing replication group.
 MySQL  192.168.11.88:3306 ssl  JS > c.status()
{
    "clusterName": "MGR1", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.11.88:3306", 
        "ssl": "DISABLED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.11.88:3306": {
                "address": "192.168.11.88:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.31"
            }, 
            "192.168.11.88:3307": {
                "address": "192.168.11.88:3307", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.31"
            }, 
            "192.168.11.88:3308": {
                "address": "192.168.11.88:3308", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.31"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "192.168.11.88:3306"
}

4.3.查看mysql shell接管后的信息

mysql_innodb_cluster_metadata库为mysql shell接管后创建的元数据库；

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+

用户mysql_innodb_cluster_101、mysql_innodb_cluster_102、mysql_innodb_cluster_103是使用mysql shell接管mgr后创建的三个用户，用户集群间同步连接使用。
其中用户名的后缀 101、102、103是server-id。

mysql> select user,host,plugin from mysql.user;
+--------------------------+-----------+-----------------------+
| user                     | host      | plugin                |
+--------------------------+-----------+-----------------------+
| mysql_innodb_cluster_101 | %         | caching_sha2_password |
| mysql_innodb_cluster_102 | %         | caching_sha2_password |
| mysql_innodb_cluster_103 | %         | caching_sha2_password |
| repl                     | %         | caching_sha2_password |
| mysql.infoschema         | localhost | caching_sha2_password |
| mysql.session            | localhost | caching_sha2_password |
| mysql.sys                | localhost | caching_sha2_password |
| root                     | localhost | caching_sha2_password |
+--------------------------+-----------+-----------------------+

检查接管参数
“adopted”: 1 说明mgr是通过手工安装并使用mysql shell进行的接管;如果"adopted": 0 说明mgr本身就是通过mysql shell进行的安装。

mysql> use mysql_innodb_cluster_metadata
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> select * from clusters\G
*************************** 1. row ***************************
    cluster_id: d4222ec1-52b4-11ed-9023-0050568aa1b4
  cluster_name: MGR1
   description: Default Cluster
       options: NULL
    attributes: {"adopted": 1, "default": true, "opt_gtidSetIsComplete": false, "group_replication_group_name": "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"}
  cluster_type: gr
  primary_mode: pm
router_options: NULL

最后修改时间：2022-10-24 08:53:13

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL 8.0.31 单机部署3节点mgr并使用mysqlsh接管

适用范围

部署步骤

1.实例规划

2.MySQL环境安装

2.1.软件安装

2.2.配置参数文件

2.3.创建用户及授权

2.4.初始化mysql数据目录

2.5.启动mysql实例

2.6.修改密码

3.配置mgr集群

3.1.检查组复制插件

3.2.创建复制用户

3.3.创建复制通道

3.4.引导主节点

3.5.启动备节点

3.6.检查mgr成员运行状态

4.0.使用mysqlsh接管

4.1.安装mysql shell

4.2.接管mgr并检查运行状态

4.3.查看mysql shell接管后的信息

评论