Thank you for sharing the link to your sources for creating those certs. I used your readme to create the needed certs, copied them to the servers, fix ownership and permissions
root@node01:/var/lib/mysql# ls -lah /etc/ssl/galera/cluster_1/
total 40K
dr-x------ 2 mysql mysql 4,0K May 7 08:55 .
dr-x------ 3 mysql mysql 4,0K May 6 16:46 ..
-rw-r--r-- 1 mysql mysql 1,3K May 7 08:55 ca.pem
-r-------- 1 mysql mysql 1,9K May 5 13:34 galera_rep.crt
-r-------- 1 mysql mysql 3,2K May 5 13:34 galera_rep.key
-r-------- 1 mysql mysql 1,4K May 7 08:55 node01.cert.pem
-r-------- 1 mysql mysql 1,7K May 7 08:55 node01.key.pem
-r-------- 1 mysql mysql 1,1K May 7 08:55 node01.req.pem
I checked my my.cnf again and used your hint Percona XtraDB cluster not available after bootstrap the first node - #4 by matthewb and grouped the sst section to the end at the config file, and removed the doubled [mysqld] section.
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/run/mysqld/mysqld.sock
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid
user= mysql
# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
######## wsrep ###############
# Path to Galera library
wsrep_provider=/usr/lib/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://192.168.1.10,192.168.1.11,192.168.1.12
# 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=192.168.1.10
# Cluster name
wsrep_cluster_name=testcluster
# TLS/SSL Usage
wsrep_provider_options="gcache.size=512M;cert.log_conflicts=yes;socket.ssl=yes;socket.ssl_cert=/etc/ssl/galera/cluster_1/node01.cert.pem;socket.ssl_key=/etc/ssl/galera/cluster_1/node01.key.pem;socket.ssl_ca=/etc/ssl/galera/cluster_1/ca.pem"
pxc_encrypt_cluster_traffic=ON
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=node1
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
[sst]
encrypt=4
ssl-ca=/etc/ssl/galera/cluster_1/ca.pem
ssl-cert=/etc/ssl/galera/cluster_1/node01.cert.pem
ssl-key=/etc/ssl/galera/cluster_1/node01.key.pem
In my opinion, this looks ok. After bootstrapping, the donor and joiner node react unchanged.
The I tried to check the certificate and the available tls versions.
So I used this command, to interact with the donner node
openssl s_client -connect 192.168.1.10:4567 -tls1_2
Here I saw
0 s:CN = MySQL_Server_8.0.41-32_Auto_Generated_Server_Certificate
i:CN = MySQL_Server_8.0.41-32_Auto_Generated_CA_Certificate
While I am not sure, if on port 4567 the SST cert should be used, this first looked strange to me.
So I copied my self signed certs to /var/lib/mysql and symlinked them to mysql default certs.
After that, I bootstrapped the cluster again.
The errors on doner and joiner node are unchanged and the second node cant join. Using the same cert as on node01 or using a different cert, does not matter.
But the CN changed as expected.
depth=1 C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = MyCoolCA
verify return:1
depth=0 C = US, ST = Anywhere, L = MyCity, O = Percona, OU = TrainingDept, CN = node01
verify return:1
So I checked the bootstrapped node and here I saw that although wsrep_provider_options is set in the config file, this values are not loaded.
Donor node, bootstrapping state:
mysql> SHOW GLOBAL VARIABLES LIKE '%wsrep_provider_options%';
| Variable_name | Value
+-------------+-------------+
| wsrep_provider_options | allocator.disk_pages_encryption = no; allocator.encryption_cache_page_size = 32K; allocator.encryption_cache_size = 16777216; base_dir = /var/lib/mysql/; base_host = 178.63.126.95; base_port = 4567; cert.log_conflicts = yes; cert.optimistic_pa = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 1; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.encryption = no; gcache.encryption_cache_page_size = 32K; gcache.encryption_cache_size = 16777216; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 128M; gcache.recover = yes; gcache.size = 512M; gcomm.thread_prio = ; gcs.fc_auto_evict_threshold = 0.75; gcs.fc_auto_evict_window = 0; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.fc_single_primary = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = ssl://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 192.168.1.10; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.wait_restored_prim_timeout = PT0S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 11; socket.checksum = 2; socket.recv_buf_size = auto; socket.send_buf_size = auto; socket.ssl = YES; socket.ssl_ca = ca.pem; socket.ssl_cert = server-cert.pem; socket.ssl_cipher = ; socket.ssl_key = server-key.pem; socket.ssl_reload = 1; |
+-------------+-------------+
1 row in set (0,01 sec)
and
mysql> SHOW GLOBAL VARIABLES LIKE 'ssl%';
+---------------------------+-----------------+
| Variable_name | Value |
+---------------------------+-----------------+
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
| ssl_session_cache_mode | ON |
| ssl_session_cache_timeout | 300 |
+---------------------------+-----------------+
10 rows in set (0,00 sec)
Does the bootstrap process uses a different my.cnf? Or is my config faulty?