High availability using galera cluster on centos 6.5

 


MariaDB Galera Cluster 10.0.12 Stable has been released and available for production use. MariaDB is a relational database management system (RDBMS). Generally we use single node of database server for small application but think about application which have thousands of users keep online at a time, In that situation we need a structure which will capable to handle this load and provides high availability. So we need to add multiple database servers interconnected with each other and keep synchronized, so in case any server goes down other servers can take place of them and provide services to users.

This article will help you to set up MariaDB Galera Cluster 10.0.12 with 3 nodes running with CentOS 6.5. Cluster server details are as following.

Cluster DB1: 10.0.0.10
Cluster DB2: 10.0.0.20
Cluster DB3: 10.0.0.30

Note: Step 1/2/3 has to be done on all cluster nodes and remaining steps are node specific.

Step 1: Add MariaDB Repositories

Create a mariadb repository /etc/yum.repos.d/mariadb.repo using following content in your system. Below repository will work on CentOS 6.x systems, For other system use repository generation tool and add to your system.

For CentOS 6 – 64bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

For CentOS 6 – 32bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Step 2: Install MariaDB and Galera

Before installing MariaDB Galera cluster packages, remove any existing MySQL or MariaDB packages installed on system. After that use following command to install on all nodes.

# yum install MariaDB-Galera-server MariaDB-client galera

Step 3: Initial MariaDB Configuration

After  installing packages in above steps do the some basic MariaDB configurations.

# service mysql start

# mysql_secure_installation

After that create a user in MariaDB on all nodes, which can access database from your network in cluster.

# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'cluster'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

and stop MariaDB service before starting cluster configuration

# service mysql stop

Step 4: Setup MariaDB Galera Cluster on DB1

Lets start setup MariaDB Galera cluster from DB1 server. Edit MariaDB server configuration file and add following values under [mariadb] section.

[root@db1 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.0.10,10.0.0.20,10.0.0.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.10'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

Start cluster using following command.

[root@db1 ~]# /etc/init.d/mysql bootstrap

Bootstrapping the clusterStarting MySQL.... SUCCESS!

If you get any problem during startup check MariaDB error log file /var/lib/mysql/.err

Step 5: Add DB2 in MariaDB Cluster

After successfully starting cluster on DB1. Start configuration on DB2. Edit MariaDB server configuration file and add following values under [mariadb] section. All the settings are similar to DB1 except wsrep_node_address, wsrep_cluster_address and wsrep_node_name.

[root@db2 ~]# vim /etc/my.cnf.d/server.cnf

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.0.10,10.0.0.20,10.0.0.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.20'
wsrep_node_name='db2'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password

Start cluster using following command.

[root@db2 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Step 6: Add DB3 in MariaDB Cluster

[root@db3 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.0.10,10.0.0.20,10.0.0.30"
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.30'
wsrep_node_name='db2'
wsrep_sst_method=rsync
wsrep_sst_auth=cluster:password
Start cluster using following command.

[root@db3 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Step 7: Test MariaDB Galera Cluster Setup

Now the cluster setup has been completed and running properly. Now you can test the cluster setup by creating database and tables at any server in cluster, it will replicate immediately to all servers in cluster.

We can also add a load balancer either haproxy or maxscale for scaling purpose.https://mysqlwonders.blogspot.com/2016/06/high-availability-using-galera-cluster.htmlhttps://mysqlwonders.blogspot.com/2016/06/high-availability-using-galera-cluster.html

Read More

Maxscale load blanacer with centos 6.5

Installing MaxScale

When logged in as root, create an installation directory for MaxScale, download it and install it:

# cd /usr/local
# mkdir skysql
# cd skysql
# curl https://downloads.skysql.com/files/SkySQL/MaxScale/maxscale.preview.0.4.tar.gz > maxscale.preview.0.4.tar.gz
# tar xvfz maxscale.preview.0.4.tar.gz
# cd maxscale

Now we have MaxScale downloaded, but for maxscale to work, the MAXSCALE_HOME environment variable has to be set up, and to achieve this we, let's create a shell-script that starts MaxScale for us.

 # vi maxstart.sh
Then set up this script like this:
#!/bin/bash
#
export MAXSCALE_HOME=/usr/local/skysql/maxscale/MaxScale
$MAXSCALE_HOME/../bin/maxscale
Once we have that script, let's make it executable:
 # chmod +x maxstart.sh

Configuring MaxScale

The next step then is to configure MaxScale. The default configuration file for MaxScale is called MaxScale.cnf and is located in the etc directory under where MAXSCALE_HOME is located.

 # vi /usr/local/skysql/maxscale/MaxScale/etc/MaxScale.cnf

In this file, each module has it's own section, as well as each server and there is also a section for MaxScale itself. Let's begin with MaxScale which has just one variables that controls the # of threads 

MaxScale uses:
[maxscale]
threads=1

Following this, we set up the servers we are to manage here, of which there are three. For every configuration section, except the one for MaxScale core, we have to tell what type of entity we are defining here, in this case it is server:

[srv1]
type=server
address=10.0.0.1
port=3306
protocol=MySQLBackend

[srv2]
type=server
address=10.0.0.2
port=3306
protocol=MySQLBackend

[srv3]
type=server
address=10.0.0.3
port=3306
protocol=MySQLBackend

 In addition we define which protocol module to use, and in this case there is not much else than MySQLBackend to choose from. As you can see, we do not define the master or slave properties of the servers, instead we let MaxScale figure that out for us by using a Monitor module, so now is the time to define that.

For the monitor to work, it will connect to the respective MySQL servers so we need to define a username and password for this. In the previous post I created a user for this called maxuser using the password maxpwd. Also, to simplify matters I created this user with full privileges, 

[MySQL Monitor]

type=monitor
module=mysqlmon
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd

The router we are about to use is the read-write split router, which manages routing writes to the master and reads to the slaves. Let's start with the router that is the central point here:

[RWSplitRouter]
type=service
router=readwritesplit
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd

The reason we need to have a username and password even for the router is that this needs to authenticate users connecting to MaxScale itself, and to do that it needs to connect to the server it manages, connect to the mysql database and get the user authorization data from there, which is why we need an account that can access the mysql database.

Now we are close to getting started with MaxScale, what is left is to set up a listener for the router we defined above:

[RWSplitListener]
type=listener
service=RWSplitRouter
protocol=MySQLClient
port=3306

That concludes the configuration of MaxScale!
Testing MaxScale

 # ./maxstart.sh

Now we should be able to connect to MaxScale and test that it works. We do this by setting up a MariaDB Client server. 

This is set up just like our previous servers, only that we only install the MariaDB client. So set up a server like before, set IPADDR to 10.0.0.4 and run, as root:

 # yum install MariaDB-client

I here assume that you have created the t1 table that we used to test replication in the previous post, if not, create it and populate it now:

# mysql -h 10.0.0.5 -u maxuser -pmaxpwd test -e "create table t1(c1 int)"

# mysql -h 10.0.0.5 -u maxuser -pmaxpwd test -e "insert into t1 values(1)"

Now we have a table to test with, so let's see what happens, and let's check that our SELECTs are being routed to our two slaves. We can do that by using the @@server_id variable, which is different
one these two of course:

# mysql -h 10.0.0.5 -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
+------+-------------+
| c1  | @@server_id |
+------+-------------+
|  1 |  10 |
+------+-------------+
Cool, this ended up with one of the slaves, so if I try it again, it shoudl end up with the other slave, right:

# mysql -h 10.0.0.5 -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
+------+-------------+
| c1  | @@server_id |
+------+-------------+
|  1 |  11 |
+------+-------------+

And if you didn't get the MaxScale to work, like seeing this in the error log:

 /usr/local/skysql/maxscale/MaxScale/log/skygw_err1.log:

Couldn't find suitable Slave from 3 candidates.

Or if you can't connect to MaxScale, then the most likely issue is that you didn't stop iptables:

 # /etc/init.d/iptables stop

All we done here for setting up some basic load balancing !

Read More