• MySQL知识库 :: c
  • Single user mode and connection pool

  • Problem:

    Since MySQL Cluster 6.2 it is possible to use connection pooling with SQL Nodes. This means that these nodes can have 1 or more connections open to cluster, and consequently perform better. However, there is a problem with the Single User Mode when you don't configure at least 1 API or SQL slot in the configuration which does not pooling.

    Solution:

    Setup your cluster configuration so you have a dedicated API (or SQL) slot with hostname and id defined. This API node will actually not run all the time, or at least not activily be part of the production system. It will only serve for administrative tasks.

     ..
     # API/SQL node for administrative tasks.
     [API]
     id = 10
     HostName=dba.mydomain.local
    
     # API/SQL nodes for production
     [API]
     id = 20
     HostName=mysql01.mydomain.local
     [API]
     id = 21
     HostName=mysql01.mydomain.local
     [API]
     id = 22
     HostName=mysql01.mydomain.local
     ..
    

    Setup the MySQL server on mysql01.mydomain.local to use connection pooling:

     [mysqld]
     ndb-connectstring = ndbmgm01.mydomain.local
     ndb-cluster-connection-pool = 3
    

    Setup another MySQL server on dba.mydomain.local, maybe also using different datadir and TCP port to do administrative tasks:

     [mysqld]
     datadir = /var/lib/mysqldba
     port = 3307
     socket = /var/lib/mysqldba/mysql.sock
     ndb-connectstring = nodeid=10,ndbmgm01.mydomain.local
    

    Now you are able to go into single user mode using the Node 10:

     ndb_mgm> ENTER SINGLE USER MODE 10;
    

    Discussion:

    When you don't setup the Cluster with an administrative API node, and you use connection pooling, you can't go into a true single user mode. Suppose you have the following configuration:

     ..
     # API/SQL nodes for production
     [API]
     id = 20
     HostName=mysql01.mydomain.local
     [API]
     id = 21
     HostName=mysql01.mydomain.local
     [API]
     id = 22
     HostName=mysql01.mydomain.local
     ..
    
     ndb_mgm> SHOW;
     ..
     [mysqld(API)]   3 node(s)
     id=20   @10.100.9.6  (mysql-5.1.30 ndb-6.3.20)
     id=21   @10.100.9.6  (mysql-5.1.30 ndb-6.3.20)
     id=22   @10.100.9.6  (mysql-5.1.30 ndb-6.3.20)
    

    The MySQL Server is configured like this:

     [mysqld]
     ndb-cluster-connection-pool=3
    

    The first problem is that when you connect to the MySQL server you get an unpredictable node id:

     mysql_session1> SHOW GLOBAL STATUS LIKE 'Ndb\_cluster\_node\_id';
     +---------------------+-------+
     | Variable_name       | Value |
     +---------------------+-------+
     | Ndb_cluster_node_id | 22    | 
     +---------------------+-------+
    
     mysql_session2> SHOW GLOBAL STATUS LIKE 'Ndb\_cluster\_node\_id';
     +---------------------+-------+
     | Variable_name       | Value |
     +---------------------+-------+
     | Ndb_cluster_node_id | 20    | 
     +---------------------+-------+
    

    If you keep a session open with Node ID 20 for example, and use it to go into single user mode, you have no guarantee that another session will get again that same node id.

    Using the proposed solution, adding an administrative API/SQL Node, you know which Node ID you can and should use. You can then setup a MySQL server which uses this node id and go into single user mode without problems (provided only 1 user connects to that SQL Node). It is probably also a good idea only to start this SQL node when really needed. It also can be used for restoring backups using ndb_restore.