Database Technologies...!

How to setup MySQL router in Kubernetes cluster? Continuing from previous post - MySQL router high availability for InnoDB cluster , in situations where application is deployed in kubernets it is better to incorporate mysql router to K8s itself. Inorder to deploy Mysql router in kubernetes two services are required along with the mysql router deployment. MySQL Router deployment Router deployment should listen on two ports for read write and read only connections. hostAliases are used to define the backend innodb cluster database servers. MySQL router docker image is used with variables - MYSQL_HOST, MYSQL_PORT, MYSQL_USER and MYSQL_PASSWORD. MYSQL_HOST variables should point to MySQL service defined in next section. mysql_router_deployment.yaml apiVersion: apps/v1 kind: Deployment metadata: name: mysql-router namespace: default spec: replicas: 1 selector: matchLabels: app: mysql-router template: metadata: labels: app: mysql-router ...

How to setup MySQL router with High availability for InnoDB cluster? MySQL router deployed as part of an Innodb cluster must be able to withstand any failures as it acts as the middle man between application and database cluster. There are two options when installing the routers, Install the MySQL routers on application servers, so the routers are a part of application availability it self. MySQL recommends this as the default installation method in the router FAQ section as well. Ref -  https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-faq.html#faq-mysql-router-how-install Install MySQL routers on separate servers. Option two can be used based on the specific design consideration of a given deployment. As an example there are some deployments where router servers are deployed in separate network segment from databases clusters and application servers. When routers are deployed separately it is a must that they are highly available. No matter running a multi node Inn...

A database change apply step for Jenkins pipeline? When designing for the fully automated deployment pipeline, databases tend to become the bottleneck more often than not. Starting with the complications of storing database changes in the SCM to applying the actual changes with availability and rollback in consideration can be challenging. Given the uniqueness of each deployment coming up with a single solution for this type of requirement is practically impossible. Having this mind, we will try to come up with a simple set of process and a supporting bash script that can help to automate the database change apply process. This particular solution was mainly proposed as an automation step for development environment where multiple dev teams are changing the same databases backend. This requires some discipline from the developers while committing the code to the SCM as well. The particular implementations is based on Jenkins MySQL and GitLab as SCM. The Idea In a step wise timel...

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 Inst...

How to get MySQL Query Time using general log data Quickly finding the query execution time can be very helpful during scenarios such as load tests. Below are simple set of steps to extract the query time , number of execution based on general log. Set general_log and log_output parameters, this should log all queries to mysql.general_log table SET global general_log = 1; SET global log_output = 'table'; Create a table with id column included to general log table CREATE TABLE `test.general_log_id` ( id MEDIUMINT NOT NULL AUTO_INCREMENT, `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumblob NOT NULL, PRIMARY KEY (id) ) ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT='General log'; Filter out the data from mysql.general_log and insert t...