In my Webinar on Using Percona Monitoring and Management (PMM) for MySQL Troubleshooting, I showed how to use direct queries to ClickHouse for advanced query analysis tasks. In the followup Webinar Q&A, I promised to describe it in more detail and share some queries, so here it goes.

PMM uses ClickHouse to store query performance data which gives us great performance and a very high compression ratio. ClickHouse stores data in column-store format so it handles denormalized data very well. As a result, all query performance data is stored in one simple “metrics” table:

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.

1. Review your schema and identify data that *must* be audited. It’s likely your company’s personal identifiable information (PII) …

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:

• Customer row id=2 was deleted …

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 …

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 .
• Pager with MySQL client program
• Safe UPDATE / DELETE with MySQL client program
• Define the maximum number of rows in result set ( SELECT’s )

Execute the OS commands inside the MySQL client program :

Yes, It is possible to execute …

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:

• Streaming replication to support large transactions
• The synchronization functions allow action coordination (wsrep_last_seen_gtid, wsrep_last_written_gtid, wsrep_sync_wait_upto_gtid)
• More granular and improved error logging. wsrep_debug is …

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:

• the webserver (apache, PHP and WordPress)
• the database server (MySQL 8.0)

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 …

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 ?
• Features of MySQL Audit.
• Why Audit is Important?
•  How to Audit only DML statements for Particular/All Users ?
• Audit Log Restrictions
• Conclusions

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)

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 requirements.txt

Make sure, the …