ThisCalculator

Everyday

Move MySQL Data Directory Safely: Step-by-Step

Updated · Standard formula, shown below

Move MySQL Data Directory Safely: Step-by-Step

Moving your MySQL data directory (the folder where databases are stored) to a new location — like a larger disk or a dedicated SSD — can free up space and improve performance. This guide walks you through the process safely, with a full backup and a test rollback plan, so your data remains intact and your service doesn’t go down unexpectedly.

What You’ll Need

  • Root or sudo access to the server.
  • Ability to stop and start the MySQL service (e.g., systemctl or service).
  • Enough free space on the target disk to hold a copy of the data directory.
  • Basic comfort with the command line and file permissions.

Step-by-Step

  1. Find the current data directory. Run mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';" or check your MySQL config file (usually /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf). The default is often /var/lib/mysql.
  2. Back up everything. Even though you’re moving the directory, you need a safety net. Use mysqldump to create a logical backup of all databases: mysqldump -u root -p --all-databases > /tmp/all-databases.sql. Also copy the entire data directory to a temporary location (e.g., cp -a /var/lib/mysql /tmp/mysql-backup) if you have the space. This second copy lets you roll back quickly if something goes wrong.
  3. Stop MySQL cleanly. Run sudo systemctl stop mysql (or sudo service mysql stop). Wait until the process is fully stopped — check with sudo systemctl status mysql to confirm it’s inactive.
  4. Copy the data directory to the new location. Use rsync to preserve permissions and ownership: sudo rsync -av /var/lib/mysql/ /new/path/mysql/. The trailing slashes matter — they copy the contents, not the folder itself. If the new path doesn’t exist, create it first with sudo mkdir -p /new/path/mysql.
  5. Verify the copy. Check that the copied directory has the same files and ownership as the original. Run sudo ls -la /new/path/mysql and compare with sudo ls -la /var/lib/mysql. The owner should be mysql:mysql.
  6. Update the config file. Open your MySQL config file (e.g., sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf) and find the line datadir = /var/lib/mysql. Change it to datadir = /new/path/mysql. Also look for any lines that reference the old path (like socket or log-bin) and update them if they point to the old directory.
  7. Handle AppArmor (if applicable). On Ubuntu/Debian, AppArmor may block MySQL from accessing the new location. Check if AppArmor is enabled: sudo aa-status. If it is, edit /etc/apparmor.d/usr.sbin.mysqld and add lines like /new/path/mysql/ r, and /new/path/mysql/** rwk,. Then reload AppArmor: sudo systemctl reload apparmor.
  8. Start MySQL. Run sudo systemctl start mysql. If it starts without errors, check the logs (sudo tail -f /var/log/mysql/error.log) for any warnings. Then verify the datadir has changed: mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';" — it should show the new path.
  9. Test your databases. Run a simple query on a database you know exists: mysql -u root -p -e "SHOW DATABASES;". If everything looks normal, you’re done.
  10. Remove the old directory (optional but recommended). Once you’re confident everything works, you can delete the old data directory to free space: sudo rm -rf /var/lib/mysql. Do this only after the new location has been stable for a few days. Keep the /tmp/mysql-backup until you’re sure.

Common Mistakes

  • Forgetting to stop MySQL before copying. If the server is running, files may be in use or partially written, leading to corruption.
  • Copying the directory into itself. If the new path is inside the old one, you’ll create an infinite loop. Always use a separate location.
  • Wrong file permissions. MySQL runs as the mysql user. If the new directory is owned by root, MySQL won’t be able to read or write it. Use chown -R mysql:mysql /new/path/mysql.
  • Ignoring AppArmor or SELinux. On many Linux distributions, security modules will block MySQL from using a non-standard path, even if the config is correct.
  • Not updating the socket path. Some clients connect via the socket file; if that path is still in the old directory, connections will fail.

Tips

  • Use rsync instead of cp — it’s faster for large directories and preserves permissions better.
  • Before starting, check disk space on the new location with df -h to make sure you have enough room.
  • If you’re moving to a new disk, make sure it’s mounted and configured to auto-mount on boot (add to /etc/fstab).
  • Keep a copy of your old config file (sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /tmp/) so you can revert quickly if needed.
  • Consider using a symlink from the old location to the new one if you have applications that hard-code the path. For example: sudo ln -s /new/path/mysql /var/lib/mysql (but only after moving the data).

Troubleshooting

  • MySQL won’t start after the move. Check the error log (sudo tail -50 /var/log/mysql/error.log). Common issues: permission errors (fix with chown -R mysql:mysql /new/path), missing directories (create them), or AppArmor denials (check sudo dmesg | grep apparmor).
  • Can connect via TCP but not via socket. The socket file path is likely still pointing to the old location. Update the socket directive in the config file and restart.
  • Data appears missing. You may have copied to the wrong subdirectory (e.g., /new/path/mysql/mysql). Double-check the directory structure — the new path should contain subdirectories like mysql, performance_schema, and your database folders.
  • Permission denied on the new directory. Run sudo chown -R mysql:mysql /new/path/mysql and sudo chmod 750 /new/path/mysql (or match the original permissions with sudo stat -c '%a' /var/lib/mysql).
  • AppArmor is blocking even after editing. After editing the profile, run sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld to reload it, then restart MySQL.

Frequently Asked Questions

Can I move the data directory while MySQL is running?

No. You must stop MySQL first. Copying a live database directory will result in corrupted data because files are being written while you copy.

Do I need to change the socket path too?

Only if the socket file is located inside the old data directory. Check the config for a socket line and update it if it points to the old path.

What if I don’t have enough space to copy the directory?

You can move the directory instead of copying it, but that’s riskier. A safer alternative is to use mysqldump to back up the data logically, then restore it to the new location after changing the datadir.

Is it safe to delete the old data directory immediately?

No. Wait at least a few days and ensure the new location works perfectly. Keep the old directory (or a backup) until you’re 100% sure.

Does this work on Windows or macOS?

The steps are similar, but the config file and service commands differ. On Windows, you’d edit my.ini and use the Services console; on macOS, the config is often /etc/my.cnf and you’d use brew services or launchctl. The core principle — stop, copy, update config, start — is the same.

What about SELinux on CentOS/RHEL?

If SELinux is enforcing, you’ll need to set the correct context on the new directory. Use sudo semanage fcontext -a -t mysqld_db_t "/new/path/mysql(/.*)?" then sudo restorecon -Rv /new/path/mysql. Otherwise, MySQL will be blocked.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.