mysql shell deploying mgr

安装 mysql

安装 mysql shell

  • mysql shell 版本推荐与 mysql 版本一致
1
2
3
4
5
6

# 解压
tar -zxvf mysql-shell-8.0.28-linux-glibc2.12-x86-64bit.tar.gz
# 移动解压后目录至安装目录
sudo mv mysql-shell-8.0.28-linux-glibc2.12-x86-64bit /data/
# 建立软连接
sudo ln -sfn /data/mysql-shell-8.0.28-linux-glibc2.12-x86-64bit/ /data/mysql-shell-8.0.28

各节点配置用户(所有节点均需操作)

  • 推荐 plan 1，由于 mysql root 用户默认不允许远程连接，在不额外创建超管用户的情况下，需在各个节点都部署 mysql shell，直接通过 CREATE USER 命令创建 mgr 用户操作更加方便。

plan1：手动创建用户

  • 使用 mysql 自带工具连接 mysql(非 mysql shell)
1

/data/mysql3306/mysql8.0.28/bin/mysql -u root -pPa55wD -S /data/mysql3306/mysql_data/data/mysql_3306.sock
  • 创建用户并配置权限
1
2
3
4
5
6
7

CREATE USER 'mgr-user'@'%' IDENTIFIED BY '123456';
GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `mgr-user`@`%` WITH GRANT OPTION;
GRANT CLONE_ADMIN,CONNECTION_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,ROLE_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `mgr-user`@`%` WITH GRANT OPTION;
GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `mgr-user`@`%` WITH GRANT OPTION;
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata`.* TO `mgr-user`@`%` WITH GRANT OPTION;
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_bkp`.* TO `mgr-user`@`%` WITH GRANT OPTION;
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_previous`.* TO `mgr-user`@`%` WITH GRANT OPTION;

plan2 校验节点 mgr 配置时生成用户

  • 使用 mysql shell 连接数据库
1

/data/mysql-shell-8.0.28/bin/mysqlsh -u root -pPa55wD -S /data/mysql3306/mysql_data/data/mysql_3306.sock
  • 检查实例是否满足 mgr 的需要
    • 使用命令 dba.configureInstance()
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

 MySQL  localhost  JS > dba.configureInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 172.16.2.181:3306

ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

########## 检查不通过，root 用户不允许作为 mgr 用户，提示使用何种方式创建用户
########## 选择第二种，创建新的最小权限用户
Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
########## 交互式输入要创建的用户名/密码
Account Name: mgr-user
Password for new account: ******
Confirm password: ******

applierWorkerThreads will be set to the default value of 4.

The instance '172.16.2.181:3306' is valid to be used in an InnoDB cluster.

Cluster admin user 'mgr_user'@'%' created.
The instance '172.16.2.181:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.

各节点执行 reset master(所有节点均需执行)

  • 使用 mysql 自带工具连接 mysql
1

/data/mysql3306/mysql8.0.28/bin/mysql -u root -pPa55wD -S /data/mysql3306/mysql_data/data/mysql_3306.sock
  • reset master
1

mysql> reset master;

校验实例是否满足安装 MGR 集群的条件

  • 使用上文新创建的 mgr 用户连接实例
1

/data/mysql-shell-8.0.28/bin/mysqlsh  -u mgr-user -p123456 -h 172.16.2.181
  • 校验集群
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

MySQL  172.16.2.181:33060+ ssl  JS > dba.configureInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 172.16.2.181:3306

applierWorkerThreads will be set to the default value of 4.

The instance '172.16.2.181:3306' is valid to be used in an InnoDB cluster.
The instance '172.16.2.181:3306' is already ready to be used in an InnoDB cluster.

Successfully enabled parallel appliers.

构建 MGR

  • mysql shell 连接一个实例
1

/data/mysql-shell-8.0.28/bin/mysqlsh  -u mgr-user -p123456 -h 172.16.2.181
  • 初始化 mgr 节点并赋值给变量
    • TEST-MGR 是集群名字可自定义
    • 如果重连 mysql-shell 此变量会丢失，后续命令无法执行，可通过 var c = dba.getCluster() 命令重新赋值
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

 MySQL  172.16.2.181:33060+ ssl  JS > var c = dba.createCluster('TEST-MGR');
A new InnoDB cluster will be created on instance '172.16.2.181:3306'.

Validating instance configuration at 172.16.2.181:3306...

This instance reports its own address as 172.16.2.181:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '172.16.2.181:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'TEST-MGR' on '172.16.2.181:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

MySQL  172.16.2.181:33060+ ssl  JS > c.addInstance('mgr-user@172.16.2.182:3306');

NOTE: The target instance '172.16.2.182:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of '172.16.2.182:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


############ 交互式选择数据整合方式，使用 clone 从第一个节点全量复制数据
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): clone
Validating instance configuration at 172.16.2.182:3306...

This instance reports its own address as 172.16.2.182:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '172.16.2.182:33061'. Use the localAddress option to override.

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: 172.16.2.182:3306 is being cloned from 172.16.2.181:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed


NOTE: 172.16.2.182:3306 is shutting down...

* Waiting for server restart... ready
* 172.16.2.182:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 73.66 MB transferred in 3 sec (24.55 MB/s)

State recovery already finished for '172.16.2.182:3306'

The instance '172.16.2.182:3306' was successfully added to the cluster.

查看 mgr 状态

  • c.status()
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

MySQL  172.16.2.181:33060+ ssl  JS > c.status()
{
    "clusterName": "TEST-MGR",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "172.16.2.181:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "172.16.2.181:3306": {
                "address": "172.16.2.181:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.28"
            },
            "172.16.2.182:3306": {
                "address": "172.16.2.182:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.28"
            },
            "172.16.2.183:3306": {
                "address": "172.16.2.183:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.28"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "172.16.2.181:3306"
}

其他使用

MySQL Shell接管现存的MGR集群

  • 初始化集群时加上 adoptFromGR:true 选项
    • 但不加也可，mysql shell 会自动检测并询问是否要接管
1

var c=dba.createCluster('MGR1', {adoptFromGr:true});

mysql shell 分页

  • 连接后设置分页
1
2
3

mysqlsh> shell.enablePager();
mysqlsh> shell.options["pager"]="less -i -n -S";
Pager has been set to 'less -i -n -S'.