Programster: 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.

Steps

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:

/var/lib/mysql-cluster/config.ini

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

sign.

[NDBD DEFAULT]
 
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M
[MYSQLD DEFAULT]
 
[NDB_MGMD DEFAULT]
DataDir=/var/lib/mysql-cluster
[TCP DEFAULT]
 
# Section for the cluster management node
[NDB_MGMD]
NodeId=1
# IP address of the first management node (this system)
HostName=$MANAGER1_IP_OR_HOSTNAME
 
[NDB_MGMD]
NodeId=2
#IP address of the second management node
HostName=$MANAGER2_IP_OR_HOSTNAME
 
# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=$DB1_IP_OR_HOSTNAME
DataDir= /var/lib/mysql-cluster
[NDBD]
# IP address of the second storage node
HostName=$DB2_IP_OR_HOSTNAME
DataDir=/var/lib/mysql-cluster
# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]

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.

ndb_mgm
show

[ 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 = 0.0.0.0
ndbcluster
# IP address of the cluster management node
ndb-connectstring=$db-manager1,$db-manager2
[mysql_cluster]
# 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

/var/lib/mysql-cluster/config.ini

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:

ndb_mgm
show

[ 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/mysql-proxy.pid
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.

MySQL Cluster - Horizontal Scaling Without Losing ACID Transactions