With MySQL 8.0, engineers improved the visibility of data locking. Usually it was not that easy to deal with the output of SHOW ENGINE INNODB STATUS and mixing Information_Schema tables like INNODB_LOCKS and INNODB_LOCK_WAITS.

So in MySQL 8.0 we instrument data locks in Performance_Schema and we changed the SYS Schema view related to the InnoDB Locks Wait too.

This means that when a transaction A is locking row R, and transaction B is waiting on this very same row, B is effectively blocked by A. What we added is extra instrumentation to expose which data is locked (R), who owns the lock (A), and who is waiting for the data (B).

Testing

Let’s have a look at this simple scenario in MySQL 5.7.21 and 8.0.4 to compare:

2 clients starting a …

As you may already know, MySQL 8.0 is coming with SQL Roles . I’ve already quoted them before in some posts (here and here). Giuseppe Maxia is doing a great job testing and promoting them (thank you for that!). Of course he also made some remarks on …

Prior to MySQL 8.0, MySQL was missing a very well know feature in other RDBMS: optimizer histograms.

The Optimizer Team implemented this feature as more and more MySQL DBAs were requesting it.

Definition

But what are histograms ? For wikipedia, a histogram is an accurate representation of the distribution of numerical data. For RDBMS, a histogram is an approximation of the data distribution within a specific column.

So in MySQL, histograms help the optimizer to find the most efficient Query Plan.

Example

To illustrate how histograms  influence the optimizer, I will use data from dbt3.

My query is simple: …

As of version 0.2.1, dbdeployer allows users to customize composite sandboxes more than ever. This is done by manipulating the default settings, which are used to deploy the sandbox templates.

In order to appreciate the customization capabilities, let's start with a vanilla deployment, and then we have a look at the possible changes.

$ dbdeployer deploy replication 8.0.4
Installing and starting master
Database installed in $HOME/sandboxes/rsandbox_8_0_4/master
. sandbox server started
Installing and starting slave 1
Database installed in …

Security features overview

One of Oracle's tenets is the focus on security. For this reason, when it took over the stewardship of MySQL, it started addressing the most common issues. It was not quick acting, but we have seen real progress:

1. MySQL 5.7 has removed the anonymous accounts, which was the greatest threat to security. Because of those accounts, and the default privileges granted to them, users without any privileges could access the "test" database and do serious damage. Additionally, because of the way the privilege engine evaluates accounts, anonymous users could hijack legitimate users, by preventing them to work …

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

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 …

Today the call for participation for Oracle Open World 2018 has started.

The 2018’s edition will take place in San Francisco as usual, from October 22nd to 25th.

As every year, MySQL will be present and if you want to participate, we encourage you to submit a session. We encourage the submission on the following topics:

• case studies / user stories of your use of MySQL
• lessons learned in running web scale MySQL
• Production DBA/DevOps perspectives into Architecture, Performance, Replication, InnoDB, Security, etc

The call for paper is open until March 22nd, don’t miss the chance to be part …

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 …

How it happened

A few years ago I started thinking about refactoring MySQL-Sandbox. I got lots of ideas and a name for the project (dbdeployer) but went no further. The initial idea (this was 2013!) was to rewrite the project in Ruby: I had been using Ruby at work and it looked like a decent replacement for Perl. My main problem was the difficulty of installation in an uncontrolled environment. If you have control over your environment (it's your laptop or you are in charge of the server configuration via Puppet or similar) then the task is easy. But if you ever need to deploy somewhere with little or no notice, it becomes a problem: there are servers where Perl is not installed, and is common that the server also have a policy forbidding all scripting languages from being deployed. …