Chapter 18 MySQL Cluster NDB 7.5

NDBCLUSTER (also known as NDB) is an in-memory storage engine offering high-availability and data-persistence features.

The NDBCLUSTER storage engine can be configured with a range of failover and load-balancing options, but it is easiest to start with the storage engine at the cluster level. MySQL Cluster's NDB storage engine contains a complete set of data, dependent only on other data within the cluster itself.

The “Cluster” portion of MySQL Cluster is configured independently of the MySQL servers. In a MySQL Cluster, each part of the cluster is considered to be a node.

There are three types of cluster nodes, and in a minimal MySQL Cluster configuration, there will be at least three nodes, one of each of these types:

For a brief introduction to the relationships between nodes, node groups, replicas, and partitions in MySQL Cluster, see Section 18.1.2, “MySQL Cluster Nodes, Node Groups, Replicas, and Partitions”.

Configuration of a cluster involves configuring each individual node in the cluster and setting up individual communication links between nodes. MySQL Cluster is currently designed with the intention that data nodes are homogeneous in terms of processor power, memory space, and bandwidth. In addition, to provide a single point of configuration, all configuration data for the cluster as a whole is located in one configuration file.

The management server manages the cluster configuration file and the cluster log. Each node in the cluster retrieves the configuration data from the management server, and so requires a way to determine where the management server resides. When interesting events occur in the data nodes, the nodes transfer information about these events to the management server, which then writes the information to the cluster log.

In addition, there can be any number of cluster client processes or applications. These include standard MySQL clients, NDB-specific API programs, and management clients. These are described in the next few paragraphs.

Standard MySQL clients.  MySQL Cluster can be used with existing MySQL applications written in PHP, Perl, C, C++, Java, Python, Ruby, and so on. Such client applications send SQL statements to and receive responses from MySQL servers acting as MySQL Cluster SQL nodes in much the same way that they interact with standalone MySQL servers.

MySQL clients using a MySQL Cluster as a data source can be modified to take advantage of the ability to connect with multiple MySQL servers to achieve load balancing and failover. For example, Java clients using Connector/J 5.0.6 and later can use jdbc:mysql:loadbalance:// URLs (improved in Connector/J 5.1.7) to achieve load balancing transparently; for more information about using Connector/J with MySQL Cluster, see Using Connector/J with MySQL Cluster.

NDB client programs.  Client programs can be written that access MySQL Cluster data directly from the NDBCLUSTER storage engine, bypassing any MySQL Servers that may be connected to the cluster, using the NDB API, a high-level C++ API. Such applications may be useful for specialized purposes where an SQL interface to the data is not needed. For more information, see The NDB API.

NDB-specific Java applications can also be written for MySQL Cluster using the MySQL Cluster Connector for Java. This MySQL Cluster Connector includes ClusterJ, a high-level database API similar to object-relational mapping persistence frameworks such as Hibernate and JPA that connect directly to NDBCLUSTER, and so does not require access to a MySQL Server. Support is also provided in MySQL Cluster ClusterJPA, an OpenJPA implementation for MySQL Cluster that leverages the strengths of ClusterJ and JDBC; ID lookups and other fast operations are performed using ClusterJ (bypassing the MySQL Server), while more complex queries that can benefit from MySQL's query optimizer are sent through the MySQL Server, using JDBC. See Java and MySQL Cluster, and The ClusterJ API and Data Object Model, for more information.

MySQL Cluster also supports applications written in JavaScript using Node.js. The MySQL Connector for JavaScript includes adapters for direct access to the NDB storage engine and as well as for the MySQL Server. Applications using this Connector are typically event-driven and use a domain object model similar in many ways to that employed by ClusterJ. For more information, see MySQL NoSQL Connector for JavaScript.

The Memcache API for MySQL Cluster, implemented as the loadable ndbmemcache storage engine for memcached version 1.6 and later, can be used to provide a persistent MySQL Cluster data store, accessed using the memcache protocol.

The standard memcached caching engine is included in the MySQL Cluster NDB 7.5 distribution. Each memcached server has direct access to data stored in MySQL Cluster, but is also able to cache data locally and to serve (some) requests from this local cache.

For more information, see ndbmemcache—Memcache API for MySQL Cluster.

Management clients.  These clients connect to the management server and provide commands for starting and stopping nodes gracefully, starting and stopping message tracing (debug versions only), showing node versions and status, starting and stopping backups, and so on. An example of this type of program is the ndb_mgm management client supplied with MySQL Cluster (see Section 18.4.5, “ndb_mgm — The MySQL Cluster Management Client”). Such applications can be written using the MGM API, a C-language API that communicates directly with one or more MySQL Cluster management servers. For more information, see The MGM API.

Oracle also makes available MySQL Cluster Manager, which provides an advanced command-line interface simplifying many complex MySQL Cluster management tasks, such restarting a MySQL Cluster with a large number of nodes. The MySQL Cluster Manager client also supports commands for getting and setting the values of most node configuration parameters as well as mysqld server options and variables relating to MySQL Cluster. See MySQL™ Cluster Manager 1.4.0 User Manual, for more information.

Event logs.  MySQL Cluster logs events by category (startup, shutdown, errors, checkpoints, and so on), priority, and severity. A complete listing of all reportable events may be found in Section 18.5.6, “Event Reports Generated in MySQL Cluster”. Event logs are of the two types listed here:

Checkpoint.  Generally speaking, when data is saved to disk, it is said that a checkpoint has been reached. More specific to MySQL Cluster, a checkpoint is a point in time where all committed transactions are stored on disk. With regard to the NDB storage engine, there are two types of checkpoints which work together to ensure that a consistent view of the cluster's data is maintained. These are shown in the following list:

For more information about the files and directories created by local checkpoints and global checkpoints, see MySQL Cluster Data Node File System Directory Files.

This section discusses the manner in which MySQL Cluster divides and duplicates data for storage.

A number of concepts central to an understanding of this topic are discussed in the next few paragraphs.

(Data) Node.  An ndbd process, which stores a replica —that is, a copy of the partition (see below) assigned to the node group of which the node is a member.

Each data node should be located on a separate computer. While it is also possible to host multiple ndbd processes on a single computer, such a configuration is not supported.

It is common for the terms “node” and “data node” to be used interchangeably when referring to an ndbd process; where mentioned, management nodes (ndb_mgmd processes) and SQL nodes (mysqld processes) are specified as such in this discussion.

Node Group.  A node group consists of one or more nodes, and stores partitions, or sets of replicas (see next item).

The number of node groups in a MySQL Cluster is not directly configurable; it is a function of the number of data nodes and of the number of replicas (NoOfReplicas configuration parameter), as shown here:

[number_of_node_groups] = number_of_data_nodes / NoOfReplicas

Thus, a MySQL Cluster with 4 data nodes has 4 node groups if NoOfReplicas is set to 1 in the config.ini file, 2 node groups if NoOfReplicas is set to 2, and 1 node group if NoOfReplicas is set to 4. Replicas are discussed later in this section; for more information about NoOfReplicas, see Section 18.3.3.6, “Defining MySQL Cluster Data Nodes”.

You can add new node groups (and thus new data nodes) online, to a running MySQL Cluster; see Section 18.5.14, “Adding MySQL Cluster Data Nodes Online”, for more information.

Partition.  This is a portion of the data stored by the cluster. There are as many cluster partitions as nodes participating in the cluster. Each node is responsible for keeping at least one copy of any partitions assigned to it (that is, at least one replica) available to the cluster.

A replica belongs entirely to a single node; a node can (and usually does) store several replicas.

NDB and user-defined partitioning.  MySQL Cluster normally partitions NDBCLUSTER tables automatically. However, it is also possible to employ user-defined partitioning with NDBCLUSTER tables. This is subject to the following limitations:

For more information relating to MySQL Cluster and user-defined partitioning, see Section 18.1.6, “Known Limitations of MySQL Cluster”, and Section 19.6.2, “Partitioning Limitations Relating to Storage Engines”.

Replica.  This is a copy of a cluster partition. Each node in a node group stores a replica. Also sometimes known as a partition replica. The number of replicas is equal to the number of nodes per node group.

The following diagram illustrates a MySQL Cluster with four data nodes, arranged in two node groups of two nodes each; nodes 1 and 2 belong to node group 0, and nodes 3 and 4 belong to node group 1.

Figure 18.2 MySQL Cluster with Two Node Groups

The data stored by the cluster is divided into four partitions, numbered 0, 1, 2, and 3. Each partition is stored—in multiple copies—on the same node group. Partitions are stored on alternate node groups as follows:

What this means regarding the continued operation of a MySQL Cluster is this: so long as each node group participating in the cluster has at least one node operating, the cluster has a complete copy of all data and remains viable. This is illustrated in the next diagram.

Figure 18.3 Nodes Required for a 2x2 Cluster

In this example, where the cluster consists of two node groups of two nodes each, any combination of at least one node in node group 0 and at least one node in node group 1 is sufficient to keep the cluster “alive” (indicated by arrows in the diagram). However, if both nodes from either node group fail, the remaining two nodes are not sufficient (shown by the arrows marked out with an X); in either case, the cluster has lost an entire partition and so can no longer provide access to a complete set of all cluster data.

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 Section 18.5.13, “MySQL Cluster Disk Data Tables”, for more information about these.) Naturally, multiple and faster CPUs can enhance performance. Memory requirements for other MySQL Cluster processes are relatively small.

The software requirements for MySQL 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 Cluster. It is not strictly necessary to compile MySQL yourself merely to be able to use MySQL Cluster. We assume that you are using the binaries appropriate to your platform, available from the MySQL Cluster software downloads page at http://dev.mysql.com/downloads/cluster/.

For communication between nodes, MySQL 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 machines not forming part of the cluster for the following reasons:

Network communication and latency.  MySQL Cluster requires communication between data nodes and API nodes (including SQL nodes), as well as between data nodes and other data nodes, to execute queries and updates. Communication latency between these processes can directly affect the observed performance and latency of user queries. In addition, to maintain consistency and service despite the silent failure of nodes, MySQL Cluster uses heartbeating and timeout mechanisms which treat an extended loss of communication from a node as node failure. This can lead to reduced redundancy. Recall that, to maintain data consistency, a MySQL Cluster shuts down when the last node in a node group fails. Thus, to avoid increasing the risk of a forced shutdown, breaks in communication between nodes should be avoided wherever possible.

The failure of a data or API node results in the abort of all uncommitted transactions involving the failed node. Data node recovery requires synchronization of the failed node's data from a surviving data node, and re-establishment of disk-based redo and checkpoint logs, before the data node returns to service. This recovery can take some time, during which the Cluster operates with reduced redundancy.

Heartbeating relies on timely generation of heartbeat signals by all nodes. This may not be possible if the node is overloaded, has insufficient machine CPU due to sharing with other programs, or is experiencing delays due to swapping. If heartbeat generation is sufficiently delayed, other nodes treat the node that is slow to respond as failed.

This treatment of a slow node as a failed one may or may not be desirable in some circumstances, depending on the impact of the node's slowed operation on the rest of the cluster. When setting timeout values such as HeartbeatIntervalDbDb and HeartbeatIntervalDbApi for MySQL Cluster, care must be taken care to achieve quick detection, failover, and return to service, while avoiding potentially expensive false positives.

Where communication latencies between data nodes are expected to be higher than would be expected in a LAN environment (on the order of 100 µs), timeout parameters must be increased to ensure that any allowed periods of latency periods are well within configured timeouts. Increasing timeouts in this way has a corresponding effect on the worst-case time to detect failure and therefore time to service recovery.

LAN environments can typically be configured with stable low latency, and such that they can provide redundancy with fast failover. Individual link failures can be recovered from with minimal and controlled latency visible at the TCP level (where MySQL Cluster normally operates). WAN environments may offer a range of latencies, as well as redundancy with slower failover times. Individual link failures may require route changes to propagate before end-to-end connectivity is restored. At the TCP level this can appear as large latencies on individual channels. The worst-case observed TCP latency in these scenarios is related to the worst-case time for the IP layer to reroute around the failures.

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

In this section, we describe changes in the implementation of MySQL Cluster in MySQL MySQL Cluster NDB 7.5 as compared to MySQL Cluster NDB 7.4 and earlier release series. Changes and features most likely to be of interest are shown in the following list:

MySQL Server offers a number of choices in storage engines. Since both NDB and InnoDB can serve as transactional MySQL storage engines, users of MySQL Server sometimes become interested in MySQL Cluster. They see NDB as a possible alternative or upgrade to the default InnoDB storage engine in MySQL 5.7. While NDB and InnoDB share common characteristics, there are differences in architecture and implementation, so that some existing MySQL Server applications and usage scenarios can be a good fit for MySQL Cluster, but not all of them.

In this section, we discuss and compare some characteristics of the NDB storage engine used by MySQL Cluster NDB 7.5 with InnoDB used in MySQL 5.7. The next few sections provide a technical comparison. In many instances, decisions about when and where to use MySQL Cluster must be made on a case-by-case basis, taking all factors into consideration. While it is beyond the scope of this documentation to provide specifics for every conceivable usage scenario, we also attempt to offer some very general guidance on the relative suitability of some common types of applications for NDB as opposed to InnoDB backends.

MySQL Cluster NDB 7.5 uses a mysqld based on MySQL 5.7, including support for InnoDB 1.1. While it is possible to use InnoDB tables with MySQL Cluster, such tables are not clustered. It is also not possible to use programs or libraries from a MySQL Cluster NDB 7.5 distribution with MySQL Server 5.7, or the reverse.

While it is also true that some types of common business applications can be run either on MySQL Cluster or on MySQL Server (most likely using the InnoDB storage engine), there are some important architectural and implementation differences. Section 18.1.5.1, “Differences Between the NDB and InnoDB Storage Engines”, provides a summary of the these differences. Due to the differences, some usage scenarios are clearly more suitable for one engine or the other; see Section 18.1.5.2, “NDB and InnoDB Workloads”. This in turn has an impact on the types of applications that better suited for use with NDB or InnoDB. See Section 18.1.5.3, “NDB and InnoDB Feature Usage Summary”, for a comparison of the relative suitability of each for use in common types of database applications.

For information about the relative characteristics of the NDB and MEMORY storage engines, see When to Use MEMORY or MySQL Cluster.

See Chapter 15, Alternative Storage Engines, for additional information about MySQL storage engines.

In the sections that follow, we discuss known limitations in current releases of MySQL Cluster as compared with the features available when using the MyISAM and InnoDB storage engines. If you check the “Cluster” category in the MySQL bugs database at http://bugs.mysql.com, you can find known bugs in the following categories under “MySQL Server:” in the MySQL bugs database at http://bugs.mysql.com, which we intend to correct in upcoming releases of MySQL Cluster:

This information is intended to be complete with respect to the conditions just set forth. You can report any discrepancies that you encounter to the MySQL bugs database using the instructions given in Section 1.7, “How to Report Bugs or Problems”. If we do not plan to fix the problem in MySQL Cluster NDB 7.5, we will add it to the list.

See Previous MySQL Cluster Issues Resolved in MySQL Cluster NDB 7.3 for a list of issues in earlier releases that have been resolved in MySQL Cluster NDB 7.5.

This section describes the web-based graphical configuration installer included as part of the MySQL Cluster distribution. Topics discussed include an overview of the installer and its parts, software and other requirements for running the installer, navigating the GUI, and using the installer to set up and start or stop a MySQL Cluster on one or more host computers.

18.2.1.2 MySQL Cluster Auto-Installer Overview

The MySQL Cluster Auto-Installer is made up of two components. The front end is a GUI client implemented as a Web page that loads and runs in a standard Web browser such as Firefox or Microsoft Internet Explorer (see Section 18.2.1.1, “MySQL Cluster Auto-Installer Requirements”). The back end is a server process (ndb_setup.py) that runs on the local machine or on another host to which you have access.

These two components (client and server) communicate with each other using standard HTTP requests and responses. The back end can manage MySQL Cluster software programs on any host where the back end user has granted access. If the MySQL Cluster software is on a different host, the back end relies on SSH for access, using the Paramiko library for executing commands remotely (see Section 18.2.1.1, “MySQL Cluster Auto-Installer Requirements”).

The remainder of this section is concerned primarily with the Web client. For more information about using the command-line tool, see Section 18.4.23, “ndb_setup.py — Start browser-based Auto-Installer for MySQL Cluster”.

MySQL Cluster Auto-Installer Interface.  This section describes the layout and navigation of the MySQL Cluster Auto-Installer, whose Welcome screen looks similar to what is shown here when it is first opened in the Web browser:

Figure 18.5 Welcome Screen For MySQL Cluster Auto-Installer

You can access the installer UI by selecting either of the options Create New MySQL Cluster or Continue Previous Cluster Configuration. A typical screen in the Auto-Installer includes the following elements:

1. Display panel.  The central area where data regarding configuration settings and controls for changing them are displayed.

2. Breadcrumb navigation.  Located in the top left and top center of the GUI, the breadcrumb navigation bar consists of a series of titles linking to screens that correspond to steps in the configuration of a MySQL Cluster. The breadcrumb allows you to jump between these stages in arbritrary order.

3. Sequential navigation.  This consists of a set of buttons labelled Previous, Next, and Finished, and can be found in the lower right-hand corner of the GUI. The sequential navigation is used to move between steps in the suggested order.

4. Settings and Help menus.  These menus can be found in the top right corner of the GUI (to the right of the breadcrumb navigation bar). Settings provides a way check and possibly alter configuration settings for the Auto-Installer; Help can be used to access the installer's built-in help files.

The locations of the elements just described are shown here in a typical page in the Auto-Installer; the numbers superimposed thereupon correspond to those used in the preceding list.

Figure 18.6 Layout of the MySQL Cluster Auto-Installer GUI

All of these elements except for the display panel are described in greater detail in the remainder of this section. Section 18.2.1.3, “Using the MySQL Cluster Auto-Installer”, describes the panels shown in the display area as well as the functionality of each panel and the controls it contains.

Arbitrary and sequential navigation.  The Auto-Installer can display any of a number of pages covering different stages in the setup and configuration of a MySQL Cluster deployment. You can navigate between pages in either of two ways. The first of these is the breadcrumb trail navigation toolbar displaying the titles of the various pages (in which the title of the current page is highlighted and disabled). From these, any desired page, in any desired order, can be reached by selecting the title of the corresponding page. This toolbar is shown here:

Figure 18.7 Detail of MySQL Cluster Auto-Installer breadcrumb navigation, showing page titles/links

The second navigation mechanism provided by the Auto-Installer consists of the Next, Previous, and Finish sequential navigation buttons at the bottom right of the page. These can be used to move to the next or previous page in predetermined order, or to go to the very last page. The buttons are enabled and disabled as needed, so that you cannot, for example, advance beyond the last page.

Settings and Help menus.  These menus are positioned adjacent to one another in the top right corner of the GUI, as shown earlier in this section. The Settings menu is shown here in more detail:

Figure 18.8 MySQL Cluster Auto-Installer Settings menu detail

The entries in the Settings menu are described here, in the following list:

• Clear configuration and restart: Remove all hosts and processes; reset all parameter values to their defaults; start the installer over at the first page.

• Automatically save configuration as cookies: Save your configuration information—such as host names, process data, and parameter values—as a cookie in the browser. When this option is chosen, all information except any SSH password is saved. This means that you can quit and restart the browser, and continue working on the same configuration from where you left off at the end of the previous session).

  Since the SSH password is never saved, you must supply this once again at the beginning of a new session, if one is used.

• Show advanced configuration options: Show advanced configuration parameters in the Auto-Installer and make these settable by the user.

  Once set, the advanced parameters continue to be used in the configuration file until they are explicitly changed or reset. This is regardless of whether the advanced parameters are currently visible in the installer; in other words, disabling the menu item does not reset the values of any of these parameters.

• Automatically get resource information for new hosts: Query new hosts automatically for hardware resource information to pre-populate a number of configuration options and values. In this case, the suggested values are not mandatory, but they are used unless explicitly changed using the appropriate editing options in the installer.

As with the installer's navigation elements, one or more of the entries in the Settings menu may be disabled due to choices you have made previously.

The Help menu is shown here, as it appears when expanded:

Figure 18.9 The MySQL Cluster Auto-Installer Help menu, expanded

The Help menu provides several options, described in the following list:

• Content: Show the built-in user guide. This is opened in a separate browser window, so that it can be used simultaneously with the installer without interrupting workflow.

• Current page: Open the built-in user guide to the section describing the page currently displayed in the installer.

• About: This will show a small dialog displaying the installer name and the version number of the MySQL Cluster distribution it was supplied with, similar to what is shown here:

  Figure 18.10 The MySQL Cluster Auto-Installer About dialog

  
  
  

The Auto-Installer also provides context-sensitive help in the form of tooltips for most input widgets. One of these tooltips is displayed when the mouse hovers over a widget or the small question mark which can sometimes appear next to a widget label.

In addition, the names of MySQL Cluster configuration parameters are linked to their descriptions in the online MySQL Cluster documentation, so that if you click on the name of a given parameter, the documentation for that parameter is shown in a separate window.

18.2.1.3 Using the MySQL Cluster Auto-Installer

• Section 18.2.1.3.1, “Starting the MySQL Cluster Auto-Installer”

• Section 18.2.1.3.2, “MySQL Cluster Auto-Installer Welcome Screen”

• Section 18.2.1.3.3, “MySQL Cluster Auto-Installer Define Cluster Screen”

• Section 18.2.1.3.4, “MySQL Cluster Auto-Installer Define Hosts Screen”

• Section 18.2.1.3.5, “MySQL Cluster Auto-Installer Define Processes Screen”

• Section 18.2.1.3.6, “MySQL Cluster Auto-Installer Define Attributes Screen”

• Section 18.2.1.3.7, “MySQL Cluster Auto-Installer Deploy Cluster Screen”

The MySQL Cluster Auto-Installer consists of several pages, each corresponding to a step in the process used to configure and deploy a MySQL Cluster, and listed here:

• Welcome: Begin using the Auto-Installer by choosing either to configure a new MySQL Cluster, or to continue configuring an existing one.

• Define Cluster: Set basic information about the cluster as a whole, such as name, hosts, and load type. Here you can also set the SSH authentication type for accessing remote hosts, if needed.

• Define Hosts: Identify the hosts where you intend to run MySQL Cluster processes.

• Define Processes: Assign one or more processes of a given type or types to each cluster host.

• Define Attributes: Set configuration attributes for processes or types of processes.

• Deploy Cluster: Deploy the cluster with the configuration set previously; start and stop the deployed cluster.

The following sections describe in greater detail the purpose and function of each of these pages, in the order just listed.

18.2.1.3.1 Starting the MySQL Cluster Auto-Installer

The Auto-Installer is provided together with the MySQL Cluster software. (See Section 18.2, “MySQL Cluster Installation”.) The present section explains how to start the installer. You can do by invoking the ndb_setup.py executable. ndb_setup.py is found in the bin within the MySQL Cluster installation directory; a typical location might be /usr/local/mysql/bin on a Linux system or C:\Program Files\MySQL\MySQL Server 5.6\bin on a Windows system, but this can vary according to where the MySQL Cluster software is installed on your system.

On Windows, you can also start the installer by running setup.bat in the MySQL Cluster installation directory. When invoked from the command line, it accepts the same options as does ndb_setup.py.

ndb_setup.py can be started with any of several options that affect its operation, but it is usually sufficient to allow the default settings be used, in which case you can start ndb_setup.py by either of the following two methods:

1. Navigate to the MySQL Cluster bin directory in a terminal and invoke it from the command line, without any additional arguments or options, like this:

   shell> ndb_setup
   

   This works regardless of operating platform.

2. Navigate to the MySQL Cluster bin directory in a file browser (such Windows Explorer on Windows, or Konqueror, Dolphin, or Nautilus on Linux) and activate (usually by double-clicking) the ndb_setup.py file icon. This works on Windows, and should work with most common Linux desktops as well.

   On Windows, you can also navigate to the MySQL Cluster installation directory and activate the setup.bat file icon.

In either case, once ndb_setup.py is invoked, the Auto-Installer's Welcome screen should open in the system's default Web browser.

In some cases, you may wish to use non-default settings for the installer, such as specifying a different port for the Auto-Installer's included Web server to run on, in which case you must invoke ndb_setup.py with one or more startup options with values overriding the necessary defaults. The same startup options can be used on Windows systems with the setup.bat file supplied for such platforms in the MySQL Cluster software distribution. This can be done using the command line, but if you want or need to start the installer from a desktop or file browser while emplying one or more of these options, it is also possible to create a script or batch file containing the proper invocation, then to double-click its file icon in the file browser to start the installer. (On Linux systems, you might also need to make the script file executable first.) For information about advanced startup options for the MySQL Cluster Auto-Installer, see Section 18.4.23, “ndb_setup.py — Start browser-based Auto-Installer for MySQL Cluster”.

18.2.1.3.2 MySQL Cluster Auto-Installer Welcome Screen

The Welcome screen is loaded in the default browser when ndb_setup.py is invoked, as shown here:

Figure 18.11 The MySQL Cluster Auto-Installer Welcome screen (Closeup)

This screen provides the following two choices for entering the installer, one of which must be selected to continue:

1. Create New MySQL Cluster: Start the Auto-Installer with a completely new cluster to be set up and deployed.

2. Continue Previous Cluster Configuration: Start the Auto-Installer at the same point where the previous session ended, with all previous settings preserved.

The second option requires that the browser be able to access its cookies from the previous session, as these provide the mechanism by which configuration and other information generated during a session is stored. In other words, to continue the previous session with the Auto-Installer, you must use the same web browser running on the same host as you did for the previous session.

18.2.1.3.3 MySQL Cluster Auto-Installer Define Cluster Screen

The Define Cluster screen is the first screen to appear following the choice made in the Welcome screen, and is used for setting general properties of the cluster. The layout of the Define Cluster screen is shown here:

Figure 18.12 The MySQL Cluster Auto-Installer Define Cluster screen

The Define Cluster screen allows you to set a number of general properties for the cluster, as described in this list:

• Cluster name: A name that identifies the cluster. The default is MyCluster.

• Host list: A comma-delimited list of one or more hosts where cluster processes should run. By default, this is 127.0.0.1. If you add remote hosts to the list, you must be able to connect to them using the SSH Credentials supplied.

• Application type: Choose one of the following:

  1. Simple testing: Minimal resource usage for small-scale testing. This the default. Not intended for production environments.

  2. Web: Maximize performance for the given hardware.

  3. Real-time: Maximize performance while maximizing sensitivity to timeouts in order to minimize the time needed to detect failed cluster processes.

• Write load: Choose a level for the anticipated number of writes for the cluster as a whole. You can choose any one of the following levels:

  1. Low: The expected load includes fewer than 100 write transactions for second.

  2. Medium: The expected load includes 100 to 1000 write transactions per second.

  3. High: The expected load includes more than 1000 write transactions per second.

• SSH Credentials: Choose Key-Based SSH or enter User and Password credentials. The SSH key or a user name with password is required for connecting to any remote hosts specified in the Host list. By default, Key-Based SSH is selected, and the User and Password fileds are blank.

18.2.1.3.4 MySQL Cluster Auto-Installer Define Hosts Screen

The Define Hosts screen, shown here, provides a means of viewing and specifying several key properties of each cluster host:

Figure 18.13 MySQL Cluster Define Hosts screen

The hosts currently entered are displayed in the grid with various pieces of information. You can add hosts by clicking the Add hosts button and entering a list of one or more comma-separated host names, IP addresses, or both (as when editing the host list on the Define Cluster screen).

Similarly, you can remove one or more hosts using the button labelled Remove selected host(s). When you remove a host in this fashion, any process which was configured for that host is also removed.

If Automatically get resource information for new hosts is checked in the Settings menu, the Auto-Installer attempts to retrieve the platform name, amount of memory, and number of CPU cores and to fill these in automatically. The status of this is displayed in the Resource info column. Fetching the information from remote hosts is not instantaneous and may take some time, particularly from remote hosts running Windows.

If the SSH user credentials on the Define Cluster screen are changed, the tool tries to refresh the hardware information from any hosts for which information is missing. However, if a given field has already been edited, the user-supplied information is not overwritten by any value fetched from that host.

The hardware resource information, platform name, installation directory, and data directory can be edited by the user by clicking the corresponding cell in the grid, by selecting one or more hosts and clicking the button labelled Edit selected host(s). This causes a dialog box to appear, in which these fields can be edited, as shown here:

Figure 18.14 MySQL Cluster Auto-Installer Edit Hosts dialog

When more than one host is selected, any edited values are applied to all selected hosts.

18.2.1.3.5 MySQL Cluster Auto-Installer Define Processes Screen

The Define Processes screen, shown here, provides a way to assign MySQL Cluster processes (nodes) to cluster hosts:

Figure 18.15 MySQL Cluster Auto-Installer Define Processes dialog

The left-hand portion of this screen contains a process tree showing cluster hosts and processes set up to run on each one. On the right is a panel which displays information about the item currently selected in the tree.

When this screen is accessed for the first time for a given cluster, a default set of processes is defined for you, based on the number of hosts. If you later return to the Define Hosts screen, remove all hosts, and add new hosts, this also causes a new default set of processes to be defined.

MySQL Cluster processes are of the following types:

• Management node.  Performs administrative tasks such as stopping individual data nodes, querying node and cluster status, and making backups. Executable: ndb_mgmd.

• Single-threaded data node.  Stores data and executes queries. Executable: ndbd.

• Multi threaded data node.  Stores data and executes queries with multiple worker threads executing in parallel. Executable: ndbmtd.

• SQL node.  MySQL server for executing SQL queries against NDB. Executable: mysqld.

• API node.  A client accessing data in NDB by means of the NDB API or other low-level client API, rather than by using SQL. See MySQL Cluster API Developer Guide, for more information.

For more information about process (node) types, see Section 18.1.1, “MySQL Cluster Core Concepts”.

Processes shown in the tree are numbered sequentially by type, for each host—for example, SQL node 1, SQL node 2, and so on—to simplify identification.

Each management node, data node, or SQL process must be assigned to a specific host, and is not allowed to run on any other host. An API node may be assigned to a single host, but this is not required. Instead, you can assign it to the special Any host entry which the tree also contains in addition to any other hosts, and which acts as a placeholder for processes that are allowed to run on any host. Only API processes may use this Any host entry.

Adding processes.  To add a new process to a given host, either right-click that host's entry in the tree, then select the Add process popup when it appears, or select a host in the process tree, and press the Add process button below the process tree. Performing either of these actions opens the add process dialog, as shown here:

Figure 18.16 MySQL Cluster Auto-Installer Add Process Dialog

Here you can select from among the available process types described earlier this section; you can also enter an arbitrary process name to take the place of the suggested value, if desired.

Removing processes.  To delete a process, right-click on a process in the tree and select delete process from the pop up menu that appears, or select a process, then use the delete process button below the process tree.

When a process is selected in the process tree, information about that process is displayed in the panel to the right of the tree, where you can change the process name and possibly its type. Important: Currently, you can change a single-threaded data node (ndbd) to a multi-threaded data node (ndbmtd), or the reverse, only; no other process type changes are allowed. If you want to make a change between any other process types, you must delete the original process first, then add a new process of the desired type.

18.2.1.3.6 MySQL Cluster Auto-Installer Define Attributes Screen

This screen has a layout similar to that of the Define Processes screen, with a process tree at the left. Unlike that screen's tree, the Define Attributes process tree is organized by process or node type, with single-threaded and multi-threaded data nodes considered to be of the same type for this purpose, in groups labelled Management Layer, Data Layer, SQL Layer, and API Layer. A panel to the right of this tree displays information regarding the item currently selected. The Define Attributes screen is shown here:

Figure 18.17 MySQL Cluster Auto-Installer Define Attributes screen

A checkbox labelled Show advanced configuration is located below the process tree. Checking this box makes advanced options visible in the information pane. These options are set and used whether or not they are visible.

You can edit attributes for a single process by selecting that process from the tree, or for all processes of the same type in the cluster by selecting one of the Layer folders. A per-process value set for a given attribute overrides any per-group setting for that attribute that would otherwise apply to the process in question. An example of such an information panel (for an SQL process) is shown here:

Figure 18.18 Define Attributes Detail With SQL Process Attributes

For some of the attributes shown in the information panel, a button bearing a plus sign is displayed to the right, which means that the value of this attribute can be overridden. This + button activates an input widget for the attribute, enabling you to change its value. When the value has been overridden, this button changes into a button showing an X, as shown here:

Figure 18.19 Define Attributes Detail, Overriding Attribute Default Value

Clicking the X button next to an attribute undoes any changes made to it; it immediately reverts to the predefined value.

All configuration attributes have predefined values calculated by the installer, based such factors as host name, node ID, node type, and so on. In most cases, these values may be left as they are. If you are not familiar with it already, it is highly recommended that you read the applicable documentation before making changes to any of the attribute values. To make finding this information easier, each attribute name shown in the information panel is linked to its description in the online MySQL Cluster documentation.

18.2.1.3.7 MySQL Cluster Auto-Installer Deploy Cluster Screen

This screen allows you to perform the following tasks:

• Review process startup commands and configuration files to be applied

• Distribute configuration files by creating any necessary files and directories on all cluster hosts—that is, deploy the cluster as presently configured

• Start and stop the cluster

The Deploy Cluster screen is shown here:

Figure 18.20 MySQL Cluster Auto-Installer Deploy Cluster screen

Like the Define Attributes screen, this screens features a process tree, organized by process type, on the left hand side. Next to each process is a status icon whose color indicates the current status of the process: green if it is running; yellow if it is starting or stopping; red if the process is stopped.

To the right of the process tree are two information panels, the upper panel showing the startup command or commands needed to start the selected process. (For some processes, more than one command may be required—for example, if initialization is necessary.) The lower panel shows the contents of the configuration file, if any, for the given process; currently, the management node process is only type of process having a configuration file. Other process types are configured using command-line parameters when starting the process, or by obtaining configuration information from the management nodes as needed in real time.

Three buttons are located immediately below the process tree. These are labelled as and perform the functions described in the following list:

• Deploy cluster: Verify that the configuration is valid. Create any directories required on the cluster hosts, and distribute the configuration files onto the hosts. A progress bar shows how far the deployment has proceeded.

• Start cluster: The cluster is deployed as with Deploy cluster, after which all cluster processes are started in the correct order.

  Starting these processes may take some time. If the estimated time to completion is too large, the installer provides an opportunity to cancel or to continue of the startup procedure. A progress bar indicates the current status of the startup procedure, as shown here:

  Figure 18.21 Progress Bar With Status of Node Startup Process

  
  
  

  The process status icons adjoining the process tree mentioned previously also update with the status of each process.

• Stop cluster: After the cluster has been started, you can stop it using the this. As with starting the cluster, cluster shutdown is not instantaneous, and may require some time complete. A progress bar, similar to that displayed during cluster startup, shows the approximate current status of the cluster shutdown procedure, as do the process status icons adjoining the process tree.

The Auto-Installer generates a my.cnf file containing the appropriate options for each mysqld process in the cluster.

This section covers installation methods for MySQL Cluster on Linux and other Unix-like operating systems. While the next few sections refer to a Linux operating system, the instructions and procedures given there should be easily adaptable to other supported Unix-like platforms. For manual installation and setup instructions specific to Windows systems, see Section 18.2.3, “Installing MySQL Cluster on Windows”.

Each MySQL Cluster host computer must have the correct executable programs installed. A host running an SQL node must have installed on it a MySQL Server binary (mysqld). Management nodes require the management server daemon (ndb_mgmd); data nodes require the data node daemon (ndbd or ndbmtd). It is not necessary to install the MySQL Server binary on management node hosts and data node hosts. It is recommended that you also install the management client (ndb_mgm) on the management server host.

Installation of MySQL Cluster on Linux can be done using precompiled binaries from Oracle (downloaded as a .tar.gz archive), with RPM packages (also available from Oracle), or from source code. All three of these installation methods are described in the section that follow.

Regardless of the method used, it is still necessary following installation of the MySQL Cluster binaries to create configuration files for all cluster nodes, before you can start the cluster. See Section 18.2.4, “Initial Configuration of MySQL Cluster”.

MySQL-Cluster-component-producttype-ndbversion.distribution.architecture.rpm

component:= {server | client [| other]}

producttype:= {gpl | advanced}

ndbversion:= major.minor.release

distribution:= {sles10 | rhel5 | el6}

architecture:= {i386 | x86_64}

shell> rpm -Uhv MySQL-Cluster-server-gpl-7.5.2-1.sles11.i386.rpm

shell> rpm -Uhv MySQL-Cluster-server-gpl-7.5.2-1.sles11.i386.rpm

shell> rpm -Uhv MySQL-Cluster-client-gpl-7.5.2-1.sles11.i386.rpm

shell> rpm -Uhv MySQL-Cluster-server-gpl-7.5.2-1.sles11.i386.rpm

mysql-cluster-gpl-ndbver-debiandebianver-arch.deb

shell> dpkg -i mysql-cluster-gpl-7.5.0-debian7-i686.deb

shell> dpkg -r mysql

This section describes installation procedures for MySQL Cluster on Windows hosts. MySQL Cluster NDB 7.5 binaries for Windows can be obtained from http://dev.mysql.com/downloads/cluster/. For information about installing MySQL Cluster on Windows from a binary release provided by Oracle, see Section 18.2.3.1, “Installing MySQL Cluster on Windows from a Binary Release”.

It is also possible to compile and install MySQL Cluster from source on Windows using Microsoft Visual Studio. For more information, see Section 18.2.3.2, “Compiling and Installing MySQL Cluster from Source on Windows”.

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

[mysql_cluster]
# Options for data node process:
ndb-connectstring=192.168.0.10  # location of management server

DataDir=C:\mysql\bin\cluster-logs

DataDir=C:\\mysql\\bin\\cluster-logs  # Escaped backslashes

DataDir=C:/mysql/bin/cluster-logs     # Forward slashes

C:\> C:\mysql\bin\ndb_mgmd.exe --install
Installing service 'MySQL Cluster Management Server' 
  as '"C:\mysql\bin\ndbd.exe" "--service=ndb_mgmd"'
Service successfully installed.

C:\> NET START ndb_mgmd
The MySQL Cluster Management Server service is starting.
The MySQL Cluster Management Server service was started successfully.

C:\> NET STOP ndb_mgmd
The MySQL Cluster Management Server service is stopping..
The MySQL Cluster Management Server service was stopped successfully.

C:\> NET START 'MySQL Cluster Management Server'
The MySQL Cluster Management Server service is starting.
The MySQL Cluster Management Server service was started successfully.

C:\> NET STOP  'MySQL Cluster Management Server'
The MySQL Cluster Management Server service is stopping..
The MySQL Cluster Management Server service was stopped successfully.

C:\> C:\mysql\bin\ndb_mgmd.exe --install=mgmd1
Installing service 'MySQL Cluster Management Server' 
  as '"C:\mysql\bin\ndb_mgmd.exe" "--service=mgmd1"'
Service successfully installed.

C:\> NET START mgmd1
The MySQL Cluster Management Server service is starting.
The MySQL Cluster Management Server service was started successfully.

C:\> NET STOP mgmd1
The MySQL Cluster Management Server service is stopping..
The MySQL Cluster Management Server service was stopped successfully.

C:\> C:\mysql\bin\ndb_mgmd.exe --remove
Removing service 'MySQL Cluster Management Server'
Service successfully removed.

C:\> C:\mysql\bin\ndb_mgmd.exe --remove=mgmd1
Removing service 'mgmd1'
Service successfully removed.

C:\> C:\mysql\bin\ndbd.exe --install
Installing service 'MySQL Cluster Data Node Daemon' as '"C:\mysql\bin\ndbd.exe" "--service=ndbd"'
Service successfully installed.

C:\> NET START ndbd
The MySQL Cluster Data Node Daemon service is starting.
The MySQL Cluster Data Node Daemon service was started successfully.

C:\> NET STOP ndbd
The MySQL Cluster Data Node Daemon service is stopping..
The MySQL Cluster Data Node Daemon service was stopped successfully.

C:\> NET START 'MySQL Cluster Data Node Daemon'
The MySQL Cluster Data Node Daemon service is starting.
The MySQL Cluster Data Node Daemon service was started successfully.

C:\> NET STOP 'MySQL Cluster Data Node Daemon'
The MySQL Cluster Data Node Daemon service is stopping..
The MySQL Cluster Data Node Daemon service was stopped successfully.

C:\> C:\mysql\bin\ndbd.exe --remove
Removing service 'MySQL Cluster Data Node Daemon'
Service successfully removed.

C:\> C:\mysql\bin\ndbd.exe --install=dnode1
Installing service 'dnode1' as '"C:\mysql\bin\ndbd.exe" "--service=dnode1"'
Service successfully installed.

C:\> NET START dnode1
The MySQL Cluster Data Node Daemon service is starting.
The MySQL Cluster Data Node Daemon service was started successfully.

C:\> NET STOP dnode1
The MySQL Cluster Data Node Daemon service is stopping..
The MySQL Cluster Data Node Daemon service was stopped successfully.

C:\> C:\mysql\bin\ndbd.exe --remove=dnode1
Removing service 'dnode1'
Service successfully removed.

In this section, we discuss manual configuration of an installed MySQL Cluster by creating and editing configuration files.

MySQL Cluster also provides a GUI installer which can be used to perform the configuration without the need to edit text files in a separate application. For more information, see Section 18.2.1, “The MySQL Cluster Auto-Installer”.

For our four-node, four-host MySQL Cluster (see Cluster nodes and host computers), it is necessary to write four configuration files, one per node host.

Configuring the data nodes 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:

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine

[mysql_cluster]
# Options for MySQL Cluster processes:
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:

[ndbd default]
# Options affecting ndbd processes on all data nodes:
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 default]
# TCP/IP options:
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 simply allow the default value to be used
                  # instead

[ndb_mgmd]
# Management process options:
hostname=192.168.0.10           # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

[ndbd]
# Options for data node "A":
                                # (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

[ndbd]
# Options for data node "B":
hostname=192.168.0.40           # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node's data files

[mysqld]
# SQL node options:
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 18.2.5, “Initial Startup of MySQL Cluster”.

For more detailed information about the available MySQL Cluster configuration parameters and their uses, see Section 18.3.3, “MySQL Cluster Configuration Files”, and Section 18.3, “Configuration of MySQL Cluster”. For configuration of MySQL Cluster as relates to making backups, see Section 18.5.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.7.11-ndb-7.5.2, Nodegroup: 0, *)
id=3    @192.168.0.40  (Version: 5.7.11-ndb-7.5.2, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.10  (Version: 5.7.11-ndb-7.5.2)

[mysqld(API)]   1 node(s)
id=4    @192.168.0.20  (Version: 5.7.11-ndb-7.5.2)

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 18.2.6, “MySQL Cluster Example with Tables and Data”, for a brief discussion.

Working with database tables and data in MySQL Cluster is not much different from doing so in standard MySQL. There are two key 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 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.7.11-ndb-7.5.2

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 Section 2.10.4, “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 18.5.12.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 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 18.4.27, “Options Common to MySQL Cluster Programs — Options Common to MySQL Cluster Programs”, for more information about this option.) The command causes the ndb_mgm, ndb_mgmd, and any ndbd or ndbmtd processes to terminate gracefully. Any SQL nodes can be terminated using mysqladmin shutdown and other means. On Windows platforms, assuming that you have installed the SQL node as a Windows service, you can use NET STOP MYSQL.

To restart the cluster on Unix platforms, run these commands:

On Windows platforms, assuming that you have installed all MySQL Cluster processes as Windows services using the default service names (see Section 18.2.3.4, “Installing MySQL Cluster Processes as Windows Services”), you can restart the cluster as follows:

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 18.5.5, “Performing a Rolling Restart of a MySQL Cluster”.

This section provides information about MySQL Cluster software and table file compatibility between different MySQL Cluster NDB 7.5 releases with regard to performing upgrades and downgrades as well as compatibility matrices and notes. You are expected already to be familiar with installing and configuring a MySQL Cluster prior to attempting an upgrade or downgrade. See Section 18.3, “Configuration of MySQL Cluster”.

To familiarize you with the basics, we will describe the simplest possible configuration for a functional MySQL Cluster. After this, you should be able to design your desired setup from the information provided in the other relevant sections of this chapter.

First, you need to create a configuration directory such as /var/lib/mysql-cluster, by executing the following command as the system root user:

shell> mkdir /var/lib/mysql-cluster

In this directory, create a file named config.ini that contains the following information. Substitute appropriate values for HostName and DataDir as necessary for your system.

# file "config.ini" - showing minimal setup consisting of 1 data node,
# 1 management server, and 3 MySQL servers.
# The empty default sections are not required, and are shown only for
# the sake of completeness.
# Data nodes must provide a hostname but MySQL Servers are not required
# to do so.
# If you don't know the hostname for your machine, use localhost.
# The DataDir parameter also has a default value, but it is recommended to
# set it explicitly.
# Note: [db], [api], and [mgm] are aliases for [ndbd], [mysqld], and [ndb_mgmd],
# respectively. [db] is deprecated and should not be used in new installations.

[ndbd default]
NoOfReplicas= 1

[mysqld  default]
[ndb_mgmd default]
[tcp default]

[ndb_mgmd]
HostName= myhost.example.com

[ndbd]
HostName= myhost.example.com
DataDir= /var/lib/mysql-cluster

[mysqld]
[mysqld]
[mysqld]

You can now start the ndb_mgmd management server. By default, it attempts to read the config.ini file in its current working directory, so change location into the directory where the file is located and then invoke ndb_mgmd:

shell> cd /var/lib/mysql-cluster
shell> ndb_mgmd

Then start a single data node by running ndbd:

shell> ndbd

For command-line options which can be used when starting ndbd, see Section 18.4.27, “Options Common to MySQL Cluster Programs — Options Common to MySQL Cluster Programs”.

By default, ndbd looks for the management server at localhost on port 1186.

Finally, change location to the MySQL data directory (usually /var/lib/mysql or /usr/local/mysql/data), and make sure that the my.cnf file contains the option necessary to enable the NDB storage engine:

[mysqld]
ndbcluster

You can now start the MySQL server as usual:

shell> mysqld_safe --user=mysql &

Wait a moment to make sure the MySQL server is running properly. If you see the notice mysql ended, check the server's .err file to find out what went wrong.

If all has gone well so far, you now can start using the cluster. Connect to the server and verify that the NDBCLUSTER storage engine is enabled:

shell> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.7.13

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW ENGINES\G
...
*************************** 12. row ***************************
Engine: NDBCLUSTER
Support: YES
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 13. row ***************************
Engine: NDB
Support: YES
Comment: Alias for NDBCLUSTER
...

The row numbers shown in the preceding example output may be different from those shown on your system, depending upon how your server is configured.

Try to create an NDBCLUSTER table:

shell> mysql
mysql> USE test;
Database changed

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

mysql> SHOW CREATE TABLE ctest \G
*************************** 1. row ***************************
       Table: ctest
Create Table: CREATE TABLE `ctest` (
  `i` int(11) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

To check that your nodes were set up properly, start the management client:

shell> ndb_mgm

Use the SHOW command from within the management client to obtain a report on the cluster's status:

ndb_mgm> SHOW
Cluster Configuration
---------------------
[ndbd(NDB)]     1 node(s)
id=2    @127.0.0.1  (Version: 5.7.11-ndb-7.5.2, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (Version: 5.7.11-ndb-7.5.2)

[mysqld(API)]   3 node(s)
id=3    @127.0.0.1  (Version: 5.7.11-ndb-7.5.2)
id=4 (not connected, accepting connect from any host)
id=5 (not connected, accepting connect from any host)

At this point, you have successfully set up a working MySQL Cluster. You can now store data in the cluster by using any table created with ENGINE=NDBCLUSTER or its alias ENGINE=NDB.

The next several sections provide summary tables of MySQL Cluster node configuration parameters used in the config.ini file to govern various aspects of node behavior, as well as of options and variables read by mysqld from a my.cnf file or from the command line when run as a MySQL Cluster process. Each of the node parameter tables lists the parameters for a given type (ndbd, ndb_mgmd, mysqld, computer, tcp, shm, or sci). All tables include the data type for the parameter, option, or variable, as well as its default, mimimum, and maximum values as applicable.

Considerations when restarting nodes.  For node parameters, these tables also indicate what type of restart is required (node restart or system restart)—and whether the restart must be done with --initial—to change the value of a given configuration parameter. When performing a node restart or an initial node restart, all of the cluster's data nodes must be restarted in turn (also referred to as a rolling restart). It is possible to update cluster configuration parameters marked as node online—that is, without shutting down the cluster—in this fashion. An initial node restart requires restarting each ndbd process with the --initial option.

A system restart requires a complete shutdown and restart of the entire cluster. An initial system restart requires taking a backup of the cluster, wiping the cluster file system after shutdown, and then restoring from the backup following the restart.

In any cluster restart, all of the cluster's management servers must be restarted for them to read the updated configuration parameter values.

Because some of these parameters can be used for configuring more than one type of cluster node, they may appear in more than one of the tables.