I cannot join nodes to cluster after bootstrap

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I’ve been trying to set up a 3-node XtraDB Cluster. I followed these steps:

[url]https://pastebin.com/LHR57aYT[/url]

The problem is that the second node does not even attempt to join the cluster after I bootstrap the first node. The wsrep_cluster_size variable shows ‘1’ on the second node.
Running tcpdump for ports 3306 and 4567, on db01, reveals that no traffic reaches the first node.

Running nmap on db02 we get:


nmap -sV -p 3306,4567 db01 

Starting Nmap 6.47 ( [url]http://nmap.org[/url] ) at 2017-08-13 11:55 PDT
Nmap scan report for db01 (10.10.10.1)
Host is up (0.0010s latency).
rDNS record for 10.10.10.1: db01.example.com
PORT STATE SERVICE VERSION
3306/tcp open mysql MySQL (unauthorized)
4567/tcp open tram

I believe I followed the instruction by the book, except using /etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf instead on /etc/mysql/my.cnf for the config.

These are the logs on db02 when mysql is started:

[url]https://pastebin.com/rYAbwkkL[/url]

Any hints?

2

It looks like the second node is being bootstrapped also. Can you provide the config files for both nodes?

3

Yes here it is:

# XtraDB Cluster
wsrep_provider=/usr/lib/galera3/libgalera_smm.so

wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://10.10.10.1,10.10.10.2,10.10.10.3

wsrep_node_name=db02
wsrep_node_address=10.10.10.2

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:P4$$W0rd

pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

It’s from here:
[url]https://pastebin.com/LHR57aYT[/url]

4

Hello,

I am having the same issue as theodotos, was a solution ever found?

​​​​​​​Thanks

5
  1. Only one node needs to be bootstrapped that too only when a new cluster is being formed.
  2. Say you need 3 node cluster then you should bootstrap 1st node and then just start node-2 and 3 pointing to the node-1.
  3. For some reason node-1 needs to be restarted then this time don’t bootstrap node-1 else it will re-create a new cluster and existing nodes will not join this new cluster.
6

I have a machine which was bootstrapped once for a cluster which is no longer available. See systemctl status mysql@bootstrap.service below:

[root@RH7 ~]# 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; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Thu 2020-02-13 18:40:39 PKT; 1min 3s ago
Process: 2171 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
Process: 2141 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=2)
Process: 1147 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=1/FAILURE)
Process: 1146 ExecStart=/usr/bin/mysqld_safe --basedir=/usr ${EXTRA_ARGS} (code=exited, status=1/FAILURE)
Process: 968 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
Main PID: 1146 (code=exited, status=1/FAILURE)

Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain mysql-systemd[1147]: ERROR! mysqld_safe wit...
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain systemd[1]: mysql@bootstrap.service: contr...1
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain mysql-systemd[2141]: WARNING: mysql pid fil...
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain mysql-systemd[2141]: ERROR! mysql already dead
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain systemd[1]: mysql@bootstrap.service: contr...2
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain mysql-systemd[2171]: WARNING: mysql pid fil...
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain mysql-systemd[2171]: WARNING: mysql may be ...
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain systemd[1]: Failed to start Percona XtraDB....
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain systemd[1]: Unit mysql@bootstrap.service e....
Feb 13 18:40:39 RH7.4-Utimaco-3.252.localdomain systemd[1]: mysql@bootstrap.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

Now i want to add as a new node in an existing cluster of 2 nodes. After updating /etc/my.cnf file on all nodes N1,N2 and newly added node N3, bootstrapped N1, then attempted to start N2 and N3 using below command.

systemctl start mysql

N2 started successfully and working fine, however, N3 was giving below message:

Failed to start mysql.service: Unit not found.

Don’t know what am i missing at N3.