MySQL InnoDB Cluster in 15 minutes

-

What is MySQL innodb cluster

MySQL innodb cluster is a high availability solutions provided by MySQL which includes both database service auto failover and Client auto failover. It utilize multiple technologies underneath to ensure its availability targets.
  • Group Replication -- Ensure distributed consensus, Make sure all member of the cluster agrees same state transaction wise.
  • MySQL Shell -- Provide cluster maintenance and monitoring interfaces.
  • MySQL Router -- Manges client failovers, provide a single access point for clients.

Setting up innodb cluster

Although a complete solution, quickly setting up cluster at least for testing purposes can be challenging using the normal process. This is mainly because it involves different technologies and the requirement of setting up at least 3 nodes. Hence we can use below Vagrant and Virtual box based solution to setup MySQL innodb cluster rapidly also with the reusability.

InnoDB cluster using Vagrant and Virtual box

  1. Install below Vagrant and Virtual box versions, specific version requirement is a limitation on the current vagrant box used
  2. Create empty folder in Windows, Using Windows cmd terminal execute below commands cd **Folder Created** vagrant box add stefanVagrant/InnoDBcluster --insecure vagrant box status
    3. Output should be similar to below,
    MySQL InnoDB Cluster Quick Setup
  3. Download zip file from -- https://github.com/stefanhubb4/innoDBCluster_vagrant
  4. Deploy the cluster using vagrant up
    This will deploy four Oracle Linux 7.8 virtual machines with below configuration.
    db nodes
    192.168.56.121 - inno1
    192.168.56.122 - inno2
    192.168.56.123 - inno3
    router
    192.168.56.100 - router
    You can verify the deployment using vagrant status, and the output should be similar to below.

    MySQL InnoDB Cluster setup

  5. Testing the cluster
    6. Below credentials can be used to check the status of deployed cluster.
    server root password - vagrant
    mysql root password - G0w@y#3031!
    mysql clususer password - G0w@y#4041! MySQL JS > shell.connect('clususer@inno1'); Creating a session to 'clususer@inno1' Please provide the password for 'clususer@inno1': *********** Save password for 'clususer@inno1'? [Y]es/[N]o/Ne[v]er (default No): Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 33 Server version: 5.7.35-log MySQL Community Server (GPL) No default schema selected; type \use <schema> to set one. <ClassicSession:clususer@inno1:3306> MySQL inno1:3306 ssl JS > var c = dba.getCluster('testcluster'); MySQL inno1:3306 ssl JS > c.status() { "clusterName": "testCluster", "defaultReplicaSet": { "name": "default", "primary": "inno1:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "inno1:3306": { "address": "inno1:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE", "version": "5.7.35" }, "inno2:3306": { "address": "inno2:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE", "version": "5.7.35" }, "inno3:3306": { "address": "inno3:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE", "version": "5.7.35" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "inno1:3306" } MySQL inno1:3306 ssl JS > \quit As per the output 3 node MySQL innodb cluster with single primary is deployed.
  6. Testing the client connectiity
    7. In order to check client connectivity ssh to mysql router server, bootstrap and start the router using below steps. Once router is started connect to innodb read write or read only ports and verify. [root@router ~]# sudo mysqlrouter --bootstrap clususer@inno1:3306 --user=mysqlrouter Please enter MySQL password for clususer: # Bootstrapping system MySQL Router instance... - Creating account(s) (only those that are needed, if any) - Verifying account (using it to run SQL queries that would be run by Router) - Storing account in keyring - Adjusting permissions of generated files - Creating configuration /etc/mysqlrouter/mysqlrouter.conf Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak' # MySQL Router configured for the InnoDB Cluster 'testCluster' After this MySQL Router has been started with the generated configuration $ /etc/init.d/mysqlrouter restart or $ systemctl start mysqlrouter or $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf InnoDB Cluster 'testCluster' can be reached by connecting to: ## MySQL Classic protocol - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 ## MySQL X protocol - Read/Write Connections: localhost:6448 - Read/Only Connections: localhost:6449 [root@router ~]# mysqlrouter & [1] 4811 [root@router ~]# logging facility initialized, switching logging to loggers specified in configuration [root@router ~]# [root@router ~]# mysql --user=clususer --password --host=localhost --port=6446 --protocol=tcp Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 137 Server version: 5.7.35-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, 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 '\h' for help. Type '\c' to clear the current input statement. mysql>

    Conclusion

    As demonstrated above using Vagrant and Virtualbox reduce the deployment time for MySQL innodb test cluster drastically.
    Also this allows easy failover testing as cluster can be reinitiated to original status by just using vagrant destroy and vagrant up.

Comments

Post a Comment

Popular posts from this blog

ORA-16433: The database or pluggable database must be opened in read/write

-
Image
ORA-16433 is an error returned when a database cannot be opened. This can be a result of multiple conditions including, The inconsistency of the database Physical corruption Loss of dependent files or access issues etc.. More often than not the solution is to recover from available backups, but there can be situations where backups are not available, specially on UAT or Test setups. Although not the ideal scenario, in this type of situation you might want to open the databases even with inconsistency.  In Comes --> _ALLOW_RESETLOGS_CORRUPTION First, if you are in a position to use _ALLOW_RESETLOGS_CORRUPTION=TRUE, that means it has to be a critical moment, and when you come across more ora- errors while you are trying to reopen the databsae, that can be annoying. Note - _ALLOW_RESETLOGS_CORRUPTION=TRUE must only be used after Oracle support confirmation as a last resort. Also make sure to take cold backup before proceeding. Basic steps to be followed to resolve ORA 16433 af...
Read more

Oracle Multitenant - Create new service for PDB using DBMS_SERVICE

-
Image
When an Oracle PDB is created it starts up a default service with the PDB name. For direct connections to    PDB this service can be used. But there are situations where new service name is required in PDB level, common example is after a PDB restoration/clone, in order to enable application connections without changes in connection string. Setting service_names parameter in PDB level is not possible hence it is not an option in multitenant setup. How to create a new PDB service on Oracle 12C Check the current services, here only the default service is running, SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NFACTORPDB READ WRITE NO SQL> select service_id, name, pdb from dba_Services; SERVICE_ID NAME PDB ---------- ---------------------------------------- ---------- 7 nfactorpdb NFACTORPDB SQL> ...
Read more

Wait for unread message on broadcast channel - Blocking Sessions

-
Image
What is Wait for unread message on broadcast channel wait event? Wait for unread message on broadcast channel is a wait event where source destination communication is involved and the database is waiting for a reply from remote party. Mostly this wait is seen related to data pump export (expdp) or import (impdp) operations. Although categorized in idle wait class "Wait for unread message on broadcast channel" can become the blocking session for some other sessions. Common causes for stuck sessions waiting on this wait event includes, Broken databases links, where remote database is not responding. Abnormally killed remote processes causing the sessions to wait. When a session is waiting on "Wait for unread message on broadcast channel" it can become a blocking session for dependent sessions. Most probably these sessions will be waiting on "library cache pin" or related concurrency wait events. The blocking_session column of (g)v$session view...
Read more