How to Delete a MySQL Database and Manage Database Issues in cPanel & WHM

1. Delete a MySQL Database

To manually delete a MySQL database on a cPanel & WHM server, follow these steps:

1. Log in to WHM as the root user.
2. Navigate to SQL Services and click phpMyAdmin.
3. Select the database you want to delete from the left sidebar.
4. Click Operations at the top.
5. Under Remove database, click Drop the database (DROP).
6. Click OK to confirm deletion.
7. If a cPanel user owned the database, delete its entry in Manage My Databases.

Important: Manually deleting a database without removing its entry in cPanel corrupts the database map and causes backup errors.

2. Enable the Slow Query Log in MySQL or MariaDB

To enable the Slow Query Log, follow these steps:

1. Log in via SSH as root.
2. Edit the my.cnf file and add the following under [mysqld]:

slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Variable names differ in older MySQL and newer MariaDB versions.

Create the log file

touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log

Restart MySQL

/usr/local/cpanel/scripts/restartsrv_mysql

Analyze slow queries

mysqldumpslow -a /var/log/mysql-slow.log

3. Resolve Password Authentication Failed Errors in PostgreSQL

If you receive a Password authentication failed error, follow these steps:

Check the pgpass file

The /root/.pgpass file must contain:

::*:postgres:PASSWORD

Edit pg_hba.conf

1. Stop Tailwatch:

touch /etc/tailwatchddisable
killall tailwatchd

2. Edit pg_hba.conf and change md5 to trust.
3. Restart PostgreSQL:

/usr/local/cpanel/scripts/restartsrv_postgresql

Change the PostgreSQL password

postgres=# alter user postgres with encrypted password 'new_pass';
postgres=# \q

4. Revert trust back to md5.
5. Re-enable Tailwatch:

rm /etc/tailwatchddisable
/usr/local/cpanel/scripts/restartsrv_tailwatchd

4. Restore a User’s Database Access with restoregrants

Use the restoregrants script to restore database privileges:

/usr/local/cpanel/bin/restoregrants --cpuser=$cpuser --db=$type --dbuser=$dbuser
/usr/local/cpanel/bin/restoregrants --cpuser=$cpuser --db=$type --all

Variables:

• $cpuser: cPanel username
• $dbuser: database user
• $type: mysql or pg

If restoregrants fails

Reset the cPanel account password to rebuild grants.

5. Install or Update PostgreSQL

Before installing or upgrading PostgreSQL, back up your databases:

pg_dumpall -U postgres > db.out

Stop PostgreSQL and move the data directory

systemctl stop postgresql.service
mv /var/lib/pgsql /var/lib/directory_file_name

6. Install or Upgrade PostgreSQL

/usr/local/cpanel/scripts/installpostgres

Then configure PostgreSQL in WHM under Configure PostgreSQL.

Import databases

psql -U postgres dbname < dumpfile.sql

7. Restore a Previous PostgreSQL Version

systemctl stop postgresql.service
mv /var/lib/pgsql /var/lib/directory_file_name
mv /var/lib/pgsql.old /var/lib/pgsql
systemctl restart postgresql.service

For more details, visit the PostgreSQL website.