There are times, you may see the following error in your MySQL/MariaDB based Cpanel server:
[ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist in engine
The issue is most likely related to your Innodb tablespace got corrupted, and hence some tables under the mysql database got locked out as some of them use Innodb storage engine. One of the outcome of the symptom is, if you try to add a user to a database, it doesn’t add or show the green notification any longer in cpanel mysql databases section. Instead it just stops.
The only and best way to properly fix this would be restore the ‘mysql’ database or just the ‘servers’ table from your backup. If you don’t have one, you may just create the ‘servers’ table using the following SQL statement:
CREATE TABLE `servers` ( `Server_name` char(64) NOT NULL, `Host` char(64) NOT NULL, `Db` char(64) NOT NULL, `Username` char(64) NOT NULL, `Password` char(64) NOT NULL, `Port` int(4) DEFAULT NULL, `Socket` char(64) DEFAULT NULL, `Wrapper` char(64) NOT NULL, `Owner` char(64) NOT NULL, PRIMARY KEY (`Server_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
You may require to drop the table first. Now, if you can’t do this either, then there is only one way left, is to uninstall your MariaDB installation, and let Cpanel/WHM to install them for you.
Get a Backup First:
cp -Rf /var/lib/mysql/mysql /root/ rm -Rf /var/lib/mysql/mysql
Uninstall MariaDB:
yum remove MariaDB*
Now, you may install the latest MariaDB from WHM >> MariaDB/MySQL Upgrade and proceed accordingly. This should install the latest for you with a fresh ‘mysql’ database for you. But it will not alter your other data files, means your other databases should be fine.
One thing, you need to remember, after a fresh mysql installation with the old data files, you will have the authorizations missing. You would have to recreate the database users manually to get the privileged table filled up.