Can't drop an NDB database because the storage engine is unavailable

Question

I tried to set up a MySQL cluster and failed. I finally got it switched back to standard MySQL, but now I have an ndbinfo database that I cannot drop. It causes my automysqlbackup cron job to report errors.

mysql> drop database ndbinfo;
ERROR 1286 (42000): Unknown storage engine 'ndbinfo'

Is there any way to manually remove the database? Or maybe a procedure that I do not know about? I did not see 'ndbinfo' referenced anywhere in the MySQL database.

It's on Ubuntu Server 22.04 from the MySQL APT repository. Output from mysql --version:

mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)

Answer 1

I finally worked out a way to fix this.

I did mysqldump on every database except information_schema, database-schema, and ndbinfo. Then I stopped mysql and renamed /var/lib/mysql.

Copied all the dump files to another system with a fresh mysql install. Created the databases with info from 'show create database' on the source system, loaded those dumps, and stopped mysql on that server.

The final step was to rsync /var/lib/mysql back to the starting server and start mysql there.

Most likely I lost a few seconds of data in the zabbix database, which is not important. Now the ndbinfo database is gone and automysqlbackup runs without errors.

Answer 2

According to the documentation, to activate the ndbcluster storage engine, add ndbcluster under [mysqld] section in /etc/my.cnf. If the file does not exist, create it.

/etc/my.cnf:

[mysqld]
ndbcluster

Then restart the MySQL server (mysqld). If you installed the server from the MySQL apt repo, systemd is used:

systemctl restart mysql

Since you probably have no NDB cluster up, the server will wait for two minutes until the connection to the NDB cluster fails.

Then connect to the mysql server and do:

mysql> DROP DATABASE ndbinfo;

Then remove the ndbcluster from [mysqld] section in /etc/my.cnf.

And then finally restart the MySQL server again.

> systemctl restart mysql

Since MySQL 8.0.31 the above will work for both mysql-server package and mysql-cluster-community-server package, before 8.0.31 one will need mysql-cluster-community-server package to be able to enable ndbcluster plugin.