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 …

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 …

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 …

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 …

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 Group replication cluster without single point of failure ?

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 . …

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: …

There are many users of MySQL in Cloud environment regions. It’s ideal that those MySQL instances are implemented with only private-ips in the respective Subnet. No public-ips. For that matter, all cloud infrastructure resources are ideally placed behind networks that provision only private-ips.  This limits any front-end attack surface for an application stack.  Certainly the… 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 .
• 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 …

If you are going to migrate your MySQL workloads to GCP’s managed database service CloudSQL, then you have to keep these points in mind. We have done a lot of CloudSQL migrations. But sometimes it’s not smooth as we thought. Generally, people don’t even think that these thinks will make the replication failure. I listing 3 things that ate our brain and time while migrating to CloudSQL.

1. Server character set:

CloudSQL by default using utf8 as the server character set. But it is customizable, we can change it any time. But still, it’ll mess up your application later. We had a MySQL server on a VM where the server’s character set was latin1. We dump the database and restore it to CloudSQL. While launching the CloudSQL we didn’t set up any Database flags. So the data restore with utf8 character set.

Before Migration

mysql> SHOW SESSION …