Ideas, releases, practical guides, and perspectives from the people building with MySQL.
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 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:
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]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 :
Yes, It is possible to execute …
[Read more]Percona is happy to announce the experimental release of Percona XtraDB Cluster 8.0. This is a major step for tuning Percona XtraDB Cluster to be more cloud- and user-friendly. This is the second experimental release that combines the updated and feature-rich Galera 4, with substantial improvements made by our development team.
Improvements and New Features in Percona XtraDB Cluster
Galera 4, included in Percona XtraDB Cluster 8.0, has many new features. Here is a list of the most essential improvements:
Today we will see how to install WordPress in Oracle Cloud (OCI).
We will use Oracle Cloud’s Free Trier and deploy 2 compute instances:
We will use one public IP to reach our site and our two servers will communicate using their own private network:
Deploying 2 compute instances
Let’s start by creating 2 compute instances:
We will use the Oracle Linux 7.7 on Micro Instances:
As I am in Belgium, I use the Frankfurt datacenter.
When our instances are deployed, we can find the public IP of one of them. This is the one we will use as webserver (you can free the public IP on the other one, the MySQL one):
At the creation, we entered a ssh key to access …
[Read more]
How to Audit only DML Actions ?
Step by step to
Enable Fine Grained MySQL Auditing for Insert/Update/Delete
Queries for all Users.
Before i talk about MySQL Audit Filtering , it is more important
to know below details:-
What is MySQL Enterprise Audit?
At high level, database auditing is the process to guard against misuse of information, track the use of database resources and activity like
-Who accessed the database(Users)
- Where these queries has been executed(Host Name).
-What kind of queries has been executed to db(events/statements).
-When did this happened(Time)
…[Read more]Few months back , I came to know about the tool Binlog2sql . The tool has very cool features like .
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 requirements.txt
Make sure, the …
[Read more]Database schema change is becoming more frequent than before, Four out of five application updates(Releases) requires a corresponding database change, For a DBA schema change is a more often a repetitive task, it might be a request from the application team for adding or modifying columns in a table and many more cases.
MySQL supports online DDL from 5.6 and the latest MySQL 8.0 supports instant columns addition.
This blog post will look at the online DDL algorithms inbuilt which can be used to perform schema changes in MySQL.
DDL Algorithms supported by InnoDB is,
INPLACE Algorithm:
INPLACE algorithm performs
operations in-place to the original table and avoids the table
copy and rebuild, whenever possible.
If the INPLACE algorithm is specified with the …