3.10 NDB Cluster Downgrades Involving Logical Downgrades of MySQL Nodes

While the upgrade cluster command can be used to perform a downgrade for an NDB Cluster (see the description for the upgrade cluster command for the restrictions that apply), special steps are required when that involves a downgrade for mysqld for which in-place downgrade is not supported. For example, downgrading from NDB Cluster 7.5 to 7.4 involves downgrading the MySQL server from 5.7 and 5.6, for which the logical downgrade method must be employed (see Downgrade Paths, for information on downgrading mysqld). This section explains the steps to take in the situation. Let us use this sample cluster as an example:

mcm> show status -r mycluster;
+--------+----------+----------+---------+-----------+----------+
| NodeId | Process  | Host     | Status  | Nodegroup | Package  |
+--------+----------+----------+---------+-----------+----------+
| 49     | ndb_mgmd | tonfisk  | running |           | 7.5-DMR1 |
| 1      | ndbmtd   | flundra  | running | 0         | 7.5-DMR1 |
| 2      | ndbmtd   | grindval | running | 0         | 7.5-DMR1 |
| 50     | mysqld   | flundra  | running |           | 7.5-DMR1 |
| 51     | mysqld   | grindval | running |           | 7.5-DMR1 |
| 52     | ndbapi   | *        | added   |           |          |
+--------+----------+----------+---------+-----------+----------+

Assuming the NDB Cluster has been upgraded from version 7.4 to 7.5 and the user then encountered a major problem and now needs to revert the cluster to version 7.4, these are the steps to follow:

1. Backup all the cluster data:

   mcm> backup cluster mycluster;
   +-------------------------------+
   | Backup completed successfully |
   +-------------------------------+

2. Use mysqldump to backup non-NDB data from each of the mysqld nodes.

3. Assuming the package for the lower version of NDB Cluster you want to downgrade to is still available to MySQL Cluster Manager (if it is not the case, use the add package command to supply the package), use the upgrade cluster command and the –-nodeid option to downgrade the management and data nodes:

   mcm> upgrade cluster -P 7.4.10 --nodeid=49,1,2 --retry mycluster;
   +-------------------------------+
   | Cluster upgraded successfully |
   +-------------------------------+

   Remember to include ALL management nodes and data nodes with the –-nodeid option, but leave out any mysqld node. The show status command shows which nodes have now been downgraded:

   mcm> show status -r mycluster;
   +--------+----------+----------+---------+-----------+----------+
   | NodeId | Process  | Host     | Status  | Nodegroup | Package  |
   +--------+----------+----------+---------+-----------+----------+
   | 49     | ndb_mgmd | tonfisk  | running |           | 7.4.10   |
   | 1      | ndbmtd   | flundra  | running | 0         | 7.4.10   |
   | 2      | ndbmtd   | grindval | running | 0         | 7.4.10   |
   | 50     | mysqld   | flundra  | running |           | 7.5-DMR1 |
   | 51     | mysqld   | grindval | running |           | 7.5-DMR1 |
   | 52     | ndbapi   | *        | added   |           |          |
   +--------+----------+----------+---------+-----------+----------+

4. Recreate the data directory for each mysqld node:

   1. Stop the mysqld node:

      mcm> stop process 50 mycluster;
      +-------------------------------+
      | Process stopped successfully  |
      +-------------------------------+

   2. Delete the data directory of the mysqld node and recreate it, as an empty folder:

      user@host$ cd mcm_data/clusters/mycluster/50/
      user@host$ rm -rf data
      user@host$ mkdir data

   3. Initialize the recreated data directory using the mysql_install_db script in the package of the NDB Cluster version you are downgrading to:

      user@host$ /clusters/7.4/scripts/mysql_install_db \
        --defaults-file=/mcm_data/clusters/mycluster/50/cfg/my.cnf \
        --datadir=/mcm_data/clusters/mycluster/50/data --basedir=/clusters/7.4.10 \
        > mysql_install_db.log 2>&1

      (When you are downgrading to NDB Cluster 7.5 or later, the mysqld data directory should be initialized with the mysqld --initialize command; see Initializing the Data Directory for details.)

   4. Restart the mysqld node using MySQL Cluster Manager (due to the downgrade already performed earlier on the management and data nodes, mcmd is going to start the mysqld node with the binary of the cluster version you are downgrading to ):

      mcm> start process 50 mycluster;
      ERROR 9003 (00MGR): Tx {690be0d6 270 0 15} timed out on agent 0 @{19 0 50
      Wait for mysqld to start} participants: 0@tonfisk:18620[X] 1@flundra:18620[ ] 2@grindval:18620[ ]

      There might be a timeout error as shown above, which is due to the fact that the mcmd user is still to be created on the mysqld node (this happens in the next step). The error can be avoided if you perform the next step soon enough. But even if the error occurs, there is no issue here, as mcmd will keep trying to connect to the mysqld node.

   5. Recreate the mcmd user on the mysqld node:

      mysql> CREATE USER 'mcmd'@'127.0.0.1' IDENTIFIED BY '<manager-password>';
      mysql> GRANT ALL PRIVILEGES ON *.* TO 'mcmd'@'127.0.0.1' IDENTIFIED BY \
        '<manager-password>' WITH GRANT OPTION;
      mysql> FLUSH PRIVILEGES;

   6. Reload onto the mysqld node the non-NDB data you backed up earlier using mysqldump:

      mysql -h 127.0.0.1 -P 3306 -u root -p < dumpfile.sql

5. Repeat step 4 above and each of its substeps for every mysqld node of your cluster.

6. After all the mysqld nodes have been downgraded and their data directories prepared, mcmd eventually reconnects with them all:

   mcm> show status -r mycluster;
   +--------+----------+----------+---------+-----------+---------+
   | NodeId | Process  | Host     | Status  | Nodegroup | Package |
   +--------+----------+----------+---------+-----------+---------+
   | 49     | ndb_mgmd | tonfisk  | running |           | 7.4.10  |
   | 1      | ndbmtd   | flundra  | running | 0         | 7.4.10  |
   | 2      | ndbmtd   | grindval | running | 0         | 7.4.10  |
   | 50     | mysqld   | flundra  | running |           | 7.4.10  |
   | 51     | mysqld   | grindval | running |           | 7.4.10  |
   | 52     | ndbapi   | *        | added   |           |         |
   +--------+----------+----------+---------+-----------+---------+