Follow the instructions (password, encripting password).
MySQL status, start, stop, restart
for u2004s01, u2004s02, u2004s03
yury@u2004s01:~$ systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-01-17 07:05:27 UTC; 9min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 27123 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 27179 (mysqld)
Status: "Server is operational"
Tasks: 37 (limit: 7012)
Memory: 354.6M
CGroup: /system.slice/mysql.service
└─27179 /usr/sbin/mysqld
Jan 17 07:05:25 u2004s01 systemd[1]: Starting MySQL Community Server...
Jan 17 07:05:27 u2004s01 systemd[1]: Started MySQL Community Server.
for u2004s01, u2004s02, u2004s03
yury@u2004s01:~$ systemctl stop mysql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to stop 'mysql.service'.
Authenticating as: yury
Password:
==== AUTHENTICATION COMPLETE ===
yury@u2004s01:~$ systemctl start mysql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to start 'mysql.service'.
Authenticating as: yury
Password:
==== AUTHENTICATION COMPLETE ===
yury@u2004s01:~$ systemctl restart mysql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to restart 'mysql.service'.
Authenticating as: yury
Password:
==== AUTHENTICATION COMPLETE ===
yury@u2004s01:~$ sudo apt-get install mysql-router
...
Preparing to unpack .../mysql-router-community_8.0.27-1ubuntu20.04_amd64.deb ...
Unpacking mysql-router-community (8.0.27-1ubuntu20.04) ...
Selecting previously unselected package mysql-router.
Preparing to unpack .../mysql-router_8.0.27-1ubuntu20.04_amd64.deb ...
Unpacking mysql-router (8.0.27-1ubuntu20.04) ...
Setting up mysql-router-community (8.0.27-1ubuntu20.04) ...
Fixing the data files location (if needed)
Setting up mysql-router (8.0.27-1ubuntu20.04) ...
...
Start and Stop MySQL Shell
for u2004s01, u2004s02, u2004s03
yury@u2004s01:~$ sudo mysqlsh
MySQL Shell 8.0.27
Copyright (c) 2016, 2021, Oracle and/or its affiliates.
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 > \quit
Bye!
Ability of MySQL Shell to connect by specifying instances
MySQL JS > dba.configureInstance('root@localhost:3306', {clusterAdmin: "'clstradmin'@'u2004s0%'"});
Please provide the password for 'root@localhost:3306': **********
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No):
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as u2004s01:3306
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.
Password for new account: **********
Confirm password: **********
applierWorkerThreads will be set to the default value of 4.
NOTE: Some configuration options need to be fixed:
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+----------------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable |
| 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 |
| server_id | 1 | <unique ID> | 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
Cluster admin user 'clstradmin'@'u2004s0%' created.
Configuring instance...
The instance 'u2004s01:3306' was configured to be used in an InnoDB cluster.
Restarting MySQL...
NOTE: MySQL server at u2004s01:3306 was restarted.
Checking Instance Configuration for InnoDB Cluster Usage
MySQL JS > dba.checkInstanceConfiguration('clstradmin@u2004s03:3306')
Please provide the password for 'clstradmin@u2004s03:3306': **********
Save password for 'clstradmin@u2004s03:3306'? [Y]es/[N]o/Ne[v]er (default No):
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as u2004s03:3306
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.
Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected
Checking instance configuration...
Instance configuration is compatible with InnoDB cluster
The instance 'u2004s03:3306' is valid to be used in an InnoDB cluster.
{
"status": "ok"
}
Configuring Production Instances for InnoDB Cluster Usage
Response of var clstrvar = dba.createCluster('tstMySqlClstr')
MySQL u2004s01:3306 ssl JS > var clstrvar = dba.createCluster('tstMySqlClstr')
A new InnoDB cluster will be created on instance 'u2004s01:3306'.
Validating instance configuration at u2004s01:3306...
This instance reports its own address as u2004s01:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'u2004s01:33061'. Use the localAddress option to override.
Creating InnoDB cluster 'tstMySqlClstr' on 'u2004s01: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.
Response of clstrvar.addInstance('clstradmin@u2004s02:3306')
MySQL u2004s01:3306 ssl JS > clstrvar.addInstance('clstradmin@u2004s02:3306')
NOTE: The target instance 'u2004s02: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 'u2004s02: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'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at u2004s02:3306...
This instance reports its own address as u2004s02:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'u2004s02: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: u2004s02:3306 is being cloned from u2004s01:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: u2004s02:3306 is shutting down...
* Waiting for server restart... ready
* u2004s02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s)
Incremental state recovery is now in progress.
* Waiting for distributed recovery to finish...
NOTE: 'u2004s02:3306' is being recovered from 'u2004s01:3306'
* Distributed recovery has finished
The instance 'u2004s02:3306' was successfully added to the cluster.
Response of clstrvar.addInstance('clstradmin@u2004s03:3306')
MySQL u2004s01:3306 ssl JS > clstrvar.addInstance('clstradmin@u2004s03:3306')
NOTE: The target instance 'u2004s03: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 'u2004s03: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'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at u2004s03:3306...
This instance reports its own address as u2004s03:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'u2004s03: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: u2004s03:3306 is being cloned from u2004s01:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: u2004s03:3306 is shutting down...
* Waiting for server restart... ready
* u2004s03:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s)
State recovery already finished for 'u2004s03:3306'
The instance 'u2004s03:3306' was successfully added to the cluster.
sudo mysqlsh
\connect clstradmin@u2004s01:3306
var clstrvar = dba.getCluster('tstMySqlClstr')
clstrvar.setupAdminAccount('cadmin')
MySQL u2004s01:3306 ssl JS > \sql SHOW DATABASES;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
MySQL u2004s01:3306 ssl JS > \sql select host, user, account_locked, password_expired from mysql.user
+-----------+---------------------------------+----------------+------------------+
| host | user | account_locked | password_expired |
+-----------+---------------------------------+----------------+------------------+
| % | cadmin | N | N |
| % | mysql_innodb_cluster_1913940761 | N | N |
| % | mysql_innodb_cluster_2564487235 | N | N |
| % | mysql_innodb_cluster_35440248 | N | N |
| localhost | mysql.infoschema | Y | N |
| localhost | mysql.session | Y | N |
| localhost | mysql.sys | Y | N |
| localhost | root | N | N |
| u2004s0% | clstradmin | N | N |
+-----------+---------------------------------+----------------+------------------+
MySQL u2004s01:3306 ssl JS > \disconnect
MySQL JS > \connect cadmin@u2004s01
Creating a session to 'cadmin@u2004s01'
Please provide the password for 'cadmin@u2004s01': **********
Save password for 'cadmin@u2004s01'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 95 (X protocol)
Server version: 8.0.27 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
Response of clstrvar.setupAdminAccount('cadmin')
MySQL u2004s01:3306 ssl JS > clstrvar.setupAdminAccount('cadmin')
Missing the password for new account cadmin@%. Please provide one.
Password for new account: **********
Confirm password: **********
Creating user cadmin@%.
Account cadmin@% was successfully created.
Note: Instead of clstrvar.setupAdminAccount('cadmin') we can use clstrvar.setupAdminAccount('cadmin@u2004s0%')
sudo mysqlsh
\connect clstradmin@u2004s01:3306
var clstrvar = dba.getCluster('tstMySqlClstr')
clstrvar.setupRouterAccount('radmin@u2004s0%')
Response of clstrvar.setupRouterAccount('radmin@u2004s0%')
MySQL u2004s01:3306 ssl JS > clstrvar.setupRouterAccount('radmin@u2004s0%')
Missing the password for new account radmin@u2004s0%. Please provide one.
Password for new account: **********
Confirm password: **********
Creating user radmin@u2004s0%.
Account radmin@u2004s0% was successfully created.
they wrote:The recommended deployment of MySQL Router is on the same host as the application!!!!!!!!!
To check if the router can be configured, we will use u2004s03-machine.
Bootstrap MySQL Router
for u2004s03
yury@u2004s03:~$ sudo mysqlrouter --bootstrap clstradmin@u2004s01:3306 --user root
Please enter MySQL password for clstradmin:
# Bootstrapping system MySQL Router instance...
- 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 /etc/mysqlrouter/mysqlrouter.conf
Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'
# MySQL Router configured for the InnoDB Cluster 'tstMySqlClstr'
After this MySQL Router has been started with the generated configuration
$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
InnoDB Cluster 'tstMySqlClstr' 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:6448
- Read/Only Connections: localhost:6449
Take a look at the responce above. There are some hints how to start the router on u2004s03-machine.
Take a look at the config with a command sudo nano /etc/mysqlrouter/mysqlrouter.conf
Warning: mysql router.conf lists certificates to be used with a router. Each certificate has expiration date!!!
365/24/7-app requires definition of MySql certificate management strategy after 10 years.
yury@u2004s03:~$ sudo openssl x509 -noout -text -in /var/lib/mysqlrouter/router-cert.pem
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 2 (0x2)
Signature Algorithm: sha256WithRSAEncryption
Issuer: CN = MySQL_Router_Auto_Generated_CA_Certificate
Validity
Not Before: Jan 18 09:45:03 2022 GMT
Not After : Jan 16 09:45:03 2032 GMT
Subject: CN = MySQL_Router_Auto_Generated_Router_Certificate
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
RSA Public-Key: (2048 bit)
...