Introduction
When configuring your cPanel system backups, you have the option to enable backups for the Entire MySQL Directory. As there is no restore tool for this type of backup, the following guide will walk you through this process.
Procedure
Please be aware that we recommend this task only be performed by a system administrator that is familiar with the commands provided. Since this task involves direct changes to the MySQL datadir, this can result in data loss if done incorrectly. If do not have an available system administrator, we advise referring to the following article.
System Administration Services
If you have an exceptionally large MySQL datadir, these tasks can take some time to complete. It is advisable that you perform the following in a screen session if this task will take an extended period of time. This will allow for the task to continue running if your session is interrupted.
Archive Extraction
You will first need to extract your MySQL directory backup from the archive. Before performing this task, be sure that you have enough available space on the drive/partition to accommodate the uncompressed data. For the purposes of this tutorial, we are assuming that the server only has 1 partition for all of its content. If your server has limited space where the MySQL data is stored, you may need to adjust these commands.
To extract the backup, you can use the tar command. In the below example, I am extracting the daily MySQL directory backup to an alternative location in the backup directory. The directory labeled 0000-00-00 represents the date of the backup you wish to restore.
tar -xvf /backup/0000-00-00/system/dirs/_var_lib_mysql_.tar.gz --directory /backup/
If you wish to restore a weekly or monthly backup the path will vary as these are stored in subdirectories of the same name. To confirm which dates and backups are available to extract, you can use the following find command to locate them.
find /backup -type d -regex '^.+[0-9]'
After performing the above command, your MySQL directory backup will be viewable within the path /backup/var/lib/mysql. After verifying that the data is present and in an expected state, you can proceed with the restoration task.
Disable MySQL
To restore the MySQL directory from the backup, you will need to fully stop and disable the service. At no time should the datadir be modified while the service is online. To disable this service, you can use the following API command.
whmapi1 configureservice service=mysql enabled=0 monitored=0
The above command should output a success like the following. If there are errors, you will need to address these before proceeding.
---
metadata:
command: configureservice
reason: Disabled mysql. Disabled monitoring for mysql.
result: 1
version: 1
To confirm that the MySQL service was properly shutdown when disabled, use the following command to check its status.
/scripts/restartsrv_mysql --check
Restoring the Directory
Once you are confident that the service is offline, you can proceed with restoring the directory. To accomplish this, we will be moving the current directory and replacing it with the backup. Due to the nature of the MySQL dataset, we strongly advise against attempts to overwrite the existing data. Doing so can cause corruption of the dataset and leave it in an unrecoverable state.
Before you can restore the backup directory, you will need to move the current to an alternate location. The following command will change the name of the existing MySQL datadir to append the current date to it. If you are using a separate backup partition, this can reduce the time of this task. If the backup drive is located on the same partition or there are space constraints, this can be moved to that location instead.
mv -v /var/lib/mysql{,.$( date +%Y%m%d )}
The output from the above command should look like the following. If there are errors or an unexpected return, this will need to be addressed before proceeding.
‘/var/lib/mysql’ -> ‘/var/lib/mysql.20200101’
Next, you will need to move the extracted database into the default datadir location. This can also be performed using the mv command. Using the prior examples, the following command would move the extracted backup to the default datadir location.
mv /backup/var/lib/mysql /var/lib/
Then set the correct permissions for the directory.
chmod 0751 /var/lib/mysql
You can verify the results with the following command. Before proceeding you will need to ensure that the datadire is present in the correct location and the directory/files are all correctly owned by the mysql user.
ls -lah /var/lib/mysql/
Re-enabling MySQL
Once you are confident that all tasks have completed successfully, the MySQL service will need to be re-enabled. The following API command can be used to make this change.
whmapi1 configureservice service=mysql enabled=1 monitored=1
You should see the following output when enabling the service.
---
metadata:
command: configureservice
reason: Enabled mysql. Enabled monitoring for mysql.
result: 1
version: 1
Now that the task is complete, you should check again to ensure that the MySQL service is running normally.
/scripts/restartsrv_mysql --check
For details on how to configure your MySQL backups, see the following article.