3. MariaDB multi-master clustering with Galera
So I don’t repeat myself from my previous article (found here), I will cover the Galera configuration process quite quickly.
Note: please be aware you will need to have the Galera yum repository available on all of your database servers.
Do the following on all database servers.
Configure Yum repository for Galera
Create a new file called /etc/yum.repos.d/galera.repo with the below contents.
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/5.5/rhel6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Install required packages
Install the necessary packages to get MariaDB and Galera ready to be used. To do this, use yum to install the below packages.
yum install -y MariaDB-Galera-server MariaDB-client
We need to enable our local installation to allow access from other servers in order to have Galera set up properly. To do this, do the following.
service mysql start mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;" mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;" service mysql stop
Now edit your local /etc/my.cnf file on each database server so it appears as follows
# # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mariadb] wsrep_cluster_address=gcomm://galera01.example.com wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_sst_auth=root:password wsrep_sst_method=rsync log-error=/var/log/mysql.log
Note: You will notice that the above config points all systems to “galera01.example.com” as the server to join the cluster. You cannot have “galera01.example.com” pointing to itself, so on this server, you need to point this to another server.
If you leave iptables filtering your systems, don’t forget to open the necessary ports for Galera to function.
To do this, run the following.
for x in 3306 4444 4567 4568 ; do iptables -I INPUT -p tcp --dport $x -j ACCEPT ; done
Lastly, we want our Galera database cluster to work behind the VIP (10.0.1.20) of our Load Balancer. Because we are using a Direct Routing method, we need to tell iptables to remap our database connections to that IP. You can do this with the following.
iptables -t nat -A PREROUTING -p tcp -d 10.0.1.20 --dport 3306 -j REDIRECT service iptables save
Here you can do you’re regular thing to ensure everything is running and set to load on reboot. To do this, run the following.
chkconfig mysql on
Starting the cluster
As we have all the systems pointing to “galera01.example.com” to join the cluster, it is important to ensure that the cluster is already available before starting all other servers.
To start the cluster from “galera01.example.com” when the entire cluster is in an offline state, run the following.
service mysql start --wsrep-cluster-address="gcomm://"
On all other servers, you can perform a normal service start once the primary server is online.
service mysql start
Provided you have not received any error messages, your cluster should be completely online. You can verify this by creating a temp database on one system and it will appear on all other nodes if everything is working as expected.
If you jump across to your master load balancer, you can also see that our 3 MariaDB servers have now popped up in the list of load balancing servers.
[root@lb01 ~]# ipvsadm -l IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP galera.example.com:mysql rr -> galera01.example.com:mysql Route 100 0 0 -> galera02.example.com:mysql Route 100 0 0 -> galera03.example.com:mysql Route 100 0 0 TCP owncloud.example.com:http rr persistent 30 TCP owncloud.example.com:https rr persistent 30 [root@lb01 ~]#