How do I relocate Data directory in mySQL to external drive (macintosh)?

Question

I have read several accounts of people installing MySQL and having space issues on their internal hard drive so the data file is moved to an external drive.
I have been through what I believe are the steps to do this with mySQL 8.0.21 using the preference pane MySQL / configuration to specify a new location for the data file. I first made a copy of the file /usr/local/mysql-8.0.21-macos10.15-x86_64/data on my external drive. When I try to start mySQL, the light flashes to green briefly, then back to red.  The error is below:
mysqld: File './binlog.index' not found (OS errno 1 - Operation not permitted)
2020-08-16T06:05:24.079096Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.21) starting as process 52332
2020-08-16T06:05:24.161137Z 0 [Warning] [MY-010091] [Server] Can't create test file /Volumes/mySQLPartition/data/mysqld_tmp_file_case_insensitive_test.lower-test
2020-08-16T06:05:24.161149Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /Volumes/mySQLPartition/data/ is case insensitive
2020-08-16T06:05:24.169722Z 0 [ERROR] [MY-010119] [Server] Aborting 2020-08-16T06:05:24.169919Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.21) MySQL Community Server - GPL

I can say that I have tried the external as both case sensitive and case-insensitive, and as HFS+ and APFS volumes.  I get the same error message about case-incentive file system regardless.
I have set the data directory to be the external volume data file /Volumes/mySQLPartition/data using the "Configuration" tab of the MySQL preference pane.
Permissions are set with:
sudo chown -R _mysql:_mysql data/
sudo chmod -R a+wrx data/

Perry Gershon · Answer

I found a workaround.  I list this as an answer because it is far from ideal.  I am bypassing the launchcd and the preference pane mySQL because I could not get around the binlog index issue that kept terminating my startup.
I can start mySQL on the command line with:
sudo /usr/local/mysql/bin/mysqld_safe --datadir=/Volumes/mySQLPartition/data/data --user=mysql --disable-log-bin &
I disable the log but it works without disabling too.
I created the external data file initially as follows:
cd [to the location you want the data]
sudo mkdir -p data
sudo chown -R _mysql:_mysql data
sudo chmod 750 data
cd /usr/local/mysql
bin/mysqld --initialize --user=mysql --datadir=/Volumes/mySQLPartition/data/data
This works.  The pref pane shows that mySQL is active (green light on) but it will not control mySQL and even the pref pane "Stop MySQL Server" will not function.
I woudl love to be able to get the pref pane to control mySQL properly.  But at least this is a workaround with mySQL data on an external Mac drive running OS 10.15.6

How do I relocate Data directory in mySQL to external drive (macintosh)?

One Answer

Add your own answers!

Ask a Question