To use the multiple nodes from one or multiple web servers, it need load balancer. There’s several LB but HAProxy is one of most popular and easy to install. Here’s documentation from Percona how to set up HAProxy on Percona XtraDB clusters.
Let’s say there are Percona DB nodes synced each other and one web server.
And I would like to make web server connected to 2 db nodes.
Here’s important part, there’s typo in the programming where the mysql username and password recorded. Fix it like below, you can change the user name and password but it should be matched with the mysql user information created above. For me, this took an hour to find out this bug. No body reported this bug on the git repository although this is at least 3 years old.
You can check health status of nodes from web browser, port 9200. http://10.7.13.91:9200/ http://10.7.13.92:9200/
Make sure the message saying: Percona XtraDB Cluster Node is synced. If it says Percona XtraDB Cluster Node is not synced. then check if the clusteruser login information matched with mysql user and credential on the file (/usr/bin/clustercheck)
You can also check through terminal.
Connectivity from web server
From web server, check if the connection to db node is working through port 3306.
mysql -uyourmysqluser -p -P 3306 -h 10.7.13.91 -e "show variables like 'wsrep_node_name';"
If there’s no problem, also check connection using port 3307, through HAProxy.
mysql -uyourmysqluser -p -P 3307 -h 127.0.0.1 -e "show variables like 'wsrep_node_name';"
If everything works fine, you will see below and now mysql is connected using host 127.0.0.1 and port 3307.
Set all node with same configuration but you will need to change wsrep_cluster_address for all your nodes and configure below line.
Also set your sstuser and password for this line:
Create SST user
Login to each nodes and login to mysql. Then create SST users.
CREATE USER 'sstuser'@'%' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Open port from terminal
Double check if port is opened and it communicate each other.
SSH, also known as Secure Socket Shell, is a network protocol that provides administrators with a secure way to access a remote computer. SSH also refers to the suite of utilities that implement the protocol. Secure Shell provides strong authentication and secure encrypted data communications between two computers connecting over an insecure network such as the Internet.
Without SSH, browser marked as “Not Secure” on the address line in Chrome. This is essential to have secure browsing.
Granting mysql access from remote is not secure, so it’s not recommendable but sometimes we need to do it. Here’s how to do.
Login to mysql with root and create user with remote connect.
GRANT ALL PRIVILEGES ON remote_db.* TO 'remote_user'@'%' IDENTIFIED BY 'passwordhere';
We need to allow remote connection from mysql configuration. mysql.cnf or mysqld.cnf files locations are differ but in my case, it is located in /etc/mysql/mysql.conf.d/ folder. vi /etc/mysql/mysql.conf.d/mysqld.cnf And comment out below line. #bind-address = 127.0.0.1
After that mysql restart desired. service mysql restart
Now if you have any firewall or network security, allow the port. In my case, I needed to allow 3306 port in AWS inbound rule.