Mysql – Alter thesql database engine doesn’t work from cluster to other

alter-tabledatabase-engineMySQLndbcluster

I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked.

It did not throw any error but did not work. I just want to know why this did not work as I got it from MySQL site that it won't work. I just need to know the reason for the same.

For client requirement I may need to alter the engine on installed database without losing any data.

Any Ideas or help that anybody can provide to resolve issue.

Best Answer

Why you can't change ndbcluster to InnoDB?

Clustering with MySQL is supported only by the NDB storage engine. ndbcluster/ndb storage engine is meant for clustering if you specify a table with ENGINE=ndbcluster; this means table is being shared with all nodes and simply you can't change it to other ENGINE types, either you should take a backup of this table and then DROP table with ndbcluster option and import by changing ndbcluster to innodb

However, it is possible to create tables using other storage engines (such as InnoDB or MyISAM) on a MySQL server being used with a MySQL Cluster, but since these tables do not use NDB, they do not participate in clustering; each such table is strictly local to the individual MySQL server instance on which it is created.

Migrating from InnoDB to ndbcluster?

There are two ways to migrate InnoDB tables with foreign keys to NDB.

  • Dump the database and edit the script so each table specifies ENGINE=NDB before re-importing the script to a new database
  • Drop the constraints, alter the tables to use the NDB engine, and recreate the constraints

Dumping the database and editing the script is a straightforward use of mysqldump and a text editor.

Converting InnoDB Tables to MySQL Cluster