Update 2020-05-05: I stopped providing consulting hours. They were not very popular, but it was worth trying.
We are all impacted by COVID-19. On my side, this virus disrupted my travel, professional and personal plans for the upcoming months. To overcome these disruptions, and following the idea of someone on MySQL Community Slack, I am experimenting with giving MySQL advice
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 they provide.
Link : https://dev.mysql.com/doc/refman/8.0/en/server-components.html
How to enable the JSON logging ?
Step 1 : ( Installing the component ) :
…
[Read more]Data security plays a major role in current age. Privacy matters a lot. Data masking is one of the Key Features when comes to security.
What is Masking ?
MySQL Audit Logging — How to Avoid Data Overload
In the last few months, our solution engineers have had many discussions with security and operation teams about how to implement MySQL auditing. In some cases, these teams have been asked to implement new database auditing in response to a security breach or to meet a regulatory requirement (PCI, HIPPA, GDPR or CCPA).
More often, however, these companies are switching audit strategies because their existing method has led to data overload. They typically start by monitoring everything, sometimes even using the general query log. They quickly learn that even with moderate database activity, the amount of generated data soon becomes overwhelming.
Below are a couple considerations for selecting your audit strategy.
Disclaimer: The following blog post does not try to provide a solution for split-brain situations, and the example provided is for demonstrative purposes only. Inconsistencies resulting from a split-brain scenario might possibly be more complex than the one shown, so do not use the following example as a complete guide.
What is Split-Brain?
A split-brain scenario is the result of two data sets (which were originally synced) losing the ability to sync while potentially continuing to receive DMLs over the same rows and ids on both sides. This could have consequences such as data corruption or inconsistencies where each side has data that does not exist on the other side.
For example before split-brain:
After split-brain:
Node1:
Node2:
It can be seen that after the split-brain scenario there are many differences between the nodes:
What You May Not Know About Random Number Generation in Sysbench
Sysbench is a well known and largely used tool to perform benchmarking. Originally written by Peter Zaitsev in early 2000, it has become a de facto standard when performing testing and benchmarking. Nowadays it is maintained by Alexey Kopytov and can be found in Github at https://github.com/akopytov/sysbench.
What I have noticed though, is that while widely-used, some aspects of sysbench are not really familiar to many. For instance, the easy way to expand/modify the MySQL tests is using the lua extension, or the embedded way it handles the random number generation.
Why This Article?
I wrote this article with the intent to show how easy it can be to customize sysbench to make it what you need. There are many different ways to extend sysbench use, and one of these is …
[Read more]One problem I have experienced during logical exporting and importing databases is the tools don’t output any progress indicator (or anything at all really) and using the -v (verbose) switch outputs every single MySQL command being run from the import file. As, importing and exporting of databases is one of the most frequent activity being performed , I Just felt the need of some tool that could help me check the approximate progress of import and export of databases going on.
So, I searched and fortunately could find a tool named Pipeviewer (PV) which allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA .
Installation :
On Centos and RHEL :
[root@vm1 vagrant]# yum install pv
[root@vm1 …
[Read more]What you may not know about random number generation in sysbench
Sysbench is a well known and largely used tool to perform benchmarking. Originally written by Peter Zaitsev in early 2000, it has become a de facto standard when performing testing and benchmarking. Nowadays it is maintained by Alexey Kopytov and can be found in Github at https://github.com/akopytov/sysbench.
What I have noticed though, is that while widely-used, some aspects of sysbench are not really familiar to many. For instance, the easy way to expand/modify the MySQL tests is using the lua extension, or the embedded way it handles the random number generation.
Why this article?
I wrote this article with the intent to show how easy it can be to customize sysbench to make it what you need. There are many different ways to extend sysbench use, and one of these is through …
[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 normal MySQL instance ?
As I told, the Global Transaction Identifier ( GTID ) will be the combination of server_uuid and transaction_id . …
[Read more]A few days ago one of our intern @mydbops reached me with a SQL query. The query scans only a row according to the execution plan. But query does not seems optimally performing.
Below is the SQL query and its explain plan. ( MySQL 5.7 )
select username, role from user_roles where username= '9977223389' ORDER BY role_id DESC LIMIT 1;
Execution plan and table structure
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_roles
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
show create table user_roles\G
*************************** 1. row ***************************
Table: …[Read more]