For performance, having a warm InnoDB Buffer Pool is very important. What does that mean ?

A warm buffer pool means that the most used pages (working set) required by the production workload are already loaded in memory (in the buffer pool). If so, MySQL doesn’t need to read the pages from disk every time it requires the most used page and speeds up the process when the needed data is already in memory.

When you start MySQL, by default the InnoDB Buffer Pool is cold and the warm up process can even take days sometimes…

So, you can already deduce that restarting mysqld is a source of having a cold Buffer Pool as it will start empty. Another reason to have a non optimal Buffer Pool is to load it unnecessary pages. This can happen during a logical dump or load. If you regularly do a mysqldump for example (don’t forget that MySQL Shell dump & load is better if you do logical dumps, but introduces also the …

This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one:

2020-07-24T06:54:00.877128Z 46 [Warning] [MY-013360] [Server] 
Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in 
a future release. Please use caching_sha2_password instead'

The context was that the user upgraded from MySQL 5.7 to MySQL 8.0 and changed the authentication plugin of all users to caching_sha2_password.

As the warning message was written to the error log almost twice per second, I suspected a monitoring tool. The user said, that no application, no monitoring was using this server yet.

Of course we verified that all users had the plugin changed.

This is important when helping people (friends, colleagues, …

In MySQL, it’s very common and recommended to you an auto_increment as Primary Key.

However that integer can have different types each having their size limitation, and they can also be signed or not.

Many people when creating a table use by default the following syntax do define their auto_increment primary key:

id int auto_increment primary key

This is a signed integer where the maximum value is 2,147,483,647.

It’s recommended as a DBA to keep an eye on how fast your tables are filling up and how much records can still be added to your tables.

And this can happen even to the bests ! (see this Github Availability Report on May 5th at 00:45)

MySQL Shell Plugin

Once again, MySQL Shell comes to the rescue with an easy plugin: …

To avoid infinite replication loops MySQL doesn’t allow you to have log_slave_updates and replicate-same-server-id.

When using GTIDs that may lead to something not expected that you may not be aware of.

In this scenario, we have 2 MySQL servers using GTID. The sever uuid part of the GTID has been modified in the illustration to make it more clear. Both servers have log_slave_updates enabled too:

So far nothing unusual. So let’s write data on the master (MySQL A):

We can see that this first …

As you may know, I’m using MySQL exclusively on GNU/Linux. To be honest for me it’s almos 20 years that the year of Linux on the desktop happened. And I’m very happy with that.

But this week-end, I got a comment on an previous post about upgrading to MySQL 8.0, asking how to proceed on Windows. And in fact, I had no idea !

So I spent some time to install a Windows VM and for the very first time, MySQL on Windows !

The goal was to describe how to upgrade from MySQL 5.7 to MySQL 8.0.

So once MySQL 5.7 was installed (using MySQL Installer), I created some data using MySQL Shell:

Of course I used latest MySQL Shell, 8.0.18 in this case. Don’t forget that if you are using MySQL Shell or MySQL Router, you must always use the latest …

Recently, PeterZ pointed a huge difference in memory usage of MySQL 8.0 compare to MySQL 5.7. This can be an issue for small instances if the same configuration for buffers like the buffer pool are not changed.

As explained in Peter’s article, this can lead to the awakening of the so feared OOM Killer !

MorganT, pointed accurately in his comment what is the source of such difference and how this was then caused by the new instrumentation added in MySQL 8.0.

Nothing is free, even as a …

Starting with version 1.32.0, dbdeployer has the ability of downloading a selection of MySQL tarballs from several sources.

This means that, when working in an empty box, you can populate it with database servers using

dbdeployer.

The “empty box” mentioned in the title is not really empty. It’s a Linux (or MacOS) host that is able to run a MySQL server. As such, it needs to have at least the prerequisites to run MySQL server (such as the libnuma and libaio packages), and a bash shell to run the scripts created by dbdeployer.

To try the thrill of an empty box that quickly becomes a working environment, we can use a docker image datacharmer/mysql-sb-base that I have created for this purpose.

$ docker pull datacharmer/mysql-sb-base
Using default tag: …

Everywhere I go to present MySQL InnoDB Cluster, during the demo of creating a cluster, many people doesn’t understand why when I’ve 2 members, my cluster is not yet tolerant to any failure.

Indeed when you create a MySQL InnoDB Cluster, as soon as you have added your second instance, you can see in the status:

    "status": "OK_NO_TOLERANCE",      
    "statusText": "Cluster is NOT tolerant to any failures.",

Quorum

Why is that ? It’s because, to be part of primary partition (the partition that holds the service, the one having a Primary-Master in Single Primary Mode, the default mode), your partition must reach the majority of nodes (quorum). In MySQL InnoDB Cluster (and many other cluster solutions), to achieve quorum, the amount of members in a partition must be > (bigger) than 50%.

So when we have 2 nodes, if there is a network issue between …

When I designed dbdeployer, I wanted to eliminate most of the issues that the old MySQL-Sandbox had:

• dependencies during installation
• mistaken tarballs
• clarity of syntax
• features (un)awareness.

Dependencies during installation did go away right from the start, as the dbdeployer executable is ready to be used without additional components. The only dependency is to have a host that can run MySQL. There is little dbdeployer can do about detecting whether or not your system can run MySQL. It depends on which version and flavor of MySQL you are running. It should not be a big deal as I assume that anyone in need of dbdeployer has already the necessary knowledge about MySQL …

On this article, I will show you how to migrate your wordpress database from the MariaDB on CentOS to the real MySQL.

Why migrating to MySQL 8.0 ?

MySQL 8.0 brings a lot of new features. These features make MySQL database much more secure (like new authentication, secure password policies and management, …) and fault tolerant (new data dictionary), more powerful (new redo log design, less contention, extreme scale out of InnoDB, …), better operation management (SQL Roles, instant add columns), many (but really many!) replication enhancements and native group replication… and finally many cool stuff like the new Document Store, the new MySQL Shell and MySQL InnoDB Cluster that you should already know if you follow this …