A basic MySQL Cluster setup

0
The easiest way to have MySQL Cluster installed on Linux is to get the official “server” RPM package and unpack it on every cluster node to be. Ta-dam, service binaries for all node types are deployed and ready to be used – right after you compiled a cluster configuration, created proper sys-V services, added firewall rules and tweaked selinux.

So it’s up to your cluster concept what service to run on each node – determining it’s node type. You can even start multiple services on a single server, of course.
Anyhow, you’ll need a decent set of configuration files. Let’s see a basic example.

Configuration

The so-called global cluster configuration file resides on the management node. It describes the whole cluster and can also contain parameters for every single member of it. It is picked up by ndb_mgmd and propagated to the other nodes.

 [ndbd default] NoOfReplicas=2 DataDir=/var/lib/mysql-cluster ServerPort=6603  [ndb_mgmd] hostname=ndb_mgm1  [ndbd] hostname=ndb_dn1  [ndbd] hostname=ndb_dn2  [mysqld] hostname=ndb_sql1  [mysqld] hostname=ndb_sql2  [api]

What we have here is a cluster of five nodes with one management node, two data nodes and two SQL nodes. The defined hostname parameters (can also contain IP addresses) ensure that each node slot can only be utilized by a single node.
The extra api section is present to enable ndb_mgm perform maintenance jobs (e.g. triggering backup of NDB data) on the cluster. The section header could also be mysqld – the result would be the same.
The ndb default section contains parameters common to all data nodes. NoOfReplicas determines that data will be replicated between the two data nodes.

NDB data nodes and SQL nodes use my.cnf as a local souce of configuration parameters. For a basic setup it’s only needed to define the management node to connect to and (on SQL nodes) to enable use of the ndbcluster storage engine.

 [mysqld] ndbcluster  [mysql_cluster] ndb_connectstring=ndb_mgm1

If you have firewalls between the nodes, ensure that ndb_mgmd can be connected on the default 1186 port and that ndbds are reachable by each other and by mysqlds. A port number of 6603 was set in the example to make this easier, because otherwise ndbds would listen for incoming connections on random ports.
If selinux is also enabled on your servers, you need to set the “mysql_connect_any” boolean’s value persistently:

 setsebool -P mysql_connect_any on

Starting and restarting a cluster

It is recommended to follow below order when starting or restarting an NDB cluster:

  • management nodes (ndb_mgmd service)
  • data nodes (ndbd service)
  • SQL/API nodes (mysqld service)

This ensures that the cluster configuration is propagated by the management node and that SQL nodes have available data nodes to connect to. Data nodes must be started simultaneously, but restarted one-at-a-time.

Init scripts for ndbd and ndb_mgmd are not shipped with the MySQL Cluster package, so either create them to have proper sys-V services or run them manually. Whichever the case, don’t forget to set the path to the global configuration file (config.ini) with the config-file (-f) parameter for ndb_mgmd.

Also bear in mind that ndb_mgmd builds a configuration cache which is used instead of the global configuration file at all subsequent launches. When changes are made to cluster configuration in the global configuration file, ndb_mgmd must be forced to reload it and update or drop its configuration cache. To achieve this, stop ndb_mgmd and then start it with the –reload or –initial parameters. If none of these happens to be successful, cache contents can also be deleted manually from the /usr/mysql-cluster directory before restarting the service.

If the management node started properly with the new configuration, a rolling restart must be done to distribute the news. This means that every node in the cluster has to be restarted, respecting the order given above.

When restarting data nodes one-by-one, always consider the actual status of the nodes. This information is available from the management client program ndb_mgm. Run it on any one of the cluster nodes and enter the SHOW console command. The cluster status information is displayed. When a “starting” keyword is seen beside a data node entry, that node is still in a startup phase and not functional yet. Do not restart another data node until this one is started! As the current cluster has only two mirrored data nodes, doing this would cause the whole cluster to fail. If you leave the cluster client console open, a “node started” message will be displayed when this happens.

Storing MySQL privileges on NDB

Standard user and privilege tables can be transfered to the cluster and so distributed to all SQL nodes. The official package provides stored routines for this purpose. These must first be created:

 mysql --user root --password <root_password> < /usr/share/mysql/ndb_dist_priv.sql

Then run the mysql_cluster_move_privileges routine on one SQL node to transfer the tables to NDB. Then run FLUSH PRIVILEGES; on the others.

 CALL mysql.mysql_cluster_move_privileges();

You can later check if the transfer was successful.

 SELECT mysql.mysql_cluster_privileges_are_distributed();

This routine should return 1 if your mysqld uses distributed privileges.

When this is done, availability of data nodes from SQL nodes gains more importance. E.g. when the SQL node is launched before data nodes have started or some network connection problems occur, mysqld fails with such error messages:

/usr/sbin/mysqld: Incorrect information in file: ‘./mysql/user.frm’
Fatal error: Can’t open and lock privilege tables: Got error 157 ‘Unknown error code’ from NDBCLUSTER

This is due to the fact that MySQL’s user table and privilege tables reside on the NDB cluster. Remember that with the default security policy in power, also selinux interferes with this communication.