Moving the MySQL Data Directory

This article will guide you through the process of moving your MySQL data directory (datadir) to a new location on your server. This might be necessary if you’re running out of disk space on the current partition or if you want to organize your server’s storage differently.

Default MySQL data directory: /var/lib/mysql/

Example new data directory: /home/mysql.datadir/

Please adjust the paths in this guide to match your specific new location.

Important prerequisites and considerations

Before you begin, please carefully review the following points. Failure to do so could result in data loss or service interruption.

• Backup Your Databases (Recommended): If space and time permit, it’s highly recommended to perform a fresh dump (backup) of all your databases before starting this process. This provides an extra layer of safety.
• Use an Empty Directory: The new location for your MySQL data directory must be an empty directory. Do not point it to a directory that already contains files or other data.
• CloudLinux with CageFS:
  • If your server is running CloudLinux with CageFS, you must update the new mount point in /etc/cagefs/cagefs.mp.
  • Refer to the official CloudLinux documentation for details: http://docs.cloudlinux.com/mount_points.html
  • After configuring MySQL but before restarting it, you will need to run cagefsctl --remount-all.
• Acronis Backups:
  • If your server uses Acronis for backups, check if there’s a backup plan specifically for MySQL. If so, you’ll need to update this plan to point to the new data directory location.
  • Additionally, the general server backup plan in Acronis (often labeled “server” backup plan) usually has an exclusion for /var/lib/mysql. You must update this exclusion to the new datadir path to prevent your MySQL data from being backed up twice and to ensure the old path is no longer excluded if it’s being used for other purposes.
• Service Downtime: This process requires stopping MySQL and other related services. Plan for a maintenance window to minimize disruption.

1. Create the new data directory

First, you’ll need to create the directory where your MySQL data will be moved and set the correct ownership and permissions.

mkdir -p /home/mysql.datadir/
chown mysql:mysql /home/mysql.datadir/
chmod 751 /home/mysql.datadir/

• mkdir -p: Creates the directory and any necessary parent directories if they don’t exist.
• chown mysql:mysql: Sets the owner and group of the directory to mysql. This is crucial for MySQL to be able to read and write data.
• chmod 751: Sets the permissions for the directory. The mysql user will have read, write, and execute permissions, the mysql group will have read and execute permissions, and others will have execute permission.

2. Initial data synchronization

Next, we’ll copy the existing MySQL data to the new directory using rsync. rsync is efficient for copying large amounts of data and can resume interrupted transfers.

rsync -aHlP /var/lib/mysql/ /home/mysql.datadir/

• -a: Archive mode (preserves permissions, ownership, timestamps, etc.).
• -H: Preserves hard links.
• -l: Copies symlinks as symlinks.
• -P: Combines --progress (shows progress during transfer) and --partial (allows resumption of interrupted transfers).

Info: It’s a good practice to run this rsync command a couple of times, especially on active servers. This minimizes the amount of data that needs to be transferred during the final sync when services are stopped.

3. Stop services

Now, you need to stop MySQL and related services before performing the final synchronization and switching the directory.

Bash

systemctl stop tailwatchd
systemctl stop crond
systemctl stop mysqld

• tailwatchd: cPanel service that monitors logs.
• crond: The cron daemon that runs scheduled tasks.
• mysqld: The MySQL server daemon.

⚠️ Warning: It is critical to ensure MySQL has completely shut down before proceeding. Moving the data directory while MySQL is running or if it restarts unexpectedly can lead to severe data corruption.

Verify that MySQL is not running:

ps aux | grep mysql
netstat -tpln | grep mysql

The ps aux | grep mysql command should not show an active mysqld process (you might see the grep command itself). The netstat -tpln | grep mysql command should not show any services listening on the MySQL port (typically 3306).

4. Final sync and switch data directory

Once you’ve confirmed MySQL is stopped, perform a final rsync to ensure all data is up-to-date in the new location. Then, move the old directory and create a symbolic link from the original location to the new one.

1. Final Sync: rsync -aHlP /var/lib/mysql/ /home/mysql.datadir/ Running this command a second time immediately should show no data being transferred, confirming the sync is complete.
2. Move Old Directory and Create Symlink: mv /var/lib/mysql /var/lib/mysql.lwold ln -s /home/mysql.datadir /var/lib/mysql
   • mv /var/lib/mysql /var/lib/mysql.lwold: Renames the original MySQL data directory. This acts as a temporary backup.
   • ln -s /home/mysql.datadir /var/lib/mysql: Creates a symbolic link. MySQL and other applications will still look for data in /var/lib/mysql, but they will be seamlessly redirected to /home/mysql.datadir/.

5. Update MySQL configuration (my.cnf)

While using a symbolic link often works without explicit configuration changes for MySQL itself, it’s best practice to update your MySQL configuration file (my.cnf or my.ini) to reflect the new data directory path. This avoids potential issues with future MySQL upgrades or other tools that might read the configuration file directly.

1. Open your MySQL configuration file. Common locations include /etc/my.cnf, /etc/mysql/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf.
2. Locate the [mysqld] section.
3. Add or modify the datadir and socket (if it was in the old datadir) parameters: Ini,
   • [mysqld] datadir=/home/mysql.datadir # If your socket file was inside /var/lib/mysql, you might also need to update it. # Example: socket=/home/mysql.datadir/mysql.sock # Or, place it in a standard location like /var/run/mysqld/mysqld.sock # and ensure MySQL has permissions to write there. Note: The socket path update is only necessary if your socket file was previously located within /var/lib/mysql/. Often, it’s in /tmp/mysql.sock or /var/run/mysqld/mysqld.sock by default, in which case you might not need to change it. Check your existing my.cnf for the current socket location.

6. CloudLinux CageFS adjustment (if applicable)

Notice: If your server is running CloudLinux with CageFS, run the following command before restarting MySQL to update the CageFS mount points:

cagefsctl --remount-all

7. Restart services

Now, restart the services you stopped earlier.

systemctl start mysqld
systemctl start tailwatchd
systemctl start crond

8. Verify MySQL and check logs

It’s crucial to verify that MySQL started correctly and is using the new data directory.

1. Check MySQL Status: systemctl status mysqld Look for an “active (running)” status.
2. Check MySQL Error Log: Examine the MySQL error log for any startup errors. The location of the error log is typically defined in my.cnf (look for log-error). Common locations include /var/log/mysqld.log, /var/log/mysql/error.log, or /home/mysql.datadir/hostname.err. If MySQL started successfully, you should see entries indicating it’s using the new datadir.
3. Log in to MySQL and check the datadir variable (Optional): mysql -u root -p Then run: SQL SHOW VARIABLES LIKE 'datadir'; This should display /home/mysql.datadir/.

If everything looks good, your MySQL data directory has been successfully moved!

9. Cleanup and final adjustments

• Remove Old Data Directory (Important for freeing space): Be very careful with this step, as deleting the wrong directory can lead to data loss. rm -rf /var/lib/mysql.lwold This is especially important if the reason for moving the datadir was to free up space on the partition where /var/lib/mysql was located.

If you encounter any issues or have questions, please don’t hesitate to contact our support team for assistance.