MySQL Cluster is a technology that enables clustering of in-memory databases in a shared-nothing system. The shared-nothing architecture allows the system to work with very inexpensive hardware, and with a minimum of specific requirements for hardware or software. MySQL Cluster is designed not to have any single point of failure. It integrates the standard MySQL server with an in-memory clustered storage engine called NDB. MySQL Cluster refers to the combination of MySQL and the NDB storage engine. A MySQL Cluster consists of a set of computers, each running one or more processes which may include a MySQL server, a data node, a management server, and (possibly) a specialized data access programs.
This post explains how to setup a small MySQL Cluster to do some basic testing, NDB API development, or just to learn about MySQL Cluster. It describes how to setup a limited MySQL Cluster installation which can run on a local workstation. This setup should never be used in production, of course.
First, start by installing the latest MySQL Community edition. Either install the Max version of MySQL, which includes MySQL Cluster, or install the Standard version and the binaries for the cluster storage engine and tools for your operating system.
Without starting the MySQL server yet, open or create the file, /etc/config.ini in a text editor and add with following lines:
# This configuration should not be used in production! [NDBD DEFAULT] NoOfReplicas = 2 DataMemory = 80M # 80M is default IndexMemory = 18M # 18M is default DataDir = /var/mysql-cluster LockPagesInMainMemory = 1 # Make sure not to use swap [NDB_MGMD DEFAULT] DataDir = /var/mysql-cluster [NDB_MGMD] HostName = localhost [NDBD]HostName = localhost [NDBD] HostName = localhost [MYSQLD] [MYSQLD] [API]
Next, configure the MySQL server by opening or creating the file /etc/my.cnf and adding the following text:
[mysqld] datadir=/var/mysql ndbcluster ndb_connectstring="localhost"
Now you’re ready to start MySQL Cluster: First launch the Management Node which will hold the configuration making it available for the other nodes. You don’t have to be root to start it, but you have to make sure that all the directories are writable by the user with which you start. Enter the following from the commant-line to start the Management Node:
ndb_mgmd -f /etc/config.ini
Next you need to start the Data Nodes from the command-line like this:
ndbd -c localhost ndbd -c localhost
To check what is running you can use the Management Client by entering the following from the command-line:
ndb_mgm -c localhost ndb_mgm> SHOW
Notice that the second line is entered from the within the Management Client and only the SHOW statement is entered. It should return results similar to this:
[ndbd(NDB)] 2 node(s) id=2 @127.0.0.1 (Version: 5.0.41, starting, Nodegroup: 0, Master) id=3 @127.0.0.1 (Version: 5.0.41, starting, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 (Version: 5.0.41) [mysqld(API)] 3 node(s) id=4 (not connected, accepting connect from any host) id=5 (not connected, accepting connect from any host) id=6 (not connected, accepting connect from any host)
Execute the SHOW command a few times in the management client until the ‘starting‘ status message is gone. The last process to start is the MySQL server. Now start MySQL as you normally would, or enter something like the following:
mysqld_safe --defaults-file=/etc/my.cnf &
If all goes well, MySQL should be running and you can now use the NDB Storage Engine. Try creating a table by entering the following SQL statement after logging into MySQL with the mysql client as root or a user with CREATE privileges:
CREATE TABLE table1 ( col_id INT NOT NULL AUTO_INCREMENT, col1 VARCHAR(20), PRIMARY KEY (col_id) ) ENGINE=NDB;
If you’re able to do this without an error, you have a basic MySQL Cluster running. Setting up a production environment is similar, but you will need more machines and more advanced configuration.