Not able to join the node to the cluster

AneeshBabu · February 23, 2021, 7:39pm

Hi there
when starting the node 3 i.e adding to cluster ,facing the below error.
let me know how to proceed.
Note : node 1,2 are online.

root@node3:/etc/mysql/mysql.conf.d# service mysql start
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.


root@node3:/etc/mysql/mysql.conf.d# systemctl status mysql.service
● mysql.service - Percona XtraDB Cluster
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Wed 2021-02-24 00:57:30 IST; 1min 33s ago
  Process: 12897 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
  Process: 12889 ExecStart=/usr/sbin/mysqld $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 12840 ExecStartPre=/bin/sh -c VAR=`bash /usr/bin/mysql-systemd galera-recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION
  Process: 12835 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 12804 ExecStartPre=/usr/bin/mysql-systemd check-grastate (code=exited, status=0/SUCCESS)
  Process: 12755 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
 Main PID: 12889 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"

Feb 24 00:56:57 node3 systemd[1]: Starting Percona XtraDB Cluster...
Feb 24 00:57:30 node3 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Feb 24 00:57:30 node3 mysql-systemd[12897]:  WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Feb 24 00:57:30 node3 mysql-systemd[12897]:  WARNING: mysql may be already dead
Feb 24 00:57:30 node3 systemd[1]: mysql.service: Failed with result 'exit-code'.
Feb 24 00:57:30 node3 systemd[1]: Failed to start Percona XtraDB Cluster.
lines 1-18/18 (END)
[6]+  Stopped                 systemctl status mysql.service

matthewb · February 23, 2021, 8:05pm

Hi @AneeshBabu , you need to provide us with your my.cnf config and the contents of the actual error log, usually in /var/log/mysqld.log

AneeshBabu · February 23, 2021, 8:22pm

Hi @matthewb

Here is the config file and error.log file attached .node3-error.log (27.1 KB)

[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
binlog_expire_logs_seconds      =       604800

wsrep_provider                  =       /usr/lib/galera4/libgalera_smm.so
binlog_format                   =       ROW
wsrep_slave_threads             =       8
wsrep_log_conflicts
innodb_autoinc_lock_mode        =       2

log_timestamps                  =       SYSTEM

# Node IP address
wsrep_node_address              =       192.168.0.3

wsrep_cluster_address=gcomm://192.168.0.3,192.168.0.2,192.168.0.1

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

AneeshBabu · February 24, 2021, 6:10am

Hi there,
Can anyone look into this issue…?

matthewb · February 24, 2021, 3:13pm

Hello @AneeshBabu,
Looking at your log file, I see this:

2021-02-24T00:47:23.688930+05:30 0 [ERROR] [MY-000000] [Galera] handshake with remote endpoint ssl://192.168.0.2:4567 failed: asio.ssl:336130315: ‘wrong version number’ ( 336130315: ‘error:1408F10B:SSL routines:ssl3_get_record:wrong version number’)
This error is often caused by SSL issues. For more information, please see:
https://per.co.na/pxc/encrypt_cluster_traffic

You have an ssl issue. Starting in PXC8, SSL is enabled by default. You need to copy the SSL certficates from whichever node was your bootstrap node to this node so that it can join the cluster.

AneeshBabu · February 24, 2021, 3:28pm

Hi @matthewb
currently iam using percona xtradb cluster 8.0.20-11.2 version.
Please let me know where is this SSL certificates located.
found 2 directories and a file at /etc/ssl

1.certs/
2.private/
3.openssl.cnf

Wayne_Leutwyler · February 24, 2021, 11:31pm

@AneeshBabu

Can you verify that "pxc-encrypt-cluster-traffic = on " on all 3 of your nodes?

AneeshBabu · February 25, 2021, 6:47am

@Wayne_Leutwyler
when pxc-encrypt-cluster-traffic = on ,mysql is not starting (for 3 nodes).
So,all the nodes are on OFF mode.

Wayne_Leutwyler · February 25, 2021, 12:40pm

@AneeshBabu, ok another couple questions.

can you post the my.cnf for one of the nodes working.
in your data directory on the node your bootstrapped do you see:

ca-key.pem
ca.pem
client-cert.pem
client-key.pem
private_key.pem
public_key.pem
server-cert.pem
server-key.pem

AneeshBabu · February 25, 2021, 2:14pm

Hi @Wayne_Leutwyler

2.
Yes i see the following files in my data directory

ca-key.pem
ca.pem
client-cert.pem
client-key.pem
private_key.pem
public_key.pem
server-cert.pem
server-key.pem

here is my .cnf file
1.
[client]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
server-id=3
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.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/lib/galera4/libgalera_smm.so
# IP address
wsrep_cluster_address=gcomm://192.168.0.1,192.168.0.2,192.168.0.3
binlog_format=ROW
wsrep_slave_threads=8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2


# Node IP address
wsrep_node_address=192.168.0.3
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc3
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
pxc-encrypt-cluster-traffic=OFF

Wayne_Leutwyler · February 25, 2021, 3:40pm

I am going to assume that node3 is still not working, and your still getting SSL errors on Node3.

If that is so, take the SSL Cert files *.pem from Node1 and put on Node3. Make sure to make a copy of the *.pem files.

Once you have copied the SSL files from Node1 to Node3 try to start node3. Please report back any issues. Include node3 log file.

matthewb · February 25, 2021, 4:39pm

@AneeshBabu, please read the documentation as explicitly stated in the error log, which you provided. https://per.co.na/pxc/encrypt_cluster_traffic
It contains everything you need to know regarding PXC and SSL certificates.

AneeshBabu · February 25, 2021, 7:37pm

Hi @Wayne_Leutwyler
Copied all the cert files under directory /etc/mysql/certs
i had shutdown all the nodes ,when bootstrapping the node1 facing the below error.
Attaching the .cnf file and error.log files
node1-configfile.txt (886 Bytes)
node1-error.log (18.5 KB)

root@node1:~# systemctl start mysql@bootstrap.service
Job for mysql@bootstrap.service failed because the control process exited with error code.
See "systemctl status mysql@bootstrap.service" and "journalctl -xe" for details.

root@node1:~# systemctl status mysql@bootstrap.service
● mysql@bootstrap.service - Percona XtraDB Cluster with config /etc/default/mysql.bootstrap
   Loaded: loaded (/lib/systemd/system/mysql@.service; disabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Fri 2021-02-26 00:51:03 IST; 6s ago
  Process: 21760 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
  Process: 21758 ExecStart=/usr/sbin/mysqld $EXTRA_ARGS $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 21710 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,
  Process: 21708 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 21671 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
 Main PID: 21758 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"

Feb 26 00:51:03 node1 systemd[1]: Starting Percona XtraDB Cluster with config /etc/default/mysql.bootstrap...
Feb 26 00:51:03 node1 systemd[1]: mysql@bootstrap.service: Main process exited, code=exited, status=1/FAILURE
Feb 26 00:51:03 node1 mysql-systemd[21760]:  WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Feb 26 00:51:03 node1 mysql-systemd[21760]:  WARNING: mysql may be already dead
Feb 26 00:51:03 node1 systemd[1]: mysql@bootstrap.service: Failed with result 'exit-code'.
Feb 26 00:51:03 node1 systemd[1]: Failed to start Percona XtraDB Cluster with config /etc/default/mysql.bootstrap.
lines 1-17/17 (END)
[11]+  Stopped                 systemctl status mysql@bootstrap.service

Wayne_Leutwyler · February 25, 2021, 8:13pm

It looks like Node1 did not start in bootstrap. At this point I would recommend the follow course of action.

It looks like SSL might be your issue. Try removing SSL from the picture. Do you need SSL?

In the my.cnf under the [mysqld] on all servers add:
ssl = 1
Change this to off:
pxc-encrypt-cluster-traffic = off

while you are testing you can comment out these lines on all nodes:

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

Bootstrap Node1. Verify it comes up ok then move on to the next. Also please review all the links below. The can assist you a great deal.

Read and follow the information on this link : https://www.percona.com/doc/percona-xtradb-cluster/LATEST/security/encrypt-traffic.html

Please take a look at this link to see and install guide.

AneeshBabu · February 25, 2021, 9:21pm

Hi @Wayne_Leutwyler
It’s working with the variable - pxc-encrypt-cluster-traffic = ON and ssl also .
Data is in sync at all the 3 nodes.
Thank you

Wayne_Leutwyler · February 25, 2021, 10:56pm

You are very welcome!