Introduction and installation of MySQL Shell

Introduction and installation of MySQL Shell

01 ReplicaSet Architecture

In the previous article, we discussed the basic concepts and limitations of ReplicaSet and the basic knowledge before deployment. Today we will look at MySQL Shell, one of the two important components in the InnoDB ReplicaSet deployment process. In order to better understand MySQL Shell, a picture is drawn as follows:

From the above figure, it is not difficult to see that MySQL Shell is the entry point for operation and maintenance personnel to manage the underlying MySQL nodes, that is, the place where the DBA executes management commands, and MySQL Router is the entry point for application connections. Its existence makes the underlying architecture transparent to the application. The application only needs to connect to MySQL Router to interact with the underlying database, and the master-slave architecture of the database is recorded in the original information of MySQL Router.

Today, we will mainly look at the process of building MySQL Shell.

02 Introduction and installation of MySQL Shell

MySQL Shell is a client tool used to manage Innodb Cluster or Innodb ReplicaSet. It can be simply understood as an entry point to ReplicaSet.

Its installation process is relatively simple: just download the corresponding version of MySQL Shell from the MySQL official website. The address is as follows:

https://downloads.mysql.com/archives/shell/

Version 8.0.20 is used here

After downloading, unzip it on the Linux server, and then you can connect to the online MySQL service through this MySQL Shell.

My online MySQL addresses are:

192.168.1.10 5607

192.168.1.20 5607

You can connect to the MySQL service directly through the following command:

/usr/local/mysql-shell-8.0.20/bin/mysqlsh '$user'@'$host':$port --password=$pass

The log after successful connection is as follows:

MySQL Shell 8.0.20

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to '[email protected]:5607'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 831
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL 192.168.1.10:5607 ssl JS >
  MySQL 192.168.1.10:5607 ssl JS >
  MySQL 192.168.1.10:5607 ssl JS >
  MySQL 192.168.1.10:5607 ssl JS >

03 MySQL Shell connects to the database and creates a ReplicaSet

The above has introduced how to use MySQL Shell to connect to the database. Now let's look at how to use MySQL Shell to create a ReplicaSet:

1. First, use the dba.configureReplicaSetInstance command to configure the replica set and create an administrator for the replica set.

MySQL 192.168.1.10:5607 ssl JS > dba.configureReplicaSetInstance('[email protected]:5607',{clusterAdmin:"'rsadmin'@'%'"})
Configuring MySQL instance at 192.168.1.10:5607 for use in an InnoDB ReplicaSet...

This instance reports its own address as 192.168.1.10:5607
WARNING: User 'rsadmin'@'%' already exists and will not be created. However, it is missing privileges.
The account 'rsadmin'@'%' is missing privileges required to manage an InnoDB cluster:
GRANT REPLICATION_APPLIER ON *.* TO 'rsadmin'@'%' WITH GRANT OPTION;
Dba.configureReplicaSetInstance: The account 'root'@'192.168.1.10' is missing privileges required to manage an InnoDB cluster. (RuntimeError)

As you can see, in the above command, we configured an instance of the replica set: 192.168.1.10:5607, and created an administrator account rsadmin, and this administrator has clusterAdmin permissions.

In the returned result, there is an error message, which prompts us that the root account we logged in lacks the replication_applier permission, so the root account cannot be used to authorize the rsadmin account. After we add replication_applier permissions to the root account, we re-execute the above command and the results are as follows:

MySQL 192.168.1.10:5607 ssl JS > dba.configureReplicaSetInstance('[email protected]:5607',{clusterAdmin:"'rsadmin'@'%'"})
Configuring MySQL instance at 192.168.1.10:5607 for use in an InnoDB ReplicaSet...

This instance reports its own address as 192.168.1.10:5607
User 'rsadmin'@'%' already exists and will not be created.

The instance '192.168.1.10:5607' is valid to be used in an InnoDB ReplicaSet.

The instance '192.168.1.10:5607' is already ready to be used in an InnoDB ReplicaSet.

This time the execution was successful.

We log in to the underlying 192.168.1.10 and check the rsadmin account. We can find that the account has been generated. The information is as follows:

select user,host,concat(user,"@'",host,"'"),authentication_string from mysql.user where user like "%%rsadmin";
+---------+------+----------------------------+-------------------------------------------+
| user | host | concat(user,"@'",host,"'") | authentication_string |
+---------+------+----------------------------+-------------------------------------------+
| rsadmin | % | rsadmin@'%' | *2090992BE9B9B27D89906C6CB13A8512DF49E439 |
+---------+------+----------------------------+-------------------------------------------+
1 row in set (0.00 sec)

show grants for rsadmin@'%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for rsadmin@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `rsadmin`@`%` WITH GRANT OPTION |
| GRANT BACKUP_ADMIN,CLONE_ADMIN,PERSIST_RO_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `rsadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `rsadmin`@`%` 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 `rsadmin`@`%` 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 `rsadmin`@`%` 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 `rsadmin`@`%` WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

Note that if the replica set instance we join is the currently connected instance, we can also use a simpler syntax:

dba.configureReplicaSetInstance('',{clusterAdmin:"'rsadmin'@'%'"})

2. Use the dba.createReplicaSet command to create a replica set and save the result in a variable, as follows:

MySQL 192.168.1.10:5607 ssl JS > var rs = dba.createReplicaSet("yeyz_test")
A new replicaset with instance '192.168.1.10:5607' will be created.

* Checking MySQL instance at 192.168.1.10:5607

This instance reports its own address as 192.168.1.10:5607
192.168.1.10:5607: Instance configuration is suitable.

* Updating metadata...

ReplicaSet object successfully created for 192.168.1.10:5607.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

As you can see, we created a replica set of yeyz_test and saved the results in the variable rs.

3. Use rs.status() to view the current replica set members

MySQL 192.168.1.10:5607 ssl JS > rs.status()
{
    "replicaSet": {
        "name": "yeyz_test",
        "primary": "192.168.1.10:5607",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "192.168.1.10:5607": {
                "address": "192.168.1.10:5607",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}

Here, you can see that the current ReplicaSet already has the instance 192.168.1.10:5607, its status is available, and its role is Primary.

4. Now we use the rs.addInstance command to add the second node and use rs.status to check the status.

It should be noted here that when adding the second node, there is a data synchronization process, and there are two strategies for this data synchronization:

Strategy 1: Full recovery

Use the MySQL Clone component, and then use the clone snapshot to overwrite all data on the new instance. This method is very suitable for adding a blank instance to an Innodb replica set.

Strategy 2: Incremental recovery

It relies on MySQL's replication feature to copy all lost transactions to the new instance, which is a quick process if there are few transactions on the new instance. This method requires that there is at least one instance in the cluster that stores the binlogs of the missing transactions. If the binlogs of the missing transactions have been cleared, this method cannot be used.

When an instance joins a cluster, MySQL Shell will automatically try to select an appropriate strategy to synchronize data without human intervention. If it cannot safely select a synchronization method, it will provide the DBA with an option to synchronize data through clone or incremental synchronization.

In the following example, data is synchronized by automatically selecting the incremental synchronization method:

MySQL 192.168.1.10:5607 ssl JS > rs.addInstance("192.168.1.20:5607")
WARNING: Concurrent execution of ReplicaSet operations is not supported because the required MySQL lock service UDFs could not be installed on instance '10.41.28.127:5607'.
Make sure the MySQL lock service plugin is available on all instances if you want to be able to execute some operations at the same time. The operation will continue without concurrent execution support.

Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as 192.168.1.20:5607
192.168.1.20:5607: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of '192.168.1.20:5607' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Incremental state recovery was selected because it seems to be safely usable.

* Updating topology
** Configuring 192.168.1.20:5607 to replicate from 192.168.1.10:5607
** Waiting for new instance to synchronize with PRIMARY...

The instance '192.168.1.20:5607' was added to the replicaset and is replicating from 192.168.1.20:5607.

MySQL 192.168.1.10:5607 ssl JS >
MySQL 192.168.1.10:5607 ssl JS > rs.status()
{
    "replicaSet": {
        "name": "yeyz_test",
        "primary": "192.168.1.10:5607",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "192.168.1.10:5607": {
                "address": "192.168.1.10:5607",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "192.168.1.20:5607": {
                "address": "192.168.1.20:5607",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for master to send event",
                    "replicationLag": null
                },
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}

After adding the second node, you can see that when you use rs.status again to view the structure of the replica set, you can see that the Secondary node has appeared, which is the newly added 192.168.1.20:5607

Of course, we can use the following commands to view more detailed output:

rs.status({extended:0})

rs.status({extended:1})

rs.status({extended:2})

Different levels display different information. The higher the level, the more detailed the information.

I have to mention a small bug here. The official document recommends writing it as follows:

ReplicaSet.status(extended=1)

The original text is as follows:

The output of ReplicaSet.status(extended=1) is very similar to Cluster.status(extended=1), but the main difference is that the replication field is always available because InnoDB ReplicaSet relies on MySQL Replication all of the time, unlike InnoDB Cluster which uses it during incremental recovery. For more information on the fields, see Checking a cluster's Status with Cluster.status().

However, in actual operation, this writing method will report an error, as follows:

MySQL 192.168.1.10:5607 ssl JS > sh.status(extended=1)
You are connected to a member of replicaset 'yeyz_test'.
ReplicaSet.status: Argument #1 is expected to be a map (ArgumentError)

I don't know if it's a bug.

5. After building the replica set, check the meta-information library table of the primary node and write data to the primary to see if the data can be synchronized.

[(none)] 17:41:10>show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
|mysql |
|mysql_innodb_cluster_metadata|
| performance_schema |
|sys|
| zjmdmm |
+-------------------------------+
6 rows in set (0.01 sec)

[(none)] 17:41:29>use mysql_innodb_cluster_metadata
Database changed
[mysql_innodb_cluster_metadata] 17:45:12>show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| async_cluster_members |
| async_cluster_views |
| clusters |
| instances |
| router_rest_accounts |
| routers |
| schema_version |
| v2_ar_clusters |
| v2_ar_members |
| v2_clusters |
| v2_gr_clusters |
| v2_instances |
| v2_router_rest_accounts |
| v2_routers |
| v2_this_instance |
+-----------------------------------------+
15 rows in set (0.00 sec)

[mysql_innodb_cluster_metadata] 17:45:45>select * from routers;
Empty set (0.00 sec)

[(none)] 17:45:52>create database yeyazhou;
Query OK, 1 row affected (0.00 sec)

As you can see, there is a meta information database mysql_innodb_cluster_metadata on the Primary node, which stores some original information. We checked the router table and found that there was no data in it because we did not configure MySQL Router. The configuration process of MySQL Router will be described in the following articles.

Create a database named yeyazhou on the Primary node, and you will find that the corresponding database has also appeared on the slave node.

192.168.1.20 [(none)] 17:41:41>show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
|mysql |
|mysql_innodb_cluster_metadata|
| performance_schema |
|sys|
| yeyazhou |
| zjmdmm |
+-------------------------------+
7 rows in set (0.00 sec)

This indicates that the replication relationship of the replica set is correct.

At this point, the entire process of connecting MySQL Shell to the MySQL instance and creating a ReplicatSet is complete.

The next article describes the process of setting up MySQL Router and how to use MySQL Router to access the underlying database.

The above is the introduction of MySQL Shell and the details of its installation. For more information about MySQL Shell, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Shell script to monitor MySQL master-slave status
  • How to install MySQL 5.7.29 with one click using shell script
  • MySQL common backup commands and shell backup scripts sharing
  • Shell script to backup MySQL database data regularly and retain it for a specified time
  • Shell script automates the creation of basic configuration of virtual machines: tomcat--mysql--jdk--maven
  • Shell script to implement mysql scheduled backup, deletion and recovery functions
  • A small Shell script to accurately count the number of rows in each Mysql table
  • Create MySQL database accounts on the server in batches through Shell scripts
  • How to add index to mysql using shell script
  • Kill a bunch of MySQL databases with just a shell script like this (recommended)
  • How to use shell scripts to automatically back up multiple MySQL databases every day

<<:  Improvements to the web server to improve website performance

>>:  25 Examples of Using Circular Elements in Web Design

Recommend

Detailed explanation of the difference between "/" and "~" in Linux

"/" is the root directory, and "~&...

Docker deployment of Flask application implementation steps

1. Purpose Write a Flask application locally, pac...

Vue implements the sample code of adding, deleting, modifying and checking the tree structure

In fact, many companies have functions similar to...

How to use fdisk to partition disk in Linux

Commonly used commands for Linux partitions: fdis...

Linux file management command example analysis [permissions, create, delete, copy, move, search, etc.]

This article describes the Linux file management ...

MySQL green decompression version installation and configuration steps

Steps: 1. Install MySQL database 1. Download the ...

Nginx installation detailed tutorial

1. Brief Introduction of Nginx Nginx is a free, o...

Example of configuring multiple SSL certificates for a single Nginx IP address

By default, Nginx supports only one SSL certifica...

JavaScript design pattern learning adapter pattern

Table of contents Overview Code Implementation Su...

Detailed explanation of NodeJS modularity

Table of contents 1. Introduction 2. Main text 2....

Vue Element front-end application development conventional Element interface components

Table of contents 1. List interface and other mod...

Solution to win10 without Hyper-V

Are you still looking for a way to enable Hyper-v...

A brief discussion on this.$store.state.xx.xx in Vue

Table of contents Vue this.$store.state.xx.xx Get...

Detailed explanation of the solution for CSS-opacity child elements to inherit the transparency of parent elements

In the process of writing the project page, I enc...

Solve the problem of insufficient docker disk space

After the server where Docker is located has been...