MySQL Fabric is an extensible framework for managing farms of MySQL Servers. Two features have been implemented - High Availability (HA) and scaling out using data sharding. These features can be used in isolation or in combination. (Ref : www.mysql.com/products/enterprise/fabric.html)
The following setup is configured to prepare the MySQL Fabric HA environment. (Sharding is to be discussed later.)
Creating the MyGroup1 with 1 Master and 3 slaves as shown above where the port numbers for the MySQL Servers are to be configured as 3316, 3326, 3336 and 3346. The MySQL Fabric Server has the MySQL as the backing store which the setup uses 3306. The whole setup with this tutorial is configured on a single machine. But you can freely extended this to multiple machine with configuration changes on those host names / ip addresses.
- Download and Install Software
- Download MySQL Server and MySQL Fabric with the MySQL Utilities
- If you have another location with MySQL BASEDIR after installation, you can create a soft link '/usr/local/mysql' pointing to the MySQL installation directory
(command : ln -s <your mysql installation> /usr/local/mysql
- Python and Python Connector for MySQ
- Assuming you have the following 3 folders
- MySQL BASEDIR=/usr/local/mysql
- MYSQL DATADIR=/mysql/myfab
- This demo script folder on ~/demo
- With the demo user, adding the MySQL Bin directory to the path
- PATH=/usr/local/mysql/bin:$PATH;export PATH
The tutorial is configured
- Setting up MySQL Fabric tutorial environment
- Creating and Starting MySQL databases (3306, 3316, 3326, 3336, 3346)
- Creating Fabric Users on databases
- Configure and Start MySQL Fabric
- Creating the MyGroup1 in Fabric
- Promoting a Database instance as Primary
- Activating the HA detection within MySQL Fabric
- Running Python application
- Auto Failover with MySQL Fabric
Setting up MySQL Fabric Tutorial Environment ((Login as the demo user <e.g. mysql>)
Creating 5 mysql databases on /mysql/myfab
Create the MySQL config file for the 5 MySQL databases
The table shows the mapping between the config files against different databases
The following is the sample config file for my0.cnf. The red lines should be changed accordingly to each database in the above mapping table.
File : ~/demo/config/my0.cnf
The above configuration ensures GTID is used. The following options are enabled
--gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency
For details of GTID setup, refers to http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html
The ~/demo/config folder should now have 5 config files (my0cnf, my1.cnf, my2.cnf, my3.cnf and my4.cnf)
Starting up MySQL Server daemon (note : mysql bin directory is in the PATH)
Create fabric user with privileges (Note : It is for demo purpose to grant all on *.*) The password for fabric user is assumed to be 'secret'.
Create fabric configuration file which determines the fabric server properties
For details of each parameters within fabric configuration file, refers to
File : ~/demo/config/fabric.cfg
Setup MySQL Fabric Server with the configuration file (fabric.cfg)
To verify the setup of mysqlfabric after the above command,
The following similar screenshot was captured after the execution of the verification command. There are tables created within fabric database.
Start up MySQL Fabric Server which connects to the backing database (3306).
The following output should be returned from the above execution.
Create MySQL Fabric Group "mygroup1" with the 4 databases (3316, 3326, 3336 and 3346)
To verify the 'mygroup1' creation within MySQL Fabric, mysqlfabric group lookup_servers command is executed on the MySQL Fabric
To have a better visual output of the mysqlfabric group lookup_servers command, the following status.py is created
By promoting the MySQL Fabric group 'mygroup1', one of the MySQL servers will be chosen to be primary.
To active the Auto Failover detection with MySQL Fabric,
Now the MySQL Fabric Server group 'mygroup1' is created with 4 servers and failover detection is activated. The next part of this tutorial will be about creating application that can use these MySQL database servers with master as READ-WRITE and slave for READ_ONLY operations. If the master fails, another MySQL servers from the Fabric will be chosen to be the master.
The following 3 python files are created for the tutorial
|setup_table.ha.py||Create the table - mycustomer via READ_WRITE connection|
|add_cust_ha.py||Add data into table - mycustomer via READ_WRITE and query the table content from READ_WRITE connection|
|read_cust_ha.py||Retrieve data from table - mycustomer via READ_ONLY connection|
File : ~/demo/scripts/setup_table_ha.py
File : ~/demo/scripts/add_cust_ha.py
File : ~/demo/scripts/read_cust_ha.py
Running the test
- setup the customer table
- add customer data which shows the data is inserted into the "PRIMARY" servers
- read customer data which shows the data is retrieved from different "SECONDARY" servers
- Looping the 'add_cust_ha.py" and shutdown the "PRIMARY", the failover detection promotes the secondary server to be the "PRIMARY"
The results from the add_cust_ha.py and read_cust_ha.py shows execution can be scalable with READ operations to different MySQL database instances. where WRITE operations are centralized into PRIMARY server.
Run the Loop with "add_cust_ha.py" as follows
Open another terminal to shutdown the PRIMARY MYSQL database (e.g. 3316, with this tutorial, no password is given for 'root')
The PRIMARY MySQL Server will be faulty and MySQL Fabric should be able to detect the failure and promote another SECONDARY server to be the PRIMARY. The loop will be able to resume service.
To verify the status after failover, use the same command of status.py
*************************** End of this Tutorial **********************************