How to Move MySQL Datadir

Question

How do you move the MySQL datadir?

Answer

Running out of disk space is typical for the primary filesystem or partition on a server. For example, if  /root or / are low on disk space, moving /var/lib/mysql to /home will free up some storage at this location. This guide will assist with moving the directory on a server with cPanel.

Warning: This procedure requires a certified administrator to prevent critical failures. The details in this article should not be used for MariaDB. Moving MariaDB to /home causes upgrades to be blocked for MariaDB. 

As of MariaDB 10.1.16, the data directory cannot reside in /home, /usr, /etc, /boot, or /root directories on systemd-equipped systems.

It is possible to disable the ProtectHome feature and use the whmapi1 API to perform the upgrade process; however, this task is intended for administrators experienced with MySQL/MariaDB.

MySQL version 5.7 was in use for this process. Please be sure to set aside downtime to perform the following steps:

1. Make a full mysqldump backup of all databases.

   # mysqldump -A --opt > /home/mysqldump.sql

2. Disable WHM MySQL monitoring to prevent restarts during the process. Restarts could cause issues during this procedure.

   # whmapi1 configureservice service=mysql enabled=1 monitored=0

3. Stop MySQL.

   # /scripts/restartsrv_mysql --stop

4. Create or edit the file /etc/systemd/system/mysqld.service.d/override.conf and ensure the following settings are present:

   CONFIG_TEXT: [Service]
   ProtectHome=false
   ProtectSystem=off

5. Reload with systemctl after the changes.

   # systemctl daemon-reload

6. Make the directory for MySQL in /home, and move it.

   # mkdir -v /home/var_mysql; mv -v /var/lib/mysql /home/var_mysql; chown -Rv mysql:mysql /home/var_mysql/mysql

7. Create an empty /var/lib/mysql directory where the socket file will be located.

   # mkdir -v /var/lib/mysql/; chown -v mysql:mysql /var/lib/mysql/

8. Edit /etc/my.cnf file to ensure it contains these directives under the [mysqld] section.

   CONFIG_TEXT: socket=/var/lib/mysql/mysql.sock
   datadir=/home/var_mysql/mysql

   Note: The socket file path may already exist; you can omit this change if the value exists.
   The datadir path may already exist as well. You must change the existing configuration option or add a comment before the entry to disable it, and then add the new datadir value. The configuration file must only have one socket and one datadir value.

9. Start MySQL.

   # /scripts/restartsrv_mysql --start

10. Enable MySQL monitoring.

    # whmapi1 configureservice service=mysql enabled=1 monitored=1

On Ubuntu systems, the AppArmor profile needs updating; this can be easily configured in the /etc/apparmor.d/local/usr.sbin.MySQL file. Update the end of this file with a configuration like the following. In this example, /home/mysql was my new MySQL directory:

CONFIG_TEXT: /home/mysql/ r,
/home/mysql/** rwk,
/home/mysql/plugin/ r,
/home/mysql/plugin/*.so* mr,

After the steps have been performed successfully, MySQL will run with the new datadir. The command below will help verify this was successful.

# lsof -p `ps -fU mysql|awk '{print $2}' |grep -v PID`

Note: If these details are followed on CloudLinux servers, additional steps may be required if websites return 500 errors or cannot connect to the databases. Please take a look below for more details.

On CloudLinux servers, sites show database-connection errors after move of MySQL data directory