Not able to join the node to the cluster

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
1 Like

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

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
1 Like

Hi there,
Can anyone look into this issue…?

1 Like

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.

1 Like

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
1 Like

@AneeshBabu

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

1 Like

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

1 Like

@AneeshBabu, ok another couple questions.

  1. can you post the my.cnf for one of the nodes working.
  2. 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

1 Like

Hi @author_wayne

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
1 Like

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.

1 Like

@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.

1 Like

Hi @author_wayne
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
1 Like

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.

2 Likes

Hi @author_wayne
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 :smile:

1 Like

You are very welcome!

1 Like