After making this changed in my.cnf it’s require to restart mysql to apply this changes, please let me know if restart of the mysql service was made?
Note that in MySQL 8 we have ALTER INSTANCE RELOAD TLS; which doesn’t require mysql restart. (ref)
For info, I usign these mysql version and wsrep version
Server version: 8.0.32-24.1 Percona XtraDB Cluster (GPL), Release rel24, Revision 793b5d9, WSREP version 26.1.4.3
Can you check your error log. They should tell you something about .key files that you’re assigning. I believe you need to use pem files instead of key / crt.
In the log I have only the problem concerning SSL Handshake. I am wondering why.
2023-05-29T01:48:09.800046Z 0 [Warning] [MY-000000] [Galera] Handshake failed: version too low
2023-05-29T01:48:10.236444Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:50:33.471324Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:52:51.183247Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:52:52.518185Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:52:52.669347Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:52:52.757302Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:52:56.146399Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:53:01.177309Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:53:06.206217Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:53:11.210617Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:53:16.139371Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:54:26.114475Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
2023-05-29T01:54:30.402854Z 0 [Warning] [MY-000000] [Galera] Handshake failed: unexpected message
2023-05-29T01:54:30.533716Z 0 [Warning] [MY-000000] [Galera] Handshake failed: unexpected message
2023-05-29T01:57:44.665170Z 0 [Warning] [MY-000000] [Galera] Handshake failed: peer did not return a certificate
I have renamed the file in .pem and reload the ssl config any luck still have the same old file.
+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------[65/1819]
| wsrep_local_state_uuid | 26f228c6-71ac-11ed-a8bc-7e43376c0c39 |
| wsrep_protocol_version | 10 |
| wsrep_last_applied | 135235 |
| wsrep_last_committed | 135235 |
| wsrep_monitor_status (L/A/C) | [ (10, 10), (135235, 135235), (135235, 135235) ] |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 10 |
| wsrep_received_bytes | 1034 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.1 |
| wsrep_local_cached_downto | 121830 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_active | false |
| wsrep_flow_control_requested | false |
| wsrep_flow_control_interval | [ 173, 173 ] |
| wsrep_flow_control_interval_low | 173
| wsrep_flow_control_interval_high | 173 |
| wsrep_flow_control_status | OFF |
| wsrep_cert_deps_distance | 0 |
| wsrep_apply_oooe | 0 |
| wsrep_apply_oool | 0 |
| wsrep_apply_window | 0 |
| wsrep_apply_waits | 0 |
| wsrep_commit_oooe | 0 |
| wsrep_commit_oool | 0 |
| wsrep_commit_window | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_cert_bucket_count | 1 |
| wsrep_gcache_pool_size | 30681928
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses |IPNODE1:3306,IPNODE2:3306,IPNODE3:3306 |
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | f1905002-fd63-11ed-af8d-4acb7bb754e3 |
| wsrep_gmcast_segment | 0 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 26f228c6-71ac-11ed-a8bc-7e43376c0c39 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 2
| wsrep_provider_capabilities | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> (modified by Percona <https://percona.com/>) |
| wsrep_provider_version | 4.14(779b689) |
| wsrep_ready | ON |
| wsrep_thread_count | 9 |
+----------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
79 rows in set (0.00 sec)
Here is my cnf
```shell
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=/var/lib/mysql/percona-pcx1-bin
log-bin-index=/var/lib/mysql/percona-pcx1-bin.index
# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
######## wsrep ###############
# Path to Galera library
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://IP NODE1,IP NODE2,IP NODE3
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# Slave thread to use
wsrep_slave_threads=8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
wsrep_node_address=IP NODE 1
# Cluster name
wsrep_cluster_name=cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=NODE NAME
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#[mysqld]
wsrep_provider_options = "socket.ssl=yes;socket.ssl_key=/etc/mysql/ssl/serverkey.pem;socket.ssl_cert=/etc/mysql/ssl/server-crt.pem;socket.ssl_ca=/etc/mysql/ssl/ca.pem"
[sst]
encrypt=4
ssl-key=/etc/mysql/ssl/serverkey.pem
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-crt.pem
the SSL file are in this directory
ll /etc/mysql/ssl/ -trh
-rw-r--r-- 1 mysql mysql 1.3K May 29 13:03 ca.pem
-rw------- 1 mysql mysql 1.8K May 29 13:03 ca-key.pem
-rw-r--r-- 1 mysql mysql 993 May 29 13:03 server-csr.pem
-rw-r--r-- 1 mysql mysql 1.2K May 29 13:04 server-crt.pem
-rw------- 1 mysql mysql 1.7K May 29 13:05 serverkey.pem
So you have PXC 8.0.32-24 and a working cluster. And you already have SSL encryption but want to replace the key files.
You should
generate ssl
have same certs on all nodes
restart mysql / reload ssl
update sst user to have REQUIRE SSL (mostly this is also already done)
I am not sure what you meant by “renamed the file in .pem”! I hope you mean using the “.pem” file instead of “.crt”/“.key”
What do you mean by “still have the same old file”? What are you expecting?
Also I see you say the “cluster is OK” and cluster node appears in “sync” state, though can you include the output of following:
show global variables like 'wsrep_provider_options'\G
Here are few links (which you might already have) for the task:
Hello,
I am not sure what you meant by “renamed the file in .pem” → mean using the .pem instead of .crt
All cert file are on the 3 nodes.
I have this on my cnf file
I make it work by naming the certs and key file like this:
-rw-r--r-- 1 mysql mysql 1196 May 30 11:40 server-cert.pem
-rw------- 1 mysql mysql 1679 May 30 11:40 server-key.pem
-rw-r--r-- 1 mysql mysql 1318 May 30 11:40 ca.pem
I don’t know why, it only accept these naming.
but thank you all for you help.
Do you guys know how to test SSL handshake within the cluster ?
2023-05-30T10:11:59.963705Z 0 [ERROR] [MY-000000] [Galera] Failed to create a new provider '/usr/lib64/galera4/libgalera_smm.so' with options 'socket.ssl_key=/etc/mysql/ssl/serverkey.pem;socket.ssl_cert=/etc/mysql/ssl/server-crt.pem;socket.ssl_ca=/etc/mysql/ssl/ca.pem;socket.ssl_key=(null);socket.ssl_ca=(null);socket.ssl_cert=(null)': Failed to initialize wsrep provider
this is OK: socket.ssl_key=/etc/mysql/ssl/serverkey.pem;socket.ssl_cert=/etc/mysql/ssl/server-crt.pem;socket.ssl_ca=/etc/mysql/ssl/ca.pem;
but why do I see this? socket.ssl_key=(null);socket.ssl_ca=(null);socket.ssl_cert=(null)'
I make it work by naming the certs and key file like this:
-rw-r--r-- 1 mysql mysql 1196 May 30 11:40 server-cert.pem
-rw------- 1 mysql mysql 1679 May 30 11:40 server-key.pem
-rw-r--r-- 1 mysql mysql 1318 May 30 11:40 ca.pem
It seems like, it only accept the name like this server-cert.pem and server-key.pem as the original cert created when mysql is initialize.