Install MariaDB Galera Cluster on Ubuntu 16.04

Today I’m glad to publish a guest post about Galera a software that I’ve used in the past to manage a multi-master mysql cluster:

MariaDB Galera Cluster is a multi-master cluster for MariaDB. MariaDB Galera Cluster currently supports only the InnoDB/XtraDB storage engine. It is mostly used for database redundancy as it consists of multiple master servers that are synchronized and connected to each other. In today’s tutorial we are going to learn how to install MariaDB Galera Cluster on a clean Ubuntu 16.04 VPS, on optimized MariaDB hosting.

Requirements:

Before we start there are a couple of requirements we need to fulfill in order to satisfy the minimum for MariaDB Galera Cluster:

• Three nodes with Ubuntu 16.04 server installed and running
• The nodes must have static IP addresses assigned in the same subnet
• Sudo privileges

1. Upgrade the system

As usual before we begin installing software we make sure our system is up to date, run the following commands to do that:

# sudo apt-get -y update

# sudo apt-get -y upgrade

2. Install MariaDB Galera Cluster

Now we are going to install MariaDB Galera Cluster, to do that we are first going to install MariaDB 10.1 as MariaDB 10.1 is “Galera ready” and contains the MariaDB Galera Server package along with the wsrep patch. MariaDB 10.1 is not available in the Ubuntu 16.04 repositories by default so we have to add the repository manually. Do this step for all three nodes.

Execute the following command to add the MariaDB repository key:

# sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

Then install the appropriate packages to run the add-apt-repository command:

# sudo apt-get -y install software-properties-common python-software-properties

Add the repository and then update the packages:

# sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirror.jaleco.com/mariadb/repo/10.1/ubuntu xenial main'

# sudo apt-get -y update

Install MariaDB and rsync:

# sudo apt-get -y install mariadb-server rsync

After the installation is complete, in order to improve the security of your MariaDB server run the mysql_secure_installation script:

# sudo mysql_secure_installation

Answer the questions like in the example below:

Enter current password for root (enter for none): press enter

Change the root password? [Y/n] n

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

If you receive this error “ERROR 1524 (HY000): Plugin ‘unix_socket’ is not loaded” when pressing enter on the first question, do the following steps:

1.Hit CTRL+C to exit the script then stop MariaDB and load MariaDB with skip-grant-tables:

# sudo systemctl stop mariadb

# sudo mysqld_safe --skip-grant-tables &

# sudo mysql -u root

Now that you’ve logged in the MariaDB shell execute the following commands:

MariaDB [(none)]> use mysql;

MariaDB [(none)]> update user set plugin="mysql_native_password";

MariaDB [(none)]> quit;

3.Kill existing MariaDB processes and start MariaDB:

# sudo kill -9 $(pgrep mysql)

# sudo systemctl start mariadb

4.Run the mysql_secure_installation script again:

# sudo mysql_secure_installation

3. Setup MariaDB Galera Cluster on the nodes

To set up MariaDB Galera Cluster we are going to use the nodes that we configured on the subnet 10.20.30.0/24, you should use your own subnet and have the nodes configured with static IP addresses. Do this step for all three nodes.

Open the following file with nano:

nano /etc/mysql/conf.d/galera-cluster.cnf

Then paste the following contents inside the file:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://10.20.30.1,10.20.30.2,10.20.30.3"

# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="10.20.30.1"
wsrep_node_name="Galera_Node1"

Save the file when you are done making changes.

Note: The node IP addresses in the wsrep_cluster_address variable need to be changed to your own node IP addresses and are in order from first to last, change the IP address in wsrep_node_address to match the IP address of the node you are configuring and change the wsrep_node_name accordingly.

4. Configure the firewall

In order to keep the nodes secure we are going to enable the Uncomplicated Firewall on the nodes, do that by typing in the following command:

# sudo ufw enable

Then enable the ports we need in order for the nodes to be able to communicate with each other:

# sudo ufw allow 3306/tcp
# sudo ufw allow 4444/tcp
# sudo ufw allow 4567/tcp
# sudo ufw allow 4568/tcp
# sudo ufw allow 4567/udp

Check the status of the firewall:

# sudo ufw status

If the firewall is configured on all three nodes, you can proceed with the step below.

5. Start the MariaDB Galera Cluster

Now, we are finally ready to start the MariaDB Galera Cluster we configured in the previous steps. First stop MariaDB on all nodes:

# sudo systemctl stop mariadb

Then start the MariaDB Galera Cluster on the first node:

# sudo galera_new_cluster

Check whether the cluster is running by executing the following command:

# sudo mysql -u root -e "show status like 'wsrep_cluster_size'"

You should see something like the following output if the cluster is running:

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 1     |

+--------------------+-------+

Start the MariaDB service on the second node:

# sudo systemctl start mariadb

Your second node should automatically connect to the cluster, check if it’s connected to the cluster by running the following command:

# sudo mysql -u root -e "show status like 'wsrep_cluster_size'"

You should see something like the following output if it’s connected to the cluster:

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 2     |

+--------------------+-------+

Start the MariaDB service on the third node:

# sudo systemctl start mariadb

Then check if it’s connected to the cluster with the following command:

# sudo mysql -u root -e "show status like 'wsrep_cluster_size'"

You should see something like the following output if it’s connected to the cluster:

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| wsrep_cluster_size | 3     |

+--------------------+-------+

6. Testing database replication on the nodes

In the final step we will test if a database is successfully replicated across the cluster, run the following command to create a test database and to display all databases so we can see if the test database was successfully created:

# sudo mysql -u root -e "create database test; show databases;"

The output should look like:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

Then on your second and third node run the following command to check if the test database was successfully replicated:

# sudo mysql -u root -e "show databases;"

The output should look like:

+--------------------+
| Database           |
+--------------------+

| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

If you can see the test database on all three nodes in the output then you have successfully set up a MariaDB Galera Cluster on three nodes.

Popular Posts:

None Found