In the world of the Databases, one of the most important value that we are all trying to achieve is High Availability. Not to loose our valuable data or in case one server fails to always have another to step in and take control.
So, it is critical to have along with our primary database, other identical instances, the replicas.
On this blog post, we will see the most famous ways to set up our replication.
MySQL offers a variety of options to achieve replication set up. Here we are going to review how to create a replica based on GTID replication by using one of the following methods:
mysqldump
rsync
MySQL Enterprise Backup (MEB)
Clone plugin
MySQL Shell, using the utilities, is another method. Please see this article.
Before we initiate any process we need to make sure that on our my.cnf, primary & replica, have set the following parameters:
We will receive the corresponding gtid that is needed for setting up our replica. The backup.sql file should be moved to the replica, a simple scp should do.
Moving along to the replica, we perform the following command:
mysql -u root -p -e "source path/backup.sql"
After this import is completed, we simple need to execute the following:
CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
start slave;
If both the above commands are executed with no issue, perform a show slave status\G to verify that both Slave_IO_Running and Slave_SQL_Running are set to yes.
2nd Replication Method – rsync
When using the rsync option, the database on the replica must not be running. And on the primary db we will need to have a lock on the tables. As soon as the tables are locked we may proceed with rsyncing the data.
So:
FLUSH TABLES WITH READ LOCK;
rsync -axvz /datadir/* root@replica:/datadir
Once the copy is completed, we will need to remove the auto.cnf (as this will give the same UUID and we will not be able to set up our replica) and we will also remove mysql.sock.
The next steps will be to start our database and set it up as a replica:
systemctl start mysqld
mysql -uroot -p
CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
start slave;
And of course do not forget to reverse the lock command on the primary:
unlock tables;
3rd Replication Method – MEB
MEB, MySQL Enterprise Backup, is a commercially licensed backup utility for MySQL databases. One of the fastest and easiest way to set up your replication while primary db is up and running.
Please note that the db on the replica should not be running and the corresponding paths should be empty, as this may cause confusion. For example, imagine having forgotten a binlog file and binlog_index to trying to read from erroneous binlogs. This will lead to having the replication broken.
In one command we are sending our data to the replica as well.
Once, this is completed we go into the /tmp folder in the primary node and inside the meta folder, we take note of the command inside the backup_gtid_purged.sql file. This is needed for setting up our replica:
reset slave;
reset master;
set global.gtid_purged=@@ (this is the command inside the backup_gtid_purged.sql)
CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
start slave;
Our replica is up and running with no trouble.
4th Replication Method – clone plugin
Last but not least we have the clone method. Clone is initiated at 8.0.17 version and it is a really useful tool.
First thing we need to do is to install the clone plugin on both the primary and wannabe replica as well as the user with the proper grants:
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
CREATE USER clone_user@'%' IDENTIFIED BY 'xxxxxx';
GRANT BACKUP_ADMIN, CLONE_ADMIN ON *.* to clone_user@'%';
Verify that plugin is indeed active:
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'clone';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone | ACTIVE |
+-------------+---------------+
And we will also set up our donor on the replica:
SET GLOBAL clone_valid_donor_list = 'Primary_host_IP:port';
Now we need to log in to the replica with the clone_user we created on the above-mentioned steps and execute the following command:
CLONE INSTANCE FROM 'clone_user'@'Primary_host_IP':port IDENTIFIED BY 'xxxxxx';
Once this is completed, we log out from clone_user and log in with root and set up our replica:
CHANGE MASTER TO MASTER_HOST='primary_IP' ,MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
start slave;
Please note that the master_user that is being used to set up our replica in all the methods needs to have access on our replica and have the replication_slave privilege.
Conclusion
To sum up, MySQL offers a variety of ways to set up tour replication, what you will choose depends on your needs for speed, performance and down time.
MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.
Cloud backup is a strategy increasingly used in organizations. Send copies of your data to the cloud, can help you to prevent a devastating IT crisis and ensure business continuity.
Currently, MySQL Enterprise Backup supports the following types of cloud storage services:
Pre-authenticated requests provide a way to let users access a bucket or an object without having their own credentials, as long as the request creator has permissions to access those objects.
You can create, delete, or list pre-authenticated requests using the Console, using the CLI, or by using an SDK to access the API. Click here to see how to create a PAR.
If you use the console, you’ll have something like:
My Oracle Cloud Infrastructure Pre-Authenticated Request name is : MEB-par-bucket-20201203-1612
An URL is generated. It is very important to save it! We’ll use it with MySQL Enterprise Backup.
I can also see my list of PARs in the console:
Create a Cloud Backup on Oracle Cloud Infrastructure Object Storage
I’m using MySQL Enterprise Backup 8.0.22. The extra options to backup your data into OCI Object Storage are:
cloud-service : Cloud service for data backup or restoration. Currently, there are 3 types of cloud storage services supported by mysqlbackup, represented by the following values for the option:
OCI: Oracle Cloud Infrastructure Object Storage
openstack: OpenStack Swift or compatible object storage services
S3: Amazon Simple Storage Service (S3) or compatible storage service.
cloud-object : The storage object for the backup image. Note that names of objects within the same bucket have to be unique.
cloud-par-url : The Pre-Authenticated Request (PAR) URL for OCI Object Storage. For a backup to OCI Object Storage, it is the PAR URL for the storage bucket; for restore and other operations on an object stored on OCI, it is the PAR URL for the object.
Click here to find the complete list of cloud storage options (OCI, Amazon S3 & OpenStack Swift options).
$ mysqlbackup --defaults-file=/etc/mysql/my.cnf --backup-dir=/meb-tmp --with-timestamp --backup-image=- --cloud-service=OCI --cloud-par-url=https://objectstorage.us-ashburn-1.oraclecloud.com/p/JL7k0DnNE8DTV<...snip...>_bucket-20200908-1001/o/ --cloud-object=myBck_20201203-1600.mbi backup-to-image
MySQL Enterprise Backup Ver 8.0.22-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Starting with following command line ...
<... snip ...>
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 27229330944
End LSN : 27229334048
-------------------------------------------------------------
201203 16:51:48 MAIN INFO: Cloud operation completed.
mysqlbackup completed OK!
Don’t forget the value “–” (dash) for backup-image parameter:
--backup-image=-
The backup is now completed and stored on the Oracle Cloud Infrastructure object storage bucket.
Restore a Backup from Oracle Cloud Infrastructure
Again, a Pre-Authenticated Request (PAR) URL for OCI Object Storage will be used. This time we will use a PAR on an object with Read PAR privileges created before the restoration.
Thus in our context, to restore a single-file backup from an OCI Object Storage to a MySQL Server, we will use:
$ mysqlbackup --defaults-file=/etc/mysql/my.cnf --backup-image=- --with-timestamp --backup-dir=/meb-tmp --cloud-service=OCI --cloud-par-url=https://objectstorage.us-ashburn-1.oraclecloud.com/p/FvbPbq7oJAGP<...snip...>_bucket-20200908-1001/o/myBck_20201203-1600.mbi copy-back-and-apply-log
MySQL Enterprise Backup Ver 8.0.22-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.
<...snip...>
201208 12:11:27 MAIN INFO: Apply-log operation completed successfully.
201208 12:11:27 MAIN INFO: Full Backup has been restored successfully.
201208 12:11:27 MAIN INFO: Cloud operation completed.
mysqlbackup completed OK! with 4 warnings
The restore is now completed.
As a side note, if you want to see the 4 warnings, take a look at the MEB logfile, located in the –backup-dir, meb-tmp in this article:
$ grep WARNING /meb-tmp/2020-12-08_11-59-58/meta/MEB_2020-12-08.11-59-58.log
201208 11:59:58 MAIN WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration.
201208 11:59:58 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.
201208 11:59:58 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
201208 12:11:27 MAIN WARNING: External plugins list found in meta/backup_content.xml. Please ensure that all plugins are installed in restored server.
Nothing really serious in this context. If you need more information, please click here.
The rest of the story is classic, restart your MySQL instance and you good to go 🙂
One more thing to know and to keep in mind, is that a cloud backup always uses one write thread. In clear backup & restore duration could be much longer than for a local operation.
However, it is a good practice, when possible, to keep a local copy of the backup file.
It is usually easier and much faster to recover from a local location.
MySQL Enterprise Edition
MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.
It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.
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.
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:
mysql_node1:3306 ssl SQL>
CREATE USER 'repl'@'172.20.0.%' IDENTIFIED BY 'S3cr€tRepl' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.20.0.%';
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:
mysql_node1:3306 ssl SQL>
CREATE USER clone_user IDENTIFIED BY "S3cr€tClone";
GRANT BACKUP_ADMIN, EXECUTE ON *.* TO clone_user;
GRANT SELECT ON performance_schema.* TO clone_user;
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:
mysql_node4:3306 ssl SQL>
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
SET PERSIST clone_valid_donor_list = 'mysql_node1:3306,mysql_node2:3306,mysql_node3:3306';
CREATE USER clone_user IDENTIFIED BY "S3cr€tClone";
GRANT CLONE_ADMIN, EXECUTE ON *.* to clone_user;
GRANT SELECT ON performance_schema.* TO clone_user;
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:
mysql_node4:3306 ssl SQL>
CLONE INSTANCE FROM 'clone_user'@'mysql_node1':3306 IDENTIFIED BY 'S3cr€tClone';
If you want to monitor the cloning progress run the following query:
mysql_node4:3306 ssl SQL>
SELECT
STATE,
CAST(BEGIN_TIME AS DATETIME) AS "START TIME",
CASE WHEN END_TIME IS NULL THEN LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
ELSE
LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ') END AS DURATION
FROM performance_schema.clone_status;
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:
mysql_node4:3306 ssl SQL>
SELECT
STATE,
ERROR_NO,
BINLOG_FILE,
BINLOG_POSITION,
GTID_EXECUTED,
CAST(BEGIN_TIME AS DATETIME) as "START TIME",
CAST(END_TIME AS DATETIME) as "FINISH TIME",
sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))) AS DURATION
FROM performance_schema.clone_status \G
and:
mysql_node4:3306 ssl SQL>
SELECT
STAGE,
STATE,
CAST(BEGIN_TIME AS DATETIME) as "START TIME",
CAST(END_TIME AS DATETIME) as "FINISH TIME",
LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ') AS DURATION
FROM performance_schema.clone_progress;
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.
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)
FORCHANNEL : 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:
mysql_node4:3306 ssl SQL>
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='S3cr€tRepl', MASTER_HOST='mysql_node2', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_SSL=1, SOURCE_CONNECTION_AUTO_FAILOVER=1, MASTER_RETRY_COUNT=3, MASTER_CONNECT_RETRY=5 FOR CHANNEL 'autof';
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:
mysql_node4:3306 ssl SQL>
START REPLICA FOR CHANNEL "autof";
Status information of the replication can be seen with SHOW REPLICA:
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:
mysql_node4:3306 ssl SQL>
SELECT
CHANNEL_NAME,
SERVICE_STATE,
HOST,
CONNECTION_RETRY_INTERVAL,
CONNECTION_RETRY_COUNT
FROM replication_connection_configuration
INNER JOIN replication_applier_status
USING (CHANNEL_NAME)
WHERE CHANNEL_NAME = 'autof'\G
*************************** 1. row ***************************
CHANNEL_NAME: autof
SERVICE_STATE: ON
HOST: mysql_node2
CONNECTION_RETRY_INTERVAL: 5
CONNECTION_RETRY_COUNT: 3
Stop the replication:
mysql_node4:3306 ssl SQL>
STOP REPLICA;
SELECT
CHANNEL_NAME,
SERVICE_STATE,
HOST,
CONNECTION_RETRY_INTERVAL,
CONNECTION_RETRY_COUNT
FROM replication_connection_configuration
INNER JOIN replication_applier_status
USING (CHANNEL_NAME)
WHERE CHANNEL_NAME = 'autof'\G
*************************** 1. row ***************************
CHANNEL_NAME: autof
SERVICE_STATE: OFF
HOST: mysql_node2
CONNECTION_RETRY_INTERVAL: 5
CONNECTION_RETRY_COUNT: 3
After a while, start the replication:
mysql_node4:3306 ssl SQL>
START REPLICA;
SELECT
CHANNEL_NAME,
SERVICE_STATE,
HOST,
CONNECTION_RETRY_INTERVAL,
CONNECTION_RETRY_COUNT
FROM replication_connection_configuration
INNER JOIN replication_applier_status
USING (CHANNEL_NAME)
WHERE CHANNEL_NAME = 'autof'\G
*************************** 1. row ***************************
CHANNEL_NAME: autof
SERVICE_STATE: ON
HOST: mysql_node2
CONNECTION_RETRY_INTERVAL: 5
CONNECTION_RETRY_COUNT: 3
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:
mysql_node4:3306 ssl SQL>
SELECT
CHANNEL_NAME,
SERVICE_STATE,
HOST,
CONNECTION_RETRY_INTERVAL,
CONNECTION_RETRY_COUNT
FROM replication_connection_configuration
INNER JOIN replication_applier_status
USING (CHANNEL_NAME)
WHERE CHANNEL_NAME = 'autof'\G
*************************** 1. row ***************************
CHANNEL_NAME: autof
SERVICE_STATE: ON
HOST: mysql_node2
CONNECTION_RETRY_INTERVAL: 5
CONNECTION_RETRY_COUNT: 3
… Stop mysql_node2 for 10 seconds …
State after the start of the source mysql_node2:
mysql_node4:3306 ssl SQL>
SELECT
CHANNEL_NAME,
SERVICE_STATE,
HOST,
CONNECTION_RETRY_INTERVAL,
CONNECTION_RETRY_COUNT
FROM replication_connection_configuration
INNER JOIN replication_applier_status
USING (CHANNEL_NAME)
WHERE CHANNEL_NAME = 'autof'\G
*************************** 1. row ***************************
CHANNEL_NAME: autof
SERVICE_STATE: ON
HOST: mysql_node2
CONNECTION_RETRY_INTERVAL: 5
CONNECTION_RETRY_COUNT: 3
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:
mysql_node4:3306 ssl SQL>
SELECT
CHANNEL_NAME,
SERVICE_STATE,
HOST,
CONNECTION_RETRY_INTERVAL,
CONNECTION_RETRY_COUNT
FROM replication_connection_configuration
INNER JOIN replication_applier_status
USING (CHANNEL_NAME)
WHERE CHANNEL_NAME = 'autof'\G
*************************** 1. row ***************************
CHANNEL_NAME: autof
SERVICE_STATE: ON
HOST: mysql_node2
CONNECTION_RETRY_INTERVAL: 5
CONNECTION_RETRY_COUNT: 3
… Stop mysql_node2 for 20 seconds …
mysql_node4:3306 ssl SQL>
SELECT
CHANNEL_NAME,
SERVICE_STATE,
HOST,
CONNECTION_RETRY_INTERVAL,
CONNECTION_RETRY_COUNT
FROM replication_connection_configuration
INNER JOIN replication_applier_status
USING (CHANNEL_NAME)
WHERE CHANNEL_NAME = 'autof'\G
*************************** 1. row ***************************
CHANNEL_NAME: autof
SERVICE_STATE: ON
HOST: mysql_node3
CONNECTION_RETRY_INTERVAL: 5
CONNECTION_RETRY_COUNT: 3
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.
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.
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 :
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;
Wait for the password change to replicate throughout the system to all slave servers
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’
On each server that is not a replication slave, discard the secondary password e.g. ALTER USER ‘myApp’@’host’ DISCARD OLD PASSWORD;
Create a user account myApp@localhost with password pwd1 :
MySQL root SQL>
CREATE USER myApp@localhost IDENTIFIED BY 'pwd1';
Now we can connect with the name and the password :
$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER() |
+-----------------+
| myApp@localhost |
+-----------------+
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 :
MySQL root SQL>
ALTER USER myApp@localhost IDENTIFIED BY 'pwd2' RETAIN CURRENT PASSWORD;
We can use the user name and the new password (pwd2) to connect :
$ mysql -u myApp -ppwd2 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER() |
+-----------------+
| myApp@localhost |
+-----------------+
But the old password (pwd1) is still valid :
$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER() |
+-----------------+
| myApp@localhost |
+-----------------+
Now it is the time to discard the secondary password (pwd1) :
MySQL root SQL>
ALTER USER myApp@localhost DISCARD OLD PASSWORD;
$ mysql -u myApp -ppwd2 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER() |
+-----------------+
| myApp@localhost |
+-----------------+
$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'myApp'@'localhost' (using password: YES)
As you can see, only the new password (pwd2) is valid.
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.
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.
Create a user that would have his account locked for 1 day after 1 consecutive failed logins :
$ mysqlsh root@localhost:3306 --sql
...
MySQL localhost:3306 ssl SQL>
CREATE USER aUser@localhost IDENTIFIED BY 'pAssw0rD' FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 1;
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 :
MySQL localhost:3306 ssl SQL>
SELECT user, host, User_attributes FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
user: aUser
host: localhost
User_attributes: {"Password_locking": {"failed_login_attempts": 1, "password_lock_time_days": 1}}
If login failed a “FAILED_LOGIN_ATTEMPTS” number of time (1 time in this example), the account will be locked :
MySQL localhost:3306 ssl SQL>
\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': *
MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 1 consecutive failed logins.
Tracking and locking could also be set up after the user creation :
$ mysqlsh root@localhost:3306 --sql
MySQL localhost:3306 ssl SQL>
CREATE USER aUser2@localhost IDENTIFIED BY 'Dr0wssAp';
ALTER USER aUser2@localhost FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME UNBOUNDED;
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 :
MySQL localhost:3306 ssl SQL>
CREATE USER aLockedUser@localhost IDENTIFIED BY RANDOM PASSWORD ACCOUNT LOCK;
+-------------+-----------+----------------------+
| user | host | generated password |
+-------------+-----------+----------------------+
| aLockedUser | localhost | @.Yp{;ONp7-G62+EfON1 |
+-------------+-----------+----------------------+
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 :
MySQL localhost:3306 ssl SQL>
SELECT user, host, account_locked FROM mysql.user WHERE user = 'aLockedUser';
+-------------+-----------+----------------+
| user | host | account_locked |
+-------------+-----------+----------------+
| aLockedUser | localhost | Y |
+-------------+-----------+----------------+
Any connection to this account will raised error 3118 :
MySQL localhost:3306 ssl SQL>
\connect aLockedUser@localhost:3306
Creating a session to 'aLockedUser@localhost:3306'
Please provide the password for 'aLockedUser@localhost:3306': ********************
MySQL Error 3118 (HY000): Access denied for user 'aLockedUser'@'localhost'. Account is locked.
This account can be activate with something like :
MySQL localhost:3306 ssl SQL>
ALTER USER aLockedUser@localhost ACCOUNT UNLOCK FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME UNBOUNDED;
Again mysql.user table will give you some information :
MySQL localhost:3306 ssl SQL>
SELECT user, host, account_locked, User_attributes FROM mysql.user WHERE user = 'aLockedUser'\G
*************************** 1. row ***************************
user: aLockedUser
host: localhost
account_locked: N
User_attributes: {"Password_locking": {"failed_login_attempts": 5, "password_lock_time_days": -1}}
Account unlock
Account can be unlocked with an ALTER USER … ACCOUNT UNLOCK statement :
MySQL localhost:3306 ssl SQL>
\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306':
MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 2 consecutive failed logins.
ALTER USER aUser@localhost ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.0047 sec)
\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': ********
Closing old connection...
Your MySQL connection id is 63
...
SELECT USER();
+-----------------+
| USER() |
+-----------------+
| aUser@localhost |
+-----------------+
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.
ALTER USER aUser@localhost FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;
Obviously when the lock duration passes. In this case, failed-login counting resets at the time of the next login attempt.
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.
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.
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 :
MySQL SQL>
CREATE USER olivier@localhost IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT;
+---------+-----------+----------------------+
| user | host | generated password |
+---------+-----------+----------------------+
| olivier | localhost | S0RR73vpVqVPr35QdK&h |
+---------+-----------+----------------------+
We can see the policy is enable for this account with mysql.user table :
SELECT user, host, Password_require_current, password_last_changed FROM mysql.user WHERE user = 'olivier'\G
*************************** 1. row ***************************
user: olivier
host: localhost
Password_require_current: Y
password_last_changed: 2020-04-03 15:08:00
Note that Password_require_current column is Y.
We can test the policy. Connect to the new created account :
MySQL SQL>
\connect olivier@localhost
Creating a session to 'olivier@localhost'
Please provide the password for 'olivier@localhost': ********************
Then modify the password :
MySQL olivier SQL>
ALTER USER USER() IDENTIFIED BY 'NEW_P4s5word';
ERROR: 3892: Current password needs to be specified in the REPLACE clause in order to change it.
MySQL olivier SQL>
ALTER USER USER() IDENTIFIED BY 'NEW_P4s5word' REPLACE 'S0RR73vpVqVPr35QdK&h';
Query OK, 0 rows affected (0.0117 sec)
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 :
MySQL SQL>
ALTER USER olivier@localhost identified by 'sïxS*Zj#&{2Svf}G';
Query OK, 0 rows affected (0.0098 sec)
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 :
MySQL SQL>
CREATE USER ethan@localhost IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT OPTIONAL;
+-------+-----------+----------------------+
| user | host | generated password |
+-------+-----------+----------------------+
| ethan | localhost | B6>}Kgbw6;_>85e]U_A[ |
+-------+-----------+----------------------+
SELECT user, host, Password_require_current, password_last_changed FROM mysql.user WHERE user = 'ethan'\G
*************************** 1. row ***************************
user: ethan
host: localhost
Password_require_current: N
password_last_changed: 2020-04-03 15:51:53
Note that Password_require_current column is N.
We can test the policy. Connect to the new created account :
MySQL SQL>
\connect ethan@localhost
Creating a session to 'ethan@localhost'
Please provide the password for 'ethan@localhost': ********************
Then modify the password :
MySQL ethan SQL>
ALTER USER USER() IDENTIFIED BY 'NEW_P4s5word';
Query OK, 0 rows affected (0.0147 sec)
ALTER USER USER() IDENTIFIED BY 'An0th3r_Pa$$word' REPLACE 'NEW_P4s5word';
Query OK, 0 rows affected (0.0118 sec)
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.
MySQL SQL>
SHOW VARIABLES LIKE 'password_require_current';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_require_current | OFF |
+--------------------------+-------+
SET PERSIST password_require_current = ON;
SHOW VARIABLES LIKE 'password_require_current';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_require_current | ON |
+--------------------------+-------+
An alternative is to write it in the configuration file (usually my.cnf or my.ini) and restart the MySQL instance.
[mysqld]
password_require_current = ON
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 :
MySQL SQL>
SHOW VARIABLES LIKE 'password_require_current';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_require_current | ON |
+--------------------------+-------+
CREATE USER defaultpvrp@localhost IDENTIFIED BY 'p0#' PASSWORD REQUIRE CURRENT DEFAULT;
We can test the policy. Connect to the new created account :
MySQL SQL>
\connect defaultpvrp@localhost
Creating a session to 'defaultpvrp@localhost'
Please provide the password for 'defaultpvrp@localhost':
...
MySQL defaultpvrp SQL>
ALTER USER USER() IDENTIFIED BY 'nEw_P4s5word';
ERROR: 3892: Current password needs to be specified in the REPLACE clause in order to change it.
ALTER USER USER() IDENTIFIED BY 'nEw_P4s5word' REPLACE 'p0#';
Query OK, 0 rows affected (0.0082 sec)
Because the global policy enable the Password Verification-Required Policy, we must use the REPLACE clause.
