Chapter 2. MySQL Cluster Multi-Computer How-To

One of the strengths of MySQL Cluster is that it can be run on commodity hardware and has no unusual requirements in this regard, other than for large amounts of RAM, due to the fact that all live data storage is done in memory. (It is possible to reduce this requirement using Disk Data tables — see Chapter 10, MySQL Cluster Disk Data Tables, for more information about these.) Naturally, multiple and faster CPUs can enhance performance. Memory requirements for other Cluster processes are relatively small.

The software requirements for Cluster are also modest. Host operating systems do not require any unusual modules, services, applications, or configuration to support MySQL Cluster. For supported operating systems, a standard installation should be sufficient. The MySQL software requirements are simple: all that is needed is a production release of MySQL 5.1.34-ndb-6.2.18 or 5.1.34-ndb-6.3.26 to have Cluster support. It is not necessary to compile MySQL yourself merely to be able to use Cluster. In this How-To, we assume that you are using the server binary appropriate to your platform, available via the MySQL Cluster software downloads page at http://dev.mysql.com/downloads/cluster.

For communication between nodes, Cluster supports TCP/IP networking in any standard topology, and the minimum expected for each host is a standard 100 Mbps Ethernet card, plus a switch, hub, or router to provide network connectivity for the cluster as a whole. We strongly recommend that a MySQL Cluster be run on its own subnet which is not shared with non-Cluster machines for the following reasons:

It is also possible to use the high-speed Scalable Coherent Interface (SCI) with MySQL Cluster, but this is not a requirement. See Chapter 11, Using High-Speed Interconnects with MySQL Cluster, for more about this protocol and its use with MySQL Cluster.

Each MySQL Cluster host computer running an SQL node must have installed on it a MySQL binary. For management nodes and data nodes, it is not necessary to install the MySQL server binary, but management nodes require the management server daemon (ndb_mgmd) and data nodes require the data node daemon (ndbd). It is also a good idea to install the management client (ndb_mgm) on the management server host. This section covers the steps necessary to install the correct binaries for each type of Cluster node.

MySQL AB provides precompiled binaries that support Cluster. However, we also include information relating to installing a MySQL Cluster after building MySQL from source. For setting up a cluster using MySQL's binaries, the first step in the installation process for each cluster host is to download the latest MySQL Cluster NDB 6.2 or MySQL Cluster NDB 6.3 binary archive (mysql-cluster-gpl-6.2.18-linux-i686-glibc23.tar.gz or mysql-cluster-gpl-6.3.26-linux-i686-glibc23.tar.gz, respectively) from the MySQL Cluster downloads area. We assume that you have placed this file in each machine's /var/tmp directory. (If you do require a custom binary, see Installing from the Development Source Tree.)

RPMs are also available for both 32-bit and 64-bit Linux platforms. For a MySQL Cluster, three RPMs are required:

In addition, you should also obtain the NDB Cluster - Storage engine basic tools RPM (for example, MySQL-Cluster-gpl-tools-6.2.18-0.sles10.i586.rpm or MySQL-Cluster-gpl-tools-6.3.26-0.sles10.i586.rpm), which supplies several useful applications for working with a MySQL Cluster. The most important of these is the MySQL Cluster management client (ndb_mgm). The NDB Cluster - Storage engine extra tools RPM (for example, MySQL-Cluster-gpl-extra-6.2.18-0.sles10.i586.rpm or MySQL-Cluster-gpl-extra-6.3.26-0.sles10.i586.rpm) contains some additional testing and monitoring programs, but is not required to install a MySQL Cluster. (For more information about these additional programs, see Chapter 6, MySQL Cluster Programs.)

The MySQL Cluster version number in the RPM file names (shown here as 6.2.18 or 6.3.26) can vary according to the version which you are actually using. It is very important that all of the Cluster RPMs to be installed have the same version number. The glibc version number (if present), and architecture designation (shown here as i586) should be appropriate to the machine on which the RPM is to be installed.

See Installing MySQL from RPM Packages on Linux, for general information about installing MySQL using RPMs supplied by MySQL AB.

After installing from RPM, you still need to configure the cluster as discussed in Section 2.3, “MySQL Cluster Multi-Computer Configuration”.

Data and SQL Node Installation — .tar.gz Binary.  On each of the machines designated to host data or SQL nodes, perform the following steps as the system root user:

Remember that the preceding steps must be repeated on each machine where an SQL node is to reside.

SQL node installation — RPM files.  On each machine to be used for hosting a cluster SQL node, install the Server RPM by executing the following command as the system root user, replacing the name shown for the RPM as necessary to match the name of the RPM downloaded from the MySQL AB web site:

shell> rpm -Uhv MySQL-Cluster-gpl-server-6.3.26-0.sles10.i586.rpm

This installs the MySQL server binary (mysqld) in the /usr/sbin directory, as well as all needed MySQL Server support files. It also installs the mysql.server and mysqld_safe startup scripts in /usr/share/mysql and /usr/bin, respectively. The RPM installer should take care of general configuration issues (such as creating the mysql user and group, if needed) automatically.

shell> rpm -Uhv MySQL-Cluster-gpl-client-6.3.26-0.sles10.i586.rpm

SQL node installation — building from source.  If you compile MySQL with clustering support (for example, by using the BUILD/compile-platform_name-max script appropriate to your platform), and perform the default installation (using make install as the root user), mysqld is placed in /usr/local/mysql/bin. Follow the steps given in MySQL Installation Using a Source Distribution to make mysqld ready for use. If you want to run multiple SQL nodes, you can use a copy of the same mysqld executable and its associated support files on several machines. The easiest way to do this is to copy the entire /usr/local/mysql directory and all directories and files contained within it to the other SQL node host or hosts, then repeat the steps from MySQL Installation Using a Source Distribution on each machine. If you configure the build with a nondefault --prefix, you need to adjust the directory accordingly.

Data node installation — RPM Files.  On a computer that is to host a cluster data node it is necessary to install only the NDB Cluster - Storage engine RPM. To do so, copy this RPM to the data node host, and run the following command as the system root user, replacing the name shown for the RPM as necessary to match that of the RPM downloaded from the MySQL AB web site:

shell> rpm -Uhv MySQL-Cluster-gpl-storage-6.2.18-0.sles10.i586.rpm

The previous command installs the MySQL Cluster data node binary (ndbd) in the /usr/sbin directory.

Data node installation — building from source.  The only executable required on a data node host is ndbd (mysqld, for example, does not have to be present on the host machine). By default when doing a source build, this file is placed in the directory /usr/local/mysql/libexec. For installing on multiple data node hosts, only ndbd need be copied to the other host machine or machines. (This assumes that all data node hosts use the same architecture and operating system; otherwise you may need to compile separately for each different platform.) ndbd need not be in any particular location on the host's file system, as long as the location is known.

Management node installation — .tar.gz binary.  Installation of the management node does not require the mysqld binary. Only the MySQL Cluster management server (ndb_mgmd) is required; you most likely want to install the management client (ndb_mgm) as well. Both of these binaries also be found in the .tar.gz archive. Again, we assume that you have placed this archive in /var/tmp.

As system root (that is, after using sudo, su root, or your system's equivalent for temporarily assuming the system administrator account's privileges), perform the following steps to install ndb_mgmd and ndb_mgm on the Cluster management node host:

Management node installation — RPM file.  To install the MySQL Cluster management server, it is necessary only to use the NDB Cluster - Storage engine management RPM. Copy this RPM to the computer intended to host the management node, and then install it by running the following command as the system root user (replace the name shown for the RPM as necessary to match that of the Storage engine management RPM downloaded from the MySQL AB web site):

shell> rpm -Uhv MySQL-Cluster-gpl-management-6.3.26-0.sles10.i586.rpm

This installs the management server binary (ndb_mgmd) to the /usr/sbin directory.

You should also install the NDB management client, which is supplied by the Storage engine basic tools RPM. Copy this RPM to the same computer as the management node, and then install it by running the following command as the system root user (again, replace the name shown for the RPM as necessary to match that of the Storage engine basic tools RPM downloaded from the MySQL AB web site):

shell> rpm -Uhv MySQL-Cluster-gpl-tools-6.3.26-0.sles10.i586.rpm

The Storage engine basic tools RPM installs the MySQL Cluster management client (ndb_mgm) to the /usr/bin directory.

shell> rpm -Uhv MySQL-Cluster-gpl-extra-6.3.26-0.sles10.i586.rpm

Management node installation — building from source.  When building from source and running the default make install, the management server binary (ndb_mgmd) is placed in /usr/local/mysql/libexec, while the management client binary (ndb_mgm) can be found in /usr/local/mysql/bin. Only ndb_mgmd is required to be present on a management node host; however, it is also a good idea to have ndb_mgm present on the same host machine. Neither of these executables requires a specific location on the host machine's file system.

In Section 2.3, “MySQL Cluster Multi-Computer Configuration”, we create configuration files for all of the nodes in our example MySQL Cluster.

MySQL Cluster on Windows (alpha).  In MySQL Cluster NDB 7.0, experimental support is added for Microsoft Windows platforms. To compile MySQL Cluster from source on Windows, you must configure the build using the WITH_NDBCLUSTER_STORAGE_ENGINE option before creating the Visual Studio project files. After running make_win_bin_dist, the MySQL Cluster binaries can be found in the bin directory of the resulting archive. For more information, see Installing MySQL from Source on Windows.

For our four-node, four-host MySQL Cluster, it is necessary to write four configuration files, one per node host.

Configuring the Storage and SQL Nodes

The my.cnf file needed for the data nodes is fairly simple. The configuration file should be located in the /etc directory and can be edited using any text editor. (Create the file if it does not exist.) For example:

shell> vi /etc/my.cnf

For each data node and SQL node in our example setup, my.cnf should look like this:

# Options for mysqld process:
[mysqld]
ndbcluster                      # run NDB storage engine
ndb-connectstring=192.168.0.10  # location of management server
# Options for ndbd process:
[mysql_cluster]
ndb-connectstring=192.168.0.10  # location of management server

After entering the preceding information, save this file and exit the text editor. Do this for the machines hosting data node “A”, data node “B”, and the SQL node.

Configuring the management node.  The first step in configuring the management node is to create the directory in which the configuration file can be found and then to create the file itself. For example (running as root):

shell> mkdir /var/lib/mysql-cluster
shell> cd /var/lib/mysql-cluster
shell> vi config.ini

For our representative setup, the config.ini file should read as follows:

# Options affecting ndbd processes on all data nodes:
[ndbd default]
NoOfReplicas=2    # Number of replicas
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
                  # For DataMemory and IndexMemory, we have used the
                  # default values. Since the "world" database takes up
                  # only about 500KB, this should be more than enough for
                  # this example Cluster setup.
# TCP/IP options:
[tcp default]
portnumber=2202   # This the default; however, you can use any port that is free
                  # for all the hosts in the cluster
                  # Note: It is recommended that you do not specify the port
                  # number at all and allow the default value to be used instead
# Management process options:
[ndb_mgmd]
hostname=192.168.0.10           # Hostname or IP address of management node
datadir=/var/lib/mysql-cluster  # Directory for management node log files
# Options for data node "A":
[ndbd]
                                # (one [ndbd] section per data node)
hostname=192.168.0.30           # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node's data files
# Options for data node "B":
[ndbd]
hostname=192.168.0.40           # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node's data files
# SQL node options:
[mysqld]
hostname=192.168.0.20           # Hostname or IP address
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

After all the configuration files have been created and these minimal options have been specified, you are ready to proceed with starting the cluster and verifying that all processes are running. We discuss how this is done in Section 2.4, “Initial Startup of MySQL Cluster”.

For more detailed information about the available MySQL Cluster configuration parameters and their uses, see Section 3.4, “MySQL Cluster Configuration Files”, and Chapter 3, MySQL Cluster Configuration. For configuration of MySQL Cluster as relates to making backups, see Section 7.3.3, “Configuration for MySQL Cluster Backups”.

Starting the cluster is not very difficult after it has been configured. Each cluster node process must be started separately, and on the host where it resides. The management node should be started first, followed by the data nodes, and then finally by any SQL nodes:

If all has gone well, and the cluster has been set up correctly, the cluster should now be operational. You can test this by invoking the ndb_mgm management node client. The output should look like that shown here, although you might see some slight differences in the output depending upon the exact version of MySQL that you are using:

shell> ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.0.30  (Version: 5.1.34-ndb-6.3.26, Nodegroup: 0, Master)
id=3    @192.168.0.40  (Version: 5.1.34-ndb-6.3.26, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.10  (Version: 5.1.34-ndb-6.3.26)
[mysqld(API)]   1 node(s)
id=4    @192.168.0.20  (Version: 5.1.34-ndb-6.3.26)

The SQL node is referenced here as [mysqld(API)], which reflects the fact that the mysqld process is acting as a MySQL Cluster API node.

You should now be ready to work with databases, tables, and data in MySQL Cluster. See Section 2.5, “Loading Sample Data into MySQL Cluster and Performing Queries”, for a brief discussion.

Working with data in MySQL Cluster is not much different from doing so in MySQL without Cluster. There are two points to keep in mind:

If you are importing tables from an existing database using the output of mysqldump, you can open the SQL script in a text editor and add the ENGINE option to any table creation statements, or replace any existing ENGINE (or TYPE) options. Suppose that you have the world sample database on another MySQL server that does not support MySQL Cluster, and you want to export the City table:

shell> mysqldump --add-drop-table world City > city_table.sql

The resulting city_table.sql file will contain this table creation statement (and the INSERT statements necessary to import the table data):

DROP TABLE IF EXISTS `City`;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);
(remaining INSERT statements omitted)

You need to make sure that MySQL uses the NDBCLUSTER storage engine for this table. There are two ways that this can be accomplished. One of these is to modify the table definition before importing it into the Cluster database. Using the City table as an example, modify the ENGINE option of the definition as follows:

DROP TABLE IF EXISTS `City`;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);
(remaining INSERT statements omitted)

This must be done for the definition of each table that is to be part of the clustered database. The easiest way to accomplish this is to do a search-and-replace on the file that contains the definitions and replace all instances of TYPE=engine_name or ENGINE=engine_name with ENGINE=NDBCLUSTER. If you do not want to modify the file, you can use the unmodified file to create the tables, and then use ALTER TABLE to change their storage engine. The particulars are given later in this section.

Assuming that you have already created a database named world on the SQL node of the cluster, you can then use the mysql command-line client to read city_table.sql, and create and populate the corresponding table in the usual manner:

shell> mysql world < city_table.sql

It is very important to keep in mind that the preceding command must be executed on the host where the SQL node is running (in this case, on the machine with the IP address 192.168.0.20).

To create a copy of the entire world database on the SQL node, use mysqldump on the noncluster server to export the database to a file named world.sql; for example, in the /tmp directory. Then modify the table definitions as just described and import the file into the SQL node of the cluster like this:

shell> mysql world < /tmp/world.sql

If you save the file to a different location, adjust the preceding instructions accordingly.

Running SELECT queries on the SQL node is no different from running them on any other instance of a MySQL server. To run queries from the command line, you first need to log in to the MySQL Monitor in the usual way (specify the root password at the Enter password: prompt):

shell> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.34-ndb-6.2.18
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

We simply use the MySQL server's root account and assume that you have followed the standard security precautions for installing a MySQL server, including setting a strong root password. For more information, see Securing the Initial MySQL Accounts.

It is worth taking into account that Cluster nodes do not make use of the MySQL privilege system when accessing one another. Setting or changing MySQL user accounts (including the root account) effects only applications that access the SQL node, not interaction between nodes. See Section 8.2, “MySQL Cluster and MySQL Privileges”, for more information.

If you did not modify the ENGINE clauses in the table definitions prior to importing the SQL script, you should run the following statements at this point:

mysql> USE world;
mysql> ALTER TABLE City ENGINE=NDBCLUSTER;
mysql> ALTER TABLE Country ENGINE=NDBCLUSTER;
mysql> ALTER TABLE CountryLanguage ENGINE=NDBCLUSTER;

Selecting a database and running a SELECT query against a table in that database is also accomplished in the usual manner, as is exiting the MySQL Monitor:

mysql> USE world;
mysql> SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5;
+-----------+------------+
| Name      | Population |
+-----------+------------+
| Bombay    |   10500000 |
| Seoul     |    9981619 |
| São Paulo |    9968485 |
| Shanghai  |    9696300 |
| Jakarta   |    9604900 |
+-----------+------------+
5 rows in set (0.34 sec)
mysql> \q
Bye
shell>

Applications that use MySQL can employ standard APIs to access NDB tables. It is important to remember that your application must access the SQL node, and not the management or data nodes. This brief example shows how we might execute the SELECT statement just shown by using the PHP 5.X mysqli extension running on a Web server elsewhere on the network:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <meta http-equiv="Content-Type"
        content="text/html; charset=iso-8859-1">
  <title>SIMPLE mysqli SELECT</title>
</head>
<body>
<?php
  # connect to SQL node:
  $link = new mysqli('192.168.0.20', 'root', 'root_password', 'world');
  # parameters for mysqli constructor are:
  #   host, user, password, database
  if( mysqli_connect_errno() )
    die("Connect failed: " . mysqli_connect_error());
  $query = "SELECT Name, Population
            FROM City
            ORDER BY Population DESC
            LIMIT 5";
  # if no errors...
  if( $result = $link->query($query) )
  {
?>
<table border="1" width="40%" cellpadding="4" cellspacing ="1">
  <tbody>
  <tr>
    <th width="10%">City</th>
    <th>Population</th>
  </tr>
<?
    # then display the results...
    while($row = $result->fetch_object())
      printf("<tr>\n  <td align=\"center\">%s</td><td>%d</td>\n</tr>\n",
              $row->Name, $row->Population);
?>
  </tbody
</table>
<?
  # ...and verify the number of rows that were retrieved
    printf("<p>Affected rows: %d</p>\n", $link->affected_rows);
  }
  else
    # otherwise, tell us what went wrong
    echo mysqli_error();
  # free the result set and the mysqli connection object
  $result->close();
  $link->close();
?>
</body>
</html>

We assume that the process running on the Web server can reach the IP address of the SQL node.

In a similar fashion, you can use the MySQL C API, Perl-DBI, Python-mysql, or MySQL AB's own Connectors to perform the tasks of data definition and manipulation just as you would normally with MySQL.

To shut down the cluster, enter the following command in a shell on the machine hosting the management node:

shell> ndb_mgm -e shutdown

The -e option here is used to pass a command to the ndb_mgm client from the shell. (See Section 6.23, “Options Common to MySQL Cluster Programs”, for more information about this option.) The command causes the ndb_mgm, ndb_mgmd, and any ndbd processes to terminate gracefully. Any SQL nodes can be terminated using mysqladmin shutdown and other means.

To restart the cluster, run these commands:

In a production setting, it is usually not desirable to shut down the cluster completely. In many cases, even when making configuration changes, or performing upgrades to the cluster hardware or software (or both), which require shutting down individual host machines, it is possible to do so without shutting down the cluster as a whole by performing a rolling restart of the cluster. For more information about doing this, see Section 5.1, “Performing a Rolling Restart of a MySQL Cluster”.