Tag Archives: Kilo

Multi master Database Cluster on OpenStack with Load Balancing

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:

Load Balancer for a Database Cluster

Fig. 1: Load Balancer for a Database Cluster


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 -
DBNode2 -
DBNode3 -

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.
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://,,
wsrep_provider                 = /usr/lib64/galera3/libgalera_smm.so
wsrep_slave_threads            = 2
wsrep_cluster_name             = SilverSkySoftDBClusterA
wsrep_node_name                = DBNode1
wsrep_node_address             =
wsrep_sst_method               = rsync
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode       = 2
pid-file = /run/mysqld/mysql.pid

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
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:

Image of how to add add a New Load Balancing Pool in OpenStack
Adding a New Load Balancing Pool in OpenStack

Then fill in the pool details as show in below picture:

image of Setting the details of the Load Balancing Pool
Setting the details of the Load Balancing Pool

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 and result is seen below.

[centos@client1 etc]$ mysql -u root -p -h -e "SHOW VARIABLES LIKE 'wsrep_node_name';"
Enter password: 
| Variable_name | Value     |
| wsrep_node_name | DBNode1 |
[centos@client1 etc]$ mysql -u root -p -h -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.

 $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.


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.


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.

Enable SPICE HTML5 Console Access in OpenStack Kilo

Spice Console Access to Instances

Documentation is a bit sparse on what configuration parameters to enable for SPICE console access. This article provides our notes for enabling SPICE on CentOS 7.1 with OpenStack Kilo.

Essentially, the Control node acts a proxy to the Compute node which has the SPICE server. Control node is client of the compute node.

Required Packages

On both Control & Compute:

yum install spice-html5

On Control:

yum install openstack-nova-spicehtml5proxy

Config Files

The file to modify is


in compute and control nodes.

In both config files, ensure  vnc_enabled=False is explicitly set. If novnc is enabled, ensure that is disabled too.

Control IP =
Compute IP =   [Internal IP - ports may need to be opened if not already there]

On Control Node

. . .


# Enable spice related features (boolean value)
# Enable spice guest agent support (boolean value)
# Keymap for spice (string value)

Iptables rule on control node

Since we are allowing access to console via port 6082 on the control node, open this port in iptables.

iptables -I INPUT -p tcp -m multiport --dports 6082 -m comment --comment "Allow SPICE connections for console access " -j ACCEPT

You can make permanent by adding the above rule to /etc/sysconfig/iptables (before the reject rules) saving and restarting iptables.

Config Changes on Compute Node

. . .


# Enable spice related features (boolean value)
# Enable spice guest agent support (boolean value)
# Keymap for spice (string value)

Restart services

On Compute

# service openstack-nova-compute restart

On Control

# service httpd restart
# service openstack-nova-spicehtml5proxy start
# service openstack-nova-spicehtml5proxy status 
# systemctl enable openstack-nova-spicehtml5proxy


Here the control node is an HTML proxy that connects to the SPICE server+port that is running when a VM is instantiated.
Here are some notes on some of the unclear options:


This line indicates the HTML5 proxy should run on localhost without IP binding ( – control node in this case.


This indicates the base URL to use when you click ‘console’ on the Horizon dashboard. Its noted that this URL must be accessible in the same network as the Horizon dashboard. In our case, this URL is the control node.


Server listen specifies where the VM instances should listen for SPICE connections. This is the local IP address (compute node)


Server_proxyclient_address is the address which clients such as HTML5 proxy will use to connect to the VMs running on the Compute Node. This is an internal address most likely not accessible to the outside world but accessible to the control node. This address is the internal IP address of the compute node.


Be sure about what config change goes in which node. Iptables is another to look out for, if you plan to use consoles regularly, make the iptables rules permanent.

“console is currently unavailable. Please try again later.”
Under the hood,
You’ll see
“ERROR: Invalid console type spice-html5 (HTTP 400)”
when you do
nova get-spice-console spice-html5

This generally means, the VM did not start with SPICE enabled. The causes for that could be one of the services did not restart after config change.
Double check the config file – make sure ‘enabled=true’ is set.



Enabling Openstack Swift Object Storage Service

On OpenStack Kilo, when we use RDO to enable Swift Object Storage service its partially misconfigured (or lack of control in packstack file).

The Swift Proxy is setup on the storage node. I could not find if I can control which node the Swift Proxy can be installed on by packstack. The issue is the swift proxy service endpoint (points to control node) mismatches where the swift proxy really is (on storage node).

Check Swift Endpoint details

Ensure swift service is indeed created.

openstack service list
| ID                               | Name       | Type          |
. . .
| a43e0d3e0d3e0d3e0d3e0d3e0d3e0d3e | swift      | object-store  |
| a5a23a23a23a23a23a23a23a23a23a23 | swift_s3   | s3            |

If swift does not show then you may not have installed it during packstack install. Edit your packstack file to install only swift.

openstack endpoint show swift
| Field        | Value                                          |
| adminurl     | http://controller_ip:8080/                     |
| enabled      | True                                           |
| id           | c243243243243243243243243243243                |
| internalurl  | http://controller_ip:8080/v1/AUTH_%(tenant_id)s|
| publicurl    | http://controller_ip:8080/v1/AUTH_%(tenant_id)s|
| region       | RegionOne                                      |
| service_id   | a43e0d3e0d3e0d3e0d3e0d3e0d3e0d3e               |
| service_name | swift                                          |
| service_type | object-store                                   |

The above is wrong. This is because no one is listening on port 8080. Check this by : netstat -plnt | grep 8080
Luckily, everything seems to be setup on the storage node – port 8080 is up, iptables rule for 8080 is set and swift files are all almost good to go.

Correcting the Endpoint

Delete the current swift endpoint ID. On the control node,

openstack endpoint delete c243243243243243243243243243243

And recreate a new one pointing to the right server (remember, the proxy was setup on storage server by packstack)

openstack endpoint create \
 --publicurl 'http://storage_ip:8080/v1/AUTH_%(tenant_id)s' \
 --internalurl 'http://storage_ip:8080/v1/AUTH_%(tenant_id)s' \
 --adminurl http://storage_ip:8080 \
 --region RegionOne \

Adjust the  swift_s3 service endpoint as well if you plan to use S3 API.

openstack endpoint delete swift_s3_id
openstack endpoint create \
 --publicurl 'http://storage_ip:8080/v1/AUTH_%(tenant_id)s' \
 --internalurl 'http://storage_ip:8080/v1/AUTH_%(tenant_id)s' \
 --adminurl http://storage_ip:8080 \
 --region RegionOne \

Adjusting the proxy-server.conf

The /etc/swift/proxy-server.conf file on the storage_ip must be edited as below. Especially, the identity_uri and auth_uri must point to Keystone IP. One other minor thing to check is if /var/cache/swift that is used for signing directory has correct selinux context. You may try sudo restorecon -R /var/cache/

. . . 
pipeline = catch_errors healthcheck cache authtoken keystoneauth container_sync bulk ratelimit staticweb tempurl slo formpost account_quotas container_quotas proxy-server
. . . 
log_name = swift
signing_dir = /var/cache/swift
paste.filter_factory = keystonemiddleware.auth_token:filter_factory
identity_uri = http://controller_ip:35357/
auth_uri = http://controller_ip:5000

admin_tenant_name = services
admin_user = swift
admin_password = secret_pass
delay_auth_decision = true
cache = swift.cache
include_service_catalog = False
. . .

Restart the proxy server

sudo service openstack-swift-proxy status

On controller, check if swift stat works.

 swift stat
 Account: AUTH_idididid
 Containers: 1
 Objects: 0
 Bytes: 0
 X-Put-Timestamp: 1400000000.00015
 Connection: keep-alive
 X-Timestamp: 1400000000.00015
 X-Trans-Id: tx12314141-12312312
 Content-Type: text/plain; charset=utf-8

Enabling S3 API for Swift Object Storage

This post shows the details of enabling S3 API for Swift Object Storage on Openstack Kilo on CentOS 7.
The main documentation is here: http://docs.openstack.org/kilo/config-reference/content/configuring-openstack-object-storage-with-s3_api.html
As of July 2015, the page seems dated as some links are broken and steps are config options are unclear.

Install Swift3 Middleware

The Swift3 middleware seems to have shifted to https://github.com/stackforge/swift3

So the correct git clone command is

git clone https://github.com/stackforge/swift3.git

python setup.py install

At the end of the above command’s execution, you should see:

Copying swift3.egg-info to /usr/lib/python2.7/site-packages/swift3-1.8.0.dev8-py2.7.egg-info
running install_scripts

Adjust proxy-server.conf

For Keystone, add “swift3 ” and “s3token” to pipeline.

For others, add swauth instead of s3token (untested).


pipeline = catch_errors healthcheck cache swift3 s3token authtoken keystoneauth ...

use = egg:swift3#swift3
paste.filter_factory = keystonemiddleware.s3_token:filter_factory
auth_port = 35357 
auth_host = keystone_ip_address 
auth_protocol = http

The important part is the filter_factory its —  keystonemiddleware and not keystone.middleware. Then restart the swift proxy service.

sudo service openstack-swift-proxy restart

Testing the Swift S3 API using S3Curl

S3Curl is a tool provided by Amazon. It can be downloaded from https://aws.amazon.com/code/128. Also note the comment in that page where you need to yum install perl-Digest-HMAC package.
You can use Horizon to create a test container and upload a small text file into it.
In our example, we have created a container called “test_container” and simple text file called “test_obj” inside the container.

Make sure you edit the s3curl.pl file to use Openstack’s Swift Proxy end point:

my @endpoints = ( '');

Retrieve the access keys from Horizon dashboard

Go to Project -> Compute -> Access & Security. Click on the API Access tab.
Note the S3 Service endpoint. In our case:

On the top right click on view credentials:
on Horizon
“EC2 Access Key” –> Is your id for S3 tools such as S3Curl.
“EC2 Secret Key” –> Is your key for S3 tools such as S3Curl.

For instance, lets say:
EC2 Access Key = HorizonEC2AccessKeyA0919319
EC2 Secret Key = HorizonEC2SecretKeyS1121551

Get the list of containers

The S3Curl command is:
./s3curl.pl --id HorizonEC2AccessKeyA0919319 --key HorizonEC2SecretKeyS1121551

Note: The above ID is the actual key not the personal .s3curl file reference. The tool will give a few warnings, but that ok we are just testing.

Expected output is:

 <?xml version='1.0' encoding='UTF-8'?>
<ListAllMyBucketsResult xmlns="http://s3.amazonaws.com/doc/2006-03-01/"><Owner><ID>admin:admin</ID><DisplayName>admin:admin</DisplayName></Owner><Buckets><Bucket><Name>test_container</Name><CreationDate>2009-02-03T16:45:09.000Z</CreationDate></Bucket></Buckets></ListAllMyBucketsResult>

The above indicates the root of our storage contains a bucket by name test_container. Lets extract the files from that container (bucket).

Get the list of objects in the container

To get the list of object inside the container, execute:

./s3curl.pl --id HorizonEC2AccessKeyA0919319 --key HorizonEC2SecretKeyS1121551

The output will have something like:

. . . <Contents><Key>test_obj</Key><LastModified>. . .

In above, key is the file. If you simply want to stream the contents of test_obj:

./s3curl.pl --id HorizonEC2AccessKeyA0919319 --key HorizonEC2SecretKeyS1121551

You should see test_obj’s contents printed out.

This concludes that our setup is working fine.