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 …

This blog was initially referring to the audit plugin, but I renamed it as check plugin to be more compatible with all the current and future methods included in it.

As you may know, it’s now possible to create your own plugins for MySQL Shell. See the following posts:

• https://lefred.be/content/overview-on-mysql-shell-8-0-17-extensions-plugins-and-how-to-write-yours/
• https://lefred.be/content/mysql-router-8-0-17s-rest-api-mysql-shell-extensions/
• …

To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:

mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));

Now, let’s create a user :

mysql> create user myuser identified by 'mypassword';

And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:

• myrole1: user has access to both tables in their entirety, reads and writes
• myrole2: user has access only to `table2`, reads and writes
• myrole3: user has only access to the column `name`of `table1` and …

MySQL 8.0 is now part of RedHat Enterprise 8 and other distros based on it like CentOS and Oracle Linux.. This is a very good thing !

However if for any reason you want to use the latest version of MySQL from the Community Repository, you may encounter some frustration if you are not familiar with the new way the package manager works.

Let’s start by verifying our system:

LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID:    OracleServer
Description:    Oracle Linux Server release 8.0
Release:    8.0
Codename:    n/a

We can see that we are on Oracle Linux 8.0. So now let’s try to install MySQL Server:

[root@localhost ~]# dnf install mysql-server
Last metadata expiration check: 0:08:15 ago on Sat 02 Nov 2019 09:54:07 AM UTC.
Dependencies resolved.
============================================================================================
  Package                 Arch   Version …

As people are getting back from vacations and application traffic is going back and hitting database backend hard, it is a good time to think about performance and prepare for the winter holiday spike. Whenever you are on-premise or in the cloud, questions are the same:

• Are we satisfied with the current MySQL performance?
• How much traffic are we currently serving?
• Is there enough capacity to stay stable as traffic increases?
• What would be the best way to stay cost-effective as we grow?

Unfortunately, the answers are not so obvious and require a thorough performance analysis. However, there is a chance that your problem has already been solved by someone else. I do not mean StackOverflow (which is a beautiful place, by the way), but some higher level questions which people usually discuss at conferences, and we have several of them ongoing or just a couple of weeks away.

…

Mid September, MySQL Community, MySQL Customers and MySQL Engineers will be in San Francisco to share their experience and present the new features of your favorite database !

The event will be held in Moscone South (just Mirko Ortensi‘s Hands-on Lab will be delivered in Moscone West).

During the week, the MySQL Community Team will host the traditional MySQL Reception. We got so great feedback from last year that we decided to renew the experience in the same awesome location, the Samovar Tea Lounge at Yerba Buena Gardens. Don’t forget that you need to register for this reception but no OOW pass is required. Please register here !

Back to the conference, you can find the full schedule for the session in the …

Answering this question is not easy. Like always, the best response is “it depends” !

But let’s try to give you all the necessary info the provide the most accurate answer. Also, may be fixing one single query is not enough and looking for that specific statement will lead in finding multiple problematic statements.

The most consuming one

The first candidate to be fixed is the query that consumes most of the execution time (latency). To identify it, we will use the sys schema and join it with events_statements_summary_by_digest from performance_schemato retrieve a real example of the query (see this post for more details).

Let’s take a look at what sys schema has to offer us related to our mission:

> show tables like …

When somebody wants to migrate from a single MySQL instance to a full HA solution using MySQL InnoDB Cluster, the best solution to reduce the downtime is to use asynchronous replication and switch database only once at a certain point in time when everything is ready. This is almost what I explained already in this post.

The most difficult part was related to the provisioning of the existing data to the new cluster members. A backup (physical or logical) was required. It should have been restored on every nodes and we had to be sure to not mess up with the GTIDs.

This is not more the case since MySQL 8.0.17 ! Now we can use the CLONE plugin to start the cluster provisioning too.

The current situation

…

You may have noticed that in many of my demos, I disable firewall and SELinux (I even use --initialize-insecure sometimes ). This is just to make things easier… But in fact enabling iptables and SELinux are not complicated.

Firewall

These examples are compatible with Oracle Linux, RedHat and CentOS. If you use another distro, the principle is the same.

For the firewall, we need first to allow incoming traffic to MySQL and MySQL X ports: 3306 and 33060:

# firewall-cmd --zone=public --add-port=3306/tcp --permanent
# firewall-cmd --zone=public --add-port=33060/tcp --permanent

If you don’t plan to restart the firewall, you just need to run the same commands without --permanent to make then immediately active.

Then we need to allow the Group Replication’s communication port. This is usually 33061 but it can be configured in …

From Drupal’s website, we can see that now the support of MySQL 8 is ready.

I just tested it and it works great !

The only restriction is related to PHP and the support for the new authentication method in php-mysqlnd.

In this previous post, I was happy because it was included in PHP 7.2.8, but this has been reverted back since then. Currently none of the latest version of PHP 7.x is supporting this authentication method.

We can easily verify this, first with the PHP version provided by default in Oracle Linux 8:

# php -i | grep "Loaded plugins\|PHP Version " | tail -n2
PHP Version => 7.2.11
Loaded plugins => mysqlnd,debug_trace,auth_plugin_mysql_native_password, …