The latest from the MySQL community

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 …

In this article, 1st of a MySQL 5.7 Security series, we will see how to enforce Strong Passwords with Password Validation Plugin when using MySQL 5.7.

Authentication with ID and password is a very simple and common (because it’s simple) way to secure the access to a resource, however the password can be the weak point of this system. In order to increase the security level, you can required that your user passwords meet certain minimal security requirements, using the MySQL Password validation plugin!

MySQL 8.0 will be GA soon (just my assumption: Oracle doesn't tell me anything about its release plans) and it's time to think about having a look at it.
If this is your first try of MySQL 8, get prepared for several impacting differences from previous versions.

In this article I won't tell you what you can do with MySQL 8: there is plenty of material about this, including in this very blog. I will instead concentrate on differences from previous versions that users need to know if they want to avoid surprises.

Data Directory

Let's start with an observation of the data directory.
After a standard installation, without any additional options, I see the following:

Files that I expected to see

auto.cnf
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
ibtmp1
(dir) mysql
(dir) …

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

Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents Performance Schema for MySQL Troubleshooting on Thursday, March 1, 2018, at 10:00 am PST (UTC-8) / 1:00 pm EST (UTC-5).

Register Now

Performance Schema, first introduced in version 5.5, is a really powerful tool. It is actively under development, and each new version provides even more instruments for database administrators.

Performance Schema is complicated. It is also not free: it can slow down performance if you enable certain …

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

In this blog post, I’ll look at how you can analyze raw MySQL query logs with ClickHouse.

For typical query performance analyses, we have an excellent tool in Percona Monitoring and Management. You may want to go deeper, though. You might be longing for the ability to query raw MySQL “slow” query logs with SQL.

There are a number of tools to load the MySQL slow query logs to a variety of data stores. For example, you can find posts showing how to do it with LogStash. While very flexible, these solutions always look too complicated and limited in functionality to me.   

By far the best solution to parse and load MySQL slow query logs (among multiple log types supported) is Charity …

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 …

Where the hell is it?

The MySQL shell is a potentially useful tool that has been intentionally made difficult to use properly.

It was introduced, with much fanfare, with the MySQL Document Store, as THE tool to bridge the SQL and no-SQL worlds. The release was less than satisfactory, though: MySQL 5.7.12 introduced a new feature (the X-protocol plugin) bundled with the server. The maturity of the plugin was unclear, as it popped out of the unknown into a GA release, without any public testing. It was allegedly GA quality, although the quantity of bug reports that were filed soon after the release proved otherwise. The maturity of the shell was known as "development preview", and so we had a supposedly GA feature that could only be used with an alpha …

The slides and video from my FOSDEM presentation on histogram support in MySQL can now be downloaded.  Check it out if you want to learn more about how you can create histograms to improve query plans in MySQL 8.0.

The presentation shows you how to create histograms over column values, 
presents the types of histograms you can create, and discusses best practices for using histograms.

Prior to MySQL 8.0, the query optimizer lacked statistics on columns that are not indexed.  With histograms the query optimizer will be able to make more accurate selectivity estimates for conditions on such columns.   With …