Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how MYSQL uses MYSQL Shell to install INNODB Cluster. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

MYSQL 8 has been released for some time, and the official solution for MGR, which is now called MGR, elegant name innodb cluster, has also been available for some time. Although the solution is more than this, the official solution should be familiar with. (note: be sure to use the official community version. If you use the latest 8.019 version of percona and still use mysqlsh, the user rights error will be reported, but this error occurs in the official version 8.014, so using the official solution needs to cooperate with the official community version. In addition, it will be a problem to install the percona version of MYSQL 8.019 in the traditional way, alas)

Basically spent half a day, studied the installation of mysql shell, but also read a lot of other articles, but in fact basically follow the above, the probability of success.

The following is a record of stepping on the pit.

The following installation environments are based on the latest version 8.019 of the official community.

[experimental environment

192.168.198.100

192.168.198.101

192.168.198.102

]

Of course, the official architecture is as follows

As part of the plan, mysql shell, in addition to the main functions mentioned last time, there is another thing that was not mentioned last time, that is, the management and installation of the cluster.

Give your account (here we use admin) the relevant permissions before using mysql shell, otherwise you will not be able to work. If you are not given the relevant permissions, mysql shell will report an error when you use it. (this only refers to when the cluster is installed)

Here are the relevant permissions.

CREATE USER admin@'%' identified by 'admin'

GRANT BACKUP_ADMIN, CLONE_ADMIN, CREATE USER, EXECUTE, FILE, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHUTDOWN, SUPER, SYSTEM_VARIABLES_ADMIN ON *. * TO 'admin'@'%' WITH GRANT OPTION

GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'admin'@'%' WITH GRANT OPTION

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'admin'@'%' WITH GRANT OPTION

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'admin'@'%' WITH GRANT OPTION

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'admin'@'%' WITH GRANT OPTION

We are also operating the mysql innodb cluster account with the above account.

Download the rpm installation package of mysqlshell directly, type mysqlsh directly, and install each MYSQL. If you adopt this solution in the future, mysqlshell will be your necessary tool.

Enter mysql shell, where there are many sets of commands about DBA in mysql shell. As you can see, these commands basically revolve around our innodb cluster.

CheckInstanceConfiguration ()

CreateReplicaSet ()

GetReplicaSet ()

StopSandboxInstance ()

ConfigureInstance ()

DeleteSandboxInstance ()

Help ()

UpgradeMetadata ()

ConfigureLocalInstance ()

DeploySandboxInstance ()

KillSandboxInstance ()

ConfigureReplicaSetInstance ()

DropMetadataSchema ()

RebootClusterFromCompleteOutage ()

CreateCluster ()

GetCluster ()

StartSandboxInstance ()

Let's first check the mysql of our stand-alone to see if there are any problems with our configuration (for innodb cluster)

Dba.checkInstanceConfiguration ('admin@192.168.198.100:3306')

We can clearly see that if I currently have a configuration error in innodb cluster, there is a problem with my current binlog_checksum setting. I need to change it to none

In the case of all ok, you can configure the next step (of course, there is likely to be no OK here, the possible problem is whether GTID is enabled or not, which account you set up is the account for innodb cluster cluster operation, whether you have permission, and whether your enforce_gtid_consistency is open or not. If you don't know all this, Baidu is recommended.)

Let's assume that everything you own is OK.

We operate directly through the admin account just now and enter mysqlsh.

Use the\ connect command to connect to your first mysql innodb cluster, where I connected 100

Dba.createCluster ('cluster') through this command we begin to build our innodb cluster

Var cluster = dba.getCluster ()

Cluster.status ()

At present, there are all the OK here, and the pit is right below, and I have read a round of articles, including Chinese and English, without talking about it.

According to the documentation, let's directly use cluster.addInstance ('admin@mgr2:3306') to add the second node to the cluster.

The error was reported, and the reason was found, because one of my machines was different from the other machines (the minor versions of primary and standby are different, although both are 8.019, but there is no way to use the features of clone between percona and the community version).

OK, we replace all the nodes with the official community version, add the nodes again, and still report an error.

Where is the card, stuck in the clone, this is also the most time-wasting troubleshooting problem, because no matter in the official documents, or in the third-party Chinese, the English documents do not talk about this issue, so it takes a little time.

In fact, we only need to do the following two tasks on the standby node (according to normal logic, the system should have judged before operating clone, but in fact, there is none at all, the process system that led to the operation was cloned, but the other party's machine did not accept the clone file at all)

INSTALL PLUGIN clone SONAME 'mysql_clone.so'

SET GLOBAL clone_valid_donor_list = 'mgr1:3306'

The key is the difference, all nodes should install plug-ins, and then need to be set in the standby node to allow the standby node to accept data from the mgr1 node, otherwise the node cannot be added to the cluster. After finishing these two sentences in mgr 2 mgr 3, the overall work will be very smooth.

So so far, the installation of my cluster through mysql shell has been successful.

The overall sense is that the cluster installation of MYSQL is installed through mysql shell, which is very convenient, but you need to have some basic knowledge.

Simple syntax for 1 JS

2 basic knowledge of CLONE

Otherwise, your installation will be difficult to go smoothly.

Of course, the overall installation process above, as well as the subsequent failover process, there will be loopholes in the above approach.

1 you need to set the parameters of the clone of the whole cluster, so that it will be easy to fail over.

2 each node should add the clone_valid_donor_list of all nodes except itself, otherwise after the failover is successful, the rejoin of the failed node will not be so smooth.

In addition, according to the above operations, the future management of INNODB CLUSTER through mysqlshell + programs will be more convenient than the previous management of MYSQL clusters, and can be more highly automated.

After reading the above, do you have any further understanding of how MYSQL uses MYSQL Shell to install INNODB Cluster? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.