CODEWITHSUNDEEP
mysqlmacoshomebrew
jwarz
jwarz

Reputation: 531

Change mysql (homebrew) data dir

I would like to store my mysql databases on an external hard drive. Right now, the data is located at the default data dir /usr/local/var/mysql/ since I have installed mysql via homebrew.

To change the data dir, I have modified the datadir entry in the following file: /usr/local/Cellar/mysql/8.0.12/homebrew.mxcl.mysql.plist 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>homebrew.mxcl.mysql</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/mysql/bin/mysqld_safe</string>
    <string>--datadir=/Volumes/EXTHD/mysql</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local/var/mysql</string>
</dict>
</plist>

Unfortunately that did not change anything. I have also created the file /etc/my.cnf with that datadir entry. No success.

What else do I have to change to move the data to an external hard drive?

Upvotes: 1

Views: 3002

Answers (2)

user28996231
user28996231

Reputation: 1

None of the editing of service files or anything worked for me. The difference seemed to be in where homebrew initializes everything on installation. It ran the command:

/opt/homebrew/Cellar/mysql/9.0.1_9/bin/mysqld --initialize-insecure --user=james --basedir=/opt/homebrew/Cellar/mysql/9.0.1_9 --datadir=/opt/homebrew/var/mysql

After I removed the /opt/homebrew/var/mysql directory and everything in it, I re-ran the command:

/opt/homebrew/Cellar/mysql/9.0.1_9/bin/mysqld --initialize-insecure --user=james --basedir=/opt/homebrew/Cellar/mysql/9.0.1_9 --datadir=/Volumes/externalssd/databases

Which initialized everything again. You don't have to create the databases directory or whichever one you are using; mysql will create it. You certainly cannot just cp -r /opt/homebrew/var/mysql /Volumes/externalssd/data or similar and expect it to work. I can't brew service mysql start maybe because of the user it runs as or something like that, I get no useful logs at all.

But I can just write a shell script to start mysql:

#!/bin/zsh

/opt/homebrew/Cellar/mysql/9.0.1_9/bin/mysqld_safe --user=james --datadir=/Volumes/externalssd/databases

And that will run it as long as I keep the terminal open; I can run it in the background of course but I don't need that. Now I can use mysql and have all databases created and used on my external SSD.

You will have to change the MySQL version, username and datadir of course to suit.

Upvotes: 0

Balazs Kelemen
Balazs Kelemen

Reputation: 91

You have to modify it in /usr/local/opt/mysql/homebrew.mxcl.mysql.plist

(You might also need the check, whether this is the right location by brew --prefix mysql)

See: brew services: where to edit configuration?

Upvotes: 1

Related Questions