MySQL 8.0.22 New Features Summary

November 10, 2020
Sakila mozaik by Olivier DASINI

Presentation of some of the new features of MySQL 8.0.22 released on October 19th, 2020.


Highlight


Slides


Download this presentation and others on my SlideShare account.


Video


That might interest you




Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!


0

Automatic connection failover for Asynchronous Replication

November 5, 2020
Plage par Olivier DASINI

TL;DR

Since MySQL 8.0.22 there is a mechanism in asynchronous replication that makes the receiver automatically try to re-establish an asynchronous replication connection to another sender, in case the current connection gets interrupted due to the failure of the current sender.



Asynchronous automatic connection failover automates the process of re-establishment of an asynchronous replication connection to another sender of sender list.
That means if a the source of a replica crashed, this replica will be able to automatically connect to another source.
One of the biggest interest is to improve Disaster Recovery (DR) architecture.

With this feature, a typical architecture is to have a 3 nodes asynchronous replication cluster.
2 primary nodes in active/passive mode (if you need active/active architecture use MySQL InnoDB Cluster) and the third one is connected to one of the primary, either for DR or for some specialized task like analytics for example.
So in case of unavailability of its primary – if the replication I/O thread stops due to the source stopping or due to a network failure – this replica will automatically connect to the other primary.


Another architecture is to use this asynchronous automatic connection failover feature with MySQL InnoDB Cluster.

Hey guess what? this is the topic of this tutorial and by the way, we will use some of the fanciest MySQL 8.0 features 🙂



Context

4 MySQL 8.0.22 instances :


– mysql_node1 : 172.20.0.11
– mysql_node2 : 172.20.0.12
– mysql_node3 : 172.20.0.13
These 3 are members of a MySQL Group Replication cluster.
I created a MySQL InnoDB Cluster (resources here and here).
I will not use MySQL Router in this tutorial.


– mysql_node4 : 172.20.0.14
It’s my replica.
It will be asynchronously connected to the Group Replication.

Because a picture is worth a thousand words, below is the architecture I want to achieve :

Asynchronous Replica of a MySQL Group Replication by Olivier DASINI

Graphs in this article are from MySQL Enterprise Monitor.



Current status

I’m using MySQL 8.0.22 :


I have a 3 nodes Group Replication cluster up and running :



Setup the replication user

Like you know, there are some preparation steps to be able to use MySQL Asynchronous Replication. If you’re not familiar with replication using GTID, please read this.

On the Group Replication primary – currently mysql_node1 – I setting up the asynchronous replication user:



Setup the clone user

The clone plugin is one of my favorite feature!

It permits cloning data locally or from a remote MySQL server instance.
Cloned data is a physical snapshot of data stored in InnoDB that includes schemas, tables, tablespaces, and data dictionary metadata.
The cloned data comprises a fully functional data directory, which permits using the clone plugin for MySQL server provisioning.

It’s a very convenient way to copy data from the source (the Group Replication) to the replica (mysql_node4).

Thanks to InnoDB Cluster the Clone plugin is already installed on the 3 members.
So on the primary member – currently mysql_node1 – I’ll create a dedicated clone user with the donor privileges for using and monitoring the clone plugin:

Note that I could have used the cluster administrator dedicated user instead of create a specialized clone user.


On mysql_node4, the future replica, I’ll create the same user but with the recipient privileges.
But before I’ll install the clone plugin and set the clone donor list:



Clone a MySQL instance

Now we have everything all set to create the replica from a member of the group.

On the future replica – mysql_node4 – we can now run the clone instance command:


If you want to monitor the cloning progress run the following query:


When the cloning is over, the MySQL instance must be restarted (that will normally happen automatically).
After the restart, you can verify that the clone completed successfully with the queries below:

and:



Add the replica

First I will setup the configuration information for a replication source server to the source list for a replication channel.
To do that we use the function: asynchronous_connection_failover_add_source
Information needed are the replication channel name, the source server address, port and network namespace, and also the weight.

More information are available here.

For this tutorial I chose the following values:

  • Channel name: autof
  • Source server addresses: mysql_node1, mysql_node2, mysql_node3
  • Source server port: 3306
  • Source server network namespace: ” (empty)
  • Weight: respectively: 50, 90, 90


The replica’s source lists for each replication channel for the asynchronous connection failover mechanism can be viewed in the Performance Schema table replication_asynchronous_connection_failover.


To set the parameters that the replica server uses for connect to the source, we use the well known CHANGE MASTER TO statement.
You already know most of its clauses, so let’s only focus on some of them:

  • SOURCE_CONNECTION_AUTO_FAILOVER : activates the asynchronous connection failover mechanism.
  • MASTER_RETRY_COUNT & MASTER_CONNECT_RETRY : define the failover time. The default setting is… 60 days, probably not what you want :). So, you (most likely) should reduced the settings. e.g. 1 minute is respectively 20 and 3. (20 x 3 = 60)
  • FOR CHANNEL : enables you to name which replication channel the statement applies to. The CHANGE MASTER TO statement applies to this specific replication channel.


Now let’s configure the replication:

Please note that my failover time in this tutorial is 15 seconds (3 x 5). Obviously the relevant setting depends on your needs. A longer duration will probably makes more sense in real life.


Then start the replication, on channel autof using START REPLICA:


 Status information of the replication can be seen with SHOW REPLICA:



Automatic connection failover

We have now configured our replication, with an InnoDB Cluster/Group Replication as a source and a standalone MySQL server as a replica.
Let’s see how the automatic connection failover works.

Restart the replica

I want to see the behavior after a restart of the replication.

State before the stop:


Stop the replication:

MySQL Enterprise Monitor


After a while, start the replication:


Replica picks up where it left off… as you would have expected.



Short unavailability of the source

I want to see the behavior after a short unavailability of the source. I mean a duration lower than the failover threshold – connection_retry_interval x connection_retry_count – 15 seconds (5×3) in this example.

State before the stop of the source, mysql_node2:

… Stop mysql_node2 for 10 seconds …

MySQL Enterprise Monitor

State after the start of the source mysql_node2:

MySQL Enterprise Monitor

Well… nothing changed!
The unavailability of the source was not long enough to trigger the failover.
That is awesome to prevent non necessary failover.



Long unavailability of the source

I want to see the behavior after a longer unavailability of the source. I mean a duration greater than the failover threshold – connection_retry_interval x connection_retry_count – 15 seconds (5×3) in this example.

State before the stop of the source, mysql_node2:

… Stop mysql_node2 for 20 seconds …

MySQL Enterprise Monitor

As expected, the asynchronous automatic connection failover took place. \o/
The new source is now mysql_node3, because it has a bigger weight than mysql_node1 (90 vs 50) and because it was available 🙂



Limitations

Please be aware that in 8.0.22 this feature lacks of some of the needed functionality to replace MySQL Router as means to replicate from an InnoDB Cluster/Group Replication cluster.

Things such as:

  • does not automatically learn about new members or members that are removed
  • does not follow the primary role, it stays connected to whatever host it was connected to
  • does not follow the majority network partition
  • does not care if a host is not part of the group any longer, as long as it can connect, it will

These limitations will be lifted in future versions.




This is a very nice feature starting with MySQL 8.0.22, useful for both MySQL Replication and MySQL Group Replication architectures.

And you know what? There is more to come 😉

Stay tuned!



References




Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

2

Plan your MySQL upgrade

October 21, 2020
Jardin Balata Martinique by Olivier DASINI


I’ve made a short video that will give you tips and tricks to successfully upgrade to MySQL 8



These information are from my presentation : Upgrade from MySQL 5.7 to MySQL 8.0



Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel.

0

MySQL 8.0.21 New Features Summary

August 27, 2020
Sakila mozaik by Olivier DASINI

Presentation of some of the new features of MySQL 8.0.21 released on July 13th, 2020.


Highlight

  • Runtime disabling Innodb Redo Log
  • JSON_VALUE fonction
  • CREATE TABLE… SELECT is atomic
  • Per-user Comments & Attributes
  • MySQL Document Store Enhancements
  • MySQL Shell Enhancements
  • MySQL Router Enhancements
  • MySQL InnoDB Cluster Enhancements
  • MySQL Group Replication Enhancements
  • Thanks to the Contributors


Slides


Download this presentation and others on my SlideShare account.



Watch my videos on my YouTube channel.


That might interest you





Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

1

MySQL 8.0.20 New Features Summary

May 26, 2020
Sakila mozaik by Olivier DASINI

Presentation of some of the new features of MySQL 8.0.20 released on April 27th, 2020.


Highlight

  • Hash Joins
  • New InnoDB Doublewrite Buffer
  • Index-Level Optimizer Hints
  • SHOW_ROUTINE Privilege
  • MySQL Shell Enhancements
  • MySQL Router Enhancements
  • MySQL InnoDB Cluster Enhancements
  • MySQL Replication Enhancements
  • MySQL NDB Cluster Enhancements
  • MySQL Enterprise New Features
  • Thanks to the Contributors


Slides


Download this presentation and others on my SlideShare account.


Video


Watch this video and others on my YouTube channel.


That might interest you





Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

3

MySQL Security – Dual Password Support

May 19, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

TL;DR

Dual-password capability makes it possible to seamlessly perform credential changes without downtime.



MySQL implements dual-password capability with syntax that saves and discards secondary passwords :

  • The RETAIN CURRENT PASSWORD clause for the ALTER USER and SET PASSWORD statements saves an account current password as its secondary password when you assign a new primary password.
  • The DISCARD OLD PASSWORD clause for ALTER USER discards an account secondary password, leaving only the primary password.

The purpose is to avoid downtime while changing passwords in a replicated environment.

Clients can use the old password while a new password is being established in a group of servers and retire the old password only when the new password has been established across the whole group.


The workflow is :

  1. On each server that is not a replication slave, establish the new password
    e.g.
    ALTER USER ‘myApp’@’host’ IDENTIFIED BY ‘NEW_password’ RETAIN CURRENT PASSWORD;
  2. Wait for the password change to replicate throughout the system to all slave servers
  3. Modify each application that uses the myApp account so that it connects to the servers using a password of ‘NEW_password’ rather than ‘OLD_password’
  4. On each server that is not a replication slave, discard the secondary password
    e.g.
    ALTER USER ‘myApp’@’host’ DISCARD OLD PASSWORD;


Let’s take a quick look using MySQL 8.0


Create a user account myApp@localhost with password pwd1 :

Now we can connect with the name and the password :

Note:
As indicated in the output, it is a very bad practice to put the password on the command line interface.


Now the DBA (super user) use ALTER USER statement with the RETAIN CURRENT PASSWORD clause to perform credential changes using the dual password mechanism by adding as primary password pwd2.
Thus pwd1 is now the secondary password :

We can use the user name and the new password (pwd2) to connect :

But the old password (pwd1) is still valid :


Now it is the time to discard the secondary password (pwd1) :

As you can see, only the new password (pwd2) is valid.



To Go Further

Reference Manual



MySQL Security Serie (1st edition)



Thanks for using MySQL!


Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

2

MySQL Security – Failed-Login Tracking and Temporary Account Locking

May 12, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

However, often this is unfortunately not enough.
Good news, MySQL 8.0 provide an easy way to increase database security with its failed-login tracking and temporary account locking feature.



TL;DR

DBA can configure user accounts such that too many consecutive login failures cause temporary account locking.


Temporary Account Locking in MySQL


After a number of consecutive time when the client failed to provide a correct password during a connection attempt, the user account can be temporary locked.

The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS (track consecutive login failures) and PASSWORD_LOCK_TIME (how many days to lock the account).

Both are options of the CREATE USER and ALTER USER statements.


Let’s have a quick look using MySQL 8.0


Account lock

Create a user that would have his account locked for 1 day after 1 consecutive failed logins :

FAILED_LOGIN_ATTEMPTS : how many consecutive incorrect passwords cause temporary account locking.
A value of 0 disables the option.

PASSWORD_LOCK_TIME : number of days the account remains locked or UNBOUNDED (ie the duration of that state does not end until the account is unlocked).
A value of 0 disables the option.


We can see the user account details with mysql.user table :

If login failed a “FAILED_LOGIN_ATTEMPTS” number of time (1 time in this example), the account will be locked :


Tracking and locking could also be set up after the user creation :

In this example this user account will be locked (until the account is unlocked – more on that later) after 2 consecutive failed attempts.


You can also lock an account explicitly using ACCOUNT LOCK clause :

In this example I created a user account with a random password generated by MySQL. This account is created locked.

Details are visible with mysql.user table :

Any connection to this account will raised error 3118 :

This account can be activate with something like :

Again mysql.user table will give you some information :


Account unlock

Account can be unlocked with an ALTER USERACCOUNT UNLOCK statement :


Other possibilities to unlock an account are :

  • Execution of an ALTER USER statement for the account that sets either FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME (or both) to any value.
    e.g.
  • Obviously when the lock duration passes.
    In this case, failed-login counting resets at the time of the next login attempt.
  • Execution of FLUSH PRIVILEGES
  • A server restart



To Go Further

Reference Manual



MySQL Security Serie (1st edition)



Thanks for using MySQL!


Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

4

MySQL Security – Password Verification-Required Policy

May 5, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

However, often this is not enough.
Password Verification-Required Policy can help you to protect your database.
It will make it harder to modify a user’s password if someone get access to user’s session and not the credentials themselves.



TL;DR

MySQL 8.0 has introduced an optional behavior that authorize users to change their password only if they could provide the current password.


Require MySQL users to provide their current password to change it


There are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password verification-required policy.

Let’s play using MySQL 8.0


PASSWORD REQUIRE CURRENT

Require that password changes specify the current password.

Syntax:
CREATE USER <user>@<host> PASSWORD REQUIRE CURRENT;
ALTER USER <user>@<host> PASSWORD REQUIRE CURRENT;


Create a user account with a password generated by MySQL and enable the password verification required policy :

We can see the policy is enable for this account with mysql.user table :

Note that Password_require_current column is Y.


We can test the policy.
Connect to the new created account :

Then modify the password :

To avoid the error 3892, we must use the REPLACE clause and provide the current password.


Please note that privileged users (users having the global CREATE USER privilege or the UPDATE privilege for the mysql system database) can change any account password without specifying the current password, regardless of the verification-required policy.

In other words, as a DBA privileged user I am able to change someone else password without the REPLACE clause :


PASSWORD REQUIRE CURRENT OPTIONAL

Do not require that password changes specify the current password (the current password may but need not be given).

Syntax:
CREATE USER <user>@<host> PASSWORD REQUIRE CURRENT OPTIONAL;
ALTER USER <user>@<host> PASSWORD REQUIRE CURRENT OPTIONAL;


Create a user account with a password generated by MySQL and enable the password verification policy but it is not required :

Note that Password_require_current column is N.


We can test the policy.
Connect to the new created account :

Then modify the password :

The current password is not required to change the password, well it is… optional 🙂


Global policy

The password verification-required policy is controlled by the password_require_current global system variable.

It can be changed online and persisted with SET PERSIST.

An alternative is to write it in the configuration file (usually my.cnf or my.ini) and restart the MySQL instance.


PASSWORD REQUIRE CURRENT DEFAULT

Defer to the global password verification-required policy for all accounts named by the statement.

Syntax:
CREATE USER <user>@<host> PASSWORD REQUIRE CURRENT DEFAULT;
ALTER USER <user>@<host> PASSWORD REQUIRE CURRENT DEFAULT;


Create a user account where its password verification policy take the global default value set a the instance level :

We can test the policy.
Connect to the new created account :

Because the global policy enable the Password Verification-Required Policy, we must use the REPLACE clause.



To Go Further

Reference Manual



MySQL Security Serie (1st edition)



Thanks for using MySQL!


Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

4

MySQL Security – Password Reuse Policy

April 28, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

However, often this is not enough. Actually, some regulations may require that users can not reuse a previous password.

You can do that by setting how often and / or how long an old password can be reuses. In this article, from my new MySQL Security series, we will see how to establish a policy for password reuse with MySQL 8.0 Password Reuse Policy.



TL;DR

MySQL provides password-reuse capability, which allows database administrators to determine the number of unique passwords a user must use before they can use an old password again.


Enable restrictions on reuse of previous passwords with MySQL


The main goal of Password Reuse Policy is to enable restrictions to be placed on reuse of previous passwords.
It can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

There are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password reuse policy.

Let’s dig into it using MySQL 8.0.


PASSWORD HISTORY

Prohibit reusing any of the last 10 (then 24) passwords :


PASSWORD REUSE INTERVAL n DAY

Require a minimum of 180 (then 365) days elapsed before permitting reuse :


Combine types of reuse restrictions

It is also possible to combine both types of reuse restrictions.
Simply use PASSWORD HISTORY and PASSWORD REUSE INTERVAL n DAY together :


Global Policy

Reuse policy can be established globally, as specified by the password_history and password_reuse_interval system variables.

The default password_history value is 0, which disables automatic password expiration.
Same for password_reuse_interval.

password_history and password_reuse_interval variables can be set in the MySQL configuration file (usually my.cnf or my.ini) but it can also be set and persisted at runtime using SET PERSIST :

The same behavior can be achieved using the my.cnf (or my.ini) file :

However it requires a server restart.


To defer the global policy for an account for both types of reuse restrictions you must use the DEFAULT clause :


To establish a global policy such that none of these restriction exist, set password_history and password_reuse_interval to 0 :


Please note that the empty password does not count in the password history and is subject to reuse at any time.



To Go Further

Reference Manual



MySQL Security Serie (1st edition)



Thanks for using MySQL!


Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

3

MySQL Security – Password Expiration Policy

April 21, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

However, often this is not enough. Actually, some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days).

In this article, we will see how to establish a policy for password expiration with MySQL 8.0 Password Expiration Policy.



TL;DR

MySQL provides password-expiration capability, which enables database administrators to require that users reset their password.


Establish a policy for password expiration with MySQL


The main goal of Password Expiration Policy is to require passwords to be changed periodically.
It can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

There are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password expiration policy.

Let’s take a closer look using MySQL 8.0.


PASSWORD EXPIRE

Force user to change its password at the first connection.

Create a user with a random password and mark that password expired :

We can see if the password is expired with mysql.user table :

Note that password_expired column is Y.

In clear, this new MySQL user will be able to connect to the server but he must reset its password before being able to executing statements

Column password_expired is now N.


PASSWORD EXPIRE INTERVAL n DAY

Force user to change its password every N days.

Create a user with password that will expire in 90 days :

We can see the password options in the mysql.user table :

Note that password_lifetime column is 90.

After 90 days any statement will generate error 1820 :

Password could be reset with ALTER USER command :


PASSWORD EXPIRE DEFAULT

This clause sets the account so that the global password expiration policy applies, as specified by the default_password_lifetime system variable.

The default default_password_lifetime value is 0, which disables automatic password expiration.
If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.

default_password_lifetime can be set in the MySQL configuration file but it can also be set and persisted at runtime using SET PERSIST :

The same behavior can be achieved using the configuration file (usually my.cnf or my.ini) :

but it will require a server restart.

To defer the global expiration policy for an account you should use Password Expire Default clause :

Lastly, to establish a global policy such that passwords never expire, set default_password_lifetime to 0 :


PASSWORD EXPIRE NEVER

It’s also possible to disable password expiration for an account.

Note that password_lifetime column is 0.

This expiration option overrides the global policy for all accounts named by the statement.



To Go Further

Reference Manual



MySQL Security Serie (1st edition)



Thanks for using MySQL!


Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.


Thanks for using MySQL!

3