MySQL has the inbuilt query rewritten plugin for rewrite the SQL statements which received by the server before execute them . Before MySQL 8.0.12 , the query rewritten plugin only supports for SELECT’s From MySQL 8.0.12 , the query rewritten plugin will supports for INSERT , REPLACE, UPDATE, DELETE , SELECT . One of our customer had the performance issue with the DELETE statements for particular table . The issue was happened after the new code deployment . In this blog, I am going to explain the implementation of the MySQL query rewrite plugin and how we overcome the issue using the MyS ..read more

MySQL have the nice feature, which helps to write the error log with JSON format . This can be achieved through the MySQL components . In this blog, I am going to show the complete walkthrough involved in this process . What is MySQL component ? from MySQL document , MySQL Server includes a component-based infrastructure for extending server capabilities. A component provides services that are available to the server and other components. (With respect to service use, the server is a component, equal to other components.) Components interact with each other only through the services ..read more

We know well the MySQL Group Replication cluster is functionally working based on the GTID . GTID is mandatory for group replication . Usually, GTID is the combination of source_id ( or server_uuid ) and transaction_id . Do you think the same approach is following in the MySQL Group Replication as well ? Well, this blog mainly going to focus on the below three topics . How the binary log GTID events are being generated in the normal MySQL instance ? How the binary log GTID events are being generated in the MySQL Group Replication cluster ? How to configure the asynchronous replication from ..read more

As a MySQL database administrator, we all using the MySQL client program for communicate to MySQL Server . Maximum the client program is used to execute the SQL’s, monitor the traffic and modify the variables . MySQL client program has some good features which can helps to make our work easier . In this blog I am going to explain some MySQL client program features which impressed me . Execute the OS commands inside the MySQL client program Create / Execute the SQL file inside the MySQL client program Query output with XML and HTML languages MySQL client program for learning MySQL commands ..read more

Few months back , I came to know about the tool Binlog2sql . The tool has very cool features like . The tool can extract SQL’s from MySQL Binary log . The tool can generate the rollback SQL’s for PITR . In this blog, I am going to explain, how the above two features can be achieved using the tool binlog2sql . Installation : The tool has been developed by Mr. Cao Danfeng . Great Job Mr. Cao Danfeng . The tool can be downloaded from the GitHub . https://github.com/danfengcao/binlog2sql git clone https://github.com/danfengcao/binlog2sql.git cd binlog2sql pip install -r requireme ..read more

Few months back , I came to know about the tool Binlog2sql . The tool has very cool features like . The tool can extract SQL’s from MySQL Binary log . The tool can generate the rollback SQL’s for PITR . In this blog, I am going to explain, how the above two features can be achieved using the tool binlog2sql . Installation : The tool has been developed by Mr. Cao Danfeng . Great Job Mr. Cao Dancing . The tool can be downloaded from the GitHub . https://github.com/danfengcao/binlog2sql git clone https://github.com/danfengcao/binlog2sql.git cd binlog2sql pip install -r requireme ..read more

The blog title seems something crazy ? Yes, by default your InnoDB transactions don’t have the Atomicity . But, you can control the atomicity of your InnoDB transaction with the variable innodb_rollback_on_timeout. We had a weird data inconsistency issue within the transaction . In this blog , I am going to explain “How it will happen?” and “How it can be fixed ?” . Whats is Atomicity ? From Wiki , Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely ..read more

                Had an interesting requirement from one of our client to have the two MySQL partitions ( partition_full / partition_half ) for store the Names based on the user input. Requirement : The table have two columns first_name and last_name. With the user input of both columns, it needs to be automatically compute the data for another column full_name . And, the status needs be consider as “FULL” . If the column last_name don’t have the input from the user, then the first_name data needs to be compute as the full_name . And, the status needs be considered as “HALF” . Need the s ..read more

Galera is the best solution for High Availability, It is being used by many peoples world wide . Galera is doing synchronous replication ( really it is Certification based replication ) to keep update the data on group nodes . In this blog I have explained about “How the Galera replication works?” . For the better understanding, I have made an architecture diagram to describe the replication flow . I have also provided the explanation for the key words which has used in the architecture diagram . Architecture flow Diagram : What is writeset ? Writeset contains all changes mad ..read more