MYSQL:: Cluster dissolve Required to reduce cost # to Standalone MYSQL Instance:
Scenario:
To dissolve the mysql innodbcluster, Need to connect primary node and then ensure all members in ONLINE state.
Then run cluster.dissolve()
Next run SET GLOBAL read_only=0; to make standalone node from readonly to read write.
Then Ask application team to connect without Mysql Router by direct ip address and port number.
=========================================================================
At one of
the node in Cluster:
Type
'\help' or '\?' for help; '\quit' to exit.
MySQL
JS > \c clusteradmin@PRIMARYNODE_IP:3306
Creating a
session to 'clusteradmin@PRIMARYNODE_IP:3306'
Fetching
schema names for autocompletion... Press ^C to stop.
Your MySQL
connection id is 4592901
Server
version: 8.0.20-commercial MySQL Enterprise Server - Commercial
No default
schema selected; type \use <schema> to set one.
MySQL PRIMARYNODE_IP:3306
ssl JS > var
cluster=dba.getCluster();
MySQL PRIMARYNODE_IP:3306
ssl JS > cluster.status();
{
"clusterName":
"uatcluster",
"defaultReplicaSet": {
"name": "default",
"primary": "SECONDARYNODE_IP1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster
is ONLINE and can tolerate up to ONE failure.",
"topology": {
"PRIMARYNODE_IP:3306": {
"address": "PRIMARYNODE_IP:3306",
"mode":
"R/O",
"readReplicas": {},
"replicationLag":
null,
"role":
"HA",
"status":
"ONLINE",
"version":
"8.0.20"
},
"SECONDARYNODE_IP1:3306":
{
"address": "SECONDARYNODE_IP1:3306",
"mode":
"R/W",
"readReplicas": {},
"replicationLag":
null,
"role":
"HA",
"status":
"ONLINE",
"version":
"8.0.20"
},
"SECONDARYNODE_IP1:3306":
{
"address": "SECONDARYNODE_IP1:3306",
"mode":
"R/O",
"readReplicas": {},
"replicationLag":
null,
"role":
"HA",
"status":
"ONLINE",
"version":
"8.0.20"
}
},
"topologyMode":
"Single-Primary"
},
"groupInformationSourceMember":
"SECONDARYNODE_IP1:3306"
}
MySQL PRIMARYNODE_IP:3306
ssl JS >
Steps
done:
================================================================
MySQL PRIMARYNODE_IP:3306 ssl JS > cluster.setPrimaryInstance('Clusteradmin@PRIMARYNODE_IP:3306');
Setting
instance 'PRIMARYNODE_IP:3306' as the primary instance of cluster
'uatcluster'...
Instance 'SECONDARYNODE_IP1:3306'
was switched from PRIMARY to SECONDARY.
Instance 'SECONDARYNODE_IP1:3306'
remains SECONDARY.
Instance 'PRIMARYNODE_IP:3306'
was switched from SECONDARY to PRIMARY.
WARNING:
The cluster internal session is not the primary member anymore. For cluster
management operations please obtain a fresh cluster handle using
dba.getCluster().
The
instance 'PRIMARYNODE_IP:3306' was successfully elected as primary.
================================================================
MySQL PRIMARYNODE_IP:3306
ssl JS >
Noting down
the cluster option as well for reference.
MySQL PRIMARYNODE_IP:3306 ssl JS > cluster.options();
{
"clusterName":
"uatcluster",
"defaultReplicaSet": {
"globalOptions": [
{
"option":
"groupName",
"value":
"175f7950-4ac8-11ed-893f-00505684085f",
"variable":
"group_replication_group_name"
},
{
"option":
"memberSslMode",
"value":
"REQUIRED",
"variable":
"group_replication_ssl_mode"
},
{
"option":
"disableClone",
"value": false
}
],
"topology": {
"PRIMARYNODE_IP:3306": [
{
"option":
"autoRejoinTries",
"value": "0",
"variable":
"group_replication_autorejoin_tries"
},
{
"option":
"consistency",
"value":
"EVENTUAL",
"variable":
"group_replication_consistency"
},
{
"option":
"exitStateAction",
"value":
"READ_ONLY",
"variable":
"group_replication_exit_state_action"
},
{
"option":
"expelTimeout",
"value":
"0",
"variable":
"group_replication_member_expel_timeout"
},
{
"option":
"groupSeeds",
"value": "SECONDARYNODE_IP1:33061,SECONDARYNODE_IP1:33061",
"variable":
"group_replication_group_seeds"
},
{
"option":
"ipWhitelist",
"value": "AUTOMATIC",
"variable":
"group_replication_ip_whitelist"
},
{
"option":
"localAddress",
"value": "PRIMARYNODE_IP:33061",
"variable":
"group_replication_local_address"
},
{
"option":
"memberWeight",
"value":
"50",
"variable":
"group_replication_member_weight"
}
],
"SECONDARYNODE_IP1:3306":
[
{
"option":
"autoRejoinTries",
"value":
"0",
"variable":
"group_replication_autorejoin_tries"
},
{
"option":
"consistency",
"value":
"EVENTUAL",
"variable":
"group_replication_consistency"
},
{
"option":
"exitStateAction",
"value":
"READ_ONLY",
"variable":
"group_replication_exit_state_action"
},
{
"option":
"expelTimeout",
"value": "0",
"variable":
"group_replication_member_expel_timeout"
},
{
"option":
"groupSeeds",
"value": "PRIMARYNODE_IP:33061,SECONDARYNODE_IP1:33061",
"variable":
"group_replication_group_seeds"
},
{
"option":
"ipWhitelist",
"value":
"AUTOMATIC",
"variable":
"group_replication_ip_whitelist"
},
{
"option":
"localAddress",
"value": "SECONDARYNODE_IP1:33061",
"variable":
"group_replication_local_address"
},
{
"option":
"memberWeight",
"value":
"50",
"variable":
"group_replication_member_weight"
}
],
"SECONDARYNODE_IP1:3306":
[
{
"option":
"autoRejoinTries",
"value":
"0",
"variable":
"group_replication_autorejoin_tries"
},
{
"option":
"consistency",
"value":
"EVENTUAL",
"variable":
"group_replication_consistency"
},
{
"option":
"exitStateAction",
"value":
"READ_ONLY",
"variable":
"group_replication_exit_state_action"
},
{
"option":
"expelTimeout",
"value":
"0",
"variable":
"group_replication_member_expel_timeout"
},
{
"option":
"groupSeeds",
"value": "SECONDARYNODE_IP1:33061,PRIMARYNODE_IP:33061,SECONDARYNODE_IP1:33061",
"variable":
"group_replication_group_seeds"
},
{
"option":
"ipWhitelist",
"value":
"AUTOMATIC",
"variable":
"group_replication_ip_whitelist"
},
{
"option":
"localAddress",
"value": "SECONDARYNODE_IP1:33061",
"variable":
"group_replication_local_address"
},
{
"option":
"memberWeight",
"value":
"50",
"variable":
"group_replication_member_weight"
}
]
}
}
}
MySQL PRIMARYNODE_IP:3306
ssl JS >
================================================================
>>Ask System team to take VM snapshot of all
nodes for database cluster.
================================================================
MySQL PRIMARYNODE_IP:3306 ssl JS > cluster.dissolve();
Cluster.dissolve:
This function is not available through a session to a read only instance
(RuntimeError)
MySQL PRIMARYNODE_IP:3306
ssl JS >
Then open
new MYSQL SHELL Windows and try to run same command,it works.
================================================================
MySQL PRIMARYNODE_IP1:3306 ssl JS > cluster.dissolve();
The cluster
still has the following registered instances:
{
"clusterName":
"uatcluster",
"defaultReplicaSet": {
"name": "default",
"topology": [
{
"address": "PRIMARYNODE_IP1:3306",
"label": "PRIMARYNODE_IP1:3306",
"role":
"HA"
},
{
"address": "PRIMARYNODE_IP1:3306",
"label": "PRIMARYNODE_IP1:3306",
"role":
"HA"
},
{
"address": "SECONDARYNODE_IP1:3306",
"label": "SECONDARYNODE_IP1:3306",
"role":
"HA"
}
],
"topologyMode":
"Single-Primary"
}
}
WARNING:
You are about to dissolve the whole cluster and lose the high availability
features provided by it. This operation cannot be reverted. All members will be
removed from the cluster and replication will be stopped, internal recovery
user accounts and the cluster metadata will be dropped. User data will be maintained
intact in all instances.
Are you
sure you want to dissolve the cluster? [y/N]: y
NOTE: The
recovery user name for instance 'SECONDARYNODE_IP1:3306' does not match the
expected format for users created automatically by InnoDB Cluster. Skipping its
removal.
Instance 'PRIMARYNODE_IP1:3306'
is attempting to leave the cluster...
Instance 'SECONDARYNODE_IP1:3306'
is attempting to leave the cluster...
Instance 'PRIMARYNODE_IP1:3306'
is attempting to leave the cluster...
The cluster
was successfully dissolved.
Replication
was disabled but user data was left intact.
================================================================
MySQL PRIMARYNODE_IP1:3306
ssl JS >
CLUSTER
DISSOLVED.
MySQL PRIMARYNODE_IP1:3306 ssl JS > var cluster =dba.getCluster();
Dba.getCluster:
This function is not available through a session to a standalone instance
(metadata exists, instance does not belong to that metadata, and GR is not
active) (RuntimeError)
MySQL PRIMARYNODE_IP1:3306
ssl JS > cluster.status();
Cluster.status:
Can't call function 'status' on a dissolved cluster (RuntimeError)
MySQL PRIMARYNODE_IP1:3306
ssl JS >
LOOKS MYSQL STANDALONE INSTANCE BECOME READONLY AFTER
CLUSTER DISSOLVE.
Before:
mysql>
SHOW GLOBAL VARIABLES LIKE '%read%';
+-----------------------------------------+---------------------------+
|
Variable_name |
Value |
+-----------------------------------------+---------------------------+
|
create_admin_listener_thread |
OFF |
|
innodb_parallel_read_threads |
4 |
|
innodb_purge_threads |
4 |
|
innodb_random_read_ahead |
OFF |
|
innodb_read_ahead_threshold |
56 |
|
innodb_read_io_threads |
4 |
|
innodb_read_only |
OFF |
|
innodb_thread_concurrency |
0 |
|
innodb_thread_sleep_delay |
10000 |
|
innodb_write_io_threads | 4 |
|
max_delayed_threads |
20 |
|
max_insert_delayed_threads |
20 |
|
myisam_repair_threads |
1 |
|
mysqlx_idle_worker_thread_timeout |
60 |
|
mysqlx_min_worker_threads |
2 |
|
mysqlx_read_timeout |
30 |
|
net_read_timeout |
30 |
|
performance_schema_max_thread_classes |
100 |
|
performance_schema_max_thread_instances | -1 |
|
read_buffer_size | 131072 |
|
read_only |
ON |
|
read_rnd_buffer_size |
262144 |
|
rpl_read_size |
8192 |
| super_read_only | ON |
|
thread_cache_size |
13 |
|
thread_handling |
one-thread-per-connection |
|
thread_stack |
286720 |
|
transaction_read_only |
OFF |
+-----------------------------------------+---------------------------+
28 rows in
set (0.01 sec)
Once we run the below in the standalone mysql instance
, database become read write mode to let users to connect and update.
SET GLOBAL read_only=0;
/* Affected rows:
0 Found rows: 0 Warnings: 0
Duration for 1 query: 0.000 sec. */
AFTER:
mysql>
SHOW GLOBAL VARIABLES LIKE '%read%';
+-----------------------------------------+---------------------------+
|
Variable_name |
Value |
+-----------------------------------------+---------------------------+
|
create_admin_listener_thread |
OFF |
|
innodb_parallel_read_threads |
4 |
|
innodb_purge_threads |
4 |
|
innodb_random_read_ahead |
OFF |
|
innodb_read_ahead_threshold |
56 |
|
innodb_read_io_threads |
4 |
|
innodb_read_only |
OFF |
|
innodb_thread_concurrency |
0 |
|
innodb_thread_sleep_delay |
10000 |
|
innodb_write_io_threads |
4 |
|
max_delayed_threads |
20 |
|
max_insert_delayed_threads |
20 |
|
myisam_repair_threads |
1 |
|
mysqlx_idle_worker_thread_timeout | 60 |
|
mysqlx_min_worker_threads |
2 |
|
mysqlx_read_timeout |
30 |
|
net_read_timeout |
30 |
| performance_schema_max_thread_classes | 100 |
|
performance_schema_max_thread_instances | -1 |
|
read_buffer_size |
131072 |
|
read_only |
OFF |
|
read_rnd_buffer_size |
262144 |
|
rpl_read_size |
8192 |
|
super_read_only |
OFF |
|
thread_cache_size |
13 |
|
thread_handling |
one-thread-per-connection |
|
thread_stack |
286720 |
|
transaction_read_only |
OFF |
+-----------------------------------------+---------------------------+
