SSL question in Cluster configuration

[Mikem]

Configured a cluster but my second added node fails to start due to what appears to be an SSL issue.

According to the install guide.

“Set up the traffic encryption settings. Each node of the cluster must use the same SSL certificates.”

• Do I need to copy the server certs to each node? They appear to be different between them.

I assume I need to add this to the .cnf file?
[mysqld]
wsrep_provider_options=”socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.
˓→pem;socket.ssl_ca=ca.pem”

and this as well,?

[sst]
encrypt=4
ssl-key=server-key.pem
ssl-ca=ca.pem
ssl-cert=server-cert.pem

[matthewb]

Hello @Mikem,
Yes, you need to copy the SSL certificates created from your bootstrap node to the other nodes. Assuming this is PXC8, you do not need to add any SSL configuration parameters to my.cnf as MySQL will look for these automatically if installed in the $DATADIR. SSL is enabled by default in PXC8.

[Mikem]

That worked and node3 is in synch. No errors on node2 but it is not synching with changes in the Master(Node 1). Do I need to do another bootstrap on Node 1? No real errors in the logs for Node2.

[matthewb]

What do you mean “not syncing with changes in node1” (btw, PXC has no concept of ‘master’) Is node2 connected to the cluster?

Bootstrapping is a process which starts a brand new cluster. You do this only on the very first node you start and will never do this again unless you completely stop all nodes.

[Mikem]

I found the issue, I had somehow missed updating the my.cnf file after I got the correct path situated. It looks like all three nodes are synched now. Thanks for all your help.

[Mikem]

I think I lost my bootstrap node. Can I just reboot it? Where can I find root cause?

mysql> select count(*) from user;
ERROR 1047 (08S01): WSREP has not yet prepared node for application use

root@iaddb01:~# cat /var/lib/mysql/grastate.dat

GALERA saved state

version: 2.1
uuid: 00000000-0000-0000-0000-000000000000
seqno: -1
safe_to_bootstrap: 0

[matthewb]

If your other two nodes are online yes, systemctl stop mysql@bootstrap but make sure you restart it normally systemctl start mysql otherwise, if you bootstrap it, you will create a NEW, SEPARATE, CLUSTER, which you don’t want. You want this node to join the others in the already established cluster.

Find the cause? Look in mysql’s error log.

[Mikem]

I stopped bootstrap service and restarted. No luck. I cleared /var/lib/mysql , still the same. Seeing this in the log.

2021-06-18T15:09:23.732444Z 0 [Warning] [MY-000000] [Galera] 1.0 (iad_db03): State transfer to 0.0 (iad_db01) failed: -125 (Operation canceled)
2021-06-18T15:09:23.733362Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():1214: Will never receive state. Need to abort.
2021-06-18T15:09:23.733388Z 0 [Note] [MY-000000] [Galera] gcomm: terminating thread

iad_db01 is the failed node. iad_db03 is one of the other two nodes working ok. not sure what error-125 means.

I tried stopping and starting the other nodes as I found an error in the my.cnf but now nothing will start.

This is node 2:

2021-06-18T15:33:08.426070Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs.cpp:gcs_open():1758: Failed to open channel ‘iad-cluster’ at ‘gcomm://45.56.115.5,45.33.103.216,45.79.213.218’: -110 (Connection timed out)
2021-06-18T15:33:08.426100Z 0 [ERROR] [MY-000000] [Galera] gcs connect failed: Connection timed out
2021-06-18T15:33:08.426123Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://45.56.115.5,45.33.103.216,45.79.213.218) failed to establish connection with cluster (reason: 7)
2021-06-18T15:33:08.426160Z 0 [ERROR] [MY-010119] [Server] Aborting

[matthewb]

I see “Connection timed out”. You confirm that node2 and node3 are online and connected to each other and that the cluster is in Primary state?

[Mikem]

I cleared everything and created a new cluster. All is good now. Thanks.

[Mikem]

Do I need to stop the bootstrap service and start the mysql service at this point?

[matthewb]

No you don’t. Again, bootstrap is just a notion for starting a new cluster. It is not a “state” that a node is in. It simply means “you are the first; don’t look for others to join”

[Naveen]

hii @matthewb can u xpln how to move ssl certificates which are generated on bootstraped node to other nodes.

[matthewb]

Hi Naveen, use scp to copy the files to other nodes.

[Naveen]

hii @matthewb ,i copied the certificates from bootstraped node to other node and edited my.cnf file on node 2 and given the certificates location.And i started the node 2.Iam getting the below error

[matthewb]

Check permissions of the key files. Make sure the paths are correct and you’ve properly configured the paths in my.cnf

[Naveen]

hii @matthewb .i enabled encryption on all nodes.

But when i try to connect to mysql server from any remote machine ,iam able to connect to it without any ssl certificates.Can u explain how can i connect to mysql server only with ssl certificates.

[matthewb]

@Naveen,
The SSL certificates you copied are for node->node communications. If you want to enforce client->node SSL, you need to copy the client.* SSL certs from the node to the client and load the certs. If you want to force SSL, you need to ALTER USER … REQUIRE SSL;

[Naveen]

@matthewb i created a user and altered the user as u said.

And from other remote machine i tried connecting to mysql server without ssl.

Can u identify what mistake iam doing.

[matthewb]

Did you confirm that the connection was not SSL? You need to run \s to see if it is not ssl.