Contents

How to Install MySQL Galera Cluster on CentOS 7

19 Nov 2017 6 627  8
How to Install MySQL Galera Cluster on CentOS 7
How to Install MySQL Galera Cluster on CentOS 7

What is MySQL Galera cluster?

MySQL Galera Cluster is a synchronous multi-master cluster which is only available for Linux and supports only the XtraDB/InnoDB storage engines. Its function is to provide high availability and high throughput using low latency allowing for close linear scalability.

MySQL Cluster is implemented from the NDB (Network Database) or NDBCLUSTER storage engine for MySQL.

A few notable MySQL features will include synchronous replication, active-active multi-master topology, read and write to any cluster node, automatic membership control, parallel replication on row level, and direct client connections. A lot of these features yield benefits for a DBMS clustering solution along with no slave lag, no lost transactions, both read and write scalability, and smaller client latencies.

Steps to configure a MySQL Cluster

Step 1: enable the Galera Cluster yum repositories

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 prerequisite packages

yum -y install galera-3 mysql-wsrep-5.7 rsync lsof policycoreutils-python firewalld

Step 3: Enable Galera Service

Next we’ll enable Galera’s service to start at boot but make sure to not start the service just yet

systemctl enable mysqld

Step 4: Enable Firewalld Service

And now we will start the firewalld service and enable the service to start at boot.

systemctl enable firewalld
systemctl start firewalld

Step 5: Configure Galera Firewalld exeptions

Here we will configure the firewall’s exceptions to allow both replication and remote MySQL connecitons then reload the firewall.

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: (Optional) Configure SELinux

Let’s configure SELinux to permissive mode for MySQL

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

NOTE: A few of the SELinux policies are going to be defined.

Step 7: Edit /etc/my.cnf

You will now need to make a backup of the my.cnf file and afterwards edit the file so it includes the below.

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=122M
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_cluster_name="galera_cluster1"
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="mysql01"
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 8: Create MySQL log file

To create the mysqld.log and then append the mysql account the appropriate permissions do the following.

touch /var/log/mysqld.log
chown mysql:mysql /var/log/mysqld.log

Step 9: Repeat steps in other node servers

You’ll now have to repeat the above steps on every MySQL node which participates in the MySQL Cluster, ensure to change the below fields in the /etc/my.cnf file in order to reveal the correct IP address and hostname which is applicable to every node.

server_id=1
wsrep_node_address="10.1.0.11"
wsrep_node_name="mysql01"

Starting up MySQL Cluster for the first time.

Step 10: Start MySQL service

Let’s start the MySQL on the first node, mysql01.

/usr/bin/mysqld_bootstrap

Step 11: MySQL 5.7 Password configuration

Once you’ve installed MySQL 5.7 you will have to search for the temporary password from /var/log/mysqld.log to login as root.

grep 'temporary password' /var/log/mysqld.log
2017-03-31T12:36:15.644109Z 1 [Note] A temporary password is generated for [email protected]: passwordhere

Step 12: Secure MySQL

In order to increase the MySQL’s installation security you’ll need to run the command below. Get all of the default they are normally the most secure. You’ll be prompted to replace the MySQL root password It’s recommended you change this.

/usr/bin/mysql_secure_installation

NOTE: The root password needs to be a well-documented password and should not match the root user password of the Linux system.

Step 13: Confirm Galera Replication

Now login to the MySQL then, run the query below to confirm the amount of nodes in the cluster.

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 14: Start MySQL on member nodes

Next start MySQL on the other member nodes, mysql02 and mysql03.

systemctl start mysqld

Step 15: Verify nodes have joined the cluster

Login to MySQL and run the below query again to confirm the number of nodes in the cluster has increased.

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)

Verify the Configuration

Step 16: Verify Replication Ports

Make sure to confirm port 4567 is actively listening for any replication connections.

lsof -i:4567
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2958 mysql   12u  IPv4  68974      0t0  TCP *:tram (LISTEN)

Step 17: Confirm MySQL listening

Also confirm if port 3306 is actively listening for any MySQL connections.

lsof -i:3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  2958 mysql   39u  IPv4  68990      0t0  TCP *:mysql (LISTEN)

Step 18: Test Replication

You may test replication by making a fresh database on one node and afterwards verify the database is replicated to other nodes. Use the query below on the source node used to make the database and onn member nodes to confirm replication.

mysql -u root -p
mysql> create database galera_test;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| galera_test        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

5 rows in set (0.01 sec)

Step 19: Node Shutdown

In this step we shutdown mysql01 and then we check the cluster size from one of the member nodes.

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

1 row in set (0.00 sec)

Step 20: Node Startup and verification

Get mysql01 online again and verify all three nodes are connected.

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

1 row in set (0.00 sec)

Step 21: Verify full cluster membership

Let’s ensure all three node IPs are currently listed in wsrep_incoming_addresses. Disconnected nodes are normally removed from the replication automatically.

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 22: Verify MySQL version

This query should confirm the version of MySQL that you are running.

mysql> SHOW VARIABLES LIKE "%version%";

+-------------------------+---------------------------------------------+
| Variable_name           | Value                                       |
+-------------------------+---------------------------------------------+
| innodb_version          | 5.7.18                                      |
| protocol_version        | 10                                          |
| slave_type_conversions  |                                             |
| tls_version             | TLSv1,TLSv1.1                               |
| version                 | 5.7.18                                      |
| version_comment         | MySQL Community Server - (GPL), wsrep_25.12 |
| version_compile_machine | x86_64                                      |
| version_compile_os      | Linux                                       |
+-------------------------+---------------------------------------------+

8 rows in set (0.01 sec)

Step 23: Verify MySQL file format

This query will confirm the default format on the MySQL Server.

mysql> SHOW VARIABLES LIKE 'innodb_file_format';

+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+

1 row in set (0.00 sec)

Complete cluster shutdown and restart

Step 24: In case a node won’t startup

In case a complete shutdown of the MySQL Cluster nodes won’t start automatically. What we wanna do first is to restart the cluster and finding which node has the safe_to_bootstrap set to 1 in the grastate.dat file.

cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    a403c026-8e57-11e7-9cba-43155149803a
seqno:   -1
safe_to_bootstrap: 1

Step 25: Re-Bootstrap MySQL Galera Cluster

Where the MySQL node is with safe_to_bootstrap set to 1 we start MySQL with the command below.

/usr/bin/mysqld_bootstrap

Step 26: Start All Nodes

Once the initial node has started, start the remaining nodes using the command below. Notice the command to start the MySQL service is changed on subsequent member nodes.

systemctl start mysqld

Step 27: Verify all nodes are in cluster

Lastly, we’ll login to MySQL and then run the query below again to verify the three nodes are online.

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)

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       |
+------------------------------+----------------------------------------------------+

That’s all.

8 Comments
Add a comment

8 Replies to “How to Install MySQL Galera Cluster on CentOS 7”

  1. I am unable to init the cluster due to missing root password (step 11):

    [[email protected]_n1 ~]# grep ‘temporary password’ /var/log/mysqld.log
    [[email protected]_n1 ~]#

    [[email protected]_n1 ~]# /usr/bin/mysql_secure_installation
    Securing the MySQL server deployment.
    Enter password for user root:
    Error: Access denied for user ‘root’@’localhost’ (using password: NO)

    [[email protected]_n1 ~]# find / -name myql_safe
    [[email protected]_n1 ~]#

    I am new to Galera. Could you please advice me what could be wrong?

    Thanks,
    George

  2. Thanks for the great article.

    One issue I had with this and took me hours to resolve was in this line:
    wsrep_cluster_address=”gcomm://10.1.0.11:3306,10.1.0.12:3306,10.1.0.13:3306″

    I found that the port number 3306 should be either omitted, or changed to 4567 to get this to work properly.

    1. I wish I read your comment before I started testing. It would save me some time 🙂
      According to documentation http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsrep-cluster-address : “wsrep_cluster_address – Defines the back-end schema, IP addresses, ports and options the node uses in connecting to the cluster.” It is back-end’s socket, and not the one used by application to connect to DB.

      Regardless that small mistake, this guide was very helpful for me.

Leave a Reply