There is a way to reset MySQL installation on a cPanel server without reinstalling, but it is not recommended, because doing this will make all databases inaccessible. However, we can do this in a critical situation such as catastrophic InnoDB corruption. In such situations, the stated goal is to reset mysql and restore from backups. Resetting the MySQL installation is a drastic step, so you should make sure there is no other way to fix your problem.
Please note: cPanel does not support this MySQL server reset.
To reset MySQL, you need to perform the following steps.
1) First, you need to take all database backup for security. Login to the server as a root user and given below command to take all database backup.
mysqldump –all-database > all_databases.sql
2) Then stop mysqld.
/etc/init.d/mysql stop
or
service mysql stop
3) Now move your mysql data directory, and /etc/my.cnf. You can create a MySQL data directory with a timestamp with the following command. If you are using another directory like /home/mysql for your mysql installation, you will need to use that directory instead, and compensate for this when installing a fresh database as well.
mv -v /var/lib/mysql{,.Backup.`date +%F.%T`}
mv -v /etc/my.cnf{,.Backup.`date +%F.%T`}
4) Now you will need to create the basic mysql data directory structure with the following command.
mysql_install_db
Please note: the above command will ask you to set a mysql root password. We will do this through WHM once mysql is working so you don’t need to set it via the command line. You will want to reset the mysql root password through WHM because otherwise, WHM will not know the root mysql password and database features will fail with access denied from mysql.
5) The next step is to set correct permissions on the mysql directory.
chown -R mysql.mysql /var/lib/mysql
6) Now we can start mysql service.
/etc/init.d/mysql start
or
service mysql start
7) Now you can check the mysql status by running the following command.
service mysql status
8) Then you need to reset mysql root password through WHM.
WHM >> SQL Services >> Reset MySQL Root Password
From there you can reset MySQL root password.
9) Run the following cPanel scripts to set up the cPanel mysql databases that cPanel relies on.
/usr/local/cpanel/bin/hulkdsetup
/usr/local/cpanel/bin/update-roundcube –force
/scripts/fullhordereset –force
/usr/local/cpanel/bin/leechprotectinstall –force
/usr/local/cpanel/bin/update-logaholic –force
10) At last, need to restore the databases from the backup and run following command to restore the grants to the database, once you add databases back in for the users. This will restore all the cPanel grants and allowing access to the databases.
/usr/local/cpanel/restoregrants –db=mysql –cpuser=USERNAME –all
If you need any further help, please do reach our support department.