Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Steps to Install & Configure MySQL 3-Node Galera Cluster on CENTOS
Step 1: Enabling the Galera Cluster yum repositories.
1 2 3 4 5 6 7 8 9 10 11 12 13 | vi /etc/yum.repos.d/galera.repo [galera] name = Galera baseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64/ gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com gpgcheck = 1 [mysql-wsrep] name = MySQL-wsrep baseurl = http://releases.galeracluster.com/mysql-wsrep-5.7.21-25.14/centos/7/x86_64/ gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.com gpgcheck = 1 |
Step 2: Install Galera and the prerequisite packages.
1 | yum -y install galera-3 mysql-wsrep-5.7 rsync lsof policycoreutils-python firewalld |
Step 3: Enable the Galera service to start at boot.
1 | systemctl enable mysqld |
Step 4: Enable the service to start at boot & Start the firewalld service.
1 2 | systemctl enable firewalld systemctl start firewalld |
Step 5: Configure firewall exceptions to allow both replication and remote MySQL connections and reload the firewall.
1 2 3 4 5 6 7 | firewall-cmd --zone=public --add-service=mysql –permanent firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --zone=public --add-port=4444/tcp --permanent firewall-cmd --zone=public --add-port=4567/tcp --permanent firewall-cmd --zone=public --add-port=4567/udp --permanent firewall-cmd --zone=public --add-port=4568/tcp --permanent firewall-cmd –reload |
Step 6: Configure SELinux to permissive mode for MySQL.
1 2 3 4 5 | semanage port -a -t mysqld_port_t -p tcp 4567 semanage port -a -t mysqld_port_t -p udp 4567 semanage port -a -t mysqld_port_t -p tcp 4568 semanage port -a -t mysqld_port_t -p tcp 4444 semanage permissive -a mysqld_t |
Step 7: Let us Configure my.cnf file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | cp /etc/my.cnf /etc/my.cnf.bak vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql binlog_format=ROW bind-address=0.0.0.0 default_storage_engine=innodb innodb_autoinc_lock_mode=2 innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=128M wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so wsrep_provider_options="gcache.size=300M; gcache.page_size=300M" wsrep_cluster_name="GaleraCluster" wsrep_cluster_address="gcomm://10.1.0.11:3306,10.1.0.12:3306,10.1.0.13:3306" wsrep_sst_method=rsync server_id=1 wsrep_node_address="10.1.0.11" wsrep_node_name="node_aplha" [mysql_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
Step 8: Repeat the above steps on each MySQL node that will participate in the MySQL Cluster. Be sure to alter the below fields in the /etc/my.cnf file to reflect the correct IP address and hostname that is applicable to each node.
1 2 3 4 5 6 7 8 9 10 11 | server_id=1 wsrep_node_address="10.1.0.11" wsrep_node_name="node_aplha" server_id=2 wsrep_node_address="10.1.0.12" wsrep_node_name="node_beta" server_id=3 wsrep_node_address="10.1.0.13" wsrep_node_name="node_gamma" |
Step 9 : Create the mysqld.log and give the mysql account the appropriate permissions on each node of the Galera Cluster.
1 2 | touch /var/log/mysqld.log chown mysql:mysql /var/log/mysqld.log |
Starting MySQL Cluster for the first time
Step 10: Start the MySQL on the first node, i.e node_alpha.
1 | /usr/bin/mysqld_bootstrap |
Step 11: After you installed MySQL 5.7 you will need to find the temporary password from /var/log/mysqld.log to login as root.
1 | grep 'temporary password' /var/log/mysqld.log |
Step 12: Login to MySQL and run the below query to confirm the number of nodes in the cluster.
1 2 3 4 5 6 7 8 | mysql -u root -p mysql> SHOW STATUS LIKE 'wsrep_cluster_size'; +-------------------------+-----------------+ | Variable_name | Value | +-------------------------+-----------------+ | wsrep_cluster_size | 1 | +-------------------------+-----------------+ 1 row in set (0.00 sec) |
Step 13: Start MySQL on the other member nodes, node_beta and node_gamma. Notice the command to start the MySQL service is different on subsequent member nodes.
1 | systemctl start mysqld |
Step 14: Login to MySQL and run the below query again to confirm the number of nodes in the cluster has increased.
1 2 3 4 5 6 7 8 | mysql -u root -p mysql> SHOW STATUS LIKE 'wsrep_cluster_size'; +---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | wsrep_cluster_size | 3 | +---------------------------+--------+ 1 row in set (0.00 sec) |
Step 15 : Let’s ensure all three node IPs are currently listed in wsrep_incoming_addresses. Disconnected nodes are normally removed from the replication automatically.
1 2 3 4 5 | mysql> SHOW STATUS LIKE 'wsrep%'; +-----------------------------------------------------------------------------+ | Variable_name | Value | +-----------------------------------------------------------------------------+ | wsrep_incoming_addresses | 10.1.0.11:3306,10.1.0.12:3306,10.1.0.13:3306 | |
Step 16 : Let’s create a mysql user for State Snapshot Transfer(SST) and add below parameters in my.cnf file
1 2 3 4 5 6 7 8 9 | mysql -uroot -p mysql > grant all on *.* to galera@'%' identified by 'passwd'; mysql > flush privileges. mysql > exit vi /ete/my.cnf wsrep_sst_method=rsync wsrep_sst_auth=galera:passwd |
At last restart the cluster nodes.
Thanks for Sharing Detailed implementation on Cluster.