You may require to change the MySQL data directory due to insufficient partition space on /var or /root and change the data directory to the /home for your server, including cPanel. However, there might also be other reasons for changing the data directory.
By default, MySQL/ MariaDB data directory is /var/lib/mysql. It stores all the databases to /var/lib/mysql.
Please refer to the following steps to change the MySQL Data Directory to /home.
1. First of all, we will create a backup of all MySQL databases so that there is no data loss if anything goes wrong.
# tar -cvf mysql.tar /var/lib/mysql
2. Once you are done with the backup, stop the MariaDB service with the command given below –
------------- On SystemD -------------
# systemctl stop mariadb
# systemctl is-active mariadb
------------- On SysVInit -------------
# service mysqld stop
# service mysqld status
3. Install the screen command -
yum install screen
screen // It will create screen
4. Now, we will sync all the MySQL databases to the /home/mysql.
The following command will create a mysql directory in /home and start the sync process.
rsync -avz /var/lib/mysql to /home
5. While syncing is in progress, you can also work on other tasks. Once multiple screens are created, you can resume/ switch between screens.
screen -r // it will resume the previous screen
ctrl A and ctrl D // for return to main screen
ctrl A and shift? // for screen command menu
6. To change MySQL/ MariaDB data directory, edit the /etc/my.cnf file with your preferred editor.
vi /etc/my.cnf
7. Change the data directory from /var/lib/mysql to the /home.
If you don’t find an existing line of datadir, you can add a new line as mentioned below –
datadir=/home/mysql
8. You will need to relink the socket file to the /tmp.
# rm -rf /tmp/mysql.sock
# ln -sf /home/mysql/mysql.sock /tmp/mysql.sock
9. To run MariaDB Service from /home, you need to modify the mariadb.service file at the /usr/lib/systemd/system/mariadb.service location.
ProtectHome=true to ProtectHome=false
10. Finally, start your MariaDB service.
systemctl start mariadb
11. Your MariaDB service may fail and show this warning message –
MariaDB cannot start after update: [Warning] Need to run systemctl daemon-reload
12. To resolve this error, you can reboot the server and start the MariaDB service again, and it will work again.
13. To verify the functionality, we will need to create a database from cPanel, which should be created inside /home/mysql.
If your database is created inside /home/mysql, it means you are successful in your task.
Please refer to cPanel > create MySQL Database for creating MySQL database.
14. If everything is working fine, you can remove the old data directory from /var/lib/mysql.
rm -rf /var/lib/mysql
