Written by Mike

May 31, 2014

MySQL Fabric on Debian Wheezy

MySQL Fabric manages a farm of MySQL servers and provides a base for sharding and high-availability. MySQL Fabric is written in Python and uses the XML-RPC protocol.

To take advantage of MySQL Fabric you can use a fabric aware connector such as Connector/Python, Connector/J or Connector/PHP.

MySQL Fabric Example

To demonstrate MySQL Fabric, the following guide will take you through the steps in building a simple Fabric environment.

In the example I will be using the following four servers:

MySQL Fabric Node:

10.0.0.1

MySQL Database Servers:

Server 1: 10.0.0.10
Server 2: 10.0.0.20
Server 3: 10.0.0.30

Setup MySQL Database Servers:

To use MySQL Fabric you will need to run MySQL 5.6.10 or later. Wheezy ships with MySQL 5.5 so we will use the Dotdeb repository to install MySQL 5.6

Add repository to Apt sources:

echo "deb http://packages.dotdeb.org wheezy all" | sudo tee -a /etc/apt/sources.list
echo "deb-src http://packages.dotdeb.org wheezy all" | sudo tee -a /etc/apt/sources.list

Install GnuPG Key:

wget http://www.dotdeb.org/dotdeb.gpg
sudo apt-key add dotdeb.gpg

Update:

sudo apt-get update

Install MySQL 5.6:

sudo apt-get install mysql-server-5.6

Set a root password when prompted.

Backup the default MySQL config:

sudo mv /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

Create a new config:

sudo nano /etc/mysql/my.cnf

Server 1:

[mysqld]
datadir=/var/lib/mysql
basedir=/usr
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fabric1
report-port=3306
server-id=10
log-bin=fabric1-bin.log

Server 2:

[mysqld]
datadir=/var/lib/mysql
basedir=/usr
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fabric2
report-port=3306
server-id=20
log-bin=fabric2-bin.log

Server 3:

[mysqld]
datadir=/var/lib/mysql
basedir=/usr
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fabric3
report-port=3306
server-id=30
log-bin=fabric3-bin.log

Restart the MySQL Server service:

sudo /etc/init.d/mysql restart

Add privileges for fabric to access the databases:

mysql -u root -p

Enter the root password you specified earlier and run the following SQL commands:

GRANT ALL ON *.* TO 'fabric'@'%';
SET PASSWORD FOR 'fabric'@'%' = PASSWORD('fabricpassword');
EXIT;

Note: To simplify the example I'm allowing the fabric user to connect from anywhere. In production this should be restricted to the other MySQL servers and Fabric Node

Setup MySQL Fabric Node:

First we need to install MySQL Server:

echo "deb http://packages.dotdeb.org wheezy all" | sudo tee -a /etc/apt/sources.list
echo "deb-src http://packages.dotdeb.org wheezy all" | sudo tee -a /etc/apt/sources.list

wget http://www.dotdeb.org/dotdeb.gpg
sudo apt-key add dotdeb.gpg

sudo apt-get update	
sudo apt-get install mysql-server-5.6

Fabric is included with MySQL Utilities 1.4.2 or later which can be downloaded from here. There is no package available for Wheezy (only Squeeze) so instead select the Linux - Generic platform and download the tar archive.

Download, extract and install the utilities:

wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.4.3.tar.gz
tar -xvf mysql-utilities-1.4.3.tar.gz 
cd mysql-utilities-1.4.3
python ./setup.py build
sudo python ./setup.py install

We now need to create a user account for fabric:

mysql -u root -p

CREATE USER 'fabric'@'localhost' IDENTIFIED BY 'fabricpassword';
GRANT ALL ON fabric.* TO 'fabric'@'localhost';
EXIT;

Edit the Fabric config file:

sudo nano /etc/mysql/fabric.cfg

Add the password you set earlier for `[Storage]` and `[Servers]`:

password = fabricpassword

Create a symlink:

sudo mkdir /usr/local/etc/mysql
sudo ln -s /etc/mysql/fabric.cfg /usr/local/etc/mysql/fabric.cfg

Setup the backing store:

sudo mysqlfabric manage setup --param=storage.user=fabric --param=storage.password=fabricpassword

Set a password for the admin user when prompted.

Let's start MySQL Fabric:

mysqlfabric manage start

You can run the node in the background with the --daemonize switch. The log will be diverted to syslog.

Create a fabric group:

On the fabric node run:

mysqlfabric group create mygroup

Add the MySQL Servers to the above group:

mysqlfabric group add mygroup 10.0.0.10
mysqlfabric group add mygroup 10.0.0.20
mysqlfabric group add mygroup 10.0.0.30

Let's check the status of the servers:

mysqlfabric group lookup_servers mygroup

Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '97058a67-e8a7-11e3-aff5-22000a4b18cd', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.10'}, {'status': 'SECONDARY', 'server_uuid': '98e85318-e8a7-11e3-aff5-22000a4aba44', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.20'}, {'status': 'SECONDARY', 'server_uuid': '994cf0c2-e8a7-11e3-aff5-22000a4aa069', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.30'}]
  activities  =
}

All three servers are listed as `SECONDARY` and `READ_ONLY`; We need to promote one to Primary:

mysqlfabric group promote mygroup --slave_id 97058a67-e8a7-11e3-aff5-22000a4b18cd

The status of the first server should have changed:

mysqlfabric group lookup_servers mygroup

Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '97058a67-e8a7-11e3-aff5-22000a4b18cd', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '10.0.0.10'}, {'status': 'SECONDARY', 'server_uuid': '98e85318-e8a7-11e3-aff5-22000a4aba44', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.20'}, {'status': 'SECONDARY', 'server_uuid': '994cf0c2-e8a7-11e3-aff5-22000a4aa069', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.30'}]
  activities  =
}

Activate the fabric group:

mysqlfabric group activate mygroup

Time to test

To keep it simple we will conduct the test on the Fabric node.

First we will need to install Connector/Python:

wget http://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-1.2.2.tar.gz
tar -xvf mysql-connector-python-1.2.2.tar.gz
cd mysql-connector-python-1.2.2
sudo python setup.py install

To test we will use a modified version of the example here.

mkdir ~/example
cd ~/example
nano fabric_example.py

Modified Example:

import mysql.connector
from mysql.connector import fabric

def add_employee(conn, emp_no, first_name, last_name):
	conn.set_property(group="mygroup", mode=fabric.MODE_READWRITE)
	cur = conn.cursor()
	cur.execute("USE employees")
	cur.execute(
		"INSERT INTO employees VALUES (%s, %s, %s)",
		(emp_no, first_name, last_name)
		)
	# We need to keep track of what we have executed in order to,
	# at least, read our own updates from a slave.
	cur.execute("SELECT @@global.gtid_executed")
	for row in cur:
		print "Transactions executed on the master", row[0]
		return row[0]

def find_employee(conn, emp_no, gtid_executed):
	conn.set_property(group="mygroup", mode=fabric.MODE_READONLY)
	cur = conn.cursor()
	# Guarantee that a slave has applied our own updates before
	# reading anything.
	cur.execute(
		"SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('%s', 0)" %
		(gtid_executed, )
	)
	for row in cur:
		print "Had to synchronize", row, "transactions."
	cur.execute("USE employees")
	cur.execute(
		"SELECT first_name, last_name FROM employees "
		"WHERE emp_no = %s", (emp_no, )
		)
	for row in cur:
		print "Retrieved", row

# Address of the Fabric, not the host we are going to connect to.
conn = mysql.connector.connect(
	fabric={"host" : "localhost", "port" : 32274,
			"username": "admin", "password" : "adminpass"
		   },
	user="fabric", password="fabricpassword", autocommit=True
	)

conn.set_property(group="mygroup", mode=fabric.MODE_READWRITE)
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS employees")
cur.execute("USE employees")
cur.execute("DROP TABLE IF EXISTS employees")
cur.execute(
	"CREATE TABLE employees ("
	"   emp_no INT, "
	"   first_name CHAR(40), "
	"   last_name CHAR(40)"
	")"
	)

gtid_executed = add_employee(conn, 12, "John", "Doe")
find_employee(conn, 12, gtid_executed)

Run the example:

python fabric_example.py

Transactions executed on the master 97058a67-e8a7-11e3-aff5-22000a4b18cd:1-12
Had to synchronize (1,) transactions.
Retrieved (u'John', u'Doe')

Check one of the slave servers:

mysql -u fabric -p -h 10.0.0.30

USE employees;
SELECT * FROM employees;

+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|     12 | John       | Doe       |
+--------+------------+-----------+
1 row in set (0.00 sec)

Let's promote one of the slaves:

mysqlfabric group lookup_servers mygroup

Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '97058a67-e8a7-11e3-aff5-22000a4b18cd', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '10.0.0.1'}, {'status': 'SECONDARY', 'server_uuid': '98e85318-e8a7-11e3-aff5-22000a4aba44', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.20'}, {'status': 'SECONDARY', 'server_uuid': '994cf0c2-e8a7-11e3-aff5-22000a4aa069', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.30'}]
  activities  =
}

Promote the second server, 10.0.0.20:

mysqlfabric group promote mygroup --slave_id=98e85318-e8a7-11e3-aff5-22000a4aba44

mysqlfabric group lookup_servers mygroup

Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '97058a67-e8a7-11e3-aff5-22000a4b18cd', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.1'}, {'status': 'PRIMARY', 'server_uuid': '98e85318-e8a7-11e3-aff5-22000a4aba44', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '10.0.0.20'}, {'status': 'SECONDARY', 'server_uuid': '994cf0c2-e8a7-11e3-aff5-22000a4aa069', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '10.0.0.30'}]
  activities  =
}

Conclusion:

MySQL Fabric provides an exciting platform that delivers high-availability and scalability with sharding. It is early days for this framework but it is definitely a technology to watch with close interest.

Mike Mead