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

MySQL is simpler than the Oracle example  because MySQL only has Nested Loops and doesn’t have Hash Join.

Here is the query, like on the Oracle example from the last blog post

select max(t1.data) 
from  t1, t2  
where t1.id = t2.id 
and t1.clus  = 1
;

So there are only 4 examples below which all do NL joins

1. full table scan on T1 and T2
   1. 7.83 secs
2. index on T1 predicate filter column
   1. 7.39 secs
3. index on T2 join column
   1. 0.49 secs
4. index on both T2 join column and T1 predicate filter column
   1. 0.06 secs

There isn’t an idea of “explain analyze” on MySQL until MySQL 8.0.18 and I did my testing on …

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

Writing MySQL Proxy in GO for self-learning: Part 1 — TCP Proxy

Weekend. Quarantine. If you bored like me, and want to learn something new, you can join my journey of writing yet another MySQL Proxy Server.

In the past, I had a crazy idea of writing MySQL Proxy Server. The first reason was to understand the MySQL Communication Protocol to let me write more efficient programs. The second one was to learn more about low-level network programming concepts in general. Finally, I have time at least to start… and you can join me!

The real usage of MySQL Proxy can vary:

• Testing and benchmarking of the infrastructure
• Testing your applications for dealing with MySQL network failures and delays
• Wrap the MySQL usage with own business layer
• Load balancing
• Multiplexing

Prelude

I’m not sure if I can …

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 up coming MySQL… 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 …

Tweet

I have over the last few years been fortunate to have two books published through Apress, Pro MySQL NDB Cluster which I wrote together with Mikiya Okuno and MySQL Connector/Python Revealed. With the release of MySQL 8 around a year ago, I started to think of how many changes there has been in the last few MySQL versions. Since MySQL 5.6 was released as GA in early 2013, some of the major features related to performance tuning includes the Performance Schema which was greatly changed in 5.6, histograms, EXPLAIN ANALYZE, hash …

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 …