Setting Up a MariaDB Enterprise Cluster, Part 2: How to Set Up a MariaDB Cluster
Posted on June 2, 2014 by Ben Stillman
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 MariaDB Corporation. You can contact us about this at sales-nam@mariadb.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 mariadb@192.168.56.223 mariadb@192.168.56.223's password: [mariadb@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 mariadb@192.168.56.221 mariadb@192.168.56.221's password: [mariadb@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 mariadb@192.168.56.222 mariadb@192.168.56.222's password: [mariadb@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 mariadb@192.168.56.223 mariadb@192.168.56.223's password: Last login: Wed Apr 2 18:08:42 2014 from 192.168.56.1 [mariadb@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.