Showing entries 11 to 20 of 235
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: MySQL 8.0 (reset)
MySQL 8.0 Flow Control in Group Replication

We are well aware that MySQL Group Replication is one of the faster evolving clustering Technology for MySQL. Flow Control plays a key factor in Group Replication performance and data integrity . In this blog I am going to explain about the Flow Control mechanism and How it has evolved in MySQL 8 ?

What is Flow Control ?

MySQL Group Replication / Native Async replication needs binary logs to get the data flow across the servers.

What makes the difference ?

In the MySQL Group Replication we are trying to achieve the Synchronous replication with the help of a Flow Control mechanism and transaction acknowledgments ( certification ).

Without Flow Control, the MySQL Group Replication is asynchronous replication ? Yes, consistency is lost.

Lets us consider

We have three nodes ( GR1, GR2, GR3 ) . Gr1 is the master and and other two servers ( GR2, GR3 ) are the …

[Read more]
MySQL 8.0: if I should optimize only one query on my application, which one should it be ?

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 …
[Read more]
MySQL 8.0 Memory Consumption on Small Devices

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 …

[Read more]
Migrate from a single MySQL Instance to MySQL InnoDB Cluster using CLONE plugin

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

[Read more]
MySQL 8.0 and wrong dates

In MySQL 8.0, when using invalid values for date data types, an error is returned. This was not the case in 5.x versions.

Let’s have a look using the table definition of bug 96361:

CREATE TABLE `new_table` (
  `id_table` int(11) NOT NULL AUTO_INCREMENT,
  `text_table` varchar(45) DEFAULT NULL,
  `date_table` date DEFAULT NULL,
  PRIMARY KEY (`id_table`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now we can try the following statement in MySQL 5.7 and MySQL 8.0:

MySQL 5.7.26> SELECT id_table, text_table 
              FROM new_table WHERE date_table = '' OR date_table IS NULL;
Empty set, 1 warning (0.01 sec)

MySQL 5.7.26> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   | …
[Read more]
MySQL InnoDB Cluster, automatic provisioning, firewall and SELinux

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 …

[Read more]
MySQL Server 8.0.17: Thanks for the Contributions

Tweet

MySQL 8.0.17 was released Monday and it includes great features such as the Clone feature and multi-valued indexes. There are also several nice contributions from the community. These are the changes that this blog is about.

The contributions to MySQL Server 8.0.17 include patches from Facebook, Daniël van Eeden, Mattias Jonsson, and Simon Mudd (all from Booking.com), Daniel Black, Yibo Cai (from Arm Technology), Josh Braden, and Zhou Mengkang. The larger contributions are:

  • The mysql client program now sends os_user and os_sudouser connection attributes, when available, to indicate the name of the operating system user running the program and the value of the SUDO_USER environment variable, respectively. For general information …
[Read more]
MySQL Router 8.0.17’s REST API & MySQL Shell Extensions

You have seen in this previous post, that since 8.0.17, it’s now possible to query the MySQL Router using its REST API.

Additionally, we also saw in this post, that since 8.0.17, we are now able to write extensions to MySQL Shell using the Extension Framework.

Let’s combine both and see how we can integrate the MySQL Router’s REST API in the Shell.

I’ve created an extension in ext.router that creates a MySQL Router Object.

The new extension, as a method to create the object:

This is an example that illustrates how to create a MySQL Router Object, as you can see you can pass the password directly as parameter but it’s not recommended in interactive mode. It’s …

[Read more]
MySQL 8.0.17 and Drupal 8.7

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, …
[Read more]
Create an Asynchronous MySQL Replica in 5 minutes

I have already posted some time ago a post related to the same topic (see here).

Today, I want to explain the easiest way to create an asynchronous replica from an existing MySQL instance, that this time has already data !

The Existing Situation and the Plan

Currently we have a MySQL server using 8.0.17 and GTID enabled on mysql1. mysql2is a single fresh installed instance without any data.

The plan is to create a replica very quickly and using only a SQL connection.

Preliminary Checks

First we verify that mysql1 has GTID enabled. If not we will enable them:

mysql> select @@server_id,@@gtid_mode,@@enforce_gtid_consistency;
+-------------+-------------+----------------------------+
| @@server_id | @@gtid_mode | @@enforce_gtid_consistency | …
[Read more]
Showing entries 11 to 20 of 235
« 10 Newer Entries | 10 Older Entries »