How to reset the MySQL server on a cPanel server without reinstalling

Ahoy, I have been asked a few times how to reset the mysql installation on cPanel without reinstalling. Doing this will make all databases inaccessable so it is HIGHLY not recommended to follow this guide, unless the stated goal is to clear out all databases after events such as catastrophic innodb corruption. In these events the stated goal is to reset mysql and restore from backups. Reseting the MySQL installation is a drastic step, and should not be performed without great consideration. If you follow this guide all your databases will be inaccessible and the only accessible databases at the end of the guide will be the minimum requirement for cPanel. If you chose to reset all mysql data, an unsupported path by cPanel, you would want to follow these steps. I recommend that you do not simply copy and paste these commands, but rather research each step and ensure that you understand the process as cPanel support will not be able to assist you in the event of an issue. You should have allready have working knowledge for mysql to attempt this. If you chose to proceed, it is at your own risk. Always ensure and verify that you have backups located in a remote destination. To reset your mysql installation and /var/lib/mysql directory, you will want to first stop mysqld: /scripts/restartsrv_mysql --stop
You will now want to move aside your mysql data directory, and /etc/my.cnf. You can move aside you mysql data directory to a backup with a timestamp with the following command. If you have another directory for your mysql installation (eg. /home/mysql) You will need to use that directory instead, and compensate for this when installing an fresh database as well. mv -v /var/lib/mysql{,.Backup.`date +%F.%T`} mv -v /etc/my.cnf{,.Backup.`date +%F.%T`}
Next we will need to create the basic mysql data directory structure with the following command. Note: this 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. mysql_install_db
The Next step is to set the correct permissions on the mysql directory chown -R mysql.mysql /var/lib/mysql
At this point we can start up our new mysql installation. /scripts/restartsrv_mysql --start
Our new mysql installation is up and running, you will want to reset the mysql root password through WHM. This will also set up access to phpmyqdmin: WHM >> SQL Services >> Reset MySQL Root Password
Now we will need to run some scripts to set up the cPanel mysql databases that cPanel relies on. NOTE: On modern versions of cPanel (v66+) these services should not be using MySQL. You'll likely not need to run these scripts. /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
Lastly, you will need to use the following command to restore the grants to the database, once you add databases back in for the users. This will restore the cPanel grants, allowing access to the databases. So you have access issues after this guide, this will be an helpful command. /usr/local/cpanel/bin/restoregrants --db=mysql --cpuser=USERNAME --all
The above script can be ran as a loop to automatically update all users on the system: for cpuser in $(ls -1 /var/cpanel/users); do /usr/local/cpanel/bin/restoregrants --db=mysql --cpuser=$cpuser --all
At this point you will have a working cPanel and mysql installation. However none of your users databases will be functional at this point. You will need to manually restore all MySQL databases, or script the restore. Below is a sample script that run in a directory containing cPanel backup files, it will restore the databases. NOTE: This script is only meant to be an starting point for custom restore scripts, and cPanel does not support it. Additionally, this script is rather old and may not function properly on newer cPanel installations as-is. #!/bin/bash #Loop through the users on the server for i in `\ls -1 /var/cpanel/users/ | \grep -v './'`; do \echo;\echo; \echo "++++ Starting $i +++++"; if [ -e $i.tar.gz ]; then \echo "Extracting SQL files"; # Extract the Databases from the backup \tar -zxf $i.tar.gz $i/mysql; if [ -e $i/mysql/roundcube.sql ]; then \echo "Importing $i's Roundcube data"; \mysql roundcube < $i/mysql/roundcube.sql; fi if [ -e $i/mysql/horde.sql ]; then \echo "Importing $i's Horde data"; \mysql horde < $i/mysql/horde.sql; fi \echo "Importing $i's databases"; for x in `\ls -1 $i/mysql/* | \grep -v 'roundcube\|horde\|openfileslimit' | \cut -d \/ -f 3 | \cut -d \. -f 1| \uniq`; do \echo " - Importing $x"; \mysql < $i/mysql/$x.create; \mysql $x < $i/mysql/$x.sql; done; /usr/local/cpanel/bin/restoregrants --db=mysql --cpuser=$i --all; else \echo "Missing $i.tar.gz"; fi done;