Wednesday, March 6, 2024

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

 

Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

Introduction: In the dynamic landscape of database management, effective data archiving is a crucial component for maintaining optimal performance and storage efficiency. This blog post outlines a seamless process for exporting a specific table from a production database, importing it into an archival server, and responsibly pruning records from the production database.

Export Script - Safely Extracting Data: The export process begins with a reliable MySQL export script, ensuring the safe extraction of data from the production database. The script, executed with the mysqldump command, exports a designated table (TABLENAME) from the specified production database (DATABASE_NAME). Noteworthy parameters include --single-transaction for consistent snapshots, --max-allowed-packet to accommodate large datasets, and --where to filter records based on the creation date.

mysqldump -umysqlbackup -p -P3306 DATABASE_NAME TABLENAME --single-transaction --max-allowed-packet=2G --no-create-info --set-gtid-purged=OFF --where="created_on < '2022-01-01'" > F:\FOREXPORTIMPORT_PROD_TO_ARCHIVAL\tablenamelessthan01012022.sql

  Import Script - Migrating to the Archival Server: Once the export is completed, the data is seamlessly imported into an archival server using the mysql command. The import script specifies the archival database (DATABASE_NAME) and utilizes the previously exported SQL file.

mysql -umysqlbackup -p -P3306 DATABASE_NAME --max-allowed-packet=1G < F:\FOREXPORTIMPORT_PROD_TO_ARCHIVAL\tablenamelessthan01012022.sql


If required, use --force flag to overwrite the existing data if any exist.

 Validation and Verification: Before proceeding with any further actions, thorough validation and verification on the archival server are imperative. Confirm that the imported data aligns with expectations and meets the archival requirements. This step ensures the integrity of the data before any modifications on the production database.

Deleting Records from Production - A Step Towards Optimization: Once the archival server data is validated, the next step involves responsibly deleting records from the production database. The provided deletion script targets the specified table (TABLENAME) based on a conditional criterion involving the primary key (primaryKey_id) and a creation date constraint. 

In the production database, execute the following query:

SELECT primary_key_id FROM TABLENAME WHERE DATE(created_on) < '2022-01-01';

 Copy the results of the query to Notepad++ and perform the following find-and-replace steps after removal of appropriate keywords to get the respective scripts:

  1. Press CTRL+H.
  2. Enter ^ in the "Find what" field.
  3. Enter Delete FROM TABLENAME WHERE primaryKey_id = in the "Replace with" field.
  4. Choose ".Regular expression".
  5. Click "Replace ALL".

After completing these steps in Notepad++, you can run the delete scripts using a batch approach rather than executing all of them at once. This ensures a more controlled and manageable process.

DELETE FROM TABLENAME WHERE primaryKey_id = values AND DATE(created_on) < '2022-01-01';

 Collaboration with Application Team: It is crucial to collaborate closely with the application team before executing any deletion scripts. Ensuring concurrence and alignment with business requirements minimizes the risk of unintentional data loss and guarantees a smooth transition to an optimized production database.

Here are essential considerations both before and after executing delete operations in a production database as part of the archival process.

Before Deletion:

  1. Timing Matters: Schedule delete operations during off-business hours to minimize the impact on ongoing operations. This ensures that the deletion process does not interfere with critical business activities and guarantees a smoother transition.

After Deletion:

  1. Table Defragmentation and Rebuilding: Following the deletion of significant amounts of data, it's essential to reclaim the space and optimize table performance. Execute the following SQL command during off-business hours to defragment and rebuild tables:


  2. ALTER TABLE tablename ENGINE=InnoDB;

    1. This command helps reclaim the deleted space within the table, optimizing storage on the underlying drive.


    2. Monitor Binary Log Files: Keep a close eye on the growth of binary log files, especially in production and any relevant slave database servers. Monitoring and managing binary logs are crucial for maintaining database integrity and preventing potential issues related to file size and storage.

      Regularly purge old binary log files using the following MySQL commands:

  3. PURGE BINARY LOGS BEFORE 'yyyy-mm-dd hh:mm:ss';


  4. This helps manage the size of binary logs and prevents unnecessary disk space consumption.
    1. Rebuilding Tables with Partitions: If the deleted table has partitions, a specialized approach is required to rebuild the tables effectively. Identify the partition name using the following command:

    SHOW CREATE TABLE tablename;

    Once you have the partition name, use the following SQL command to rebuild the table:
  5. ALTER TABLE tablename REBUILD PARTITION partition_name;

    Conclusion: In this comprehensive guide, we've explored a structured approach to exporting, importing, and pruning MySQL tables for effective data archiving. By following these steps meticulously, database administrators can maintain a well-organized, efficient database environment while preserving data integrity throughout the process. This step is particularly crucial for maintaining optimal performance, especially when dealing with partitioned tables.

    Effectively managing data in a production database requires a meticulous approach to deletion processes. By considering the timing of deletions, implementing post-deletion optimization strategies, and monitoring critical aspects like binary logs and partitions, database administrators can ensure a seamless archival process without compromising data integrity or system performance. Adopting these best practices contributes to a well-maintained and optimized database environment.

 

Tuesday, May 9, 2023

MYSQL::Activate Slave server Again to sync with existing Master::Cluster Dissolve::Windows

Scenario: Activating existing slave again to sync with master server after cluster dissolved task happened in the master servers (group replication Innodb Cluster).

Steps:

1.Stop slave to ensure no process to master server.

mysql> STOP SLAVE;

The STOP SLAVE command in MySQL is used to stop the slave replication process on a slave server in a master-slave replication setup. When executed on the slave server, it halts the replication process and stops the slave from applying any new changes received from the master.

Query OK, 0 rows affected, 1 warning (0.00 sec)

2.Change master command to set the new master server ip and credentials details.

 mysql> CHANGE MASTER TO MASTER_HOST='MASTERDBSERVERIP', MASTER_PORT=3306, MASTER_USER='db_repl',

    ->  MASTER_PASSWORD='Password',MASTER_DELAY = 0,MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 1 warning (0.05 sec)

 The CHANGE MASTER TO command in MySQL is used on a slave server in a master-slave replication setup to configure and change the replication settings. It is used to specify the master server's connection details and position from which the slave should start replicating.

3.Then start slave to sync master copy to slave.

mysql> START SLAVE;

Query OK, 0 rows affected (0.00 sec)

The START SLAVE command in MySQL is used to start the replication process on a slave server in a master-slave replication setup. When executed on the slave server, it initiates the connection to the master server and begins replicating the changes received from the master.

4.Check show slave status results.

The SHOW SLAVE STATUS command in MySQL is used to display detailed information about the current status of a slave server in a master-slave replication setup. When executed on the slave server, it provides a comprehensive overview of the slave's replication state and configuration.

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: MASTERDBSERVERIP

                  Master_User: db_repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: MASTERDBSERVERNAME-bin.001763

          Read_Master_Log_Pos: 843694033

               Relay_Log_File: SLAVEDBSERVERNAME-relay-bin.000002

                Relay_Log_Pos: 10832949

        Relay_Master_Log_File: MASTERDBSERVERNAME-bin.001763

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 843694033

              Relay_Log_Space: 10833158

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 13

                  Master_UUID: 08538d8c-ed98-11ea-852d-005056844240

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 08538d8c-ed98-11ea-852d-005056844240:942835-942878

            Executed_Gtid_Set: 08538d8c-ed98-11ea-852d-005056844240:1-942878,

28179efb-72c5-11e4-b496-0050569242d9:1-13995703,

29a42f08-7c8e-11ed-969f-005056846111:1-69949262:70421923-70422753,

329fcb1a-c0dd-11eb-82b1-005056847352:1-66160,

6a6ae23d-183f-11eb-ae83-005056846111:1-114606407:114931406-114935574,

ae4d4435-ecbc-11ea-a835-0050568415e0:1-348529,

d1f71bf8-c0dd-11eb-82b1-005056847352:1-258121803,

d9014b6b-1812-11eb-976b-005056846111:1-292573,

dfc8fe40-336a-11ed-a8ea-005056847352:1-48770981

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

       Master_public_key_path:

        Get_master_public_key: 0

            Network_Namespace:

1 row in set (0.00 sec)

Interpret the Output: The SHOW SLAVE STATUS command provides a result set with various columns containing information about the replication status. Some of the important columns and their meanings include:

  1. Slave_IO_State: The current state of the slave I/O thread.
  2. Master_Host and Master_Port: The hostname and port of the master server.
  3. Slave_IO_Running and Slave_SQL_Running: Whether the slave I/O thread and slave SQL thread are running (Yes) or stopped (No).
  4. Seconds_Behind_Master: The replication lag in seconds, indicating how far behind the slave is compared to the master.
  5. Last_Error: Any error message related to replication.
  6. Master_Log_File and Read_Master_Log_Pos: The binary log file and position from which the slave is currently reading.
  7. Relay_Log_File and Relay_Log_Pos: The relay log file and position, indicating the last processed event on the slave.
  8. Exec_Master_Log_Pos: The position of the last executed event on the slave.

These columns provide insights into the replication status, lag, error messages, and positions within the replication process.

 

MYSQL::Audit Requirement:: Keyring file rotation in MYSQL windows ::Master -SLave

 MYSQL::Audit Requirement:: Keyring file rotation in MYSQL windows ::Master -Slave

The ALTER INSTANCE ROTATE INNODB MASTER KEY statement in MySQL is used to rotate the master key for InnoDB tablespace encryption in a master-slave replication setup. This statement is specific to the InnoDB storage engine and is used when you have enabled encryption for InnoDB tablespaces using the keyring plugin.

Note down the keyring file timestamp before rotation.



When you execute the ALTER INSTANCE ROTATE INNODB MASTER KEY statement on the master server, it performs the following actions:


Generates a new master key: The statement generates a new master key for InnoDB tablespace encryption. The master key is used to encrypt and decrypt the InnoDB tablespaces.


Encrypts new data with the new master key: After generating the new master key, the statement starts encrypting new data that is written to the InnoDB tablespaces using the new key.


Re-encrypts existing data: The statement also initiates a background process that gradually re-encrypts existing data in the InnoDB tablespaces using the new master key. This process ensures that all data is eventually re-encrypted with the new key.


Synchronizes key rotation with replication: The ALTER INSTANCE ROTATE INNODB MASTER KEY statement ensures that the rotation of the master key is synchronized with the replication process in a master-slave setup. It ensures that the new key and the re-encryption process are propagated to the slave servers, allowing them to use the new master key for decryption.

Need to execute this command in master server only.

mysql>ALTER INSTANCE ROTATE INNODB MASTER KEY;



By rotating the InnoDB master key, you enhance the security of your encrypted data by periodically changing the encryption key. This helps protect against potential security threats or key exposure.

Note down the keyring file timestamp after rotation to check latest timestamp come.



It's important to note that this statement should be executed on the master server in a master-slave replication setup, and the key rotation and re-encryption process will be automatically propagated to the slave servers.

Note: These steps can be used in Innodb Cluster group replication also. For 4TB database, it took less than 10 seconds only.

MYSQL::Enterprise backup getting failed after cluster dissolve::

ErrorERROR: Unexpected number of rows from MySQL query 'SELECT member_role FROM performance_schema.replication_group_members WHERE member_id=@@server_uuid': got 0, expected == 1.

Anyone experienced this error. MySQL enterprise full backup, differential and incremental backup having this error in the backup log. Please advise any to need to be taken or can we ignore.

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

230507 15:35:23 MAIN    INFO: SSL: attempting to use an SSL connection.

230507 15:35:23 MAIN    INFO: Group replication setup detected.

230507 15:35:23 MAIN   ERROR: Unexpected number of rows from MySQL query 'SELECT member_role FROM performance_schema.replication_group_members WHERE member_id=@@server_uuid': got 0, expected == 1.

230507 15:35:23 MAIN WARNING: This backup operation cannot update the backup_history table.

230507 15:35:23 MAIN    INFO: Full Image Backup operation completed successfully.

230507 15:35:23 MAIN    INFO: Backup image created successfully.

230507 15:35:23 MAIN    INFO: Image Path = Z:\DBNAME_PROD_WEEKLY_FULL_BKP\FULL_20230605_220000.img

230507 15:35:23 MAIN    INFO: MySQL binlog position: filename SERVERNAME-bin.001803, position 136791673

230507 15:35:23 MAIN    INFO: GTID_EXECUTED is 08538d8c-ed98-11ea-852d-005056844240:1-1658361,28179efb-72c5-11e4-b496-0050569242d9:1-13995703,29a42f08-7c8e-11ed-969f-005056846111:1-69949262:70421923-70422753,329fcb1a-c0dd-11eb-82b1-005056847352:1-66160,6a6ae23d-183f-11eb-ae83-005056846111:1-114606407:114931406-114935574,ae4d4435-ecbc-11ea-a835-0050568415e0:1-348529,d1f71bf8-c0dd-11eb-82b1-005056847352:1-258121803,d9014b6b-1812-11eb-976b-005056846111:1-292573,dfc8fe40-336a-11ed-a8ea-005056847352:1-48770981

-------------------------------------------------------------

   Parameters Summary         

-------------------------------------------------------------

   Start LSN                  : 13530353956352

   End LSN                    : 13531799802630

-------------------------------------------------------------

mysqlbackup completed OK! with 2 warnings

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

Solution:


mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+

| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |

+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+

| group_replication_applier |           |             |        NULL | OFFLINE      |             |                |

+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+

1 row in set (0.00 sec)


UNINSTALL PLUGIN GROUP_REPLICATION;

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

The UNINSTALL PLUGIN GROUP_REPLICATION; command is used in MySQL to uninstall or remove the Group Replication plugin. Group Replication is a MySQL plugin that enables you to create a highly available replication group of MySQL servers. It provides a multi-master replication solution with automatic conflict detection and resolution.

When you execute the UNINSTALL PLUGIN GROUP_REPLICATION; command, it removes the Group Replication plugin from the MySQL server. This means that the server will no longer participate in a Group Replication setup and will not be part of a replication group.

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

mysql> uninstall plugin group_replication;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

mysql> set global super_read_only=0;

  Query OK, 0 rows affected (0.00 sec)

mysql> uninstall plugin group_replication;

Query OK, 0 rows affected (0.03 sec)

mysql> set global super_read_only=1;

Query OK, 0 rows affected (0.00 sec)

Then backup task started to run successfully.

Here are a few things to keep in mind when using the UNINSTALL PLUGIN GROUP_REPLICATION; command:

  1. Replication Group: Before uninstalling the Group Replication plugin, ensure that the server you're uninstalling from is not an essential member of the replication group. Otherwise, removing the plugin from a member server can disrupt the replication group's functionality.

  2. Backup: Make sure you have a backup of your data or a plan in place to ensure data availability during the uninstallation process. Removing the plugin doesn't affect the data stored on the server, but it's always a good practice to have backups in case of any unexpected issues.

  3. Impact: Uninstalling the Group Replication plugin only affects the specific server where the command is executed. Other servers in the replication group will continue functioning as long as they still have the plugin installed and configured.

Thursday, April 13, 2023

MYSQL:: Cluster dissolve Required to reduce cost # to Standalone MYSQL Instance

 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                       |

+-----------------------------------------+---------------------------+

 

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

  Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables Introduction: In the dynamic landscape of database management...