High availability MySQL cluster + Ignition redundancy

Ignition
1

Objective: To add 99.999% high availability multi master replication between Master and Backup MySQL nodes of Ignition redundancy with MySQL cluster.

Reference:

  1. clusterdb.com/mysql-cluster/ … -installer
  2. daviwa.blogspot.ae/2014/01/mysql … mysql.html
  3. xuri.me/2013/11/20/install-mysq … 4-lts.html

Pls spend some time on the above links, especially the first one, before testing MySQL cluster which includes MySQL 5.6 by default. You must completely uninstall any MySQL server installed/running on your machines. It will NOT work with an independent MySQL 5.6 installation either.

Software:

  1. OS: Ubuntu 14.04/64 headless server.

  2. MySQL Cluster 7.3.6 - “debian package” – Debian Linux 6.0 (x86, 64-bit), DEB
    dev.mysql.com/downloads/cluster/

  3. Ignition 7.7/64 linux

4 Java 8 installed on all servers and client.

  1. MySQL workbench 6.2 - “debian package” – Ubuntu Linux 14.04 (x86, 64-bit), DEB
    dev.mysql.com/downloads/workbench/

Hardware:

  1. Physical machines with 4GB ram - 2 nos
    (You can use one physical m/c and one VM also)

  2. Laptop client - 1 no,

Description:

The MySQL cluster has 3 software components.

  1. Management Node - To configure the cluster with auto installer

  2. SQL Nodes - MySQL clients (Ignition) connects to the Data nodes through this node.

  3. Data Nodes - The schema and tables are stored in NDBCLUSTER engine in these nodes. Kindly note that MySQL cluster replication works only with NDBCLUSTER engine tables. Ignition table management uses innodb engine by default. You must convert all the tables to NDBCLUSTER engine for replication between Master/Backup nodes to work. You must use MySQL workbench 6.2 to manage the database.

Installation:

  1. Extract the debian package on your Ubuntu laptop.
    sudo dpkg -i mysql-cluster-gpl-7.3.6-debian6.0-x86_64.deb

This will extract to the “/opt” folder. DON’T change this location. Open the /bin folder and run the “ndb_setup.py” file. This will launch the auto-installer on your browser. Bookmark this location. If you close your browser, you can NOT relaunch the installer again by running “ndb_setup.py” file because the installer is already launched. Just click the bookmark.

localhost:8081/content.html

The installation is straight forward. Watch the video in this link.
clusterdb.com/mysql-cluster/ … -installer

My cluster setup: (Watch the screen shots below)

  1. Laptop - “Management Layer” - static ip address 192.168.1.7.
    (All configurations were carried out through SSH client)

  2. Physical machine #1 - “Data layer + SQL layer” - static ip address 192.168.1.2
    (Ignition Master is running on this m/c)

  3. Physical machine #2 - “Data layer + SQL layer” - static ip address 192.168.1.55
    (Ignition Backup is running on this m/c)

Conclusion:

The following tests were carried out:

1.Around 500,000 historical records were logged into the “demo.sqlt_data_1_2014_09” table (NDBCLUSTER engine). Master /Backup ignition gateways were stopped alternatively several times while MySQL was running on both nodes. Master/Backup ignition gateways failied over / took over perfectly and all the latest records were replicating almost instantaneously between both nodes.

When Backup ignition took over from Master, there was 30 seconds delay / data loss as per the redundancy configuration. But when Master took over from the Backup, no data loss was observed. Datalogging was happening at the rate of around 10 records / second through many tags.

  1. Physical nodes (Ignition + MySQL) 1 and 2 were powered off/on alternatively. The results were 100% same as above. Master Ignition database connect URL is “jdbc:mysql://localhost:3306/demo”. So during failover, each ignition gateway was logging into it’s own local MySQL server and when the other node came up, the replication happened almost instantaneously. The chart recorder showed merged historical and real time trends on both machines without any break.

MySQL failover test:

Both Master/Backup ignition gateways running but one of the MySQL servers failing – This test was NOT carried out:

This test needs atleast “4 nodes + load balancer + Virtual IP router” so that both ignition gateways will see only one ip address for both MySQL servers. In fact, MySQL cluster is designed for this type of failover.

I am working on it and will let you know the results. This is only a pilot test and needs careful planning and better understanding before taking to the production level. Meanwhile, if you test the cluster, pls share your experience. Thanks.

Installation steps (6 steps):

  1. Select “Create New MySQL Cluster” from tthe first page and follow the screen shots below.
    2 Cluster type and SSH credentials.jpg896×560 59.2 KB


    3 Select and Edit Hosts.jpg896×560 72.4 KB


    4 Define Processes and Cluster Topology.jpg896×560 69.6 KB


    5 Define Processes Parameters.jpg896×560 76.4 KB


    6 Deploy Configuration and start MySQL Cluster.jpg896×560 77.2 KB

2

This is good to have. Thank you.

3

nmudge

Thanks Nick. Your Ignition blog is my favourite and has wealth of information. If you like, you can do some more research on this topic and republish my writeup in your blog.

I suggest MySQL node failover test where Ignition master / backup is automatically switched over to the next available node via VIP router.

You can check these 2 links.

linuxvirtualserver.org/docs/ … lived.html

behindtheracks.com/2014/04/redun … eepalived/

4

Hi R.Alamsha,

Thank you. I am glad to hear that my blog is of interest to you. Thank you for the opportunity to replublish your write up here on my blog. I think I would like to do that. I will of course attribute the content to you and link to this forum post. I will also do some research.

Thanks,