16.InnoDB Cluster 简单搭建

server_id=1  ##这里每个mysql节点都要保持不一样
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
loose-plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"   # 这里的所有节点都要配置相同
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db01:33061"
loose-group_replication_group_seeds= "db01:33061,db02:33061,db03:33061"
loose-group_replication_bootstrap_group=off
default_authentication_plugin = mysql_native_password # 这里注意需要这个密码插件，不然会默认caching_sha2_password

alter user 'root'@'localhost' identified by '123';
create user 'root'@'%' identified by '123';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

systemctl stop firewalld
systemctl disabled firewalld
# vi /etc/selinux/conf    将enfocing改成disabled
# sestatus  查看

192.168.10.134 db01
192.168.10.130 db02
192.168.10.131 db03
192.168.10.132 db04

/etc/init.d/mysqld start

[root@db01 ~]# mysqlsh
MySQL Shell 8.0.25

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 > shell.connect('root@localhost:3306')
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 32
Server version: 8.0.25 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@localhost:3306>
 MySQL  localhost:3306 ssl  JS > 
 MySQL  localhost:3306 ssl  JS > dba.checkInstanceConfiguration('root@localhost:3306')
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as db01: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 'db01:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

 "status" : "ok"

[root@db04 /]# mysqlsh
MySQL Shell 8.0.25

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 > shell.connect('root@192.168.10.134:3306')
Creating a session to 'root@192.168.10.134:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 108
Server version: 8.0.25 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
<ClassicSession:root@192.168.10.134:3306>
 MySQL  192.168.10.134:3306 ssl  JS > var cluster = dba.createCluster('clusterTest')

 Mysql  192.168.10.134:3306 ssl  JS > var cluster = dba.getCluster()
 MySQL  192.168.10.134:3306 ssl  JS > cluster.addInstance('root@db03:3306')

WARNING: A GTID set check of the MySQL instance at 'db03:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

db03:3306 has the following errant GTIDs that do not exist in the cluster:
c5d28887-db0b-11ed-8e2e-000c293f8c20:1-7

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of db03:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

Please select a recovery method [C]lone/[A]bort (default Abort): c
Validating instance configuration at db03:3306...

This instance reports its own address as db03:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'db03: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: db03:3306 is being cloned from db02:3306
** Stage DROP DATA: Completed
** Clone Transfer  
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: db03:3306 is shutting down...

* Waiting for server restart... ready
* db03:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.20 MB transferred in about 1 second (~72.20 MB/s)

State recovery already finished for 'db03:3306'

 MySQL  192.168.10.134:3306 ssl  JS > cluster.status()
{
    "clusterName": "clusterTest", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "db01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "db01:3306": {
                "address": "db01:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "db02:3306": {
                "address": "db02:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }, 
            "db03:3306": {
                "address": "db03:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.25"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "db01:3306"
}

[root@db04 ~]# mysqlrouter --bootstrap  root@db01:3306  -d myrouter --user=root
Please enter MySQL password for root: 
# Bootstrapping MySQL Router instance at '/root/myrouter'...

- 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 /root/myrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'clusterTest'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /root/myrouter/mysqlrouter.conf

the cluster 'clusterTest' 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

[root@db04 ~]# cd myrouter/
[root@db04 myrouter]# ls
data  log  mysqlrouter.conf  mysqlrouter.key  run  start.sh  stop.sh