How do I reinitialize MySQL or MariaDB without reinstalling the RPMs?

Introduction

The steps outlined in this guide move the existing data. You will need cPanel backups or SQL dumps in order to restore your account databases through the cPanel interface or the MySQL command-line once MySQL or MariaDB has been initialized. cPanel support cannot assist you with these steps, and we do recommend consulting with a qualified system administrator before proceeding. 

There may be a time when you need to reinitialize MySQL. You can use the information below to reinitialize the MySQL server; this will move the existing installation and reinstall the MySQL server. We recommend reading through this guide in its entirety before starting the process.

Procedure

To reinitialize MySQL, perform the following steps:

1. Disable the service:

   whmapi1 configureservice service=mysql enabled=1 monitored=0

2. Stop the service:

   /scripts/restartsrv_mysql --stop

3. Check to make sure you have enough disk space before you move the MySQL folder; this can be done with du and df -h:

   du checks how much total space is in your MySQL directory:

   [root@server ~]# du -h --max-depth=1 /var/lib/mysql
   20M /var/lib/mysql/mysql
   680K /var/lib/mysql/sys
   4.0K /var/lib/mysql/user_test
   4.0K /var/lib/mysql/performance_schema
   7.1M /var/lib/mysql/angel_wp
   897M /var/lib/mysql/angel_test_group
   46M /var/lib/mysql/user_vtiger
   4.2M /var/lib/mysql/angel_wp2
   1.2G /var/lib/mysql

   And df shows the total disk usage:

   [root@server ~]# df -h
   Filesystem Size Used Avail Use% Mounted on
   devtmpfs 3.9G 0 3.9G 0% /dev
   tmpfs 3.9G 4.0K 3.9G 1% /dev/shm
   tmpfs 3.9G 74M 3.8G 2% /run
   tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
   /dev/mapper/centos-root 50G 13G 38G 25% /
   /dev/sda1 1014M 306M 709M 31% /boot
   /dev/mapper/centos-home 42G 16G 26G 39% /home
   /dev/loop0 2.2G 3.6M 2.1G 1% /tmp
   tmpfs 783M 0 783M 0% /run/user/0

4. Move the directory containing MySQL. If your home data directory for MySQL is different, adjust the path accordingly:

   mv /var/lib/mysql{,.cPbak} -v && mkdir -v /var/lib/mysql && chown -v mysql. /var/lib/mysql

5. Run the initialize option for MySQL:

   mysqld --initialize --user=mysql

   For MariaDB, run this command instead:

   mysql_install_db --user=mysql

6. Restart MySQL again:

   /scripts/restartsrv_mysql --start

7. Reenable the monitoring service:

   whmapi1 configureservice service=mysql enabled=1 monitored=1

8. Reset the MySQL password:

   /scripts/mysqlconnectioncheck

9. Disable the validate_password plugin option. To do this, refer to this article:

   MySQL "Your password does not satisfy the current policy requirements"

10. Restore the databases:
    • Through cPanel: Backup for cPanel
    • Through MySQL directly:

      Reloading SQL format dumps

      How to import or restore a database via SSH

11. Restore the grants: How to Restore Database Grants
12. Restore database access: How to Restore a User's Database Access
    If the system runs CloudLinux run the following two commands as well.

13. cagefsctl -M 

14. cagefsctl --force-update