MySQL Shell: 02 deploy InnoDB ReplicaSet

The following article is from universal repair laboratory, Mr. Gong

In January 2020, in addition to the coronavirus whose source information is like a mystery story, MySQL officially released a new version of 8.0.19, and the biggest highlight is the ReplicaSet function.

InnoDBReplicaSet consists of a primary database and multiple secondary databases, which can be manually failed over to the new primary database in case of failure by using ReplicaSet objects and AdminAPI operations.

The official MySQL Router also supports ReplicaSet, which can be automatically configured and used without manual configuration of files.

InnoDB ReplicaSet prerequisites:

Only MySQL 8.0 and later is supported
Only GTID based replication is supported
Row based replication (RBR) only, statement based replication (SBR) not supported
Replication filtering is not supported, for example, copying only one library or table.
A replica set can contain at most one primary primary instance, supporting one or more secondary secondary instances. There is no limit to the number of secondary instances that can be added, but do not do too much because it will occupy routing resources.
ReplicaSet must be managed by MySQL Shell, such as the creation and management of replication accounts. Cannot manipulate replica set directly with SQL statement

In general, InnoDB ReplicaSet itself cannot provide high availability. Compared with InnoDB Cluster, InnoDB ReplicaSet is not perfect and has many limitations. Therefore, it is recommended to deploy InnoDB Cluster as much as possible.

The limitations of InnoDB ReplicaSet include:

There is no automatic failover. If the primary server is not available, you need to manually trigger the failover using the AdminAPI before you can make any changes again.
Transactions that have not been applied before the failure may be lost. Although the secondary instance is still available for reading, it cannot prevent some data loss due to unexpected stop or unavailability.
There is no way to prevent inconsistencies after crashes or unavailability. If failover promotes a secondary node while the secondary node is still available (for example, because of a network partition), it can cause inconsistencies due to brain splits.

1. Configure ReplicaSet

#Add host name to / etc/hosts

192.168.0.77 bj77

192.168.0.78 bj78

192.168.0.79 bj79

#Modify the parameter files in three libraries to ensure that the server ID is different. gtid is enabled:

#Test library 1:

report_host = bj77

report_port = 3306

enforce_gtid_consistency = 1

gtid_mode = 1

server_id = 777

#Test library 2:

report_host = bj78

report_port = 3306

enforce_gtid_consistency = 1

gtid_mode = 1

server_id = 888

#Test library 3:

report_host = bj79

report_port = 3306

enforce_gtid_consistency = 1

gtid_mode = 1

server_id = 999

#Start MySQL Shell at node 1 (192.168.0.77 bj77):

mysqlsh -uroot -P3306 -p"*****"

#Configure the ReplicaSet instance and enter the root password as prompted

MySQL 8.0.19 localhost:33060+ ssl  JS > dba.configureReplicaSetInstance('root@localhost:3306', {clusterAdmin: "'rsadmin'@'%'", clusterAdminPassword: 'TestRepl@123'});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 ReplicaSet...This instance reports its own address as bj77:3306Clients 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.The instance 'bj77:3306' is valid to be used in an InnoDB ReplicaSet.Cluster admin user 'rsadmin'@'%' created.The instance 'bj77:3306' is already ready to be used in an InnoDB ReplicaSet.

#Create ReplicaSet

MySQL 8.0.19 localhost:33060+ ssl  JS > var rs = dba.createReplicaSet("TestReplicaset")A new replicaset with instance 'bj77:3306' will be created.* Checking MySQL instance at bj77:3306This instance reports its own address as bj77:3306bj77:3306: Instance configuration is suitable.* Updating metadata...ReplicaSet object successfully created for bj77:3306.Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

#Check status:

#Add the second node: bj78 192.168.0.78 (still operating on node 1)

#Failed to use the user configuration just created here because rsadmin was not created on node 2.

#Therefore, if you steal a lazy user, it will be automatically generated by the system

#Recovery method from database: select C clone method

#Check the cluster status again. You can see that node 2 has been added and the role is SECONDARY

#Add node 3 in the same way: bj79 192.168.0.79 (still operating at node 1)

#Check the cluster status. There are three nodes, one PRIMARY and two SECONDARY

MySQL 8.0.19 localhost:3306 ssl  JS > rs.status() {    "replicaSet": {        "name": "TestReplicaset",         "primary": "bj77:3306",         "status": "AVAILABLE",         "statusText": "All instances available.",         "topology": {            "bj77:3306": {                "address": "bj77:3306",                 "instanceRole": "PRIMARY",                 "mode": "R/W",                 "status": "ONLINE"            },             "bj78:3306": {                "address": "bj78:3306",                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "receiverStatus": "ON",                     "receiverThreadState": "Waiting for master to send event",                     "replicationLag": null                },                 "status": "ONLINE"            },             "bj79:3306": {                "address": "bj79:3306",                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "receiverStatus": "ON",                     "receiverThreadState": "Waiting for master to send event",                     "replicationLag": null                },                 "status": "ONLINE"            }        },         "type": "ASYNC"    }}

#Connect to 2 SECONDARY nodes and see that the replication status is normal.

#The format of automatic establishment of replication account: MySQL ﹣ InnoDB ﹣ RS ﹣ plus the local server ﹣ ID

[root@localhost][(none)]> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: bj77                  Master_User: mysql_innodb_rs_888  <---Node 2 server_id=888                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000007          Read_Master_Log_Pos: 28380               Relay_Log_File: relay.000002                Relay_Log_Pos: 3637        Relay_Master_Log_File: binlog.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes[root@localhost][(none)]> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: bj77                  Master_User: mysql_innodb_rs_999 <---Node 3 server_id=999                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000007          Read_Master_Log_Pos: 32768               Relay_Log_File: relay.000002                Relay_Log_Pos: 3579        Relay_Master_Log_File: binlog.000007             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

#You can also see the three users and their corresponding permissions in the user table

2. Manage ReplicaSet

Common commands:

ReplicaSet.addInstance() add instance
ReplicaSet.removeInstance() remove instance
ReplicaSet.getName() check ReplicaSet name
ReplicaSet.status() check the status information of ReplicaSet
ReplicaSet.rejoinInstance() rejoins the cluster
\help ReplicaSet or ReplicaSet.help() for help commands

MySQL 8.0.19 localhost:3306 ssl  mysql  JS > \help ReplicaSetNAME      ReplicaSet - Represents an InnoDB ReplicaSet.DESCRIPTION      The ReplicaSet object is used to manage MySQL server topologies that use      asynchronous replication. It can be created using the      dba.createReplicaSet() or dba.getReplicaSet() functions.PROPERTIES      name            Returns the name of the replicaset.FUNCTIONS      addInstance(instance[, options])            Adds an instance to the replicaset.      disconnect()            Disconnects all internal sessions used by the replicaset object.      forcePrimaryInstance(instance, options)            Performs a failover in a replicaset with an unavailable PRIMARY.      getName()            Returns the name of the replicaset.      help([member])            Provides help about this class and it s members      listRouters([options])            Lists the Router instances.      rejoinInstance(instance[, options])            Rejoins an instance to the replicaset.      removeInstance(instance[, options])            Removes an Instance from the replicaset.      removeRouterMetadata(routerDef)            Removes metadata for a router instance.      setPrimaryInstance(instance, options)            Performs a safe PRIMARY switchover, promoting the given instance.      status([options])            Describe the status of the replicaset.      For more help on a specific function, use the \help shell command, e.g.:      \help ReplicaSet.addInstance

3. Master-slave switching test

#Current cluster roles:

192.168.0.77 bj77 -- main

192.168.0.78 bj78 -- from

192.168.0.79 bj79 -- from

#Use setPrimaryInstance to manually switch:

#In this example, you will change bj78 to PRIMARY and bj77 to a SECONDARY member.

 MySQL 8.0.19 localhost:3306 ssl  mysql  JS > var rs = dba.getReplicaSet()  You are connected to a member of replicaset 'TestReplicaset'. MySQL 8.0.19 localhost:3306 ssl  mysql  JS > rs.setPrimaryInstance('bj78:3306')bj78:3306 will be promoted to PRIMARY of 'TestReplicaset'.The current PRIMARY is bj77:3306.* Connecting to replicaset instances** Connecting to bj77:3306** Connecting to bj78:3306** Connecting to bj79:3306** Connecting to bj77:3306** Connecting to bj78:3306** Connecting to bj79:3306* Performing validation checks** Checking async replication topology...** Checking transaction state of the instance...* Synchronizing transaction backlog at bj78:3306* Updating metadata* Acquiring locks in replicaset instances** Pre-synchronizing SECONDARIES** Acquiring global lock at PRIMARY** Acquiring global lock at SECONDARIES* Updating replication topology** Configuring bj77:3306 to replicate from bj78:3306** Changing replication source of bj79:3306 to bj78:3306bj78:3306 was promoted to PRIMARY.

#Check the status. The current main library is bj78.

MySQL 8.0.19 localhost:3306 ssl  mysql  JS > rs.status(){    "replicaSet": {        "name": "TestReplicaset",         "primary": "bj78:3306",         "status": "AVAILABLE",         "statusText": "All instances available.",         "topology": {            "bj77:3306": {                "address": "bj77:3306",                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "receiverStatus": "ON",                     "receiverThreadState": "Waiting for master to send event",                     "replicationLag": null                },                 "status": "ONLINE"            },             "bj78:3306": {                "address": "bj78:3306",                 "instanceRole": "PRIMARY",                 "mode": "R/W",                 "status": "ONLINE"            },             "bj79:3306": {                "address": "bj79:3306",                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "receiverStatus": "ON",                     "receiverThreadState": "Waiting for master to send event",                     "replicationLag": null                },                 "status": "ONLINE"            }        },         "type": "ASYNC"    }}

4. Failover

Unlike InnoDB Cluster, InnoDB ReplicaSet has no automatic fault detection and group replication mechanism. In the event of an unexpected failure of the primary library, it does not fail over automatically. If the primary library is damaged and unavailable, the ReplicaSet is actually read-only and a new primary library must be selected.

Use ReplicaSet.forcePrimaryInstance() to force the configuration (failover) of a PRIMARY instance. This can only be used in the event of a disaster where the current PRIMARY library is not available and cannot be restored.

This test switches to bj79

#Current cluster roles:

192.168.0.77 bj77 from

192.168.0.78 bj78 main

192.168.0.79 bj79 from

#This command is not valid when the main library is available.

MySQL 8.0.19 localhost:3306 ssl  mysql  JS > rs.forcePrimaryInstance('bj79:3306')* Connecting to replicaset instances** Connecting to bj77:3306** Connecting to bj79:3306* Waiting for all received transactions to be applied** Waiting for received transactions to be applied at bj77:3306** Waiting for received transactions to be applied at bj79:3306bj79:3306 will be promoted to PRIMARY of the replicaset and the former PRIMARY will be invalidated.* Checking status of last known PRIMARYPRIMARY bj78:3306 is still available.Operation not allowed while there is still an available PRIMARY. Use setPrimaryInstance() to safely switch the PRIMARY.ReplicaSet.forcePrimaryInstance: PRIMARY still available (MYSQLSH 51116)

#kill mysqld_safe and mysqld on the main database bj78

Steps

#Connect to bj79 and confirm that bj78, the current cluster master database, is no longer available for error reporting.

 MySQL 8.0.19 localhost:3306 ssl  JS > var rs = dba.getReplicaSet() You are connected to a member of replicaset 'TestReplicaset'. MySQL 8.0.19 localhost:3306 ssl  JS > rs.status()ERROR: Unable to connect to the PRIMARY of the replicaset TestReplicaset: bj78:3306: Can't connect to MySQL server on 'bj78' (111)Cluster change operations will not be possible unless the PRIMARY can be reached.If the PRIMARY is unavailable, you must either repair it or perform a forced failover.See \help forcePrimaryInstance for more information.WARNING: MYSQLSH 51118: PRIMARY instance is unavailable{    "replicaSet": {        "name": "TestReplicaset",         "primary": "bj78:3306",         "status": "UNAVAILABLE",         "statusText": "PRIMARY instance is not available, but there is at least one SECONDARY that could be force-promoted.",         "topology": {            "bj77:3306": {                "address": "bj77:3306",                 "fenced": true,                 "instanceErrors": [                    "ERROR: Replication I/O thread (receiver) has stopped with an error."                ],                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "expectedSource": "bj78:3306",                     "receiverLastError": "error reconnecting to master 'mysql_innodb_rs_777@bj78:3306' - retry-time: 60 retries: 2 message: Can't connect to MySQL server on 'bj78' (111)",                     "receiverLastErrorNumber": 2003,                     "receiverLastErrorTimestamp": "2020-01-23 19:55:59.782554",                     "receiverStatus": "ERROR",                     "receiverThreadState": "",                     "replicationLag": null,                     "source": "bj78:3306"                },                 "status": "ERROR",                 "transactionSetConsistencyStatus": null            },             "bj78:3306": {                "address": "bj78:3306",                 "connectError": "bj78:3306: Can't connect to MySQL server on 'bj78' (111)",                 "fenced": null,                 "instanceRole": "PRIMARY",                 "mode": null,                 "status": "UNREACHABLE"            },             "bj79:3306": {                "address": "bj79:3306",                 "fenced": true,                 "instanceErrors": [                    "ERROR: Replication I/O thread (receiver) has stopped with an error."                ],                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "expectedSource": "bj78:3306",                     "receiverLastError": "error reconnecting to master 'mysql_innodb_rs_999@bj78:3306' - retry-time: 60 retries: 2 message: Can't connect to MySQL server on 'bj78' (111)",                     "receiverLastErrorNumber": 2003,                     "receiverLastErrorTimestamp": "2020-01-23 19:55:59.782926",                     "receiverStatus": "ERROR",                     "receiverThreadState": "",                     "replicationLag": null,                     "source": "bj78:3306"                },                 "status": "ERROR",                 "transactionSetConsistencyStatus": null            }        },         "type": "ASYNC"    }}

#Force promoting bj79 as a new master library

 MySQL 8.0.19 localhost:3306 ssl  JS > rs.forcePrimaryInstance('bj79:3306')* Connecting to replicaset instances** Connecting to bj77:3306** Connecting to bj79:3306* Waiting for all received transactions to be applied** Waiting for received transactions to be applied at bj79:3306** Waiting for received transactions to be applied at bj77:3306bj79:3306 will be promoted to PRIMARY of the replicaset and the former PRIMARY will be invalidated.* Checking status of last known PRIMARYNOTE: bj78:3306 is UNREACHABLE* Checking status of promoted instanceNOTE: bj79:3306 has status ERROR* Checking transaction set status* Promoting bj79:3306 to a PRIMARY...* Updating metadata...bj79:3306 was force-promoted to PRIMARY.NOTE: Former PRIMARY bj78:3306 is now invalidated and must be removed from the replicaset.* Updating source of remaining SECONDARY instances** Changing replication source of bj77:3306 to bj79:3306Failover finished successfully.

#Check current status:

#Note that it is available? Partial, that is, although the main library is available, there are SECONDARY nodes that are not available

 MySQL 8.0.19 localhost:3306 ssl  JS > rs.status(){    "replicaSet": {        "name": "TestReplicaset",         "primary": "bj79:3306",         "status": "AVAILABLE_PARTIAL",         "statusText": "The PRIMARY instance is available, but one or more SECONDARY instances are not.",         "topology": {            "bj77:3306": {                "address": "bj77:3306",                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "receiverStatus": "ON",                     "receiverThreadState": "Waiting for master to send event",                     "replicationLag": null                },                 "status": "ONLINE"            },             "bj78:3306": {                "address": "bj78:3306",                 "connectError": "bj78:3306: Can't connect to MySQL server on 'bj78' (111)",                 "fenced": null,                 "instanceRole": null,                 "mode": null,                 "status": "INVALIDATED"            },             "bj79:3306": {                "address": "bj79:3306",                 "instanceRole": "PRIMARY",                 "mode": "R/W",                 "status": "ONLINE"            }        },         "type": "ASYNC"    }}

#Start the original main library bj78:

Steps

#bj78 cannot join the cluster after startup:

"bj78:3306": {     "address": "bj78:3306",      "fenced": false,      "instanceErrors": [          "WARNING: Instance was INVALIDATED and must be removed from the replicaset.",           "ERROR: Instance is NOT a PRIMARY but super_read_only option is OFF. Accidental updates to this instance are possible and will cause inconsistencies in the replicaset."     ],      "instanceRole": null,      "mode": null,      "status": "INVALIDATED",      "transactionSetConsistencyStatus": "OK"},

#Rejoin the cluster using rs.rejoinInstance()

 MySQL 8.0.19 localhost:3306 ssl  JS > rs.rejoinInstance('bj78:3306')  * Validating instance...** Checking transaction state of the instance...The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'bj78: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'.Incremental state recovery was selected because it seems to be safely usable.* Rejoining instance to replicaset...** Configuring bj78:3306 to replicate from bj79:3306** Checking replication channel status...** Waiting for rejoined instance to synchronize with PRIMARY...* Updating the Metadata...The instance 'bj78:3306' rejoined the replicaset and is replicating from bj79:3306.

#Finally, check that the cluster has completely returned to normal

 MySQL 8.0.19 localhost:3306 ssl  JS > rs.status(){    "replicaSet": {        "name": "TestReplicaset",         "primary": "bj79:3306",         "status": "AVAILABLE",         "statusText": "All instances available.",         "topology": {            "bj77:3306": {                "address": "bj77:3306",                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "receiverStatus": "ON",                     "receiverThreadState": "Waiting for master to send event",                     "replicationLag": null                },                 "status": "ONLINE"            },             "bj78:3306": {                "address": "bj78:3306",                 "instanceRole": "SECONDARY",                 "mode": "R/O",                 "replication": {                    "applierStatus": "APPLIED_ALL",                     "applierThreadState": "Slave has read all relay log; waiting for more updates",                     "receiverStatus": "ON",                     "receiverThreadState": "Waiting for master to send event",                     "replicationLag": null                },                 "status": "ONLINE"            },             "bj79:3306": {                "address": "bj79:3306",                 "instanceRole": "PRIMARY",                 "mode": "R/W",                 "status": "ONLINE"            }        },         "type": "ASYNC"    }}

In general, the management mode of ReplicaSet is similar to MongoDB, but the functions provided are not perfect and the purpose is not very clear. There should be no production scenario to dare to use in the short term. We will do further tests after the surrounding facilities are completed in the later stage.

#Archive of historical articles