Posts

MySQL Audit triggers

-
Image
Introduction How do you enable auditing in MySQL? There are multiple options including plugins, external tools, custom scripts etc. Among these using triggers is a popular and straight forward method when the audit scope is well defined. Specially when it is required set of application tables for DML this is a suitable option. Generating audit triggers can be tedious task as it will be repetitive task to create the triggers. Also, over time it would be difficult to track what tables are being audited for which options. Also using ad hoc audit triggers results in ad hoc audit meta data. Solution What if we can use built in MySQL procedure to have standard way to enable DML audit for given set of tables and track what tables being audited for which audit options. https://github.com/stefanhubb4/MySQLAudit_triggers.git This repo provide MySQL procedures that will help in creating DML (update, insert, delete) audit triggers quickly while managing metadata on audited tables. A
Post a Comment
Read more

Production ready AWS MySQL RDS instance using terraform

-
Using Terraform for AWS RDS implementation is not new. It is a well defined process with required resources already defined by Hashicorp and AWS. But one pitfall associated with IAC as a whole is the possibility that some of the production implementation considerations that might have been covered when provisioning manually might not be highlighted when the infrastructure is initiated using terraform. Idea of this post is to list down such points and provide sample terraform script for production ready AWS Mysql rds.  1. Configure backups, maintenance windows, version upgrade preference. Make sure to include variables "rds_backupWindow" , variable "rds_maintenanceWindow", variable "rds_backupRetentionPeriod". Ensure time windows are set as per the timezone setting of AWS data center in use. Refer 02-rds-variables.tf.   2. Use separate file for parameter management. This should include all rds parameters such as db instance name, size and also the databa
Post a Comment
Read more

MySQL router kubernetes deployment

-
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
Post a Comment
Read more

MySQL router high availability for InnoDB cluster

-
Image
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 Innodb
Post a Comment
Read more

Jenkins MySQL - auto apply database changes from SCM

-
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
1 comment
Read more

MySQL InnoDB Cluster in 15 minutes

-
Image
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
2 comments
Read more

MySQL check query execution time, number of executions - Using general log

-
Image
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
Post a Comment
Read more