Since MySQL Cluster 6.2 it is possible to setup a pool inside the mysqld process opening multiple connections to a MySQL Cluster. This is pretty cool, but it might give troubles when you have to use Single User Mode. Take for example the following configuration for your SQL Nodes:
[SQL]
Id=10
Hostname= 10.100.9.6
[SQL]
Id=11
Hostname= 10.100.9.6
[SQL]
Id=12
Hostname= 10.100.9.7
[SQL]
Id=13
Hostname= 10.100.9.7
No further slots are defined, and you setup your two MySQL Servers to use connection pooling:
[mysqld]
ndb-cluster-connection-pool = 2
This will result in the following output of SHOW in the ndb_mgm client tool:
[mysqld(API)] 4 node(s)
id=10 @10.100.9.6 (mysql-5.1.30 ndb-6.3.20)
id=11 @10.100.9.6 (mysql-5.1.30 ndb-6.3.20)
id=12 @10.100.9.7 (mysql-5.1.30 ndb-6.3.20)
id=13 @10.100.9.7 (mysql-5.1.30 ndb-6.3.20)
Two problems:
- You don’t have a free slot for restoring backups using ndb_restore, or for using other ndb command line tools.
- If you go single user mode, e.g. doing ALTER TABLE, you can’t choose 10 or 11 because those will be used by other MySQL connections.
Solution: leave a free [API] slot available to do administrative tasks. Best is to asign it a dedicated NodeID and Hostname so nobody else can use it but the DBA. It’s quite simple, and each MySQL Cluster setup should have this already, even before connection pooling was available. Connection pooling makes it just harder because when you say ‘ENTER SINGLE USER MODE 12’, you connect to the host ‘10.100.9.6’, you are not sure you’ll get this Node ID at all! Eventually you will, but mind that other MySQL connections can use it too.