Configuring MySQL enterprise databases with caching_sha2_password authentication

The default authentication plugin for MySQL is mysql_native_password. To make MySQL secure, you can use caching_sha2_password authentication plugin. The plugin requires Transport Layer Security (TLS) to be used for all MySQL connections. It also requires SSL to be enabled for MySQL.

About this task

Before you begin, you must generate SSL certificates like private keys, public keys, and CA certificates in the data directory. The required certificates include server certificates, client certificates, and CA certificates.

Note: The CA certificate can be your own or a third party certificate.

To enable SSL for MySQL on Linux:

Procedure

Install MySQL 8.x on a Linux system by using the following commands:

login as su mysql
run command /usr/sbin/mysqld --initialise
exit
cd /var/lib/mysql
/etc/pki/tls/ldapserver.key -out /etc/pki/tlsldapserver.crt
dn: cn=config
changetype: modify
add: olcTLSCACertificateFile
olcTLSCACertificateFile: /etc/pki/tls/ldapserver.crt
add: olcTLSCACertificateKeyFile
olcTLSCACertificateKeyFile: /etc/pki/tls/ldapserver.key
add: olcTLSCertificateFile
olcTLSCertificateFile: /etc/pki/tls/ldapserver.crt

In /etc/my.cnf enter the below values
[mysqld]
bind-address=0.0.0.0
port=3306
user=root
datadir=/var/lib/mysql
server_id=1
socket=/var/lib/mysql/mysql.sock

Rename the org SSL certificates that were generated from the database level during the MySQL database installation.

[user@sl mysql]$ sudo mv server-key.pem server-key.pem_org
[user@sl mysql]$ sudo mv server-cert.pem server-cert.pem_org
[user@sl mysql]$ sudo mv client-key.pem client-key.pem_org
[user@sl mysql]$ sudo mv public_key.pem public_key.pem_org
[user@sl mysql]$ sudo mv private_key.pem private_key.pem_org
[user@sl mysql]$ sudo mv ca.pem ca.pem_org

Generate the CA certificate

[root@sl mysql]# sudo openssl genrsa 2048 > ca-key.pem

Verify CA certificate is generated.

[root@sl mysql]# ls -lrt
-rw-------. 1 mysql mysql 1679 Nov 18 08:27 ca-key.pem

Save the ca-key.pem file in the ca.pem folder:

[root@sl mysql]# sudo openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

Enter the information to be incorporated into your certificate request. This entry is known as a Distinguished Name (DN). You can leave some fields blank, and some fields have a default values. Enter a period '(.)' to leave the field blank.
```
Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:CA
Locality Name (eg, city) [Default City]:LA
Organization Name (eg, company) [Default Company Ltd]:A
Organizational Unit Name (eg, section) []:SAFELINX
Common Name (eg, your name or your server's hostname) []:MyCA
Email Address []:
[root@sl mysql]#
```

CA certificates are generated.

[root@sl mysql]# ls -lrt
-rw-------. 1 mysql mysql     1679 Nov 18 08:27  ca-key.pem
-rw-r--r--. 1 root  root      1294 Nov 18 08:35  ca.pem

Generate the following Server certificates: server-key.pem and server-req.pem

[root@sl mysql]# sudo openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

To write a new private key for the server-key.pem certificate, enter the information to be incorporated into your certificate request. This entry is DN. You can leave some fields blank, and some fields have a default values. Enter a period '(.)' to leave the field blank.

Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:CA
Locality Name (eg, city) [Default City]:LA
Organization Name (eg, company) [Default Company Ltd]:A
Organizational Unit Name (eg, section) []:SAFELINX
Common Name (eg, your name or your server's hostname) []:MyCA
Email Address []:
Enter the following extra attributes for certificate request
A challenge password []:root123
An optional company name []:
[root@sl mysql]#

This command generates two files for the CA and server each:

[root@sl mysql]# ls -lrt
-rw-------. 1 mysql mysql     1679 Nov 18 08:27  ca-key.pem
-rw-r--r--. 1 root  root      1294 Nov 18 08:35  ca.pem
-rw-------. 1 root  root      1704 Nov 18 08:41  server-key.pem
-rw-r--r--. 1 root  root      1021 Nov 18 08:44  server-req.pem

Remove the challenge password which isroot123 by running the following command.
```
[root@sl mysql]# sudo openssl rsa -in server-key.pem -out server-key.pem
```
This command reduces the file size as shown:
```
[root@sl mysql]# ls -lrt
-rw-r--r--. 1 root  root      1021 Nov 18 08:44  server-req.pem
-rw-------. 1 root  root      1679 Nov 18 08:49  server-key.pem
```

Verify the signature of all certificates that the following command generates:

[root@sl mysql]# sudo openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=C = US, ST = CA, L = LA, O = A, OU = SAFELINX, CN = DB_Server
Getting CA Private Key
[root@sl mysql]#

If the signature verifies as OK, it means CA certificate is validated with server certificate (server-cert.pem)

[root@sl mysql]# ls -lrt
-rw-------. 1 mysql mysql     1679 Nov 18 08:27  ca-key.pem
-rw-r--r--. 1 root  root      1294 Nov 18 08:35  ca.pem
-rw-r--r--. 1 root  root      1021 Nov 18 08:44  server-req.pem
-rw-------. 1 root  root      1679 Nov 18 08:49  server-key.pem
-rw-r--r--. 1 root  root      1155 Nov 18 08:56  server-cert.pem (newly generated)
[root@sl mysql]#

Generate Client certificate by running the following command:

[root@sl mysql]#
[root@sl mysql]# sudo openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

To write a new private key for the client-key.pem certificate, enter the information to be incorporated into your certificate request. This entry is DN. You can leave some fields blank, and some fields have a default values. Enter a period '(.)' to leave the field blank.

Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:CA
Locality Name (eg, city) [Default City]:LA
Organization Name (eg, company) [Default Company Ltd]:A
Organizational Unit Name (eg, section) []:SAFELINX
Common Name (eg, your name or your server's hostname) []:MyClient (unique name for all the clients can be used) 
Email Address []:

Enter the following extra attributes for certificate request
A challenge password []:root123
An optional company name []:
[root@sl mysql]#

Client certificates are generated.

[root@sl mysql]# ls -lrt
-rw-------. 1 root  root      1704 Nov 18 09:05  client-key.pem
-rw-r--r--. 1 root  root      1021 Nov 18 09:12  client-req.pem

Remove the challenge password set by using the following command.

[root@sl mysql]# sudo openssl rsa -in client-key.pem -out client-key.pem

This command reduces the file size:

[root@sl mysql]# ls -lrt
-rw-r--r--. 1 root  root      1021 Nov 18 09:12  client-req.pem  (file size reduced after removing password)
-rw-------. 1 root  root      1679 Nov 18 09:20  client-key.pem

Verify client certificates with the CA

[root@sl mysql]# sudo openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=C = US, ST = CA, L = LA, O = A, OU = SAFELINX, CN = MyClient
Getting CA Private Key
[root@sl mysql]#  Signature is Okay

[root@sl mysql]# ls -lrt
-rw-r--r--. 1 root  root      1021 Nov 18 08:44  server-req.pem
-rw-------. 1 root  root      1679 Nov 18 08:49  server-key.pem
-rw-r--r--. 1 root  root      1155 Nov 18 08:56  server-cert.pem
-rw-r--r--. 1 root  root      1021 Nov 18 09:12  client-req.pem
-rw-------. 1 root  root      1679 Nov 18 09:20  client-key.pem
-rw-r--r--. 1 root  root      1151 Nov 18 09:22  client-cert.pem(Client certificate is generated)

A total 8 certificates are set in the MySQL server (two CA, three server, and three client):

-rw-------. 1 mysql mysql     1679 Nov 18 08:27  ca-key.pem
-rw-r--r--. 1 root  root      1294 Nov 18 08:35  ca.pem
-rw-r--r--. 1 root  root      1021 Nov 18 08:44  server-req.pem
-rw-------. 1 root  root      1679 Nov 18 08:49  server-key.pem
-rw-r--r--. 1 root  root      1155 Nov 18 08:56  server-cert.pem
-rw-r--r--. 1 root  root      1021 Nov 18 09:12  client-req.pem
-rw-------. 1 root  root      1679 Nov 18 09:20  client-key.pem
-rw-r--r--. 1 root  root      1151 Nov 18 09:22  client-cert.pem
[root@sl mysql]#

All certificates point to data directory. Therefore store your SSL certificates in the /var/lib/mysql or data directory. To save the certificates to the /var/lib/mysqldirectory, add the following lines to the/etc/my.cnf file.

[mysqld]
bind-address=0.0.0.0
port=3306
user=root
datadir=/var/lib/mysql
server_id=1
socket=/var/lib/mysql/mysql.sock
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
default_authentication_plugin=caching_sha2_password

Restart the mysqlddatabase:

[root@sl mysql]# sudo systemctl restart mysqld

Connect to the database:

[root@sl mysql]#
[root@sl mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution

mysql> status
--------------
mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution)
Connection id:          8
Current database:
Current user:           root@sl
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.26 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 2 min 1 sec

mysql>
mysql> SHOW variables like 'ssl_%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| ssl_ca        | /var/lib/mysql/ca.pem         |
| ssl_capath    |                                     |
| ssl_cert      | /var/lib/mysql/server-cert.pem|
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_fips_mode | OFF                            |
| ssl_key       | /var/lib/mysql/server-key.pem  |
+---------------+--------------------------------+

mysql>
mysql> SHOW variables like '%CA%';
+-----------------------------------------------+-----------------------+
| Variable_name                                 | Value                 |
+-----------------------------------------------+-----------------------+
| admin_ssl_ca                                  |                       |
| admin_ssl_capath                              |                       |
| binlog_cache_size                             | 32768                 |
| binlog_stmt_cache_size                        | 32768                 |
| caching_sha2_password_auto_generate_rsa_keys  | ON                    |
| caching_sha2_password_digest_rounds           | 5000                  |
| caching_sha2_password_private_key_path        | private_key.pem       |
| caching_sha2_password_public_key_path         | public_key.pem        |
| default_authentication_plugin                 | caching_sha2_password |
| group_concat_max_len                          | 1024                  |
| group_replication_consistency                 | EVENTUAL              |
| have_query_cache                              | NO                    |
| host_cache_size                               | 279                   |
| init_replica                                  |                       |
| innodb_dedicated_server                       | OFF                   |
| innodb_disable_sort_file_cache                | OFF                   |
| innodb_ft_cache_size                          | 8000000               |
| innodb_ft_result_cache_limit                  | 2000000000            |
| innodb_ft_total_cache_size                    | 640000000             |
| innodb_io_capacity                            | 200                   |
| innodb_io_capacity_max                        | 2000                  |
| innodb_lru_scan_depth                         | 1024                  |
| innodb_purge_rseg_truncate_frequency          | 128                   |
| innodb_replication_delay                      | 0                     |
| innodb_stats_auto_recalc                      | ON                    |
| innodb_undo_log_truncate                      | ON                    |
| key_cache_age_threshold                       | 300                   |
| key_cache_block_size                          | 1024                  |
| key_cache_division_limit                      | 100                   |
| local_infile                                  | OFF                   |
| log_replica_updates                           | ON                    |
| log_slow_replica_statements                   | OFF                   |
| lower_case_file_system                        | OFF                   |
| lower_case_table_names                        | 0                     |
| max_binlog_cache_size                         | 18446744073709547520  |
| max_binlog_stmt_cache_size                    | 18446744073709547520  |
| mysqlx_ssl_ca                                 |                       |
| mysqlx_ssl_capath                             |                       |
| pseudo_replica_mode                           | OFF                   |
| replica_allow_batching                        | OFF                   |
| replica_checkpoint_group                      | 512                   |
| replica_checkpoint_period                     | 300                   |
| replica_compressed_protocol                   | OFF                   |
| replica_exec_mode                             | STRICT                |
| replica_load_tmpdir                           | /var/tmp              |
| replica_max_allowed_packet                    | 1073741824            |
| replica_net_timeout                           | 60                    |
| replica_parallel_type                         | DATABASE              |
| replica_parallel_workers                      | 0                     |
| replica_pending_jobs_size_max                 | 134217728             |
| replica_preserve_commit_order                 | OFF                   |
| replica_skip_errors                           | OFF                   |
| replica_sql_verify_checksum                   | ON                    |
| replica_transaction_retries                   | 10                    |
| replica_type_conversions                      |                       |
| replication_optimize_for_static_plugin_config | OFF                   |
| replication_sender_observe_commit_only        | OFF                   |
| rpl_stop_replica_timeout                      | 31536000              |
| schema_definition_cache                       | 256                   |
| skip_replica_start                            | OFF                   |
| sql_replica_skip_counter                      | 0                     |
| ssl_ca                                        | /var/lib/mysql/ca.pem |
| ssl_capath                                    |                       |
| stored_program_cache                          | 256                   |
| stored_program_definition_cache               | 256                   |
| table_definition_cache                        | 2000                  |
| table_open_cache                              | 4000                  |
| table_open_cache_instances                    | 16                    |
| tablespace_definition_cache                   | 256                   |
| thread_cache_size                             | 9                     |
+-----------------------------------------------+-----------------------+

mysql>
mysql> SHOW GLOBAL VARIABLES LIKE 'tls_version';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| tls_version   | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+---------------+-------------------------------+
mysql> SHOW GLOBAL VARIABLES LIKE 'Ssl_version';
Empty set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'have_ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+

Connect to the MySQL server locally with all generated certificates. The output is as follows:

[root@sl user]# mysql --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem -u wgdb -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl connection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> status
--------------
mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution)
Connection id:          10
Current database:
Current user:           wgdb@sl
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384 (Cipher in Use hence the secure connection is verified)
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.26 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 2 min 47 sec

At the client side, create a directory to store all the client certificates:
```
[root@sl client_cert]# sudo mkdir /etc/client_cert
```
- Copy the client certificates from server to client system to the /etc/client_cert. This certificate location is required for connecting remotely to the SQL server.

Establish a remote connection to MySQL with SSL (secured connection)

[root@sl client_cert]# mysql --ssl-ca=/etc/client_cert/ca.pem --ssl-cert=/etc/client_cert/client-cert.pem --ssl-key=/etc/client_cert/client-key.pem -u wgdb -h 192.168.1.105 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.26 Source distribution

Edit the /etc/my.cnf file and add the following lines to the client section:

[client]
ssl-ca=/etc/client_cert/ca.pem
ssl-cert=/etc/client_cert/client-cert.pem
ssl-key=/etc/client_cert/client-key.pem

Establish a Safelinx connection to the remote MySQL server with SSL.

Install the safelinx server on the client computer.
Install the gateway manager (wgcfg)
Start the gateway manager

mysql> status
--------------
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)
Connection id:          11
Current database:
Current user:           wgdb@192.168.1.103
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.26 Source distribution
Protocol version:       10
Connection:             192.168.1.105 via TCP/IP (Note:     server is running at 192.168.1.105 client "wgdb" from wgdb@192.168.1.103
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 36 min 26 sec

For more information on how to connect to MySQL remote server, see Completing the initial MySQL database configuration on Linux

Because the preceeding authentication plugin requires TLS for all MySQL connections, you can switch to default mysql_native_password authentication plugin.

To configure the mysql_native_password plugin:

Edit the my.cnf MySQL config file, and update the following line under the [mysqld] entry:
```
default-authentication-plugin=mysql_native_password
```
Save the config file and restart the MySQL server.
If the MySQL user is already created with the caching_sha2_password plugin then enter the following line to alter the username and host values for mysql_native_password plugin.
```
ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
```