Created a new node and attempted to add it to the cluster, replacing an existing node. Do I need to bootstrap the main node again? I shut down all nodes and updated their my.cnf to reflect new node IP. Then attempted a restart.
Hello @Mikem,
Any time you completely stop the cluster (all nodes) you must always bootstrap the first node. This informs that node that it should not look to join an existing cluster.
Thanks Matthew, kind if what I figured, So to add a new node, do I need to update the wsrep_cluster_address in my.cnf on all running nodes including the new one and simply start the new node?
Technically, no. wsrep_cluster_address is not the definition of your cluster. The joining node only needs to know about 1 other node. As part of the communications protocol, nodes will share IPs of their connected nodes with all other connected nodes. Best practice says to add all nodes to each wsrep_cluster_address but it is not a requirement.
@matthewb I did everything as documented . But still, my second node is not connected. I am using a vagrant ubuntu machine.
I tried thousand times.
Do I need to set any SSL ?
How to debug these things?
For node 1
# 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
wsrep_provider_options=”socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem”
# 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.56.4,192.168.56.5
# 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.56.4
# Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-1
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem
And for node 2
# 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
wsrep_provider_options=”socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem”
# 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.56.4,192.168.56.5
# 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.56.5
# Cluster name
wsrep_cluster_name=pxc-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-2
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem
error
2022-11-19T13:44:00.123259Z 0 [ERROR] [MY-000000] [Galera] failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out)
at gcomm/src/pc.cpp:connect():161
2022-11-19T13:44:00.123300Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_core.cpp:gcs_core_open():219: Failed to open backend connection: -110 (Connection timed out)
2022-11-19T13:44:01.124810Z 0 [Note] [MY-000000] [Galera] gcomm: terminating thread
2022-11-19T13:44:01.125007Z 0 [Note] [MY-000000] [Galera] gcomm: joining thread
2022-11-19T13:44:01.125522Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs.cpp:gcs_open():1811: Failed to open channel 'pxc-cluster' at 'gcomm://192.168.56.4,192.168.56.5': -110 (Connection timed out)
2022-11-19T13:44:01.125656Z 0 [ERROR] [MY-000000] [Galera] gcs connect failed: Connection timed out
2022-11-19T13:44:01.125766Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://192.168.56.4,192.168.56.5) failed to establish connection with cluster (reason: 7)
2022-11-19T13:44:01.125972Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-11-19T13:44:01.126942Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-21.1) Percona XtraDB Cluster (GPL), Release rel21, Revision 250bc93, WSREP version 26.4.3.