MySQL Cluster - Horizontal Scaling Without Losing ACID Transactions

I could write a lot about what a MySQL cluster means, but the video below is probably much better.

Use Cases

The reason I'm interested in it is the ability to have high availability/redundancy whilst not losing ACID (Atomic, Consistency, Isolation, Durability) transactions. E.g. I don't want my database taken offline by a single DDOS attack

Another use case may be that you can exploit services like Digital Ocean being most cost-effective for small instances. E.g. if your database is larger than 60GB in size, it is probably cheaper to run a cluster than to use one large instance, and you get all the benefits of a cluster.

If you are using a single DC to make use of the private networking, then bear in mind that multiple virtual machines could be running on the same physical node, drastically increasing the likelihood of multiple VM's failing simultaneously. Digital Ocean are aware of this need and have a feature planned to resolve the issue.

Node Types

Cluster Manager

The cluster manager is responsible for managing the cluster (who knew!?). It will detect if one of the nodes goes offline and keep the cluster working by having the other nodes take over and synchronizing the data back when the failed node comes back online. If the management nodes all go offline, then a "Split-Brain" is likely to arise with inconsistent data between the working databases. By having more than one management node, you prevent the management area acting as a single point of failure for your cluster.

MySQL Proxy

The MySql proxy basically passes the queries onto the databases which allows you to make those databases publicly inaccessable for security. If there is more than one database that it is hooked up to, then it will act like a load balancer by passing the queries on in a round-robin fashion. The proxy automatically identifies when a server becomes unavailable and marks it accordingly, thus providing failover functionality. By having multiple proxys, your load-balancing might be less "perfect", but you gain high availability (e.g. what happens if you had only one proxy and it goes down?). [ reference material ]

Database Node

The workhorse that stores the data and actually handles the queries. This is what most people think of when it comes to the database.

MySQL Cluster is implemented through the NDB or NDBCLUSTER storage engine for MySQL (“NDB” stands for Network Database). This means you won't be running InnoDB storage engine.


    For this tutorial, I am going to use six virtualbox ubuntu 14.04 virtual machines (2 storage nodes, 2 managers, 2 proxies). You can use physical hardware if you like. So first prepare three machines with ubuntu 14.04 installed.
    Download MySQL cluster onto all of the nodes except the proxies.

    Install Management Nodes

    Turn two nodes into management nodes by running the following steps on two of the nodes. If you prefer to have only one management VM, just leave it out of the configuration but you are losing your redundancy.
    The management server is the process that reads the cluster configuration file and distributes this information to all nodes in the cluster that request it. It also maintains a log of cluster activities. Management clients can connect to the management server and check the cluster's status. [src ]
      Create a temporary location for where we are going to work
      sudo mkdir /tmp/mysql-mgm
      cd /tmp/mysql-mgm
      Download the latest version of the cluster software. You will need to create an oracle account if you haven't got one already. Don't worry, it doesn't cost money.
      wget [url here]
      Extranct the tar.
      sudo tar xvfz mysql-cluster*.tar.gz
      Navigate into the xtracted folder and move the binaries.
      cd mysql-cluster-*
      sudo cp bin/ndb_mgm /usr/bin/.
      sudo cp bin/ndb_mgmd /usr/bin/.
      Change the permissions to allow non-root users to execute the binaries and remove the downloaded material. It will be removed anyways on next reboot since it is in /tmp.
      sudo chmod 755 /usr/bin/ndb_mg*
      cd /usr/src
      sudo rm -rf /usr/src/mysql-mgm
      Create the cluster configuration file at:
      sudo mkdir /var/lib/mysql-cluster
      sudo editor /var/lib/mysql-cluster/config.ini
      Copy and paste the following contents into the file, making sure to fill in any areas with the
      # Section for the cluster management node
      # IP address of the first management node (this system)
      #IP address of the second management node
      # Section for the storage nodes
      # IP address of the first storage node
      DataDir= /var/lib/mysql-cluster
      # IP address of the second storage node
      # one [MYSQLD] per storage node
      You need to define all managers and storage nodes at this point but not the proxies. They come later.
      The management host nodes need the NodeID specifed whereas the NDBD (storage) nodes do not.

      [ How My Config Looks ]
      Start the management node with the following command:
      sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/
      You can automate the start procedure when the server starts, by running the following commands:
      sudo su
      crontab -l > /tmp/mycron
      echo "@reboot ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/" >> /tmp/mycron
      crontab /tmp/mycron
      The tutorial I was using to learn told me to just add that command to /etc/init.d and change the permissions, but this did not work for me in ubuntu 14.04
      Follow the same procedure on the second management node and use the same configuration file. You mustn’t change the node ID’s!
      After having performed all the steps again for the second management node, check that they are operational by entering the following commands into either manager's terminal.

      [ You should see something like the following on your nodes. ]

    Install The Storage/Database Nodes

      Create a the user mysql which will have the mysql group.
      sudo useradd mysql
      cd /usr/local
      sudo wget [download link here]
      sudo tar xvfz mysql-cluster-*
      sudo ln -s mysql-cluster-gpl-[version number]-linux-glibc2.5-x86_64 mysql
      cd mysql
      sudo apt-get install libaio1 libaio-dev
      sudo scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
      # Change the owner to the newly created mysql group
      sudo chown -R root:mysql .
      sudo chown -R mysql data
      Move the startup script to /etc/init.d and change it's permissions.
      sudo cp support-files/mysql.server /etc/init.d/.
      sudo chmod 755 /etc/init.d/mysql.server
      Copy the bin files to the /usr/bin location and create a symlink to keep references correct.
      cd /usr/local/mysql/bin
      sudo mv * /usr/bin
      cd ..
      sudo rm -rf /usr/local/mysql/bin
      sudo ln -s /usr/bin /usr/local/mysql/bin
      Create the mysql configuration file by editing and copy/pasting the following command
      echo '[mysqld]
      bind-address =
      # IP address of the cluster management node
      # IP address of the cluster management node
      ndb-connectstring=$db-manager1,$db-manager2' | sudo tee /etc/my.cnf
      Just make sure to comma separate all managers that you have. E.g. if you have 3, then specify 3, if just 1 then remove one.
      Create the data folders for MySQL
      sudo mkdir /var/lib/mysql-cluster
      Initialize the cluster and start the service. We only need to intialize when you start the node for the very first time, or whenever the
      file has changed.
      cd /var/lib/mysql-cluster
      sudo ndbd –-initial
      sudo /etc/init.d/mysql.server start
      Secure the MySQL installation by running the appropriate script:
      sudo /usr/local/mysql/bin/mysql_secure_installation
      Set nbd to start on reboot
      sudo su
      crontab -l > /tmp/mycron
      echo "@reboot ndbd" >> /tmp/mycron
      crontab /tmp/mycron
      Now run all the steps again for the second storage node.
      Now that you have finished, on any of the 4 servers you just deployed run the following commands:

      [ You should see something like above ]

    Install Proxies

      sudo apt-get install mysql-proxy
      sudo mkdir /etc/mysql-proxy
      Now edit the following command by replacing all the
      variables before running it.
      echo '[mysql-proxy]
      daemon = true
      proxy-address = $THIS_PROXIES_IP:3306
      proxy-skip-profiling = true
      keepalive = true
      event-threads = 50
      pid-file = /var/run/
      log-file = /var/log/mysql-proxy.log
      log-level = debug
      proxy-backend-addresses = $storage1_hostname_or_IP:3306,$storage2_hostname_or_IP:3306
      proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/balance.lua' | sudo tee /etc/mysql-proxy/mysql-proxy.conf
      Now run the following:
      echo 'ENABLED="true"
      OPTIONS="--defaults-file=/etc/mysql-proxy.conf --plugins=proxy"' | sudo tee /etc/default/mysql-proxy
      Now you can start/stop/status the proxy service with
      /etc/init.d/mysql-proxy start/stop/status
      Redo all the steps above for all the other proxy servers.
    That's it! You should now be able to use the database through the proxy. Make sure to add users to each database node individually as users do not replicate. Also ensure that all tables use the NDBCLUSTER engine, otherwise they won't replicate (no more MyISAM or Innodb)
    I have not tested this final part yet and will remove this warning once I have. For now it's off to bed! If you find any problems/issues then don't hesitate to look through the references below.


No comments:

Post a Comment