How to Change MySQL Data Directory on macOS

Modifying the datadir of MySQL on macOS can indeed fail due to issues related to permissions, configuration file locations, and more. Below is a detailed set of steps and considerations that take into account the characteristics of macOS, which I hope will help you succeed.

This case primarily targets MySQL installed via Homebrew (a very common installation method on macOS) or the official DMG package. Please adjust some commands according to your actual installation method (especially for starting/stopping services and configuration file paths).

Core Idea:

1. 1. Backup! (The most important step)
2. 2. Stop the MySQL service.
3. 3. Determine the current datadir and the location of the configuration file my.cnf.
4. 4. Create a new data directory and set the correct permissions.
5. 5. Migrate data to the new directory.
6. 6. Modify the MySQL configuration file to point to the new directory.
7. 7. (May need) Handle macOS App Sandbox or other permission restrictions (rare, but possible).
8. 8. Start the MySQL service.
9. 9. Verify.
10. 10. Clean up old data (optional).

Detailed Steps:

Step 0: Backup the Database! Before performing any operations, be sure to back up all important databases. Using mysqldump is a good method:

mysqldump -u root -p --all-databases > ~/all_databases_backup.sql
# Enter your MySQL root password

Replace ~/all_databases_backup.sql with the path where you want to store the backup file.

Step 1: Stop the MySQL Service

• • If you installed using Homebrew:

  brew services stop mysql
  # Or if it's a specific version, like [email protected]
  # brew services stop [email protected]

  Confirm the service has stopped: brew services list

• • If you installed using the official DMG (usually has a startup item in System Preferences):
• • Try stopping the service through the MySQL panel in System Preferences.
• • Or use the command line:

  sudo /usr/local/mysql/support-files/mysql.server stop

Step 2: Determine the Current datadir and Configuration File my.cnf Location

• • Find the current datadir: If MySQL is still running (or you remember the previous value), you can log into MySQL and execute:

  SHOW VARIABLES LIKE 'datadir';

  Note this path; it is your old data directory. The default path for Homebrew installations is usually /usr/local/var/mysql/ or /opt/homebrew/var/mysql/ (for Apple Silicon). The default path for official DMG installations is usually /usr/local/mysql/data/.

• • Find the my.cnf configuration file: MySQL looks for configuration files in a specific order. You can check which locations it will look at using the following command:

  mysql --verbose --help | grep my.cnf

  Common possible locations include:

• /etc/my.cnf
• /etc/mysql/my.cnf
• /usr/local/etc/my.cnf (commonly used with Homebrew)
• /opt/homebrew/etc/my.cnf (commonly used with Homebrew on Apple Silicon)
• ~/.my.cnf (user personal configuration)
• The official DMG installation package may place a default one in /usr/local/mysql/ or may not have one at all, requiring you to create it yourself.

Step 3: Create a New Data Directory and Set Permissions

1. 1. Select a new location: Assume your chosen new location is /Volumes/ExternalSSD/mysql_data (please replace it with your actual, spacious path).
2. 2. Create the directory:

   mkdir -p /Volumes/ExternalSSD/mysql_data

3. 3. Set permissions: This is a crucial step. The MySQL process usually runs as the _mysql user. You need to ensure this user has full read and write permissions on the new directory.

   sudo chown -R _mysql:_mysql /Volumes/ExternalSSD/mysql_data
   sudo chmod -R 700 /Volumes/ExternalSSD/mysql_data

• • chown -R _mysql:_mysql sets the owner and group of the directory and all its contents to _mysql.
• • chmod -R 700 sets permissions so that only the owner (i.e., _mysql) has read, write, and execute permissions, which is the recommended security setting for data directories.

Step 4: Migrate Data

Using rsync is a good choice as it better preserves permissions and attributes and can show progress. Ensure the MySQL service is stopped!

# Assume the old directory is /usr/local/var/mysql/
# Note that the trailing slash / on the old directory indicates copying the contents of the directory, not the directory itself
sudo rsync -av /usr/local/var/mysql/ /Volumes/ExternalSSD/mysql_data/

• • Replace /usr/local/var/mysql/ with the actual old datadir path you found in Step 2.
• • Replace /Volumes/ExternalSSD/mysql_data/ with the new datadir path you created in Step 3.
• • -a option means archive mode, preserving permissions, ownership, timestamps, etc.
• • -v option means verbose output, showing the files being copied.

Wait for the data copy to complete. If the data volume is large, it may take some time.

Step 5: Modify the MySQL Configuration File

Edit the my.cnf file you found in Step 2. Use sudo as these files usually require administrative permissions to edit.

sudo nano /usr/local/etc/my.cnf
# Or sudo vim /usr/local/etc/my.cnf
# Or the other path you found

Find the section [mysqld]. If it does not exist, add a [mysqld]. Under the [mysqld] section, find the datadir configuration item.

• • If it exists: Modify its value to your new path.
• • If it does not exist: Add a line.

The modified/added content should look something like this:

[mysqld]
# ... other mysqld configurations ...
datadir = /Volumes/ExternalSSD/mysql_data
# Ensure the path is your newly created path
# ... other mysqld configurations ...

Save and close the file (in nano, it’s Ctrl+X, then press Y to confirm, and then Enter).

Step 6: (Optional) Handle macOS Special Permission Issues (Usually Not Required)

In rare cases, macOS’s security mechanisms (like App Sandbox, if MySQL is configured to run within it) may prevent MySQL from accessing files in non-standard locations. If subsequent startup fails and the logs indicate permission issues, and you are sure the filesystem permissions (chown, chmod) are set correctly, you may need to check this aspect. However, this is not common for standard Homebrew or official DMG installations. If encountered, check the MySQL error log for specific information.

Step 7: Start the MySQL Service

Use the command corresponding to Step 1 to start the service:

• • Homebrew:

  brew services start mysql
  # Or brew services start [email protected]

• • Official DMG:

  sudo /usr/local/mysql/support-files/mysql.server start

Step 8: Verify

1. 1. Check the service status:

• • Homebrew: brew services list (check if the status is started)
• • Official DMG: sudo /usr/local/mysql/support-files/mysql.server status

• • For example, check the /Volumes/ExternalSSD/mysql_data/your_hostname.err file.
• • Common errors include: insufficient permissions (Permission denied), file not found (File not found), syntax errors in the configuration file, etc.

mysql -u root -p

SHOW VARIABLES LIKE 'datadir';

SHOW DATABASES;
USE your_database_name;
SHOW TABLES;
SELECT * FROM your_table_name LIMIT 1; -- Try to read data

Step 9: Clean Up Old Data (Optional, Confirm Before Doing)

Once you confirm that MySQL is running stably on the new data directory and all data is normal, you may consider deleting the old data directory to free up space.

Very Important: Confirm again that you no longer need the old data and that the backup is available before proceeding!

# Confirm the old directory path again!
sudo rm -rf /usr/local/var/mysql_OLD # It is strongly recommended to rename the old directory first, observe for a few days before deleting
# For example: sudo mv /usr/local/var/mysql /usr/local/var/mysql_OLD
# Wait a few days to confirm everything is correct before executing sudo rm -rf /usr/local/var/mysql_OLD

Common Issues and Troubleshooting:

• • Startup Failure/Permission Errors:
• • Carefully check if the chown and chmod commands in Step 3 were executed correctly and if the paths are correct.
• • Check if the parent directory of the new datadir has sufficient permissions for the _mysql user to access it. For example, if /Volumes/ExternalSSD itself prevents the _mysql user from accessing it, it will also fail. Typically, external hard drives have open permissions when mounted, so this is usually not a problem.
• • Carefully read the MySQL error log (your_hostname.err), which will provide the most direct reason for failure.
• • Configuration File Not Taking Effect:
• • Confirm that you modified the my.cnf file that MySQL actually loads (refer to the method in Step 2).
• • Ensure the datadir configuration item is under the [mysqld] section and that there are no syntax errors (such as typos in the path, extra spaces, etc.).
• • Data Loss or Corruption:
• • If MySQL was still running during the migration or if the migration was interrupted, it could lead to data corruption. Always perform the migration after MySQL has completely stopped.
• • If issues occur, immediately stop MySQL and restore using the backup from Step 0.

I hope this detailed step-by-step guide helps you successfully migrate your MySQL data directory! Good luck!