Multi Master Database Replication
Multi Master database replication in a cluster of databases allows applications to write to any database node and data is available at other nodes within short order. The main advantage is high availability deployment, high read performance and scalability.
Overall Design
We are aiming have an application layer accessing a database cluster via a Load balancer as show in picture below:
Fig. 1: Load Balancer for a Database Cluster
Trove
For providing databases services on OpenStack we considered Trove. However, its broken on Kilo. There is no easy way to get a ‘Trove Image’ and launch it. There is a nice and automated script located here at the RDO page that actually creates an image. However, after the image is registered, it errors out upon DB instance launch. Given that Open Stack Trove documentation was not helpful so there was no motivation for us to debug that further as it would be much more riskier for us to maintain any hacked code. Wish it worked. Moving on to other options… Enter Galera Cluster and MySQL Cluster products.
Using other options
In the world of MySQL based multi master replication cluster databases, there are few popular ones:
- MariaDB Galera Cluster
- Percona XtraDB Cluster
- MySQL Cluster
Out of the three, we chose Percona XtraDB Cluster (PXC). Mainly because of slightly better support for tables without primary keys [1] [2] – Note Galera is used both in MariaDB and PXC. However, some users have still reported issues on not having PK on MariaDB. Generally, you must have PK for every table. We could have used MariaDB Galera Cluster, however, either the documentation is not maintained or has a pretty strict rule about primary keys required. Unfortunately, that is a significant restriction. MySQL Cluster on the other hand has a huge learning curve for setup and administration. This might be something to consider when scaling up to millions of queries per second. MySQL Cluster bears no resemblance to MariaDB or Percona’s cluster counterparts so its a completely different mindset.
Instance Preparation
We use CentOS 7.1 instances that create a new volume for OS disk. The database volume itself is on a separate volume: vdb.
Swap File Preparation
Normally, the instances don’t have swap file enabled (check by swapon --summary). So prepare a swap file like so:
fallocate -l 1G /swapfile; dd if=/dev/zero of=/swapfile bs=1M count=1024; chmod 600 /swapfile; mkswap /swapfile; swapon /swapfile swapon --summary
MySQL data directory preparation
Next, prepare the secondary hard that will hold the data directory of mysql
fdisk /dev/vdb new partition, extended. new partition, logical. w (to write the partition table)
Now make a file system. Ensure you have a valid partion created (vdb5 – in this case).
mkfs.ext4 /dev/vdb5
Automount swap and data directory
Create mysql directory as we have not yet installed mysql and setup /etc/fstab
mkdir /var/lib/mysql echo "/swapfile none swap defaults 0 0" >> /etc/fstab echo "/dev/vdb5 /var/lib/mysql ext4 defaults 0 2" >> /etc/fstab
Mount the fstab file and make sub directory for data (I like to use non default directories so I know whats going on)
mount -av mkdir /var/lib/mysql/mysql_data touch /var/lib/mysql/mysql_data/test_file
Finally restore security context on the mysql directory
restorecon -R /var/lib/mysql
Database Node List
In our case we have 3 database servers all with CentOS 7.1.
DBNode1 - 10.0.32.23 DBNode2 - 10.0.32.24 DBNode3 - 10.0.32.25
Security Groups, Iptables & Selinux
We need to open these ports for each of the database nodes:
TCP 873 (rsync) TCP 3306 (Mysql) TCP 4444 (State Transfer) TCP 4567 (Group Communication - GComm_port) TCP 4568 (Incremental State Transfer port = GComm_port+1)
Selinux was set to Permissive (setenforce 0) — temporarily while installation was done. Ensure the above ports allowed by a security group applied to the database instances.
For every node, we need to install the PXC database software. Install, but don’t start the mysql service yet.
Installing the Database Percona XtraDB Cluster Software
Before you install, there is a pre-requisite to install socat. This package should installed from the base repository. If you have epel, remove it (assuming this node is going to be used only for database).
sudo yum remove epel-release sudo yum install -y socat;
Installing the Database Percona XtraDB Cluster Software
Install the Percona repo and software itself.
sudo yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm; sudo yum install Percona-XtraDB-Cluster-56
First Node (Primary) in Cluster setup
In order to start a new cluster, the very first node should be started in specific way – aka bootstrapping. This will cause the node to assume its the primary of the DB cluster that we are going make come to life.
First edit the /etc/my.cnf so setup your requirements.
# Edit to your requirements. [mysqld] datadir=/var/lib/mysql/mysql_data user=mysql log_bin = mysql-bin binlog_format = ROW innodb_buffer_pool_size = 200M innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 20M innodb_file_per_table = 1 wsrep_cluster_address = gcomm://10.0.32.23,10.0.32.24,10.0.32.25 wsrep_provider = /usr/lib64/galera3/libgalera_smm.so wsrep_slave_threads = 2 wsrep_cluster_name = SilverSkySoftDBClusterA wsrep_node_name = DBNode1 wsrep_node_address = 10.0.32.23 wsrep_sst_method = rsync innodb_locks_unsafe_for_binlog = 1 innodb_autoinc_lock_mode = 2 [mysqld_safe] pid-file = /run/mysqld/mysql.pid syslog
Start the bootstrap service
systemctl start mysql@bootstrap.service
This special service uses the my.cnf with wsrep_cluster_address = gcomm:// (no IPs) and start the MySQL server as the first node. This creates a new cluster. Be sure to run this service only at create cluster time and not at node join time.
While this first node is running, login to each of the other nodes DBNode2 & DBNode3 and use the my.cnf from above as a template. For each node update the wsrep_node_name and wsrep_node_address. Note that The wsrep_cluster_address should contain all IP addresses of that node.
Start the mysql service on each of the nodes 2 & 3 while node 1 is still running:
systemctl start mysql
Verify Cluster is up and nodes are joined
It should show Value: 3 (indicating 3 nodes are joined)
mysql> select @@hostname\G show global status like 'wsrep_cluster_size' \G *************************** 1. row *************************** @@hostname: dbserver1.novalocal 1 row in set (0.00 sec) *************************** 1. row *************************** Variable_name: wsrep_cluster_size Value: 3 1 row in set (0.00 sec)
Start Node 1 back in normal mode
On the Node 1, restart in normal mode:
systemctl stop mysql@bootstrap.service; systemctl start mysql
Verify database and replication actually happens
In one of the node, say DBNode3, create a sample database and table.
mysql -u root -p CREATE DATABASE my_test_db; USE my_test_db; CREATE TABLE my_test_table (test_year INT, test_name VARCHAR(255)); INSERT INTO my_test_table (test_year, test_name) values (1998, 'Hello year 1998');
On an another node, say DBNode2, check the table and rows are visible:
mysql -u root -p SELECT @@hostname\G SELECT * from my_test_db.my_test_table; *************************** 1. row *************************** @@hostname: dbserver2.novalocal 1 row in set (0.00 sec) +-----------+-----------------+ | test_year | test_name | +-----------+-----------------+ | 1998 | Hello year 1998 | +-----------+-----------------+ 1 row in set (0.00 sec)
This confirms our cluster is up and running.
Don’t forget to enable the mysql service to start automatically – systemctl enable mysql
Also set the root password for MySQL.
Managing Users in Clustered Database
In the cluster setup, the mysql.* is not replicated so manually creating an user in mysql.* table will be limited to local. So you can use CREATE USER statements to create users that are replicated across the cluster. A sample is:
CREATE USER 'admin'@'%' IDENTIFIED BY 'plainpassword'; GRANT ALL ON *.* TO 'admin'@'%';
You can log into any other node to the new user is created.
In addition, you can use MySQL workbench to databases in the cluster.
OpenStack Load Balancer
OpenStack Load balancer as a service (LBaaS) is easily enabled in RDO packstack and other installs. To create a Load balancer for the database cluser we created above, click on the Load balancer menu under Network and click add pool as show in figure below:
Then fill in the pool details as show in below picture:
Note that we are using TCP protocol in the case as we need to allow MySQL connections. For simplicity of testing use ROUND_ROBIN balancing method.
Next, add the VIP for the load balancer from the Actions column. In the VIP setup choose protocol TCP and port as 3306
Next, add the members of the pool by selecting ‘Members’ tab and then selecting the Database Nodes. For now you can keep weight as 1.
Get the VIP address by clicking the VIP link at the Load balancer pool. Once you get the IP, you can optionally choose to associate a floating IP. This can be done by going compute -> Access & Security. Allocate an IP to your project. Then click on Associate. In the drop down, you should the the vip’s name and IP you provided.
This completes the Load balancer setup.
Testing the Load Balancer
A simple test is to query the load balancer’s VIP with mySQL client. In our case the VIP is 172.16.99.35 and result is seen below.
[centos@client1 etc]$ mysql -u root -p -h 172.16.99.35 -e "SHOW VARIABLES LIKE 'wsrep_node_name';" Enter password: +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | wsrep_node_name | DBNode1 | +-----------------+---------+ [centos@client1 etc]$ mysql -u root -p -h 172.16.99.35 -e "SHOW VARIABLES LIKE 'wsrep_node_name';" Enter password: +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | wsrep_node_name | DBNode2 | +-----------------+---------+
You can see that each query is being routed to different nodes.
Simplistic PHP Test App
On an another VM, install apache and PHP. Start Apache and insert a PHP file as below. The database is the one we create above.
<?php
$user = "root";
$pass = "your_password";
$db_handle = new PDO("mysql:host=dbcluster1.testdomain.com;dbname=my_test_db", $user, $pass);
print "<pre>";
foreach ($db_handle->query("SELECT test_name FROM my_test_table") as $row)
{
print "Name from db " . $row['test_name'] . "<br />";
}
print "\n";
foreach ($db_handle->query("SHOW VARIABLES LIKE 'wsrep_%'") as $row) {
print $row['Variable_name'] . " = " . $row['Value'];
print "\n";
}
print_r ($row);
print "</pre>";
$db_handle = null;
?>
From the browser navigate to the URL where this file is.
This would show the data from the table and various wsrep variables. Each time you refresh the page you should see wsrep_node_address, wsrep_node_name changing so you know load balancer is working.
Monitoring
In general, the cluster needs to be monitored for crashed databases etc. The OpenStack load balancer can monitor the members in the pool and set it to inactive state.
Crashed Node Recovery
Recovery of crashed nodes with little impact to overall cluster is one of main reasons why we go with a cluster. A very nice article about various ways to recover a crashed node is on Percona’s site.
Conclusion
We described how to create a database cluster and configure a load balancer on top. Its not a very complex process. The entire environment was in OpenStack Kilo.