MySQL-cluster how-to (with load-balancing & failover)

Since I recently configured and installed a MySQL-cluster, I thought I’d share the procedure. A lot of the examples around explains how to set it all up on the same machine for “testing purposes” — which, in theory, is the same as setting it up on different machines. I’ll be explaining the latter, that is, installing it onto different machines.
1. Prepare db0 + db1
Go to MySQLs homepage, and find the download-link for the latest MySQL Cluster-package, available here. Then proceed as shown below (changing the ‘datadir’ to your likings);

root@db0:~# cd /usr/local/
root@db0:/usr/local# wget -q http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/mysql-cluster-gpl-7.1.10-linux-x86_64-glibc23.tar.gz/from/http://mirror.switch.ch/ftp/mirror/mysql/
root@db0:/usr/local# mv index.html mysql-cluster-gpl-7.1.10-linux-x86_64-glibc23.tar.gz
root@db0:/usr/local# tar -xzf mysql-cluster-gpl-7.1.10-linux-x86_64-glibc23.tar.gz
root@db0:/usr/local# rm -f mysql-cluster-gpl-7.1.10-linux-x86_64-glibc23.tar.gz
root@db0:/usr/local# ln -s mysql-cluster-gpl-7.1.10-linux-x86_64-glibc23 mysql
root@db0:/usr/local# ls -ald mysql*
lrwxrwxrwx  1 root root    45 2011-03-04 18:30 mysql -> mysql-cluster-gpl-7.1.10-linux-x86_64-glibc23
drwxr-xr-x 13 root mysql 4096 2011-03-05 00:42 mysql-cluster-gpl-7.1.10-linux-x86_64-glibc23
root@db0:/usr/local# cd mysql
root@db0:/usr/local/mysql# mkdir /opt/oracle/disk/mysql_cluster
root@db0:/usr/local/mysql# mkdir /opt/oracle/disk/mysql_cluster/mysqld_data
root@db0:/usr/local/mysql# groupadd mysql
root@db0:/usr/local/mysql# useradd -g mysql mysql
root@db0:/usr/local/mysql# chown mysql:root /opt/oracle/disk/mysql_cluster/mysqld_data
root@db0:/usr/local/mysql# scripts/mysql_install_db --user=mysql --no-defaults --datadir=/opt/oracle/disk/mysql_cluster/mysqld_data/
root@db0:/usr/local/mysql# cp support-files/mysql.server /etc/init.d/
root@db0:/usr/local/mysql# chmod +x /etc/init.d/mysql.server
root@db0:/usr/local/mysql# update-rc.d mysql.server defaults

Repeat on db1. Do not start the MySQL-server yet.

root@db0:/usr/local/mysql# vim /etc/my.cnf

Put the following in the my.cnf-file;

[mysqld]
basedir=/usr/local/mysql
datadir=/opt/oracle/disk/mysql_cluster/mysqld_data
event_scheduler=on
default-storage-engine=ndbcluster
ndbcluster
ndb-connectstring=db0.internal,db1.internal     # IP/host of the NDB_MGMD-nodes

key_buffer = 512M
key_buffer_size = 512M
sort_buffer_size = 512M
table_cache = 1024
read_buffer_size = 512M

[mysql_cluster]
ndb-connectstring=db0.internal,db1.internal     # IP/host of the NDB_MGMD-nodes

Repeat on db 1. And (again), do not start the MySQL-server yet.


2. Prepare ndb_mgmd
We can now prepare ndb_mgmd as follows;

root@db0:~# cd /usr/local/mysql
root@db0:/usr/local/mysql# chmod +x bin/ndb_mgm*
root@db0:/usr/local/mysql# mkdir /var/lib/mysql-cluster
root@db0:/usr/local/mysql# vim /var/lib/mysql-cluster/config.ini

Put the following into the config.ini-file;

[NDBD DEFAULT]
NoOfReplicas=2
DataDir=/var/lib/mysql-cluster
DataMemory=8G
IndexMemory=4G

[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]

# 2 Managment Servers
[NDB_MGMD]
HostName=db0.internal    # IP/host of first NDB_MGMD-node
NodeId=1

[NDB_MGMD]
HostName=db1.internal    # IP/host of second NDB_MGMD-node
NodeId=2

# 2 Storage Engines
[NDBD]
HostName=db0.internal    # IP/host of first NDBD-node
NodeId=3
[NDBD]
HostName=db1.internal    # IP/host of second NDBD-node
NodeId=4

# 2 MySQL Clients
# Lave this blank to allow rapid changes of the mysql clients.
[MYSQLD]
[MYSQLD]

There are two variables in the above config you’d want to pay attention to; DataMemory and IndexMemory. These values needs to be changed according to how large tables you need. Without setting these values, they default to 80MB (DataMemory) and 18MB (IndexMemory), which is not much (after around 200.000 rows, you’ll get messages stating that the table is full when trying to insert new rows). My values are probably way to high for most cases, but since we have a few tables with a lot of messages, and lots of RAM, I just set them a bit high to avoid issues. Keep in mind that NDBD will allocate/reserve the amount of memory you set for DataMemory (so in my config above, NDBD uses 8GB of memory from the second the service is started).
Now we’re ready to start the management-server for the first time. Please notice the use of the parameter ‘–initial’. This should only be used the first time you start it. Once you’ve started it for the first time, you remove the ‘–initial’ parameter.

root@db0:/usr/local/mysql/bin# ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial --config-dir=/var/lib/mysql-cluster/
MySQL Cluster Management Server mysql-5.1.51 ndb-7.1.10

Repeat on db1.
When done, we go back to the storage-servers.


3. Finalize db0 + db1
Now we make the ndb data-dirs, and start the ndb-service;

root@db0:/usr/local/mysql# mkdir /var/lib/mysql-cluster
root@db0:/usr/local/mysql# cd /var/lib/mysql-cluster
root@db0:/var/lib/mysql-cluster# /usr/local/mysql/bin/ndbd --initial
2011-03-04 22:51:54 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2011-03-04 22:51:54 [ndbd] INFO     -- Angel allocated nodeid: 3

Repeat on db1.
We’re now going to alter some of the tables, so that they use the ‘ndbcluster’-engine. This is to ensure that user/host-priviledges also gets synced (so that if you add a user on one server, it gets replicated to the other).

root@db0:/var/lib/mysql-cluster# /etc/init.d/mysql.server start
Starting MySQL.. * 
root@db1:/var/lib/mysql-cluster# /etc/init.d/mysql.server start
Starting MySQL.. *
root@db0:/usr/local/mysql# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.51-ndb-7.1.10-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> ALTER TABLE mysql.user ENGINE=NDBCLUSTER;
Query OK, 6 rows affected (0.25 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.db ENGINE=NDBCLUSTER;
Query OK, 2 rows affected (0.16 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.host ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.tables_priv ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.columns_priv ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Richard pointed out that he had to alter two more tables to use the ‘ndbcluster’-engine in order for it to work. It was not needed when I originally set this up, but recent versions might have introduced more tables. If you encounter errors, alter the following two tables as well;

mysql> ALTER TABLE mysql.procs_priv ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mysql.proxies_priv ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

Do the following regardless if you alter the two tables above or not;

mysql> SET GLOBAL event_scheduler=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE EVENT `mysql`.`flush_priv_tables` ON SCHEDULE EVERY 30 second ON COMPLETION PRESERVE DO FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

Now we’ll stop the MySQL-service on both servers, and copy the data on db0 over to db1. Then we’ll start the servers again.

root@db0:/usr/local/mysql# /etc/init.d/mysql.server stop
root@db1:/usr/local/mysql# /etc/init.d/mysql.server stop
root@db0:/opt/oracle/disk/mysql_cluster# scp -r mysqld_data/ db1:/opt/oracle/disk/mysql_cluster/
root@db0:/usr/local/mysql# /etc/init.d/mysql.server start
Starting MySQL.. *
root@db1:/usr/local/mysql# /etc/init.d/mysql.server start
Starting MySQL.. *

4. Testing
So far, so good. We’re now gonna check if the management/control of the cluster works as it should.

root@db0:~# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: db0.internal:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @10.10.10.10  (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0)
id=4 @10.10.10.20  (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 2 node(s)
id=1 @10.10.10.10  (mysql-5.1.51 ndb-7.1.10)
id=2 @10.10.10.20  (mysql-5.1.51 ndb-7.1.10)

[mysqld(API)] 2 node(s)
id=5 @10.10.10.10  (mysql-5.1.51 ndb-7.1.10)
id=6 @10.10.10.20  (mysql-5.1.51 ndb-7.1.10)

Seems to be working just fine! However, we also want to make sure that replication works. We’re going to populate a database with some data, and check that it replicates to the other server. We’re also going to shut down one server, alter some data, and start it again, to see if the data synchronizes.

root@db0:/var/lib/mysql-cluster# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-ndb-7.1.10-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed

mysql> CREATE TABLE loltest (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO loltest () VALUES (1); 
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM loltest;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

We now move over to db1 to check if it got replicated.

root@db1:/var/lib/mysql-cluster# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.51-ndb-7.1.10-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> SELECT * FROM loltest;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

That seems to be working just fine, as well. However, we also want to test that the servers synchronize when a server comes back up after being down (so that changes done to the other server gets synchronized).

root@db0:/var/lib/mysql-cluster# /etc/init.d/mysql.server stop
Shutting down MySQL..... *


root@db0:~# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: db0.internal:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @10.10.10.10  (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0)
id=4 @10.10.10.20  (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 2 node(s)
id=1 @10.10.10.10  (mysql-5.1.51 ndb-7.1.10)
id=2 @10.10.10.20  (mysql-5.1.51 ndb-7.1.10)

[mysqld(API)] 2 node(s)
id=5 (not connected, accepting connect from any host)
id=6 @10.10.10.20  (mysql-5.1.51 ndb-7.1.10)


root@db1:/var/lib/mysql-cluster# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.51-ndb-7.1.10-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> INSERT INTO loltest () VALUES (99);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO loltest () VALUES (999);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO loltest () VALUES (100);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM loltest;
+------+
| i    |
+------+
|    1 |
|  100 |
|   99 |
|  999 |
+------+
4 rows in set (0.00 sec)

root@db0:/var/lib/mysql-cluster# /etc/init.d/mysql.server start
Starting MySQL. * 
root@db0:/var/lib/mysql-cluster# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-ndb-7.1.10-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> SELECT * FROM loltest;
+------+
| i    |
+------+
|   99 |
|  999 |
|    1 |
|  100 |
+------+
4 rows in set (0.00 sec)

That also seems to be working just fine. That also concludes the configuration of the cluster it self.

5. Failover/redundancy/load-balancing
The only thing that’s left is to setup a mysql-proxy, which all the clients will use as it’s MySQL hostname. This mysql-proxy is then ‘the middleman’, completely transparent for both the servers and the clients. Should a server go down, the clients won’t notice it. It also does automatic load-balancing. If you proceed with this, keep in mind that this mysql-proxy becomes a single-point-of-failure in the setup (hence it kinda makes the whole MySQL-cluster useless).
In my setup, I chose to install the mysql-proxy on the mysql-mgmt machine.I’ve installed mysql-proxy on it’s own virtual host. Since this is virtualized, it’s also redundant should something happen. You could also use two physical machines, and use Linux HA etc, however that’s quite more complex than using a VM (at least if you already have virtualization available).

root@mysql-proxy:~# apt-get install mysql-proxy
root@mysql-proxy:~# mkdir /etc/mysql-proxy
root@mysql-proxy:~# cd /etc/mysql-proxy
root@mysql-proxy:/etc/mysql-proxy# vim mysql-proxy.conf

Add the following to the mysql-proxy.conf-file;

[mysql-proxy]
daemon = true
keepalive = true
proxy-address = mysql-proxy.internal:3306

# db0
proxy-backend-addresses = db0.internal:3306

# db1
proxy-backend-addresses = db1.internal:3306

Depending on what version of mysql-proxy you use, it might complain about the above syntax. On newer versions of mysql-proxy, you have to put all the backend addresses into one proxy-backend-addresses-parameter. The above config would then look like this;

[mysql-proxy]
daemon = true
keepalive = true
proxy-address = mysql-proxy.internal:3306

# db0 + db1
proxy-backend-addresses = db0.internal:3306,db1.internal:3306

Then you can start the mysql-proxy service;

root@mysql-proxy:/etc/mysql-proxy# mysql-proxy --defaults-file=/etc/mysql-proxy/mysql-proxy.conf

Now point your clients to use the hostname of the mysql-proxy server, and you’re good to go!


6. Init-scripts (automatic startup at boot)
The ‘ndbd’- and ‘ndb_mgmd’-services needs init-scripts in order to be loaded automatically at boot. Since they don’t seem to be provided in the MySQL Cluster-package, I made them myself.
The init-script that’s included with the mysql-proxy service didn’t work for me, so I wrote my own for that as well.
Copy them, save them in /etc/init.d/. Then make them executable (chmod +x /etc/init.d/<filename>). Finally you add them to rc.d, so that they’re loaded at boot; update-rc.d <filename> defaults.
/etc/init.d/ndbd

#!/bin/bash # Linux Standard Base comments ### BEGIN INIT INFO # Provides: ndbd # Required-Start: $local_fs $network $syslog $remote_fs # Required-Stop: $local_fs $network $syslog $remote_fs # Should-Start: # Should-Stop: # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: mysql cluster manager client # Description: mysql cluster manager client ### END INIT INFO   ndbd_bin=/usr/local/mysql/bin/ndbd   if ! test -x $ndbd_bin; then echo "Can't execute $ndbd_bin"; exit; fi   start_ndbd(){ number_of_ndbd_pids=`ps aux|grep -iv "grep"|grep -i "/usr/local/mysql/bin/ndbd"|wc -l` if [ $number_of_ndbd_pids -eq 0 ]; then $ndbd_bin echo "ndbd started." else echo "ndbd is already running." fi }   stop_ndbd(){ number_of_ndbd_pids=`ps aux|grep -iv "grep"|grep -i "/usr/local/mysql/bin/ndbd"|wc -l` if [ $number_of_ndbd_pids -ne 0 ]; then ndbd_pids=`pgrep ndbd` for ndbd_pid in $(echo $ndbd_pids); do kill $ndbd_pid 2> /dev/null done   number_of_ndbd_pids=`ps aux|grep -iv "grep"|grep -i "/usr/local/mysql/bin/ndbd"|wc -l`   if [ $number_of_ndbd_pids -eq 0 ]; then echo "ndbd stopped." else echo "Could not stop ndbd." fi else echo "ndbd is not running." fi }     case "$1" in 'start' ) start_ndbd ;; 'stop' ) stop_ndbd ;; 'restart' ) stop_ndbd start_ndbd ;; *) echo "Usage: $0 {start|stop|restart}" >&2 ;; esac

/etc/init.d/ndb_mgmd

#!/bin/bash # Linux Standard Base comments ### BEGIN INIT INFO # Provides: ndb_mgmd # Required-Start: $local_fs $network $syslog $remote_fs # Required-Stop: $local_fs $network $syslog $remote_fs # Should-Start: # Should-Stop: # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: mysql cluster manager # Description: mysql cluster manager ### END INIT INFO   ndb_mgmd=/usr/local/mysql/bin/ndb_mgmd config_file=/var/lib/mysql-cluster/config.ini config_dir=/var/lib/mysql-cluster   if ! test -x $ndb_mgmd; then echo "Can't execute $ndb_mgmd" exit; fi   start_ndb_mgmd(){ number_of_ndb_mgmd_pids=`ps aux|grep -iv "grep"|grep -i "$ndb_mgmd"|wc -l` if [ $number_of_ndb_mgmd_pids -eq 0 ]; then $ndb_mgmd -f $config_file --config-dir=$config_dir echo "ndb_mgmd started." else echo "ndb_mgmd is already running." fi }   stop_ndb_mgmd(){ number_of_ndb_mgmd_pids=`ps aux|grep -iv "grep"|grep -i "$ndb_mgmd"|wc -l` if [ $number_of_ndb_mgmd_pids -ne 0 ]; then ndb_mgmd_pids=`pgrep ndb_mgmd` for ndb_mgmd_pid in $(echo $ndb_mgmd_pids); do kill $ndb_mgmd_pid 2> /dev/null done   number_of_ndb_mgmd_pids=`ps aux|grep -iv "grep"|grep -i "$ndb_mgmd"|wc -l`   if [ $number_of_ndb_mgmd_pids -eq 0 ]; then echo "ndb_mgmd stopped." else echo "Could not stop ndb_mgmd." fi else echo "ndb_mgmd is not running." fi }     case "$1" in 'start' ) start_ndb_mgmd ;; 'stop' ) stop_ndb_mgmd ;; 'restart' ) stop_ndb_mgmd start_ndb_mgmd ;; *) echo "Usage: $0 {start|stop|restart}" >&2 ;; esac

/etc/init.d/mysql-proxy

#! /bin/bash
### BEGIN INIT INFO
# Provides:             mysql-proxy
# Required-Start:       $local_fs $network $syslog $remote_fs
# Required-Stop:        $local_fs $network $syslog $remote_fs
# Should-Start:
# Should-Stop:
# Default-Start:        2 3 4 5
# Default-Stop:         0 1 6
# Short-Description:    MySQL Proxy
# Description:          MySQL Proxy
### END INIT INFO
 
mysql_proxy=/usr/bin/mysql-proxy
config_file=/etc/mysql-proxy/mysql-proxy.conf
 
if ! test -x $mysql_proxy; then
 echo "Can't execute $mysql_proxy" 
 exit;
fi
 
start_mysql_proxy(){
 number_of_mysql_proxy_pids=`ps aux|grep -iv "grep"|grep -i "/usr/bin/mysql-proxy"|wc -l`
 if [ $number_of_mysql_proxy_pids -eq 0 ]; then
  $mysql_proxy --defaults-file=$config_file
  echo "mysql-proxy started."
 else
  echo "mysql-proxy is already running."
 fi
}
 
stop_mysql_proxy(){
 number_of_mysql_proxy_pids=`ps aux|grep -iv "grep"|grep -i "/usr/bin/mysql-proxy"|wc -l`
        if [ $number_of_mysql_proxy_pids -ne 0 ]; then
  mysql_proxy_pids=`pgrep mysql-proxy`
         for mysql_proxy_pid in $(echo $mysql_proxy_pids); do
                 kill $mysql_proxy_pid 2> /dev/null
         done
 
  number_of_mysql_proxy_pids=`ps aux|grep -iv "grep"|grep -i "/usr/bin/mysql-proxy"|wc -l`
 
         if [ $number_of_mysql_proxy_pids -eq 0 ]; then
                 echo "mysql-proxy stopped."
         else
                 echo "Could not stop mysql-proxy."
         fi
 else
  echo "mysql-proxy is not running."
 fi
}
 
 
case "$1" in
    'start' )
        start_mysql_proxy
        ;;
    'stop' )
 stop_mysql_proxy
        ;;
    'restart' )
        stop_mysql_proxy
 start_mysql_proxy
        ;;
    *)
        echo "Usage: $0 {start|stop|restart}" >&2
        ;;
esac