|
|
The purpose of this blog-post and next one (Part II) is to see how MySQL Fabric can be
used as a HA solution. Reason for this is many users today have
the need for HA and automatic failover, but not many users need
the sharding functionality, I wanted to focus on creating one HA
group and see how failover is handled using MySQL Fabric.
Talking to customers and users of MySQL they all want to have HA,
the normal demands are:
- Do not want to alter application code
- Easy to install and manage
There are a lot of solutions for achieving HA around MySQL in the
MySQL ecosystem but I think one key benefit of MySQL Fabric is
that it is built into the connectors. This will make the
technology easy to adopt for developers and easy to manage for
DBA's since there are a minimal of extra parts to take care
of.
Getting started with MySQL Fabric, I consumed the following blogs
and docs:
- http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html
- http://vnwrites.blogspot.se/
- http://mysqlmusings.blogspot.se/
If you want to see all resources around MySQL Fabric I highly
recommend this post in the MySQL forum for Fabric.
Install MySQL Fabric My setup will have 4 MySQL instances, one
for MySQL Fabric backing store and 3 servers for my HA
group.
The setup will be as shown in picture on
right side with our 4 MySQL instances running on port 63307 -
63309. Instance with port number 63306 will be used by MySQL
Fabric to store internal meta data used by Fabric. MySQL instaces
63307 - 63309 will be part of a HA group called mygroup.
High-availability groups are very important part of MySQL fabric,
these are collections of servers that shall work together to
deliver a database service to the application that connects to
MySQL Fabric.
In next post we will look at a python application and see how
this works MySQL Fabric.
That was a bit of background but now we need to get started,
first we need is 4 MySQL instances, I installed everything
underneath /opt/fabric, see configuration options for my MySQL
instances bellow.
mkdir /opt/fabric/mysql1;
mkdir /opt/fabric/mysql2;
mkdir /opt/fabric/mysql3;
mkdir /opt/fabric/mysql4;
mysql_install_db --datadir=/opt/fabric/mysql1
mysql_install_db --datadir=/opt/fabric/mysql2
mysql_install_db --datadir=/opt/fabric/mysql3
mysql_install_db --datadir=/opt/fabric/mysql4
mysqld --socket=/opt/fabric/mysql1/my.sock --port=63306
--datadir=/opt/fabric/mysql1 --server-id=1 \
--log-error=/opt/fabric/mysql1/mysql.err
--innodb_buffer_pool_size=64MB --gtid_mode=ON \
--log-bin
--log-slave-updates --enforce-gtid-consistency \
--basedir=/path/to/mysql-src
--innodb-flush-log-at-trx-commit=2 --log-bin \
--performance-schema-instrument='%=ON'
> /opt/fabric/mysql1/mysql.out & 2>&1
mysqld --socket=/opt/fabric/mysql2/my.sock --port=63307
--datadir=/opt/fabric/mysql2 --server-id=2 \
--log-error=/opt/fabric/mysql2/mysql.err
--innodb_buffer_pool_size=64MB --gtid_mode=ON \
--log-bin
--log-slave-updates --enforce-gtid-consistency \
--basedir=/path/to/mysql-src
--innodb-flush-log-at-trx-commit=2 --log-bin \
--performance-schema-instrument='%=ON'
> /opt/fabric/mysql2/mysql.out & 2>&1
And for instance 3 and 4 I used; port 63308 and 63309, server-id
3 and 4.
Next step is to fetch MySQL Fabric code from here, MySQL Fabric is part of the latest release
of MySQL Utilities called 1.4.1 alpha.
I opted for tar-file so used installation manual here, next step is to download and install
Fabric aware connectors for Python, I again followed the manual
here.
Now we are finally ready to start playing with MySQL
fabric.
First we need to do some minor changes to MySQL Fabric
configuration file (for me this was located at
/etc/mysql/fabric.cfg), under section Storage you need to change
address to your MySQL backing store (for me this is
localhost:63306) and also see to if you have appropriate setting
for user, password and database under the same section, you will
need these in next step.
Lets configure our backing store for MySQL Fabric, I will use
MySQL on port 63306 as backing store. All that's needed to do
manually is creating the database with same name as in
configuration above and also grants usage for the user created in
configuration above.
mysql -uroot -proot -S /opt/fabric/mysql1/my.sock
mysql> create database fabric;
mysql> CREATE USER fabric@localhost IDENTIFIED BY
'fabric';
mysql> GRANT ALL ON fabric.* TO fabric@localhost;
Once this step is done we can proceed to initiate our backing
store before we can finally start our fabric instance.
# Configure Fabric data node (63306)
bash$ mysqlfabric manage setup
# Check newly created tables
mysqlshow -ufabric -pfabric -h127.0.0.1 -P63306 fabric
# Start fabric
bash$ mysqlfabric manage start
Next step is to create our HA group and add our 3 MySQL
instances.
# Create group and add my MySQL instances
bash$ mysqlfabric group create mygroup
bash$ mysqlfabric group add mygroup 127.0.0.1:63307 root
root
bash$ mysqlfabric group add mygroup 127.0.0.1:63308 root
root
bash$ mysqlfabric group add mygroup 127.0.0.1:63309 root
root
# Control state of Fabric:
bash$ mysqlfabric group lookup_servers mygroup
bash$ mysqlfabric group check_group_availability mygroup
# Promote one of your servers at master, <UUID> is
optional.
bash$ mysqlfabric group promote mygroup <UUID for one of the
servers above>
# Activate automatic failover
bash$ mysqlfabric group activate mygroup
After this step you should see Fabric is running and one of your
MySQL instances have been elected as primary and the other ones
are slaves.
bash$ mysqlfabric group lookup_servers mygroup
Command :
{ success = True
return =
[['ef4593df-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63307',
True, 'PRIMARY'], ['ef49ce23-9e2b-11e3-8a45-5c260a4a26ad',
'127.0.0.1:63309', False, 'SECONDARY'],
['ef49d0b6-9e2b-11e3-8a45-5c260a4a26ad', '127.0.0.1:63308',
False, 'SECONDARY']]
activities =
}
We now have a running MySQL fabric and all our MySQL instances
are ready to start working!
In the next blog post (Part II) we will create a example application
and look at some failover scenarios.
See you soon!