This week I was setting up a MySQL cluster. Here are a few steps on how to do a basic setup - for a good initial overview of MySQL cluster I'd recommend having a look at
this page of the manual. This example launches all daemons by hand, no fancy boot scripts.
Can download the software from http://dev.mysql.com/downloads/cluster/, in my case I downloaded the Debian packages although downloading the tgz file would be the same as there is no scripts in the control information files.
My example runs the following nodes:
2 x NDB data nodes - they will run the databases in memory so we need enough RAM (7 GB each for me)
1 x management node
2 x SQL API nodes - the SQL interface to NDB engine. Our app will point to them.
The data nodes will run the NDB daemon, SQL API nodes will run the mysql database software and the management node the management daemon. For each one of the 4 working nodes the IP / fqdn of the management server(s) must be stated on the configuration file, and the manaement server need to know each one of the working node's IPs / fqdn - see later the configuration file's content.
Installing
Copy the debian package and install it on each one of the servers:
$
sudo dpkg -i mysql-cluster-advanced-7.3.7-debian7-x86_64.deb
This will install the software in
/opt/mysql/server-5.6/. To continue the installation we will need to create the mysql user and install some dependencies (libaio in Ubuntu 14):
$
sudo groupadd mysql
$
sudo useradd -g mysql mysql
$
sudo apt-get install libaio1
The original package contains a support install script:
$
sudo /opt/mysql/server-5.6/scripts$ sudo ./mysql_install_db --user=mysql
Configuration
Now we have the software in all 5 servers. For the data and SQL nodes we will create the
/etc/my.cnf config file:
[mysqld]
ndbcluster
ndb-connectstring=172.3.5.172
[mysql_cluster]
ndb-connectstring=172.3.5.172
You can specify the node id with the parameter ndb-nodeid=X , or you can let the management node handle that for you.
On the management server, make sure the directory
/var/lib/mysql-cluster exists and create the file
config.ini with this content:
[ndbd default]
NoOfReplicas=2
DataMemory=4500M # Space to store DB records
IndexMemory=400M # Space used to store hash indexes
MaxNoOfAttributes=10000 # max number of replication attributes
MaxNoOfTables=1000
MaxNoOfOrderedIndexes=1000
MaxNoOfConcurrentOperations=128000
MaxNoOfExecutionThreads=8
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
DataDir=/var/lib/mysql-cluster
[tcp default]
SendBufferMemory=12M
ReceiveBufferMemory=12M
[ndb_mgmd]
nodeId=1
hostname=172.3.5.172
datadir=/var/lib/mysql-cluster
[ndbd]
nodeId=2
hostname=172.3.5.170
[ndbd]
nodeId=3
hostname=172.3.5.171
datadir=/opt/mysql/server-5.6/data/
[mysqld]
hostname=172.3.5.173
[mysqld]
hostname=172.3.5.174
Running the software
Now we have all configuration files in place. These are the software we will launch on each of the nodes:
Data nodes: Daemon /opt/mysql/server-5.6/bin/ndbd (--initial):
$ sudo /opt/mysql/server-5.6/bin/ndbd
2015-02-04 02:21:21 [ndbd] INFO -- Angel connected to '172.3.172:1186'
2015-02-04 02:21:21 [ndbd] INFO -- Angel allocated nodeid: 3
MGMT node: Daemon /opt/mysql/server-5.6/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini (--initial)
$ /opt/mysql/server-5.6/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.21 ndb-7.3.7
SQL API nodes: Script /opt/mysql/server-5.6/support-files/mysql.server (start|stop|reload)
$ sudo /opt/mysql/server-5.6/support-files/mysql.server start
Starting MySQL
.. *
Note that the (--initial) can be used the first time, or when there is a configuration change - but under special circumstances detailed in the
manual. For the MGMT node, on configuration change is best to use (
--reload) to refresh the parameters.
If all went good, you may see this status on the MGMT node:
$ sudo /opt/mysql/server-5.6/bin/ndb_mgm -e "show"
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @172.3.5.170 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0)
id=3 @172.3.5.171 (mysql-5.6.21 ndb-7.3.7, Nodegroup: 0, *)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.3.5.172 (mysql-5.6.21 ndb-7.3.7)
[mysqld(API)] 2 node(s)
id=4 @172.3.5.173 (mysql-5.6.21 ndb-7.3.7)
id=5 @172.3.5.174 (mysql-5.6.21 ndb-7.3.7)
You can trace errors by checking the log files :
- Data nodes: /opt/mysql/server-5.6/data/ndb_*_out.log
- MGMT node: /var/lib/mysql-cluster/ndb_*_cluster.log
- SQL API nodes: /opt/mysql/server-5.6/data/<hostname or IP>.err
If the nodes are not connecting, you may see a screen like this:
$ sudo /opt/mysql/server-5.6/bin/ndb_mgm -e "show"
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @172.3.5.170 (mysql-5.6.21 ndb-7.3.7, starting, Nodegroup: 0)
id=3 (not connected, accepting connect from 172.3.5.171)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.31.5.172 (mysql-5.6.21 ndb-7.3.7)
[mysqld(API)] 2 node(s)
id=4 (not connected, accepting connect from 172.3.5.173)
id=5 (not connected, accepting connect from 172.3.5.174)
If so, check the log files for errors.
Testing the cluster
When all is up and running, we can go to any of the SQL API nodes and login as root - default setup has no password:
$ /opt/mysql/server-5.6/bin/mysql -u root
mysql> CREATE DATABASE MY_CLUSTER;
mysql> USE MY_CLUSTER;
mysql> CREATE TABLE cluster_table( ID INT) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into cluster_table values (5);
Query OK, 1 row affected (0.00 sec)
Now go to the other SQL API node, and check if data is there:
$ /opt/mysql/server-5.6/bin/mysql -u root
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MY_CLUSTER |
| mysql |
| ndbinfo |
| performance_schema |
+--------------------+
8 rows in set (0.00 sec)
mysql> use MY_CLUSTER;
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 cluster_table;
+------+
| ID |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
If we can see both nodes accessing the same data, initial setup is done.
