How to Change Data Directory in MySQL: Step-by-Step Guide

To change the MySQL data directory, stop the MySQL server, move the existing data files to the new directory, update the datadir setting in the MySQL configuration file (usually my.cnf), and restart the server. Ensure proper permissions on the new directory for MySQL to access the data.

Syntax

The main step to change the data directory is to update the datadir parameter in the MySQL configuration file.

• datadir: Specifies the path where MySQL stores its database files.
• Configuration file: Usually /etc/my.cnf or /etc/mysql/my.cnf on Linux systems.
• MySQL service commands: Used to stop and start the MySQL server safely.

ini

[mysqld]
datadir=/new/path/to/mysql/data

Example

This example shows how to move the MySQL data directory to /var/lib/mysql_new on a Linux system.

bash

# Stop MySQL server
sudo systemctl stop mysql

# Move existing data directory to new location
sudo mv /var/lib/mysql /var/lib/mysql_new

# Update ownership and permissions
sudo chown -R mysql:mysql /var/lib/mysql_new

# Edit MySQL config file (e.g., /etc/mysql/my.cnf) and set:
# datadir=/var/lib/mysql_new

# Start MySQL server
sudo systemctl start mysql

# Verify MySQL is running and using new data directory
sudo systemctl status mysql

Output

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since ...

# No errors means MySQL is running with new data directory

Common Pitfalls

• Not stopping MySQL before moving data: Can cause data corruption.
• Incorrect permissions: MySQL user must own the new data directory.
• SELinux or AppArmor restrictions: Security modules may block access to new directory.
• Forgetting to update datadir in config: MySQL will still use old directory.

bash

# Wrong: Moving data without stopping MySQL
sudo mv /var/lib/mysql /var/lib/mysql_new

# Right: Stop MySQL first
sudo systemctl stop mysql
sudo mv /var/lib/mysql /var/lib/mysql_new
sudo chown -R mysql:mysql /var/lib/mysql_new
sudo systemctl start mysql

Quick Reference

Step	Command/Action	Notes
1	Stop MySQL server	sudo systemctl stop mysql
2	Move data directory	sudo mv /var/lib/mysql /new/path
3	Set ownership	sudo chown -R mysql:mysql /new/path
4	Update config file	Set datadir=/new/path in my.cnf
5	Start MySQL server	sudo systemctl start mysql
6	Verify status	sudo systemctl status mysql

Key Takeaways

Always stop the MySQL server before moving the data directory to avoid corruption.

Update the datadir parameter in the MySQL configuration file to point to the new location.

Ensure the new data directory has correct ownership and permissions for the MySQL user.

Check for security modules like SELinux or AppArmor that may block access to the new directory.

Verify MySQL starts correctly after the change and uses the new data directory.