MySQL innodb cluster setup 实践

最新推荐文章于 2026-06-19 12:43:47 发布

原创最新推荐文章于 2026-06-19 12:43:47 发布 · 1.1k 阅读

25 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #innodb cluster

基于之前创建的多实例，进一步实践下innodb cluster的配置。

Mysqld_multi manage multi instances 实践-CSDN博客

start the instances

[root@oraclelinux ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3310 is running

MySQL server from group: mysqld3311 is running

MySQL server from group: mysqld3312 is running

[root@oraclelinux ~]#

Account setup

create the account on each instances

mysql> create user 'innodbcluster'@'oraclelinux' identified by 'Qwe000123';

Query OK, 0 rows affected (0.03 sec)

mysql> grant all on *.* to 'innodbcluster'@'oraclelinux';

Query OK, 0 rows affected (0.02 sec)

Configure all the instances

[root@oraclelinux ~]# mysqlsh --mysql -uinnodbcluster -h oraclelinux -p -P 3310

Please provide the password for 'innodbcluster@oraclelinux:3310': *********

Save password for 'innodbcluster@oraclelinux:3310'? [Y]es/[N]o/Ne[v]er (default No): y

MySQL Shell 8.0.42

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.

Creating a Classic session to 'innodbcluster@oraclelinux:3310'

Fetching schema names for auto-completion... Press ^C to stop.

Your MySQL connection id is 21

Server version: 8.0.42 MySQL Community Server - GPL

No default schema selected; type \use <schema> to set one.

MySQL oraclelinux:3310 ssl JS > dba.configureinstance();

invokeMember (configureinstance) on org.graalvm.polyglot.nativeapi.PolyglotNativeAPI$PolyglotProxyObject@eaf9d31 failed due to: Unknown identifier: configureinstance (TypeError)

MySQL oraclelinux:3310 ssl JS > dba.configureInstance();

Configuring local MySQL instance listening at port 3310 for use in an InnoDB cluster...

ERROR: The account 'innodbcluster'@'oraclelinux' is missing privileges required to manage an InnoDB cluster:

--- 发现权限不够，赋予权限 try again

mysql> grant all on *.* to 'innodbcluster'@'oraclelinux' with grant option;

Query OK, 0 rows affected (0.02 sec)

MySQL oraclelinux:3310 ssl JS > dba.configureInstance();

Configuring local MySQL instance listening at port 3310 for use in an InnoDB cluster...

This instance reports its own address as oraclelinux:3310

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

ERROR: User 'innodbcluster' can only connect from 'oraclelinux'. 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 'innodbcluster' with same grants and password

2) Create a new admin account for InnoDB cluster with minimal required grants

3) Ignore and continue

4) Cancel

Please select an option [1]: 3

applierWorkerThreads will be set to the default value of 4.

NOTE: Some configuration options need to be fixed:

+----------------------------------------+---------------+----------------+--------------------------------------------------+

+----------------------------------------+---------------+----------------+--------------------------------------------------+

| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |

| gtid_mode | OFF | ON | Update read-only variable and restart the server |

+----------------------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.

Do you want to perform the required configuration changes? [y/n]: y

Do you want to restart the instance after configuring it? [y/n]: y

Configuring instance...

WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release. (Code 1287).

The instance 'oraclelinux:3310' was configured to be used in an InnoDB cluster.

Restarting MySQL...

NOTE: MySQL server at oraclelinux:3310 was restarted.

MySQL oraclelinux:3310 ssl JS >

Create cluster & add members

MySQL oraclelinux:3310 ssl JS > var cluster = dba.createCluster('newcluster')

A new InnoDB Cluster will be created on instance 'oraclelinux:3310'.

Validating instance configuration at oraclelinux:3310...

This instance reports its own address as oraclelinux:3310

Instance configuration is suitable.

NOTE: Group Replication will communicate with other members using 'oraclelinux:3310'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

Creating InnoDB Cluster 'newcluster' on 'oraclelinux:3310'...

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.

MySQL oraclelinux:3310 ssl JS > var cluster=dba.getCluster()

MySQL oraclelinux:3310 ssl JS > cluster.addInstance('innodbcluster@oraclelinux:3311')

NOTE: The target instance 'oraclelinux:3311' 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 'oraclelinux:3311' 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'.

Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):

Validating instance configuration at oraclelinux:3311...

This instance reports its own address as oraclelinux:3311

Instance configuration is suitable.

NOTE: Group Replication will communicate with other members using 'oraclelinux:3311'. Use the localAddress option to override.

* Checking connectivity and SSL configuration...

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: oraclelinux:3311 is being cloned from oraclelinux:3310

** Stage DROP DATA: Completed

** Clone Transfer

FILE COPY ############################################################ 100% Completed

PAGE COPY ############################################################ 100% Completed

REDO COPY ############################################################ 100% Completed

NOTE: oraclelinux:3311 is shutting down...

* Waiting for server restart... ready

* oraclelinux:3311 has restarted, waiting for clone to finish...

** Stage RESTART: Completed

* Clone process has finished: 74.70 MB transferred in about 1 second (~74.70 MB/s)

State recovery already finished for 'oraclelinux:3311'

The instance 'oraclelinux:3311' was successfully added to the cluster.

MySQL oraclelinux:3310 ssl JS > cluster.addInstance('innodbcluster@oraclelinux:3312')

NOTE: The target instance 'oraclelinux:3312' 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 'oraclelinux:3312' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

......

Check the cluster status

Configue & start mysqlrouter

[mysql@oraclelinux ~]$ mysqlrouter --bootstrap innodbcluster@oraclelinux:3310 --directory /home/mysql/myroute --conf-use-sockets --user mysql

Please enter MySQL password for innodbcluster:

# Bootstrapping MySQL Router 8.0.42 (MySQL Community - GPL) instance at '/home/mysql/myroute'...

- Creating account(s) (only those that are needed, if any)

- Verifying account (using it to run SQL queries that would be run by Router)

- Storing account in keyring

- Adjusting permissions of generated files

- Creating configuration /home/mysql/myroute/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'newcluster'

After this MySQL Router has been started with the generated configuration

$ mysqlrouter -c /home/mysql/myroute/mysqlrouter.conf

InnoDB Cluster 'newcluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446, /home/mysql/myroute/mysql.sock

- Read/Only Connections: localhost:6447, /home/mysql/myroute/mysqlro.sock

## MySQL X protocol

- Read/Write Connections: localhost:6448, /home/mysql/myroute/mysqlx.sock

- Read/Only Connections: localhost:6449, /home/mysql/myroute/mysqlxro.sock

[mysql@oraclelinux ~]$ ./myroute/start.sh

[mysql@oraclelinux ~]$ PID 3641 written to '/home/mysql/myroute/mysqlrouter.pid'

stopping to log to the console. Continuing to log to filelog

[mysql@oraclelinux ~]$

[mysql@oraclelinux ~]$ netstat -tunlp | grep mysqlrouter

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp 0 0 0.0.0.0:8443 0.0.0.0:* LISTEN 3641/mysqlrouter

tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 3641/mysqlrouter

tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 3641/mysqlrouter

tcp 0 0 0.0.0.0:6448 0.0.0.0:* LISTEN 3641/mysqlrouter

tcp 0 0 0.0.0.0:6449 0.0.0.0:* LISTEN 3641/mysqlrouter

[mysql@oraclelinux ~]$

TEST operations

[root@oraclelinux ~]# mysql -udba -p -P6446 -horaclelinux

Enter password:

ERROR 2003 (HY000): Can't connect to MySQL server on 'oraclelinux:6446' (111)

[root@oraclelinux ~]#

[root@oraclelinux ~]# mysql -udba -p -P6446 -horaclelinux

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2433

Server version: 8.0.42 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases

-> ;

+-------------------------------+

| Database |

+-------------------------------+

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

| test |

+-------------------------------+

6 rows in set (0.00 sec)

mysql> use test

Database changed

mysql> create table t1(id int primary key, name nvarchar(15));

Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql> insert into t1 values (1,'aaa');

Query OK, 1 row affected (0.02 sec)

mysql> select * from t1

-> ;

+----+------+

| id | name |

+----+------+

| 1 | aaa |

+----+------+

1 row in set (0.00 sec)

mysql>

[root@oraclelinux ~]# mysql -udba -p -P6447 -horaclelinux

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 71

Server version: 8.0.42 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+-------------------------------+

| Database |

+-------------------------------+

| information_schema |

| mysql |

| mysql_innodb_cluster_metadata |

| performance_schema |

| sys |

| test |

+-------------------------------+

6 rows in set (0.00 sec)

mysql> use test

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> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t1 |

+----------------+

1 row in set (0.00 sec)

mysql> select * from t1;

+----+------+

| id | name |

+----+------+

| 1 | aaa |

+----+------+

1 row in set (0.01 sec)

mysql> insert into t1 values (2,'bbb');

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

mysql>

restart the server , restart the culster.

MySQL oraclelinux:3310 ssl JS > dba.rebootClusterFromCompleteOutage('newcluster')

Restoring the Cluster 'newcluster' from complete outage...

Cluster instances: 'oraclelinux:3310' (OFFLINE), 'oraclelinux:3311' (OFFLINE), 'oraclelinux:3312' (OFFLINE)

Waiting for instances to apply pending received transactions...

Validating instance configuration at oraclelinux:3310...

This instance reports its own address as oraclelinux:3310

Instance configuration is suitable.

* Waiting for seed instance to become ONLINE...

oraclelinux:3310 was restored.

Validating instance configuration at oraclelinux:3311...

This instance reports its own address as oraclelinux:3311

Instance configuration is suitable.

Rejoining instance 'oraclelinux:3311' to cluster 'newcluster'...

Re-creating recovery account...

NOTE: User 'mysql_innodb_cluster_3311'@'%' already existed at instance 'oraclelinux:3310'. It will be deleted and created again with a new password.

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...

** Transactions replicated ############################################################ 100%

The instance 'oraclelinux:3311' was successfully rejoined to the cluster.

Validating instance configuration at oraclelinux:3312...

This instance reports its own address as oraclelinux:3312

Instance configuration is suitable.

Rejoining instance 'oraclelinux:3312' to cluster 'newcluster'...

Re-creating recovery account...

NOTE: User 'mysql_innodb_cluster_3312'@'%' already existed at instance 'oraclelinux:3310'. It will be deleted and created again with a new password.

* Waiting for the Cluster to synchronize with the PRIMARY Cluster...

** Transactions replicated ############################################################ 100%

The instance 'oraclelinux:3312' was successfully rejoined to the cluster.

The Cluster was successfully rebooted.