June 2, 2014

Setting Up a MariaDB Enterprise Cluster, Part 2: How to Set Up a MariaDB Cluster

Set up a 3-Node MariaDB Cluster with HA Proxy on VirtualBox

Today we will build a three node MariaDB Cluster 5.5 using the Oracle VirtualBox virtual machine template we created in Part 1.

For this tutorial, we will need three IP addresses. I will be using the following. I recommend noting what IP addresses you will be using unless you're going to use the same.

	       Hostname		        IP
Node 1 – mariadbcluster1 – 192.168.56.221
Node 2 – mariadbcluster2 – 192.168.56.222
Node 3 – mariadbcluster3 – 192.168.56.223

For the remainder of this tutorial, I will refer to each as Node 1, Node 2, and Node 3.

To get started, make sure your virtual machine template is powered off.

bens-mbp:~ ben$ ssh root@192.168.56.210
root@192.168.56.210's password:
Last login: Wed Apr  2 14:06:40 2014 from 192.168.56.1
[root@tutorial_template~]# poweroff

Broadcast message from root@tutorial_template(/dev/pts/0) at 14:12 ...

The system is going down for power off NOW!
[root@tutorial_template~]# Connection to 192.168.56.210 closed by remote host.
Connection to 192.168.56.210 closed.
bens-mbp:~ben$

Now to clone it three times. The following steps will be repeated for each copy.

Highlight the virtual machine template in the left column, and press Command + O. This should open a new window where we can name the new virtual machine. Be sure to check "Reinitialize the MAC address of all network cards".

Next is choosing "Full clone" or "Linked clone". Select "Full clone" and continue.

You should now see two virtual machines in the left column; your template and the newly created clone.

Highlight the new clone, and click "Start" towards the upper left corner of the window (green arrow icon). Once it starts, log in as root and run the clone_network_setup.sh script. The script will reboot the virtual machine. The IP address and hostname come from the table at the top of this tutorial.

[root@tutorial_template~]# ~/clone_network_setup.sh 192.168.56.221 mariadbcluster1

You should now be able to login to the new clone via iTerm.

bens-mbp:~ben$ ssh root@192.168.56.221
The authenticity of host '192.168.56.221 (192.168.56.221)' can't be established.
RSA key fingerprint is 37:9a:b9:2d:4b:29:c7:a6:87:76:5a:24:89:1a:a6:cb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.221' (RSA) to the list of known hosts.
root@192.168.56.221's password:
Last login: Wed Apr  2 15:13:40 2014
[root@mariadbcluster1~]#

Verify the hostname and network interface is setup.

[root@mariadbcluster1 ~]# echo $HOSTNAME mariadbcluster1
[root@mariadbcluster1 ~]# ifconfig eth1
eth1     Link encap:Ethernet  HWaddr 08:00:27:57:D7:49
         inet addr:192.168.56.221  Bcast:192.168.56.255  Mask:255.255.255.0
         inet6 addr: fe80::a00:27ff:fe57:d749/64 Scope:Link
         UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
         RX packets:92 errors:0 dropped:0 overruns:0 frame:0
         TX packets:73 errors:0 dropped:0 overruns:0 carrier:0
         collisions:0 txqueuelen:1000
         RX bytes:10177 (9.9 KiB)  TX bytes:8475 (8.2 KiB)

Ping google.com.

[root@mariadbcluster1 ~]# ping google.com
PING google.com (74.125.196.138) 56(84) bytes of data.
64 bytes from yk-in-f138.1e100.net (74.125.196.138): icmp_seq=1 ttl=63 time=48.3 ms
64 bytes from yk-in-f138.1e100.net (74.125.196.138): icmp_seq=2 ttl=63 time=49.9 ms
64 bytes from yk-in-f138.1e100.net (74.125.196.138): icmp_seq=3 ttl=63 time=53.4 ms
^C
--- google.com ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2470ms
rtt min/avg/max/mdev = 48.394/50.612/53.462/2.132 ms

Node 1 is ready. Repeat these steps to create Node 2 and Node 3.

At this point, you should have three nodes all setup and ready to install MariaDB Cluster.

Node 1:

Last login: Wed Apr  2 15:29:53 on ttys001
bens-mbp:~ ben$ ssh root@192.168.56.221
root@192.168.56.221's password: 
Last login: Wed Apr  2 15:27:17 2014 from 192.168.56.1
[root@mariadbcluster1~]#  

Node 2:

Last login: Wed Apr  2 15:30:41 on ttys001
bens-mbp:~ ben$ ssh root@192.168.56.222
The authenticity of host '192.168.56.222 (192.168.56.222)' can't be established.
RSA key fingerprint is 37:9a:b9:2d:4b:29:c7:a6:87:76:5a:24:89:1a:a6:cb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.222' (RSA) to the list of known hosts.
root@192.168.56.222's password: 
Last login: Wed Apr  2 15:29:03 2014
[root@mariadbcluster2 ~]# 

Node 3:

Last login: Wed Apr  2 15:30:50 on ttys001
bens-mbp:~ ben$ ssh root@192.168.56.223
The authenticity of host '192.168.56.223 (192.168.56.223)' can't be established.
RSA key fingerprint is 37:9a:b9:2d:4b:29:c7:a6:87:76:5a:24:89:1a:a6:cb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.223' (RSA) to the list of known hosts.
root@192.168.56.223's password: 
Last login: Wed Apr  2 15:29:24 2014 
[root@mariadbcluster3 ~]# 

On all three nodes, we'll install MariaDB-Galera-server, MariaDB client, galera, rsync, percona-toolkit, and percona-xtrabackup. Only the results from Node 1 are shown, but it was done on all three nodes.

[root@mariadbcluster1~]# yum install MariaDB-Galera-server MariaDB-client galera rsync
percona-toolkit precona-xtrabackup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.cmich.edu
* extras: yum.singlehop.com
* updates: mirrors.centarra.com
Setting up Install Process
No package precona-xtrabackup available.
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-Galera-server.x86_64 0:5.5.36-1.el6 will be installed
--> Processing Dependency: perl(strict) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(File::Temp) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(File::Copy) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(File::Path) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(File::Basename) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(Sys::Hostname) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(POSIX) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: /usr/bin/perl for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(vars) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(DBI) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: MariaDB-common for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
--> Processing Dependency: perl(Getopt::Long) for package: MariaDB-Galera-server-5.5.36-1.el6.x86_64
---> Package MariaDB-client.x86_64 0:5.5.36-1.el6 will be installed
---> Package galera.x86_64 0:25.3.2-1.rhel6 will be installed
---> Package percona-toolkit.noarch 0:2.2.7-1 will be installed
--> Processing Dependency: perl(DBD::mysql) >= 1.0 for package: percona-toolkit-2.2.7-1.noarch
--> Processing Dependency: perl(Time::HiRes) for package: percona-toolkit-2.2.7-1.noarch
--> Processing Dependency: perl(IO::Socket::SSL) for package: percona-toolkit-2.2.7-1.noarch
---> Package rsync.x86_64 0:3.0.6-9.el6_4.1 will be installed
--> Running transaction check
---> Package MariaDB-common.x86_64 0:5.5.36-1.el6 will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-5.5.36-1.el6.x86_64
---> Package perl.x86_64 4:5.10.1-136.el6 will be installed
--> Processing Dependency: perl-libs = 4:5.10.1-136.el6 for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl-libs for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl(version) for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl(Pod::Simple) for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: perl(Module::Pluggable) for package: 4:perl-5.10.1-136.el6.x86_64
--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.10.1-136.el6.x86_64
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
---> Package perl-IO-Socket-SSL.noarch 0:1.31-2.el6 will be installed
--> Processing Dependency: perl(Net::SSLeay) >= 1.21 for package: perl-IO-Socket-SSL-1.31-2.el6.noarch
--> Processing Dependency: perl(Net::LibIDN) for package: perl-IO-Socket-SSL-1.31-2.el6.noarch
---> Package perl-Time-HiRes.x86_64 4:1.9721-136.el6 will be installed
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:5.5.36-1.el6 will be obsoleting
---> Package mysql-libs.x86_64 0:5.1.71-1.el6 will be obsoleted
---> Package perl-Module-Pluggable.x86_64 1:3.90-136.el6 will be installed
---> Package perl-Net-LibIDN.x86_64 0:0.12-3.el6 will be installed
---> Package perl-Net-SSLeay.x86_64 0:1.35-9.el6 will be installed
---> Package perl-Pod-Simple.x86_64 1:3.13-136.el6 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.13-136.el6.x86_64
---> Package perl-libs.x86_64 4:5.10.1-136.el6 will be installed
---> Package perl-version.x86_64 3:0.77-136.el6 will be installed
--> Running transaction check
---> Package perl-Pod-Escapes.x86_64 1:1.04-136.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================================
Package                             Arch                 Version                       Repository             Size
=====================================================================================================================
Installing:

MariaDB-Galera-server               x86_64               5.5.36-1.el6                  mariadb                25 M
MariaDB-client                      x86_64               5.5.36-1.el6                  mariadb                10 M
MariaDB-compat                      x86_64               5.5.36-1.el6                  mariadb                2.7 M
    replacing mysql-libs.x86_64 5.1.71-1.el6
galera                              x86_64               25.3.2-1.rhel6                mariadb               7.5 M
percona-toolkit                    noarch               2.2.7-1                       percona               1.7 M
rsync                              x86_64               3.0.6-9.el6_4.1               base                  334 k
Installing for dependencies:
MariaDB-common                     x86_64               5.5.36-1.el6                  mariadb                23 k
perl                               x86_64               4:5.10.1-136.el6              base                   10 M
perl-DBD-MySQL                     x86_64               4.013-3.el6                   base                  134 k
perl-DBI                           x86_64               1.609-4.el6                   base                  705 k
perl-IO-Socket-SSL                 noarch               1.31-2.el6                    base                   69 k
perl-Module-Pluggable              x86_64               1:3.90-136.el6                base                   40 k
perl-Net-LibIDN                    x86_64               0.12-3.el6                    base                   35 k
perl-Net-SSLeay                    x86_64               1.35-9.el6                    base                  173 k
perl-Pod-Escapes                   x86_64               1:1.04-136.el6                base                   32 k
perl-Pod-Simple                    x86_64               1:3.13-136.el6                base                  212 k
perl-Time-HiRes                    x86_64               4:1.9721-136.el6              base                   48 k
perl-libs                          x86_64               4:5.10.1-136.el6              base                  578 k
perl-version                       x86_64               3:0.77-136.el6                base                   51 k

Transaction Summary
=====================================================================================================================
Install     19 Package(s)

Total download size: 59 M
Is this ok [y/N]: y
Downloading Packages:
(1/19): MariaDB-Galera-5.5.36-centos6-x86_64-server.rpm                                      |  25 MB     00:15     
(2/19): MariaDB-5.5.36-centos6-x86_64-client.rpm                                             | 10 MB 00:06
(3/19): MariaDB-5.5.36-centos6-x86_64-common.rpm                                             | 23 kB 00:00
(4/19): MariaDB-5.5.36-centos6-x86_64-compat.rpm                                             | 2.7 MB 00:02
(5/19): galera-25.3.2-1.rhel6.x86_64.rpm                                                     | 7.5 MB 00:06
(6/19): percona-toolkit-2.2.7-1.noarch.rpm                                                   | 1.7 MB 00:01
(7/19): perl-5.10.1-136.el6.x86_64.rpm                                                       | 10 MB 00:09
(8/19): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                | 134 kB 00:00
(9/19): perl-DBI-1.609-4.el6.x86_64.rpm                                                      | 705 kB 00:00
(10/19): perl-IO-Socket-SSL-1.31-2.el6.noarch.rpm                                            | 69 kB 00:00
(11/19): perl-Module-Pluggable-3.90-136.el6.x86_64.rpm                                       | 40 kB 00:00
(12/19): perl-Net-LibIDN-0.12-3.el6.x86_64.rpm                                               | 35 kB 00:00
(13/19): perl-Net-SSLeay-1.35-9.el6.x86_64.rpm                                               | 173 kB 00:00
(14/19): perl-Pod-Escapes-1.04-136.el6.x86_64.rpm                                            | 32 kB 00:00
(15/19): perl-Pod-Simple-3.13-136.el6.x86_64.rpm                                             | 212 kB 00:00
(16/19): perl-Time-HiRes-1.9721-136.el6.x86_64.rpm                                           | 48 kB 00:00
(17/19): perl-libs-5.10.1-136.el6.x86_64.rpm                                                 | 578 kB 00:01
(18/19): perl-version-0.77-136.el6.x86_64.rpm                                                | 51 kB 00:00
(19/19): rsync-3.0.6-9.el6_4.1.x86_64.rpm                                                    | 334 kB 00:00

---------------------------------------------------------------------------------------------------------------------
Total                                                                               1.2 MB/s |  59 MB     00:48     

warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY 
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona 
Importing GPG key 0xCD2EFD2A:
 Userid : Percona MySQL Development Team <mysql-dev@percona.com>
 Package: percona-release-0.0-1.x86_64 (@/percona-release-0.0-1.x86_64)
 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-percona 
Is this ok [y/N]: y 
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY 
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 
Importing GPG key 0x1BB943DB:
 Userid: "Daniel Bartholomew (Monty Program signing key) <dbart@askmonty.org>"
 From : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 
Is this ok [y/N]: y 
Running rpm_check_debug 
Running Transaction Test 
Transaction Test Succeeded 
Running Transaction
 Installing : MariaDB-compat-5.5.36-1.el6.x86_64 1/20
 Installing : MariaDB-common-5.5.36-1.el6.x86_64 2/20
 Installing : 1:perl-Pod-Escapes-1.04-136.el6.x86_64 3/20
 Installing : 1:perl-Pod-Simple-3.13-136.el6.x86_64 4/20
 Installing : 4:perl-libs-5.10.1-136.el6.x86_64 5/20
 Installing : 3:perl-version-0.77-136.el6.x86_64 6/20
 Installing : 1:perl-Module-Pluggable-3.90-136.el6.x86_64 7/20
 Installing : 4:perl-5.10.1-136.el6.x86_64 8/20
 Installing : perl-DBI-1.609-4.el6.x86_64 9/20
 Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 10/20
 Installing : 4:perl-Time-HiRes-1.9721-136.el6.x86_64 11/20
 Installing : perl-Net-SSLeay-1.35-9.el6.x86_64 12/20
 Installing : perl-Net-LibIDN-0.12-3.el6.x86_64 13/20
 Installing : perl-IO-Socket-SSL-1.31-2.el6.noarch 14/20
 Installing : galera-25.3.2-1.rhel6.x86_64 15/20
 Installing : MariaDB-Galera-server-5.5.36-1.el6.x86_64 16/20

chown: cannot access `/var/lib/mysql': No such file or directory 
140402 15:39:33 [Note] WSREP: Read nil XID from storage engines, skipping position init 
140402 15:39:33 [Note] WSREP: wsrep_load(): loading provider library 'none' 
140402 15:39:33 [Note] WSREP: Service disconnected. 
140402 15:39:34 [Note] WSREP: Some threads may fail to exit. 
140402 15:39:34 [Note] WSREP: Read nil XID from storage engines, skipping position init 
140402 15:39:34 [Note] WSREP: wsrep_load(): loading provider library 'none' 
140402 15:39:34 [Note] WSREP: Service disconnected. 
140402 15:39:35 [Note] WSREP: Some threads may fail to exit.

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! 
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password' 
'/usr/bin/mysqladmin' -u root -h mariadbcluster1 password 'new-password'

Alternatively you can run: 
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions.

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Support MariaDB development by buying support/new features from SkySQL Ab. You can contact us about this at sales@skysql.com. Alternatively consider joining our community based development effort: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/

 Installing : percona-toolkit-2.2.7-1.noarch 17/20
 Installing : MariaDB-client-5.5.36-1.el6.x86_64 18/20
 Installing : rsync-3.0.6-9.el6_4.1.x86_64 19/20
 Erasing : mysql-libs-5.1.71-1.el6.x86_64 20/20
 Verifying : 1:perl-Module-Pluggable-3.90-136.el6.x86_64 1/20
 Verifying : percona-toolkit-2.2.7-1.noarch 2/20
 Verifying : perl-IO-Socket-SSL-1.31-2.el6.noarch 3/20
 Verifying : 1:perl-Pod-Escapes-1.04-136.el6.x86_64 4/20
 Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 5/20
 Verifying : MariaDB-common-5.5.36-1.el6.x86_64 6/20
 Verifying : 4:perl-5.10.1-136.el6.x86_64 7/20
 Verifying : 4:perl-Time-HiRes-1.9721-136.el6.x86_64 8/20
 Verifying : perl-DBI-1.609-4.el6.x86_64 9/20
 Verifying : 1:perl-Pod-Simple-3.13-136.el6.x86_64 10/20
 Verifying : 4:perl-libs-5.10.1-136.el6.x86_64 11/20
 Verifying : MariaDB-compat-5.5.36-1.el6.x86_64 12/20
 Verifying : rsync-3.0.6-9.el6_4.1.x86_64 13/20
 Verifying : perl-Net-SSLeay-1.35-9.el6.x86_64 14/20
 Verifying : 3:perl-version-0.77-136.el6.x86_64 15/20
 Verifying : MariaDB-client-5.5.36-1.el6.x86_64 16/20
 Verifying : galera-25.3.2-1.rhel6.x86_64 17/20
 Verifying : MariaDB-Galera-server-5.5.36-1.el6.x86_64 18/20
 Verifying : perl-Net-LibIDN-0.12-3.el6.x86_64 19/20
 Verifying : mysql-libs-5.1.71-1.el6.x86_64 20/20

Installed:
 MariaDB-Galera-server.x86_64 0:5.5.36-1.el6                 MariaDB-client.x86_64 0:5.5.36-1.el6
 MariaDB-compat.x86_64 0:5.5.36-1.el6                        galera.x86_64 0:25.3.2-1.rhel6
 percona-toolkit.noarch 0:2.2.7-1                            rsync.x86_64 0:3.0.6-9.el6_4.1

Dependency Installed:
 MariaDB-common.x86_64 0:5.5.36-1.el6                        perl.x86_64 4:5.10.1-136.el6
 perl-DBD-MySQL.x86_64 0:4.013-3.el6                         perl-DBI.x86_64 0:1.609-4.el6
 perl-IO-Socket-SSL.noarch 0:1.31-2.el6                      perl-Module-Pluggable.x86_64 1:3.90-136.el6
 perl-Net-LibIDN.x86_64 0:0.12-3.el6                         perl-Net-SSLeay.x86_64 0:1.35-9.el6
 perl-Pod-Escapes.x86_64 1:1.04-136.el6                      perl-Pod-Simple.x86_64 1:3.13-136.el6
 perl-Time-HiRes.x86_64 4:1.9721-136.el6                     perl-libs.x86_64 4:5.10.1-136.el6
 perl-version.x86_64 3:0.77-136.el6

Replaced:
 mysql-libs.x86_64 0:5.1.71-1.el6

Complete!

On all three nodes, open ports 873 (rsync), 4567 (Galera), 3306 (MariaDB). I've opened them to the entire subnet.

[root@mariadbcluster1 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 3306 -j ACCEPT
[root@mariadbcluster1 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 4567 -j ACCEPT
[root@mariadbcluster1 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 873 -j ACCEPT
[root@mariadbcluster1 ~]# /etc/init.d/iptables save iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]
[root@mariadbcluster2 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 3306 -j ACCEPT
[root@mariadbcluster2 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 4567 -j ACCEPT
[root@mariadbcluster2 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 873 -j ACCEPT
[root@mariadbcluster2 ~]# /etc/init.d/iptables save iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]
[root@mariadbcluster3 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 3306 -j ACCEPT
[root@mariadbcluster3 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 4567 -j ACCEPT
[root@mariadbcluster3 ~]# iptables -A INPUT -i eth1 -p tcp -m tcp --source 192.168.56.1/24 --dport 873 -j ACCEPT
[root@mariadbcluster3 ~]# /etc/init.d/iptables save iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]

On all three nodes, disable SELINUX. SELINUX prevents Galera from starting.

[root@mariadbcluster1 ~]# sed -i.bak 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
[root@mariadbcluster1 ~]# setenforce 0
[root@mariadbcluster2 ~]# sed -i.bak 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
[root@mariadbcluster2 ~]# setenforce 0
[root@mariadbcluster3 ~]# sed -i.bak 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
[root@mariadbcluster3 ~]# setenforce 0

On all three nodes; start mysql, run mysql_secure_installation, then stop mysql. The mysql database is not replicated via Galera, so this needs to be done on each database and before we setup Galera. Otherwise, errors will occurs.

[root@mariadbcluster1 ~]# service mysql start 
Starting MySQL.... SUCCESS! 

[root@mariadbcluster1 ~]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
     SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.

Set root password? [Y/n] 
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] 
... Success!

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] 
... Success!

By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed 
[mysqld] 
before moving into a production environment. 
# --status - new status of this node

[mysqld] Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB! 
[root@mariadbcluster1 ~]# service mysql stop 
Shutting down MySQL. SUCCESS!

On all three nodes, mv /etc/my.cnf.d/server.cnf to /etc/my.cnf.d/server.cnf.bak, and vi a new server.cnf.

[root@mariadbcluster1 ~]# cd /etc/my.cnf.d/ 
[root@mariadbcluster1 my.cnf.d]# mv server.cnf server.cnf.bak 
[root@mariadbcluster1 my.cnf.d]# vi server.cnf

Insert the following into the new server.cnf file. Be sure to set the following:

wsrep_cluster_name = whatever you want, but the same on all three wsrep_cluster_address = change the IPs to match the IPs you're using wsrep_node_address = IP address of node wsrep_node_incoming_address = IP address of node

These are only the MariaDB Cluster / Galera related options. For this tutorial, we're setting up wsrep_sst_method to use rsync.

[mysqld]
user=mysql

# (This must be substituted by wsrep_format)
binlog_format=ROW

# Currently only InnoDB storage engine is supported
default-storage-engine=innodb

# to avoid issues with 'bulk mode inserts' using autoinc
innodb_autoinc_lock_mode=2

# This is a must for paralell applying
innodb_locks_unsafe_for_binlog=1

# Query Cache is not supported with wsrep
query_cache_size=0
query_cache_type=0

# Override bind-address
# In some systems bind-address defaults to 127.0.0.1, and with mysqldump SST
# it will have (most likely) disastrous consequences on donor node
bind-address=0.0.0.0

##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Provider specific configuration options
#wsrep_provider_options=

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="mariadb_cluster"

# Group communication system handle
wsrep_cluster_address=gcomm://192.168.56.221,192.168.56.222,192.168.56.223

# Human-readable node name (non-unique). Hostname by default.
#wsrep_node_name=

# Base replication <address|hostname>[:port] of the node.
# The values supplied will be used as defaults for state transfer receiving,
# listening ports and so on. Default: address of the first network interface.
wsrep_node_address=192.168.56.221

# Address for incoming client connections. Autodetect by default.
wsrep_node_incoming_address=192.168.56.221

# How many threads will process writesets from other nodes
wsrep_slave_threads=1

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset
automatically
wsrep_auto_increment_control=1

# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
wsrep_sst_method=rsync

# Address which donor should send State Snapshot to.
# Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
#wsrep_sst_receive_address=

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password> wsrep_sst_auth=

# Desired SST donor name.
#wsrep_sst_donor=

# Reject client queries when donating SST (false)
#wsrep_sst_donor_rejects_queries=0

# Protocol version to use
# wsrep_protocol_version=

Now on Node 1, let's bootstrap the cluster using mysqld –wsrep-new-cluster.

[root@mariadbcluster1 my.cnf.d]# mysqld --wsrep-new-cluster &
[1] 1814
[root@mariadbcluster1 my.cnf.d]# 140402 16:35:34 [Note] WSREP: Read nil XID from storage engines, skipping
position init
140402 16:35:34 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so'
140402 16:35:34 [Note] WSREP: wsrep_load(): Galera 25.3.2(r170) by Codership Oy  loaded successfully.
140402 16:35:34 [Note] WSREP: CRC-32C: using "slicing-by-8" algorithm.
140402 16:35:34 [Note] WSREP: Found saved state: 2e6511f3-baa2-11e3-9464-5a277b988cb5:0
140402 16:35:34 [Note] WSREP: Passing config to GCS: base_host = 192.168.56.221; base_port = 4567;
cert.log_conflicts = no; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0;
gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0;
gcs.fc_factor = 1; gcs.fc_limit = 16; gcs.fc_master_slave = NO; gcs.max_packet_size = 64500; gcs.max_throttle =
0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = NO;
repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.proto_max = 5
140402 16:35:34 [Note] WSREP: Assign initial position for certification: 0, protocol version: -1
140402 16:35:34 [Note] WSREP: wsrep_sst_grab()
140402 16:35:34 [Note] WSREP: Start replication
140402 16:35:34 [Note] WSREP: Setting initial position to 2e6511f3-baa2-11e3-9464-5a277b988cb5:0
140402 16:35:34 [Note] WSREP: protonet asio version 0
140402 16:35:34 [Note] WSREP: Using CRC-32C (optimized) for message checksums.
140402 16:35:34 [Note] WSREP: backend: asio
140402 16:35:34 [Note] WSREP: GMCast version 0
140402 16:35:34 [Note] WSREP: (57b3559f-baa6-11e3-bc11-b625640cef64, 'tcp://0.0.0.0:4567') listening at
tcp://0.0.0.0:4567
140402 16:35:34 [Note] WSREP: (57b3559f-baa6-11e3-bc11-b625640cef64, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
140402 16:35:34 [Note] WSREP: EVS version 0
140402 16:35:34 [Note] WSREP: PC version 0
140402 16:35:34 [Note] WSREP: gcomm: bootstrapping new group 'mariadb_cluster'
140402 16:35:34 [Note] WSREP: Node 57b3559f-baa6-11e3-bc11-b625640cef64 state prim
140402 16:35:35 [Note] WSREP: view(view_id(PRIM,57b3559f-baa6-11e3-bc11-b625640cef64,1) memb {57b3559f-baa6-11e3-bc11-b625640cef64,0
} joined {
} left {
} partitioned {
})
140402 16:35:35 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.56.221:4567
140402 16:35:35 [Note] WSREP: discarding pending addr proto entry 0x7f3eddcd3cc0
140402 16:35:35 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.56.222:4567
140402 16:35:35 [Note] WSREP: discarding pending addr proto entry 0x7f3eddcd3fc0
140402 16:35:35 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.56.223:4567
140402 16:35:35 [Note] WSREP: discarding pending addr proto entry 0x7f3eddcd4080
140402 16:35:35 [Note] WSREP: gcomm: connected
140402 16:35:35 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
140402 16:35:35 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
140402 16:35:35 [Note] WSREP: Opened channel 'mariadb_cluster'
140402 16:35:35 [Note] WSREP: Waiting for SST to complete.
140402 16:35:35 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1
140402 16:35:35 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 57b44664-baa6-11e3-b4a4-f674111a1007
140402 16:35:35 [Note] WSREP: STATE EXCHANGE: sent state msg: 57b44664-baa6-11e3-b4a4-f674111a1007
140402 16:35:35 [Note] WSREP: STATE EXCHANGE: got state msg: 57b44664-baa6-11e3-b4a4-f674111a1007 from 0 (mariadbcluster1)
140402 16:35:35 [Note] WSREP: Quorum results:
	version = 3,
	component = PRIMARY,
	conf_id = 0,
	members = 1/1 (joined/total),
	act_id = 0,
	last_appl. = -1,
	protocols = 0/5/2 (gcs/repl/appl),
	group UUID = 2e6511f3-baa2-11e3-9464-5a277b988cb5
140402 16:35:35 [Note] WSREP: Flow-control interval: [16, 16]
140402 16:35:35 [Note] WSREP: Restored state OPEN -> JOINED (0)
140402 16:35:35 [Note] WSREP: New cluster view: global state: 2e6511f3-baa2-11e3-9464-5a277b988cb5:0, view# 1:
Primary, number of nodes: 1, my index: 0, protocol version 2
140402 16:35:35 [Note] WSREP: SST complete, seqno: 0
140402 16:35:35 [Note] WSREP: Member 0 (mariadbcluster1) synced with group.
140402 16:35:35 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
140402 16:35:35 InnoDB: The InnoDB memory heap is disabled
140402 16:35:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140402 16:35:35 InnoDB: Compressed tables use zlib 1.2.3
140402 16:35:35 InnoDB: Using Linux native AIO
140402 16:35:35 InnoDB: Initializing buffer pool, size = 128.0M
140402 16:35:35 InnoDB: Completed initialization of buffer pool
140402 16:35:35 InnoDB: highest supported file format is Barracuda.
140402 16:35:35 InnoDB: Waiting for the background threads to start
140402 16:35:36 Percona XtraDB (http://www.percona.com) 5.5.36-MariaDB-33.0 started; log sequence number 1598771
140402 16:35:36 [Note] Plugin 'FEEDBACK' is disabled.
140402 16:35:36 [Note] Server socket created on IP: '0.0.0.0'.
140402 16:35:36 [Note] Event Scheduler: Loaded 0 events
140402 16:35:36 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140402 16:35:36 [Note] WSREP: REPL Protocols: 5 (3, 1)
140402 16:35:36 [Note] WSREP: Assign initial position for certification: 0, protocol version: 3
140402 16:35:36 [Note] WSREP: Service thread queue flushed.
140402 16:35:36 [Note] WSREP: Synchronized with group, ready for connections
140402 16:35:36 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140402 16:35:36 [Note] mysqld: ready for connections.
Version: '5.5.36-MariaDB-wsrep' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server,
wsrep_25.9.r3961

Node 1 is now the first node of the cluster, and is awaiting the other nodes to connect. Start Node 2 and Node 3 with service mysql start.

[root@mariadbcluster2 ~]# service mysql start
Starting MySQL..... SUCCESS! 
[root@mariadbcluster3 ~]# service mysql start
Starting MySQL..... SUCCESS! 

To verify all nodes are connected to the cluster, you can view the status wsrep_cluster_size. Log into MariaDB from any of the nodes.

bens-mbp:~ ben$ ssh skysql@192.168.56.223
skysql@192.168.56.223's password: 
[skysql@mariadbcluster3 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show status like '%wsrep_cluster_size%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

Here are all of the wsrep related statuses.

MariaDB [(none)]> show status like '%wsrep_%';
+------------------------------+-------------------------------------------------------------+
| Variable_name                | Value                                                      |
+------------------------------+-------------------------------------------------------------+
| wsrep_local_state_uuid       | 31f07ce3-baaa-11e3-b945-f72a90507c82                       |
| wsrep_protocol_version       | 5                                                          |
| wsrep_last_committed         | 0                                                          |
| wsrep_replicated             | 0                                                          |
| wsrep_replicated_bytes       | 0                                                          |
| wsrep_repl_keys              | 0                                                          |
| wsrep_repl_keys_bytes        | 0                                                          |
| wsrep_repl_data_bytes        | 0                                                          |
| wsrep_repl_other_bytes       | 0                                                          |
| wsrep_received               | 10                                                         |
| wsrep_received_bytes         | 2447                                                       |
| wsrep_local_commits          | 0                                                          |
| wsrep_local_cert_failures    | 0                                                          |
| wsrep_local_replays          | 0                                                          |
| wsrep_local_send_queue       | 0                                                          |
| wsrep_local_send_queue_avg   | 0.000000                                                   |
| wsrep_local_recv_queue       | 0                                                          |
| wsrep_local_recv_queue_avg   | 0.000000                                                   |
| wsrep_local_cached_downto    | 18446744073709551615                                       |
| wsrep_flow_control_paused_ns | 0                                                          |
| wsrep_flow_control_paused    | 0.000000                                                   |
| wsrep_flow_control_sent      | 0                                                          |
| wsrep_flow_control_recv      | 0                                                          |
| wsrep_cert_deps_distance     | 0.000000                                                   |
| wsrep_apply_oooe             | 0.000000                                                   |
| wsrep_apply_oool             | 0.000000                                                   |
| wsrep_apply_window           | 0.000000                                                   |
| wsrep_commit_oooe            | 0.000000                                                   |
| wsrep_commit_oool            | 0.000000                                                   |
| wsrep_commit_window          | 0.000000                                                   |
| wsrep_local_state            | 4                                                          |
| wsrep_local_state_comment    | Synced                                                     |
| wsrep_cert_index_size        | 0                                                          |
| wsrep_causal_reads           | 0                                                          |
| wsrep_incoming_addresses     | 192.168.56.222:3306,192.168.56.223:3306,192.168.56.221:3306 |
| wsrep_cluster_conf_id        | 23                                                         |
| wsrep_cluster_size           | 3                                                          |
| wsrep_cluster_state_uuid     | 31f07ce3-baaa-11e3-b945-f72a90507c82                       |
| wsrep_cluster_status         | Primary                                                    |
| wsrep_connected              | ON                                                         |
| wsrep_local_bf_aborts        | 0                                                          |
| wsrep_local_index            | 1                                                          |
| wsrep_provider_name          | Galera                                                     |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                          |
| wsrep_provider_version       | 25.3.2(r170)                                               |
| wsrep_ready                  | ON                                                         |
+------------------------------+-------------------------------------------------------------+
46 rows in set (0.00 sec)

You can now create a database on Node 1:

bens-mbp:~ ben$ ssh skysql@192.168.56.221
skysql@192.168.56.221's password: 
[skysql@mariadbcluster1 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database can_you_see_me_now;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| can_you_see_me_now |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

And see it immediately on Node 2 and Node 3:

bens-mbp:~ ben$ ssh skysql@192.168.56.222
skysql@192.168.56.222's password: 
[skysql@mariadbcluster2 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| can_you_see_me_now |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

bens-mbp:~ ben$ ssh skysql@192.168.56.223
skysql@192.168.56.223's password: 
Last login: Wed Apr  2 18:08:42 2014 from 192.168.56.1
[skysql@mariadbcluster3 ~]$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| can_you_see_me_now |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Now try creating a table and inserting a record on Node 1.

MariaDB [can_you_see_me_now]> SELECT @@hostname;
+-----------------+
| @@hostname      |
+-----------------+
| mariadbcluster1 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> USE can_you_see_me_now;

MariaDB [can_you_see_me_now]> CREATE TABLE yes_i_can ( id INT NOT NULL
AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)

MariaDB [can_you_see_me_now]> show tables;
+------------------------------+
| Tables_in_can_you_see_me_now |
+------------------------------+
| yes_i_can                    |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [can_you_see_me_now]> INSERT INTO yes_i_can (name) VALUES ('Ben');
Query OK, 1 row affected (0.00 sec)

MariaDB [can_you_see_me_now]> SELECT * FROM yes_i_can;
+----+------+
| id | name |
+----+------+
|  3 | Ben  |
+----+------+
1 row in set (0.00 sec)

Then view it on Node 3.

MariaDB [can_you_see_me_now]> SELECT @@hostname;
+-----------------+
| @@hostname      |
+-----------------+
| mariadbcluster3 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> use can_you_see_me_now;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [can_you_see_me_now]> show tables;
+------------------------------+
| Tables_in_can_you_see_me_now |
+------------------------------+
| yes_i_can                    |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [can_you_see_me_now]> SELECT * FROM yes_i_can;
+----+------+
|id | name |
+----+------+
| 3 | Ben  |
+----+------+
1 row in set (0.00 sec)

Congrats. You now have a functioning three-node MariaDB Cluster.

About Ben Stillman

Ben Stillman is a Principal Consultant working with MariaDB and MySQL.

Read all posts by Ben Stillman