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:
Code:
/etc/init.d/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.
Code:
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.
The Next step is to set the correct permissions on the mysql directory
Code:
chown -R mysql.mysql /var/lib/mysql
At this point we can start up our new mysql installation.
Code:
/etc/init.d/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:
Code:
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.
Code:
/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.
Code:
/usr/local/cpanel/restoregrants --db=mysql --cpuser=USERNAME --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 do this manually or script the restore. Below is a
sample script that run in a directory containing cPanel backup files, it
will restore the databases. This script is only meant to be an
starting point for custom restore scripts, and cPanel does not support
it. Logoholic data will still need to be migrated manually.
Code:
#!/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;
