Unable to start mysql service - PXC 8.0.27

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

Hi there,
Not able to start mysql service .

[test@clusterdb1 ~]$ sudo systemctl status   mysql@bootstrap.service
● mysql@bootstrap.service - Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap
   Loaded: loaded (/usr/lib/systemd/system/mysql@.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2022-05-11 16:27:46 IST; 1h 58min ago
  Process: 20950 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
  Process: 20947 ExecStart=/usr/sbin/mysqld $EXTRA_ARGS $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 20859 ExecStartPre=/bin/sh -c VAR=`bash /usr/bin/mysql-systemd galera-recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 20856 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 20815 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
 Main PID: 20947 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"
    Error: 2 (No such file or directory)

May 11 16:27:44 clusterdb1 systemd[1]: Starting Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap...
May 11 16:27:46 clusterdb1 systemd[1]: mysql@bootstrap.service: main process exited, code=exited, status=1/FAILURE
May 11 16:27:46 clusterdb1 mysql-systemd[20950]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
May 11 16:27:46 clusterdb1 mysql-systemd[20950]: WARNING: mysql may be already dead
May 11 16:27:46 clusterdb1 systemd[1]: Failed to start Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap.
May 11 16:27:46 clusterdb1 systemd[1]: Unit mysql@bootstrap.service entered failed state.
May 11 16:27:46 clusterdb1 systemd[1]: mysql@bootstrap.service failed.

Error log file

2022-05-11T16:27:46.786232+05:30 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-05-11T16:27:46.786246+05:30 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-05-11T16:27:46.786386+05:30 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue.
2022-05-11T16:27:46.786403+05:30 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_new failed
2022-05-11T16:27:46.786464+05:30 0 [Note] [MY-000000] [Galera] Loading provider /usr/lib64/galera4/libgalera_smm.so initial position: 00000000-0000-0000-0000-000000000000:-1
2022-05-11T16:27:46.786489+05:30 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library '/usr/lib64/galera4/libgalera_smm.so'
2022-05-11T16:27:46.788485+05:30 0 [Note] [MY-000000] [Galera] wsrep_load(): Galera 4.10(9728532) by Codership Oy <info@codership.com> (modified by Percona <https://percona.com/>) loaded successfully.
2022-05-11T16:27:46.788533+05:30 0 [Note] [MY-000000] [Galera] CRC-32C: using 64-bit x86 acceleration.
2022-05-11T16:27:46.789312+05:30 0 [ERROR] [MY-000000] [Galera] Bad value '/var/lib/mysql/SERVER-cert.pem' for SSL parameter 'socket.ssl_cert': 33558530: 'error:02001002:system library:fopen:No such file or directory': 22 (Invalid argument)
         at galerautils/src/gu_asio.cpp:ssl_prepare_context():444
2022-05-11T16:27:46.789384+05:30 0 [ERROR] [MY-000000] [Galera] Failed to create a new provider '/usr/lib64/galera4/libgalera_smm.so' with options 'socket.ssl_key=(null);socket.ssl_ca=/var/lib/mysql/ca.pem;socket.ssl_cert=/var/lib/mysql/SERVER-cert.pem': Failed to initialize wsrep provider
2022-05-11T16:27:46.789405+05:30 0 [ERROR] [MY-000000] [WSREP] Failed to load provider
2022-05-11T16:27:46.789436+05:30 0 [ERROR] [MY-010119] [Server] Aborting
2022-05-11T16:27:46.789657+05:30 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.27-18.1)  Percona XtraDB Cluster (GPL), Release rel18, Revision ac35177, WSREP version 26.4.3.
Log of wsrep recovery (--wsrep-recover):
 INFO: WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery_verbose.0fuJ3T' --pid-file='/var/lib/mysql/mysqlcluster-test-db1-recover.pid'
 INFO: WSREP: Recovered position b84627d5-d05e-11ec-9229-4b8fa8095ff9:3
2022-05-11T17:15:36.941870+05:30 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27-18.1) starting as process 21282
2022-05-11T17:15:36.944726+05:30 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-05-11T17:15:36.944740+05:30 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-05-11T17:15:36.944879+05:30 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue.
2022-05-11T17:15:36.944896+05:30 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_new failed
2022-05-11T17:15:36.944920+05:30 0 [Note] [MY-000000] [WSREP] New joining cluster node configured to use specified SSL artifacts
2022-05-11T17:15:36.944983+05:30 0 [Note] [MY-000000] [Galera] Loading provider /usr/lib64/galera4/libgalera_smm.so initial position: b84627d5-d05e-11ec-9229-4b8fa8095ff9:3
2022-05-11T17:15:36.945015+05:30 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library '/usr/lib64/galera4/libgalera_smm.so'
2022-05-11T17:15:36.947017+05:30 0 [Note] [MY-000000] [Galera] wsrep_load(): Galera 4.10(9728532) by Codership Oy <info@codership.com> (modified by Percona <https://percona.com/>) loaded successfully.
2022-05-11T17:15:36.947061+05:30 0 [Note] [MY-000000] [Galera] CRC-32C: using 64-bit x86 acceleration.
2022-05-11T17:15:36.947880+05:30 0 [ERROR] [MY-000000] [Galera] Bad value '/var/lib/mysql/SERVER-cert.pem' for SSL parameter 'socket.ssl_cert': 33558530: 'error:02001002:system library:fopen:No such file or directory': 22 (Invalid argument)
         at galerautils/src/gu_asio.cpp:ssl_prepare_context():444
2022-05-11T17:15:36.947952+05:30 0 [ERROR] [MY-000000] [Galera] Failed to create a new provider '/usr/lib64/galera4/libgalera_smm.so' with options 'socket.ssl_key=(null);socket.ssl_ca=/var/lib/mysql/ca.pem;socket.ssl_cert=/var/lib/mysql/SERVER-cert.pem': Failed to initialize wsrep provider
2022-05-11T17:15:36.947989+05:30 0 [ERROR] [MY-000000] [WSREP] Failed to load provider

2022-05-11T17:15:36.948020+05:30 0 [ERROR] [MY-010119] [Server] Aborting
2022-05-11T17:15:36.948272+05:30 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.27-18.1)  Percona XtraDB Cluster (GPL), Release rel18, Revision ac35177, WSREP version 26.4.3.
Log of wsrep recovery (--wsrep-recover):
 INFO: WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery_verbose.iGUWWA' --pid-file='/var/lib/mysql/mysqlcluster-test-db1-recover.pid'
 INFO: WSREP: Recovered position b84627d5-d05e-11ec-9229-4b8fa8095ff9:3
2022-05-11T17:16:06.003461+05:30 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27-18.1) starting as process 21473
2022-05-11T17:16:06.006499+05:30 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-05-11T17:16:06.006513+05:30 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-05-11T17:16:06.006651+05:30 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue.
2022-05-11T17:16:06.006668+05:30 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_new failed
2022-05-11T17:16:06.006683+05:30 0 [Note] [MY-000000] [WSREP] New joining cluster node configured to use specified SSL artifacts
2022-05-11T17:16:06.006733+05:30 0 [Note] [MY-000000] [Galera] Loading provider /usr/lib64/galera4/libgalera_smm.so initial position: b84627d5-d05e-11ec-9229-4b8fa8095ff9:3
2022-05-11T17:16:06.006757+05:30 0 [Note] [MY-000000] [Galera] wsrep_load(): loading provider library '/usr/lib64/galera4/libgalera_smm.so'
2022-05-11T17:16:06.008747+05:30 0 [Note] [MY-000000] [Galera] wsrep_load(): Galera 4.10(9728532) by Codership Oy <info@codership.com> (modified by Percona <https://percona.com/>) loaded successfully.
2022-05-11T17:16:06.008797+05:30 0 [Note] [MY-000000] [Galera] CRC-32C: using 64-bit x86 acceleration.
2022-05-11T17:16:06.009533+05:30 0 [ERROR] [MY-000000] [Galera] Bad value '/var/lib/mysql/SERVER-cert.pem' for SSL parameter 'socket.ssl_cert': 33558530: 'error:02001002:system library:fopen:No such file or directory': 22 (Invalid argument)
         at galerautils/src/gu_asio.cpp:ssl_prepare_context():444
2022-05-11T17:16:06.009603+05:30 0 [ERROR] [MY-000000] [Galera] Failed to create a new provider '/usr/lib64/galera4/libgalera_smm.so' with options 'socket.ssl_key=(null);socket.ssl_ca=/var/lib/mysql/ca.pem;socket.ssl_cert=/var/lib/mysql/SERVER-cert.pem': Failed to initialize wsrep provider
2022-05-11T17:16:06.009630+05:30 0 [ERROR] [MY-000000] [WSREP] Failed to load provider
2022-05-11T17:16:06.009664+05:30 0 [ERROR] [MY-010119] [Server] Aborting
2022-05-11T17:16:06.009918+05:30 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.27-18.1)  Percona XtraDB Cluster (GPL), Release rel18, Revision ac35177, WSREP version 26.4.3.
2

Your SSL configuration is invalid. Please look at your my.cnf and fix the SSL parameters. The error log is quite explicit in this.

3

Hi there,
Below is the config file of node1.
Please let me know if i miss anything in the config file.

use case : PXC - 3 node cluster


[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_timestamps = system

# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800
wsrep_provider=/usr/lib64/galera4/libgalera_smm.so

# IP address
wsrep_cluster_address=gcomm://192.168.2.1,192.168.2.2,192.168.2.3


binlog_format=row
wsrep_slave_threads=8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2

lower_case_table_names=1
# Node IP address
wsrep_node_address=192.168.2.2
wsrep_cluster_name=pxc-cluster-dev
wsrep_node_name=pxc1

pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
pxc-encrypt-cluster-traffic=ON



  ssl-ca=/etc/mysql/certs/ca.pem
  ssl-cert=/etc/mysql/certs/SERVER-cert.pem
  ssl-key=/etc/mysql/certs/SERVER-key.pem