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

The following mainly brings you the solution of using InnoDB Cluster to solve the high availability of MySQL database. I hope these contents can bring you practical use, which is also the main purpose of this article that I edit to solve the high availability of MySQL database using InnoDB Cluster. All right, don't talk too much nonsense, let's just read the following.

MySQL InnoDB Cluster provides a complete high-availability solution for MySQL. MySQL Shell includes AdminAPI, which makes it easy to configure and manage at least three MySQL CVM instance groups as InnoDB clusters. Each MySQL CVM instance runs MySQL Group Replication, which provides a mechanism for replicating data in the innodb cluster with built-in failover. MySQL Router can automatically configure itself according to the cluster you deploy to transparently connect the client application to the CVM instance. If the CVM instance fails unexpectedly, the cluster will be reconfigured automatically. In the default single-master mode, the InnoDB cluster has a read-write CVM instance-the primary CVM. Multiple secondary CVM instances are copies of the primary CVM. If the primary CVM fails, the secondary CVM will be automatically promoted to the role of the primary CVM. MySQL Router detects this and forwards the client application to the new primary cloud server.

[root@wallet01] # mysql- uroot-pEnter password:mysql > grant all privileges on *. * to 'root'@'wallet01' identified by' abcd@1234';Query OK, 0 rows affected, 1 warning (0.05 sec) mysql > flush privileges;Query OK, 0 rows affected (0.06 sec) # yum install-y mysql-shell [root@wallet01] # mysqlsh-- log-level=DEBUG3MySQL Shell 8.0.18Copyright (c) 2016, 2019, 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. MySQL JS > shell.connect ('root@wallet01:3306') Creating a session to' root@wallet01:3306'Please provide the password for 'root@wallet01:3306': * Save password for' root@wallet01:3306'? [y] es/ [N] o / ne [v] er (default No): yFetching schema names for autocompletion... Press ^ C to stop.Your MySQL connection id is 290Server version: 5.7.27-log MySQL Community Server (GPL) No default schema selected; type\ use to set one. MySQL wallet01:3306 JS > var cluster = dba.createCluster ('walletCluster', {adoptFromGR: true}) A new InnoDB cluster will be created based on the existing replication group on instance' wallet01:3306'.Creating InnoDB cluster 'walletCluster' on' wallet01:3306'...Adding Seed Instance...Adding Instance 'wallet03:3306'...Adding Instance' wallet01:3306'...Adding Instance 'wallet02:3306'...Resetting distributed recovery credentials across the cluster...Cluster successfully created based on existing replication group. MySQL wallet01:3306 JS > cluster.status () {"clusterName": "walletCluster", "defaultReplicaSet": {"name": "default", "primary": "wallet01:3306", "ssl": "DISABLED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure." "topology": {"wallet01:3306": {"address": "wallet01:3306", "mode": "mode W", "readReplicas": {}, "role": "HA", "status": "ONLINE"} "wallet02:3306": {"address": "wallet02:3306", "mode": "readReplicas": {}, "role": "HA", "status": "ONLINE"} "wallet03:3306": {"address": "wallet03:3306", "mode": "readReplicas": {}, "role": "HA", "status": "ONLINE"}} "topologyMode": "Single-Primary"} "groupInformationSourceMember": "wallet01:3306"} MySQL wallet01:3306 JS >\ quitBye! [root@wallet01 ~] # yum install-y mysql-router [root@wallet01 ~] # mysqlrouter-bootstrap root@wallet01:3306-user=mysqlrouter-name=router01Please enter MySQL password for root: WARNING: The MySQL server does not have SSL configured and metadata used by the router may be transmitted unencrypted.# Reconfiguring system MySQL Router instance...- Checking for old Router accounts-No prior Router accounts found- Creating mysql account 'mysql_router1_a8933v9tcn8v'@ '%' for cluster management- Storing account in keyring- Adjusting permissions of generated files- Creating configuration / etc/mysqlrouter/mysqlrouter.conf# MySQL Router 'router01' configured for the InnoDB cluster' walletCluster'After this MySQL Router has been started with the generated configuration $/ etc/init.d/mysqlrouter restartor $mysqlrouter-c / etc/mysqlrouter/mysqlrouter.confthe cluster 'walletCluster' can be reached by connecting to:## MySQL Classic protocol- Read/Write Connections: localhost:6446- Read/Only Connections: localhost:6447## MySQL X protocol- Read/ Write Connections: localhost:64460- Read/Only Connections: localhost:64470Existing configuration backed up to'/ etc/mysqlrouter/mysqlrouter.conf.bak' [root@wallet01 ~] # / etc/init.d/mysqlrouter startStarting mysqlrouter: [OK] [root@wallet01 ~] # / etc/init.d/mysqlrouter statusmysqlrouter (pid 24451) is running... [root@wallet02 ~] # yum install-y mysql-router [root@wallet02 ~ ] # mysqlrouter-- bootstrap root@wallet01:3306-- user=mysqlrouter-- name=router02 [root@wallet02 ~] # / etc/init.d/mysqlrouter startStarting mysqlrouter: [OK] [root@wallet02 ~] # / etc/init.d/mysqlrouter statusmysqlrouter (pid 6906) is running... [root@wallet03 ~] # yum install-y mysql-router [root@wallet03 ~] # mysqlrouter-bootstrap root@wallet01:3306-user=mysqlrouter- -name=router03 [root@wallet03] # / etc/init.d/mysqlrouter startStarting mysqlrouter: [OK] [root@wallet03] # / etc/init.d/mysqlrouter statusmysqlrouter (pid 18081) is running...

For the above on the use of InnoDB Cluster to solve the high availability of MySQL database solutions, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.