Add or Remove a node in MySQL Innodb Cluster

-

Add or Remove a node in MySQL Innodb Cluster


In any database Cluster Solution ,it's regular activity to add or remove node from the cluster. In this blog , We will discuss about addition and removal of the cluster node from the MySQL Innodb cluster.

Remove a node from the MySQL Innodb cluster.


1) Connect to primary node and check status.

mysqlsh --uri clusteradmin@mysqlhost01:3306

MySQL  mysqlhost01:3306 ssl  JS > cluster.status()
{
    "clusterName": "mysqlcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlhost01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysqlhost01:3306": {
                "address": "mysqlhost01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost02:3306": {
                "address": "mysqlhost02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost03:3306": {
                "address": "mysqlhost03:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysqlhost01:3306"
}


2) Remove the node from the cluster.

cluster.removeInstance('clusteradmin@mysqlhost03:3306')

 MySQL  mysqlhost01:3306 ssl  JS > cluster.removeInstance('clusteradmin@mysqlhost03:3306')
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

Instance 'mysqlhost03:3306' is attempting to leave the cluster...

The instance 'mysqlhost03:3306' was successfully removed from the cluster.

3) Check Cluster status post node removal.

MySQL  mysqlhost01:3306 ssl  JS > cluster.status()
{
    "clusterName": "mysqlcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlhost01:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "mysqlhost01:3306": {
                "address": "mysqlhost01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost02:3306": {
                "address": "mysqlhost02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysqlhost01:3306"
}


Addition of node to the MySQL Innodb cluster.

1) Connect to primary node and check status.

mysqlsh --uri clusteradmin@mysqlhost01:3306

MySQL  mysqlhost01:3306 ssl  JS > cluster.status()
{
    "clusterName": "mysqlcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlhost01:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "mysqlhost01:3306": {
                "address": "mysqlhost01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost02:3306": {
                "address": "mysqlhost02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysqlhost01:3306"

}

2) Add the node to MySQL Innodb Cluster
cluster.addInstance('clusteradmin@mysqlhost03:3306');


MySQL  mysqlhost01:3306 ssl  JS > cluster.addInstance('clusteradmin@mysqlhost03:3306');
Please provide the password for 'clusteradmin@mysqlhost03:3306': ************************
Save password for 'clusteradmin@mysqlhost03:3306'? [Y]es/[N]o/Ne[v]er (default No):
NOTE: The target instance 'mysqlhost03:3306' has not been pre-provisioned (GTID set
is empty). The Shell is unable to decide whether incremental distributed 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
'mysqlhost03:3306' with a physical snapshot from an existing cluster member.
To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental distributed 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 at mysqlhost03:3306...
This instance reports its own address as mysqlhost03:3306
Instance configuration is suitable.
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: mysqlhost03:3306 is being cloned from mysqlhost02:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed
** Stage RECOVERY: \
NOTE: mysqlhost03:3306 is shutting down...
* Waiting for server restart... ready
* mysqlhost03:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 58.50 MB transferred in about 1 second (~inf TB/s)
State recovery already finished for 'mysqlhost03:3306'
The instance 'mysqlhost03:3306' was successfully added to the cluster.

3) Check cluster status post node addition.

 MySQL  mysqlhost01:3306 ssl  JS > cluster.status()
{
    "clusterName": "Mysqlcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlhost01:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysqlhost02:3306": {
                "address": "mysqlhost02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost01:3306": {
                "address": "mysqlhost01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "mysqlhost03:3306": {
                "address": "mysqlhost03:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysqlhost01:3306"
}

=================================================================





Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restore MySQL Database from mysqlbackup

-
Image
 Restore MySQL Database from mysqlbackup  In todays world, One of the most common activity is to perform database restore using old backup or performing PIT recovery for database.  In this blog , We will show you how to perform restore and recovery of the MySQL database using backup taken via mysqlbackup. In order to restore Mysql database from MySQL backup , We will follow below steps: 1) Shut down MySQL Database Server/Service Check the status of the mysql service using command : systemctl status mysql Shut down mysql service using command : systemctl stop mysql 2) Delete all existing file of MySQL Server Delete all files inside the server's data directory. Also delete all files inside the directories specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory. 3) Restore full Backup mysqlbackup  --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --d...
Read more

Oracle Database 19C Installation on Windows Server 2016

-
Image
Oracle Database 19C Installation on Windows Server 2016 In Database world, Its important to be on the latest version of the database flavor. Though Oracle have release 21c Database as well but by far Oracle 19C is most stable version.( You also check our similar blog for  Installation of Microsoft SQL Server 2016 ) Please do also check out blog for  Oracle 19c New Features to know about Oracle 19c features.  In this blog, We will be installing Oracle 19C DB on Windows Server. 1) Create required directory for Oracle Home and Oracle Base. Oracle Base: E:\app\oracle Oracle Home: E:\app\oracle\db\19.0.0\dbhome_1 2) Move the binary to E:\app\oracle\product\19.0.0 directory and unzip 3) Check unzipped directory. 4)Right Click on setup and  Run Setup as Administrator: 5)   On Configuration Screen, Select Set up software only 6)   On Database Installation Option page, Select Single instance Database installation for stand alone database server. 7)  O...
Read more

MySQL InnoDB Cluster Restore/Create Issue : - Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000)

-
Image
MySQL InnoDB Cluster Restore Issue : - Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000) Recently ,We have been restoring and recreating MySQL InnoDB Cluster and faced below issue after firing : MySQL mysqlhost01:3306 ssl JS > var cluster = dba.createCluster('testdbCluster') .. ..... ... Creating InnoDB cluster 'testdbCluster' on 'mysqlhost01:3306'... Adding Seed Instance... ERROR: Unable to start Group Replication for instance 'mysqlhost01:3306'. Please check the MySQL server error log for more information. Dba.createCluster: Group Replication failed to start: MySQL Error 3094 (HY000): mysqlhost01:3306: The START GROUP_REPLICATION command failed as the applier module failed to start. (RuntimeError) MySQL mysqlhost01:3306 ssl JS > Below is full command output with error message: Below are the message in MySQL error log: This issue is Caused by the old(original server) relay log information exiting in metadata tables ....
Read more