In this blog post, I want to go over some of the day-to-day MySQL tools and methods DBAs use to analyze queries and visualize “what is going on?” I won’t be going into the nitty-gritty details of each of these tools, I just want to introduce you to them and show you what they look like so you will know what types of information they provide.

This isn’t a comprehensive list by any means but consider it a primer for those starting with MySQL and wanting to know what a query is going to do or is doing.

The two sides of query analysis are examining a query BEFORE you run it, and then analyzing what actually happened AFTER you run it.

Let’s start with the tools you can use to predict a query’s future.

In the beginning, there was EXPLAIN. The venerable EXPLAIN command has been with us a long time as a built-in MySQL utility statement. Its purpose is to explain that what the optimizer …

In our last blog, we had a look at MySQL 8 and mentioned new features available here. However, some of those features are already in MariaDB and have been for a while. We will here compare database roles in both server dialects.

The Purpose of Database Roles

It’s common for numerous users within an organization to share the same database privileges. A role bundles a number of privileges together so that the DBA can set the privileges for a group of users all at once, rather than having to set each user’s privileges individually.  

With shared applications, it is not uncommon for multiple users to share the same user account. The drawback to this arrangement is that there is no way to see which actual user was responsible for which action.

Roles make managing this much easier. For example, there …

 Last_SQL_Error: Could not execute <Update/insert/delete>_rows event on table <dbname>.<tablename>; Can’t find record in ‘<tablename>’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log <master binary log file name>, end_log_pos <master log file position>

This is one of the error for MySQL Replication when MASTER and SLAVE replica not in sync. The Reason for replication getting out of sync due to various reasons. But the common cause is particular table row or data not matched/missing on the slave while applying transaction received from MASTER. This transaction can be insert/update/delete type.

In this blog post will discuss support for MEMORY storage engine table with MySQL Replication and few examples with a possible solution. As per official MySQL DOC, …

In this blog post, we’ll checkout Percona Monitoring and Management vs. gnuplot when it comes to creating great metrics graphs. gnuplot is great, but PMM might provide graphs that are easier to read.

Percona Monitoring and Management (PMM) is our free and open-source platform for managing and monitoring MySQL®, MariaDB® and MongoDB® performance, and is based on Grafana and Prometheus. It collects many kinds of metrics: OS metrics, MySQL metrics or MongoDB metrics. But there are times where you just ran a benchmark or collected your specific metrics and you want to visualize them.

For a long time, I used gnuplot for that (and I still like it). But when I was working on a recent blog post, one of my colleagues wrote me: …

Publishing a book internationally turned out to be a bit more complex than I
originally thought. Therefore there are three different ways to order the book
MySQL Cluster 7.5 inside and out.

The E-book which is now available world-wide.
The paperback version. This is also now available world-wide.
Finally the bound version which is available from Nordic countries
and Germany and Switzerland.

The original idea was to publish it as an E-book and as a bound book.
Given that the book is 640 pages long I felt that I wanted a bound book
to ensure that I can read the book a lot. I've got a few copies of the bound
book at home and I have it on my desk all the time together with Jesper
and Mikiyas Pro MySQL NDB Cluster book.

As it turned out the printer only had international agreements to
print paperback books with figures in black and white …

The latest release of Percona Monitoring and Management (PMM) 1.8.0 is available as of February 27, 2018.

Percona Monitoring and Management is a free and open-source platform for managing and monitoring MySQL and MongoDB performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

This release introduces many improvements in the user interface and optimizations of performance.

New landing page

The home page has been removed in favor of the …

In this blog post,  I’ll look at how to catch slow and frequent queries with ProxySQL.

More and more people are using ProxySQL because it is a great tool and it can help DBAs a lot. But many people do not realize that it is more powerful than it looks. It has many features and possibilities. I am going to show you one of my favorite “tricks” / use cases.

There are plenty of blog posts explaining how ProxySQL works. I am not going to that again. Instead, let’s jump straight to the point. There is a table in ProxySQL called “stats.stats_mysql_query_digest”. It is one of my favorite tables because it basically records all the queries that were running against ProxySQL. Without collecting any queries on the MySQL server, I can find …

As for each previous version, to upgrade to MySQL 8.0 you must first read the upgrade section in the manual. It’s important to know that you should upgrade without skipping a major version, so upgrade from 5.6 to 8.0 is not supported neither recommended.

Please also note that upgrades from any MySQL 8.0 RC won’t work. We support upgrade process only from GA versions.

Then one of the most important section to be aware of, is the incompatibility changes brought with the new version.

Once you have read the upgrade section in the manual and the release note of the version you want to install, with MySQL 8.0 and its new MySQL Shell, we developed …

Bad query performance is the most common problem DBA’s have to deal with. There are numerous ways to collect, process and analyze the data related to query performance - we’ve covered one of the most popular tools, pt-query-digest, in some of our previous blog posts:

Become a MySQL DBA blog series

• Analyzing your SQL Workload using pt-query-digest
• Deep Dive SQL Workload Analysis using pt-query-digest

When you use ClusterControl though, this is not always needed. You can use the data available in ClusterControl to solve your problem. In this blog post, we’ll look into how ClusterControl can …

The ability to restore MySQL logical backups is a significant part of disaster recovery procedures. It’s a last line of defense.

Even if you lost all data from a production server, physical backups (data files snapshot created with an offline copy or with Percona XtraBackup) could show the same internal database structure corruption as in production data. Backups in a simple plain text format allow you to avoid such corruptions and migrate between database formats (e.g., during a software upgrade and downgrade), or even help with migration from completely different database solution.

Unfortunately, the restore speed for logical backups is usually bad, and for a big database it could require days …