Changing MySQL Data Directory on macOS and Using It With Docker

We will learn how to change the data directory for MySQL, and how to use this with a Docker container.

Posted by Tahir Waseer on November 01, 2020

I’ve been using mysql for lot of my projects over time and normally it is not a problem to work with it’s default configurations. But it is usually a good idea to make things user specific to have a better control over your data.

In my case I’ve installed mysql via Homebrew and wanted to share data with my docker container running on my local environment. You can avoid this altogether if you are starting from scratch and using docker for your development environment and don’t have a need to install mysql on your machine. By default Homebrew version of mysql stores data at /opt/homebrew/var/mysql/.

You can check your data directory using;

mysql -u USER -p -e 'SHOW VARIABLES WHERE Variable_Name = "datadir"'

Replace USER with your user i.e root.

I’m using Macbook for development but this should work on Linux as well with some minor changes.

Prerequisites

  • MySQL installed on your machine
  • Docker installed on your machine

Step 1: Change MySQL Data Directory

1.1. Stop MySQL server

First, stop the MySQL server if it’s running:

brew services stop mysql

1.2. Edit MySQL configuration file

Next, edit the MySQL configuration file, /opt/homebrew/etc/my.cnf, using your preferred text editor:

nano /opt/homebrew/etc/my.cnf

1.3. Add datadir setting

In the configuration file, add the following line under the [mysqld] section, replacing /Users/yourusername/mysql-data with the desired path for the new data directory:

datadir=/Users/yourusername/mysql-data

Save and close the file.

1.4. Create the new data directory

Create the new data directory:

mkdir /Users/yourusername/mysql-data

1.5. Copy existing data to the new data directory

Copy the existing MySQL data to the new data directory:

cp -R /opt/homebrew/var/mysql/* /Users/yourusername/mysql-data/

1.6. Update permissions

Update the ownership and permissions for the new data directory:

sudo chown -R _mysql:_mysql /Users/yourusername/mysql-data

Replace _mysql:_mysql with the user and group you found earlier if they are different.

1.7. Start MySQL server

Start the MySQL server:

brew services start mysql

MySQL should now be using the new data directory located in your user’s home directory.

Step 2: Use the New Data Directory with Docker

2.1. Create a Docker Compose file

Create a docker-compose.yml file with the following contents:

version: '2'
services:
  mysql:
    image: mysql:5.7
    container_name: mysql_container
    environment:
      MYSQL_ROOT_PASSWORD: your_root_password
    volumes:
      - /Users/yourusername/mysql-data:/var/lib/mysql
    ports:
      - "3306:3306"

Replace your_root_password with a strong password for the MySQL root user. Make sure to update the volume path to match the new data directory you’ve created.

2.2. Start the Docker container

Start the Docker container using the docker-compose.yml file:

docker-compose up -d

The MySQL container should now be running and using the new data directory located in your user’s home directory.

Conclusion

With this setup, you can easily manage your MySQL data outside the container and ensure it persists even when the container is removed or updated. There are many other ways to achieve this like you can use like docker named volumes to persist data, but this is what I used and it worked for me. I hope this helps you too.