MySQL Node not Connecting to Management Nodes in NDB Cluster

Question

I am trying to deploy a 8 machine MySQL NDB Cluster with 2 Management Nodes, 2 SQL Nodes and 4 Data Nodes. The base operating system is Ubuntu 16.04 and the MySQL Cluster software version is 7.5.4. Running everything on dedicated servers.

I am using private networking and have defined all hosts with their private IPs in /etc/hosts

My config.ini file reads as follows:

[ndbd default]
NoOfReplicas=2
DataMemory=16384M
IndexMemory=1024M
LockPagesInMainMemory=1
NoOfFragmentLogFiles=300
MaxNoOfConcurrentOperations=100000
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
RedoBuffer=32M
MaxNoOfTables=2048
MaxNoOfOrderedIndexes=512
[mysqld default]
[ndb_mgmd default]
[tcp default]
SendBufferMemory=20M
ReceiveBufferMemory=20M
# Cluster Control / Management node
[ndb_mgmd]
NodeId=1
hostname=dbc1.enhancier.net
[ndb_mgmd]
NodeId=2
hostname=dbc2.enhancier.net
# Data Node 1
[ndbd]
NodeId=3
hostname=dbc3.enhancier.net
DataDir= /var/lib/mysql-cluster
# Data Node 2
[ndbd]
NodeId=4
HostName=dbc4.enhancier.net
DataDir= /var/lib/mysql-cluster
# Data Node 3
[ndbd]
NodeId=5
HostName=dbc5.enhancier.net
DataDir= /var/lib/mysql-cluster
# Data Node 4
[ndbd]
NodeId=6
HostName=dbc6.enhancier.net
DataDir= /var/lib/mysql-cluster
# SQL Node
[mysqld]
NodeId=7
hostname=dbc7.enhancier.net
# If you to add new SQL Node
[mysqld]
NodeId=8
hostname=dbc8.enhancier.net

On the MySQL node the my.cnf file reads as:

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
ndbcluster
ndb-connectstring=dbc1.enhancier.net,dbc2.enhancier.net
[mysql_cluster]
ndb-connectstring=dbc1.enhancier.net,dbc.enhancier.net
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

On starting the Management nodes, both come online fine however when I start the MySQL node, it does not see the Management nodes.

The ndb_mgm -e show command output is as follows:
root@dbc1:~# ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=3 (not connected, accepting connect from dbc3.enhancier.net)
id=4 (not connected, accepting connect from dbc4.enhancier.net)
id=5 (not connected, accepting connect from dbc5.enhancier.net)
id=6 (not connected, accepting connect from dbc6.enhancier.net)
[ndb_mgmd(MGM)] 2 node(s)
id=1 @10.80.45.5 (mysql-5.7.16 ndb-7.5.4)
id=2 @10.80.45.7 (mysql-5.7.16 ndb-7.5.4)
[mysqld(API)] 2 node(s)
id=7 (not connected, accepting connect from dbc7.enhancier.net)
id=8 (not connected, accepting connect from dbc8.enhancier.net)

NOTE: I have not connected the data nodes.... there is no issue in data node connections. This is the 9th try hence I am certain that data node connection will not be an issue. It has never been.

The log file reads the following:

root@dbc7:~# cat /var/log/mysqld.log
2017年03月09日T11:09:54.561861Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2017年03月09日T11:09:54.761186Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017年03月09日T11:09:54.761235Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017年03月09日T11:09:54.761248Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.16-ndb-7.5.4-cluster-gpl) starting as process 2378 ...
2017年03月09日T11:09:54.763968Z 0 [Note] InnoDB: PUNCH HOLE support not available
2017年03月09日T11:09:54.763977Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017年03月09日T11:09:54.763980Z 0 [Note] InnoDB: Uses event mutexes
2017年03月09日T11:09:54.763982Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017年03月09日T11:09:54.763984Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017年03月09日T11:09:54.763986Z 0 [Note] InnoDB: Using Linux native AIO
2017年03月09日T11:09:54.764101Z 0 [Note] InnoDB: Number of pools: 1
2017年03月09日T11:09:54.764144Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017年03月09日T11:09:54.764759Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017年03月09日T11:09:54.768703Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017年03月09日T11:09:54.769895Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017年03月09日T11:09:54.781020Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017年03月09日T11:09:54.786031Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017年03月09日T11:09:54.786059Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017年03月09日T11:09:54.815368Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017年03月09日T11:09:54.817085Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017年03月09日T11:09:54.817104Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017年03月09日T11:09:54.818254Z 0 [Note] InnoDB: Waiting for purge to start
2017年03月09日T11:09:54.868460Z 0 [Note] InnoDB: 5.7.16 started; log sequence number 2536550
2017年03月09日T11:09:54.869200Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/data/ib_buffer_pool
2017年03月09日T11:09:54.869672Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017年03月09日T11:09:54.871666Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170309 16:39:54
2017年03月09日T11:10:30.361480Z 0 [Warning] NDB: server id set to zero - changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
2017年03月09日T11:10:30.361716Z 0 [Note] NDB Binlog: Starting...
2017年03月09日T11:10:30.361865Z 1 [Note] NDB Binlog: Started
2017年03月09日T11:10:30.361878Z 1 [Note] NDB Binlog: Setting up
2017年03月09日T11:10:30.362043Z 1 [Note] NDB Binlog: Created schema Ndb object, reference: 0x0, name: 'Ndb Binlog schema change monitoring'
2017年03月09日T11:10:30.362107Z 1 [Note] NDB Binlog: Created injector Ndb object, reference: 0x0, name: 'Ndb Binlog data change monitoring'
2017年03月09日T11:10:30.362116Z 1 [Note] NDB Binlog: Setup completed
2017年03月09日T11:10:30.362124Z 1 [Note] NDB Binlog: Wait for server start completed
2017年03月09日T11:10:30.362346Z 0 [Note] NDB Index Stat: Starting...
2017年03月09日T11:10:30.362392Z 0 [Note] NDB Index Stat: Wait for server start completed
2017年03月09日T11:10:30.362517Z 0 [Note] NDB Util: Starting...
2017年03月09日T11:10:30.362699Z 2 [Note] NDB Util: Wait for server start completed
2017年03月09日T11:10:30.420524Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017年03月09日T11:10:30.420637Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017年03月09日T11:10:30.420648Z 0 [Note] IPv6 is available.
2017年03月09日T11:10:30.420653Z 0 [Note] - '::' resolves to '::';
2017年03月09日T11:10:30.420658Z 0 [Note] Server socket created on IP: '::'.
2017年03月09日T11:10:30.425248Z 0 [Note] Event Scheduler: Loaded 0 events
2017年03月09日T11:10:30.425335Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.16-ndb-7.5.4-cluster-gpl' socket: '/tmp/mysql.sock' port: 3306 MySQL Cluster Community Server (GPL)
2017年03月09日T11:10:30.425382Z 1 [Note] NDB Binlog: Check for incidents
2017年03月09日T11:10:30.425383Z 0 [Note] NDB Index Stat: Wait for cluster to start
2017年03月09日T11:10:30.425386Z 2 [Note] NDB Util: Wait for cluster to start
2017年03月09日T11:10:30.425390Z 1 [Note] NDB Binlog: Wait for cluster to start
2017年03月09日T11:11:00.435836Z 0 [Warning] NDB : Tables not available after 30 seconds. Consider increasing --ndb-wait-setup value
2017年03月09日T11:28:35.749942Z 0 [Note] Giving 2 client threads a chance to die gracefully
2017年03月09日T11:28:35.749958Z 0 [Note] Shutting down slave threads
2017年03月09日T11:28:35.778243Z 0 [Note] NDB Util: Stopped
2017年03月09日T11:28:36.289365Z 1 [Note] NDB Binlog: Server shutdown detected while waiting for ndbcluster to start...
2017年03月09日T11:28:36.289416Z 1 [Note] NDB Binlog: Shutting down
2017年03月09日T11:28:36.289471Z 1 [Note] NDB Binlog: Stopping...
2017年03月09日T11:28:36.290311Z 0 [Note] NDB Binlog: Stopped
2017年03月09日T11:28:37.750155Z 0 [Note] Forcefully disconnecting 0 remaining clients
2017年03月09日T11:28:37.750213Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2017年03月09日T11:28:37.750365Z 0 [Note] Binlog end
2017年03月09日T11:28:37.750397Z 0 [Note] NDB Binlog: Stop
2017年03月09日T11:28:37.750407Z 0 [Note] NDB Binlog: Wakeup
2017年03月09日T11:28:37.750414Z 0 [Note] NDB Binlog: Stop completed
2017年03月09日T11:28:37.750656Z 0 [Note] Shutting down plugin 'ngram'
2017年03月09日T11:28:37.750668Z 0 [Note] Shutting down plugin 'ARCHIVE'
2017年03月09日T11:28:37.750676Z 0 [Note] Shutting down plugin 'ndb_transid_mysql_connection_map'
2017年03月09日T11:28:37.750683Z 0 [Note] Shutting down plugin 'ndbinfo'
2017年03月09日T11:28:37.751545Z 0 [Note] Shutting down plugin 'ndbcluster'
2017年03月09日T11:28:37.751563Z 0 [Note] NDB Index Stat: Stop
2017年03月09日T11:28:37.751571Z 0 [Note] NDB Index Stat: Wakeup
2017年03月09日T11:28:37.778474Z 0 [Note] NDB Index Stat: Stopping...
2017年03月09日T11:28:37.778523Z 0 [Note] NDB Index Stat: Stopped
2017年03月09日T11:28:37.778591Z 0 [Note] NDB Index Stat: Stop completed
2017年03月09日T11:28:37.778626Z 0 [Note] NDB Util: Stop
2017年03月09日T11:28:37.778635Z 0 [Note] NDB Util: Wakeup
2017年03月09日T11:28:37.778643Z 0 [Note] NDB Util: Stop completed
2017年03月09日T11:28:41.668886Z 0 [Note] Shutting down plugin 'partition'
2017年03月09日T11:28:41.668933Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2017年03月09日T11:28:41.668944Z 0 [Note] Shutting down plugin 'CSV'
2017年03月09日T11:28:41.668953Z 0 [Note] Shutting down plugin 'MEMORY'
2017年03月09日T11:28:41.668962Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2017年03月09日T11:28:41.668972Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2017年03月09日T11:28:41.668984Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017年03月09日T11:28:41.668997Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017年03月09日T11:28:41.669008Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017年03月09日T11:28:41.669019Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017年03月09日T11:28:41.669040Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017年03月09日T11:28:41.669047Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017年03月09日T11:28:41.669054Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017年03月09日T11:28:41.669061Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017年03月09日T11:28:41.669068Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017年03月09日T11:28:41.669075Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017年03月09日T11:28:41.669081Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017年03月09日T11:28:41.669088Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017年03月09日T11:28:41.669097Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017年03月09日T11:28:41.669109Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017年03月09日T11:28:41.669116Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017年03月09日T11:28:41.669122Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2017年03月09日T11:28:41.669145Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2017年03月09日T11:28:41.669152Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017年03月09日T11:28:41.669158Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017年03月09日T11:28:41.669164Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017年03月09日T11:28:41.669171Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017年03月09日T11:28:41.669177Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017年03月09日T11:28:41.669183Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017年03月09日T11:28:41.669189Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017年03月09日T11:28:41.669195Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017年03月09日T11:28:41.669200Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2017年03月09日T11:28:41.669207Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017年03月09日T11:28:41.669213Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2017年03月09日T11:28:41.669219Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2017年03月09日T11:28:41.669225Z 0 [Note] Shutting down plugin 'InnoDB'
2017年03月09日T11:28:41.669391Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017年03月09日T11:28:41.669566Z 0 [Note] InnoDB: Starting shutdown...
2017年03月09日T11:28:41.769816Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/mysql/data/ib_buffer_pool
2017年03月09日T11:28:41.770153Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170309 16:58:41
2017年03月09日T11:28:43.083813Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2536578
2017年03月09日T11:28:43.085266Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017年03月09日T11:28:43.085275Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017年03月09日T11:28:43.085292Z 0 [Note] Shutting down plugin 'MyISAM'
2017年03月09日T11:28:43.085299Z 0 [Note] Shutting down plugin 'sha256_password'
2017年03月09日T11:28:43.085301Z 0 [Note] Shutting down plugin 'mysql_native_password'
2017年03月09日T11:28:43.085505Z 0 [Note] Shutting down plugin 'binlog'
2017年03月09日T11:28:43.085769Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
2017年03月09日T11:28:43.124535Z mysqld_safe mysqld from pid file /usr/local/mysql/data/dbc7.enhancier.net.local.lan.pid ended

NOTE: I have stopped the MySQL service. A graceful shutdown.

After hours of researching and having rebuilt the cluster at least 8 times, I am completely lost. I have tried many troubleshooting steps found on the net. Some of them are:

1. Ensured that there is no SELinux on any of the machines - All machines are fresh and Ubuntu anyways does not come with SELinux by default

2. AppArmor is not installed

3. The loopback address is not configured to the hostname

4. Reinstalled the OS and started fresh various times and tried different configs like 1 Management node - 1 SQL Node - 2 Data Nodes, 1 Management cum data note - 2 data nodes, etc.

5. Tried IP addresses instead of hostnames - Tried public IPs as well as Private IPs

6. Enusred that each server is being able to ping the other on the private networking

7. No firewall, IPTables nor UFW, is installed

Any pointers will be greatly appreciated. My apologies if my question sounds too stupid. I am a noob at this.

Answer 1

I would remove the [mysql_cluster] section in the my.cnf file entirely, as it's redundant and can only lead to confusion (you already have two different settings for ndb-connectstring there). So I would go from this config block:

ndb-connectstring=dbc1.enhancier.net,dbc2.enhancier.net
[mysql_cluster]
ndb-connectstring=dbc1.enhancier.net,dbc.enhancier.net

To this:

ndb-connectstring="10.80.45.5:1186,10.80.45.7:1186"

That should eliminate a number of potential causes by specifying the same IP addresses that the management server lists, explicitly specifying the port, and wrapping the string in quotes.

If things still don't work, then at least we've eliminated a few basic things. The next thing to double check is to ensure that the mysqld processes can communicate with the data nodes over the network, given the specified IP address and port combinations.

But now that I look at the ndb_mgm SHOW output again, and what you even noted yourself, the data nodes are not running! When the mysqld (SQL API) node starts up, it contacts the management server (ndb_mgmd) in order to discover the data nodes (ndbd/ndbmtd processes). It then attempts to contact the data nodes in order to retrieve the data dictionary (pulls in .frm blobs for the tables) and other object and cluster metadata. That's what is timing out after 30 seconds:

[Warning] NDB : Tables not available after 30 seconds. Consider increasing --ndb-wait-setup value

Of course it's not able to pull in the table metadata because the data nodes are not responding as they're not running. :) The management nodes only serve as a discovery mechanism in this sequence, they don't hold any data.

FWIW, the docs aren't very good on this topic, so I don't blame you for getting confused here.

P.S. If you just want to get a running and working system, I would also recommend trying the Auto Installer.

• Mangesh: Thanks for your response Matt. I tried changing the connection string as per your suggestion and deleted the mysql_cluster block, however, that does not help. Will toy with the auto installer however not sure if that will really solve the issue. On the net, a lot of people have a similar issue with the auto installer as well.
• Matt Lord: OK, I edited my original answer. Hopefully that helps!
• Mangesh: Matt, I cannot begin to thank you enough.... I was really disappointed and everything that I was trying was yielding no results. Your suggestions helped. The issue was two-fold as you rightly guided - The connection string needed the port number explicitly and the data nodes had to be started. Thank you so much...... Thanks once again for helping me.
• Matt Lord: You're most welcome! Glad that you're up and running :)