How to Safely Change the Location of MySQL/MariaDB Data Directory on cPanel/WHM Servers?

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. There might be any other reason for changing the data directory as well. 

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. We assume you are 

  1. First of all, we will create a backup of all the MySQL databases so in case anything goes wrong, we don’t incur any data loss.

    # tar -cvf mysql.tar /var/lib/mysql
  2. Once you are done with the backup Stop the MariaDB service with the below command. 
 ------------- On SystemD ------------- 
# systemctl stop mariadb
# systemctl is-active mariadb

------------- On SysVInit ------------- 
# service mysqld stop
# service mysqld status
  1. Install the screen command 

    yum install screen
    screen // It will create screen
  2. Now, we will sync all the MySQL databases to the /home/mysql. Following command will create a mysql directory in /home and it will start the sync process.

    rsync -avz /var/lib/mysql to /home
  3. While syncing is in progress, you can work on other tasks as well. Once multiple screens are created, you can resume/switch between screens as follows.

    screen -r  //  it will resume previous screen
    ctrl A and ctrl D // for return to main screen
    ctrl A and shift ? // for screen command menu
  4. To Change MySQL/MariaDB data directory, edit the file /etc/my.cnf with your favorite editor.

    vi /etc/my.cnf
  5. 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 below. 

    datadir=/home/mysql 
  6. 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
  7. To run MariaDB Service from /home, you need to modify mariadb.service file at location /usr/lib/systemd/system/mariadb.service.

    ProtectHome=true to ProtectHome=false
    
  8. Finally, start your MariaDB service. 

    systemctl start mariadb
  9. Your MariaDB service may fail with below warning. To resolve this error, you can reboot the server and start the MariaDB service again and it will work again. 

    MariaDB cannot start after update: [Warning]  Need to run systemctl daemon-reload
    
  10. To verify the functionality, we will need to create a database from cPanel and it should be created inside /home/mysql. Please refer to cPanel > create MySQL Database for creating MySQL database. If your database is created inside /home/mysql, it means you are successful in your task. 

  11. If everything is working fine, you can remove the old data directory from /var/lib/mysql.

    rm -rf  /var/lib/mysql

Related Articles

How to check Various cPanel logs like WHM, Apache, MySQL, Email, Error etc?

You can use cPanel to access your Linux hosting account with GUI. It is relatively easier to work...

How to install Magento 2.4.2 on Centos 7.x with cPanel?

Magento is an open-source e-commerce application that written in PHP language. Magento also uses...

How to Install Visual Studio Code on CentOS 7?

Visual Studio Code is a Microsoft Product and an open-source cross-platform code editor. It...

What is IP forwarding in Linux? How to enable IP forwarding?

IP forwarding helps you to set up the OS to work as a router. You can set IP forwarding when you...

How To Install Postfix on Ubuntu 18.04?

Postfix is an open-source Mail Transfer Agent (MTA). It uses to route and deliver emails on a...

  • 0 Users Found This Useful

Was this answer helpful?