在MySQL8.0中，用户认证插件默认值为caching_sha2_password，可以实现SHA-256加密认证。
1
2
3
4
5
6
|
msandbox@(none) 15:39: show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
|
在创建用户时，也可以指定认证插件
1
|
CREATE USER 'sha2user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';
|
caching_sha2_password会将用户名和密码通过哈希进行缓存当用户访问时，会跟缓存条目进行匹配，匹配则认证成功，否则会对mysql.user进行校验，校验通过再进行缓存，提升认证效率。当删除用户、修改用户、修改认证方式都会清空认证缓存，flush privileges同样也会清空认证缓存。
caching_sha2_password只能通过加密的安全连接或RSA密钥进行密码交换的非加密连接进行访问，caching_sha2_password_auto_generate_rsa_keys参数默认会在数据库启动时生成对应的公钥和私钥。我们可以通过Caching_sha2_password_rsa_public_key查看公钥值
1
2
3
4
5
6
7
8
9
10
11
12
13
|
msandbox@(none) 15:28: show status like 'Caching_sha2_password_rsa_public_key';
+--------------------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------------------+-------------------------------------+
| Caching_sha2_password_rsa_public_key | -----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA0N+rEZ/4F4CIsVdi+kif
M7YZgpyNv/qyJJv/hWf2Cwc/edS0yrQNUlsQzb6QRqlwCRRehgRiqu5ZPHLuxs+J
osCHkPZZkIVJO8j1GfmP1Pk8um7WVeAY7JEqmW6lzdrN3Ar2G74n+0C7HvchRI2u
aH5dK1FNtauz6f7owRo5xI4DdWG0MWCRq+xWeqlzdVUk+qDWlIwud6qPxAQO6azU
L2lztHKjBuWjXEoFSvZCC19wMKeDRxrk/eLSWCzFmquJRXB8oFlRnxl0OEnnYze1
qxAdSirG0Z+eRNmpMdqhIsz0Qrekznl/xX1yOeBkJVFZCbpn7SaKxBE2gKWoHkem
ewIDAQAB
-----END PUBLIC KEY-----
|
对于命令行客户端可以与服务器进行基于RSA密钥对进行认证连接，例如：mysqldump，mysqlslow等。但默认情况下，MySQL服务端并不会将RSA公钥发送给客户端，因此需要指定下列选项之一来获取RSA公钥
- –get-server-public-key：通过请求访问获取RSA公钥
- –server-public-key-path：客户端从服务端拷贝RSA公钥，相对更安全一点
对于MySQL主从复制来说change master to命令需要通过MASTER_PUBLIC_KEY_PATH选项指定RSA公钥文件，或通过GET_MASTER_PUBLIC_KEY获取源端的公钥
对于MGR来说，节点相互通信认证需要开启参数group_replication_recovery_get_public_key，否则可能出现如下情况
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
root@(none) 01:38: select * from performance_schema.replication_connection_status\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
GROUP_NAME: e91c4508-d45a-11e9-911e-005056ab71f1
SOURCE_UUID: e91c4508-d45a-11e9-911e-005056ab71f1
THREAD_ID: NULL
SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_recovery
GROUP_NAME:
SOURCE_UUID:
THREAD_ID: NULL
SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000
RECEIVED_TRANSACTION_SET:
LAST_ERROR_NUMBER: 2061
LAST_ERROR_MESSAGE: error connecting to master 'repl@10.255.210.9:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
LAST_ERROR_TIMESTAMP: 2021-01-25 01:26:54.897330
LAST_QUEUED_TRANSACTION:
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000
|
Tips:Caching SHA-2 Pluggable Authentication