MySQL 8 innodb replication 一体化的种种你会不会？

六人行中的搞笑马修前两天走了，终年54岁，一个时代过去了，来了走了，过好每一天，每一天都是新的开始，都当做是美好的一天。

127.0.0.1 localhost
127.0.0.1 mysql1
192.168.198.100 mysql1
192.168.198.101 mysql2

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
~                                                                                                                                                   
~                                   

[root@localhost][(none)]> create user repl@'%' identified by 'repl';
Query OK, 0 rows affected (10.01 sec)

[root@localhost][(none)]> grant all  on *.*  to repl@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql@mysql1:~$ mysqlsh repl@'192.168.198.100'
Please provide the password for 'repl@192.168.198.100': ****
Save password for 'repl@192.168.198.100'? [Y]es/[N]o/Ne[v]er (default No): yes
MySQL Shell 8.0.34

Copyright (c) 2016, 2023, 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.
Creating a session to 'repl@192.168.198.100'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 11 (X protocol)
Server version: 8.0.34 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  192.168.198.100:33060+ ssl  JS > dba.configureReplicaSetInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...

This instance reports its own address as mysql1: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.

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 |
+----------------------------------------+---------------+----------------+----------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Configuring instance...
The instance 'mysql1:3306' was configured to be used in an InnoDB ReplicaSet.

                                

mysql@mysql1:~$ cat /data/mysql/mysqld-auto.cnf 
{"Version": 2, "mysql_static_variables": {"slave_parallel_workers": {"Value": "4", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809532860}}, "replica_parallel_workers": {"Value": "4", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809532860}}}, "mysql_dynamic_variables": {"binlog_transaction_dependency_tracking": {"Value": "WRITESET", "Metadata": {"Host": "", "User": "repl", "Timestamp": 1698630809533983}}}}mysql@mysql1:~$ 
                          

 MySQL  192.168.198.100:33060+ ssl  JS > dba.createReplicaSet('repl')
A new replicaset with instance 'mysql1:3306' will be created.

* Checking MySQL instance at mysql1:3306

This instance reports its own address as mysql1:3306
mysql1:3306: Instance configuration is suitable.

* Checking connectivity and SSL configuration...
* Updating metadata...

ReplicaSet object successfully created for mysql1:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

<ReplicaSet:repl>
                   

2023-10-30T03:03:50.912684-00:00 14 [ERROR] [MY-013117] [Repl] Replica I/O for channel 'mysqlsh.test': Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it). Error_code: MY-013117
2023-10-30T03:03:50.912755-00:00 14 [Note] [MY-010570] [Repl] Replica I/O thread exiting for channel 'mysqlsh.test', read up to log 'FIRST', position 4
         

 MySQL  mysql1:33060+ ssl  JS > repl.addInstance('mysql2:3306')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as mysql2:3306
mysql2:3306: Instance configuration is suitable.

* Checking async replication topology...

* Checking connectivity and SSL configuration...

* Checking transaction state of the instance...

NOTE: The target instance 'mysql2:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'mysql2:3306' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset 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): C
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: mysql2:3306 is being cloned from mysql1:3306
** Stage DROP DATA: Completed




** Clone Transfer      FILE COPY  ============================================================    0%  Not Started    PAGE COPY  ============================================================    0%  Not Started    REDO COPY  ============================================================    0%  Not Started** Clone Transfer      FILE COPY  ############################################################  100%  Completed    PAGE COPY  ############################################################  100%  Completed    REDO COPY  ############################################################  100%  Completed
NOTE: mysql2:3306 is shutting down...

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

** Changing replication source of mysql2:3306 to mysql1:3306
** Waiting for new instance to synchronize with PRIMARY...

** Transactions replicated  ============================================================    0% ** Transactions replicated  ###########################################################=   98% ** Transactions replicated  ############################################################  100% 

The instance 'mysql2:3306' was added to the replicaset and is replicating from mysql1:3306.

* Waiting for instance 'mysql2:3306' to synchronize the Metadata updates with the PRIMARY...

** Transactions replicated  ============================================================    0% ** Transactions replicated  ##########################################################==   97% ** Transactions replicated  ############################################################  100% 

         

 MySQL  mysql1:33060+ ssl  JS > repl.status()
{
    "replicaSet": {
        "name": "repl", 
        "primary": "mysql1:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }, 
            "mysql2:3306": {
                "address": "mysql2:3306", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for source to send event", 
                    "replicationLag": null, 
                    "replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3"
                }, 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}

 MySQL  mysql1:33060+ ssl  JS > repl.setPrimaryInstance('mysql2:3306')
mysql2:3306 will be promoted to PRIMARY of 'repl'.
The current PRIMARY is mysql1:3306.

* Connecting to replicaset instances
** Connecting to mysql1:3306
** Connecting to mysql2:3306
** Connecting to mysql1:3306
** Connecting to mysql2:3306

* Performing validation checks
** Checking async replication topology...
** Checking transaction state of the instance...

* Synchronizing transaction backlog at mysql2:3306

** Transactions replicated  ============================================================    0% ** Transactions replicated  ############################################################  100% ** Transactions replicated  ############################################################  100% 

* Updating metadata

* Acquiring locks in replicaset instances
** Pre-synchronizing SECONDARIES
** Acquiring global lock at PRIMARY
** Acquiring global lock at SECONDARIES

* Updating replication topology
** Changing replication source of mysql1:3306 to mysql2:3306

mysql2:3306 was promoted to PRIMARY.

 MySQL  mysql1:33060+ ssl  JS > repl.status()
{
    "replicaSet": {
        "name": "repl", 
        "primary": "mysql2:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "mysql1:3306": {
                "address": "mysql1:3306", 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Waiting for an event from Coordinator", 
                    "applierWorkerThreads": 4, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for source to send event", 
                    "replicationLag": null, 
                    "replicationSsl": "TLS_AES_256_GCM_SHA384 TLSv1.3"
                }, 
                "status": "ONLINE"
            }, 
            "mysql2:3306": {
                "address": "mysql2:3306", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE"
            }
        }, 
        "type": "ASYNC"
    }
}