My MySQL set-up

            Install MySQL via homebrew

brew install mysql

Data will be stored by default at /usr/local/var/mysql. You can confirm this as follows:

$>mysql.server start
$>mysql -u root
mysql> select @@datadir;
+-----------------------+
| @@datadir             |
+-----------------------+
| /usr/local/var/mysql/ |
+-----------------------+
1 row in set (0.00 sec)
$>mysql.server stop

Moving the data directory means editing the my.cnf file at my.cnf which can exist in multiple locations. See below …

$>my_print_defaults --help
...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
...

I edited /usr/local/etc/my.cnf. This worked and didn’t require root privileges. Make sure the server has been stopped before you do this.

My data directory is at /volumes/safe/sql and my file currently looks like:

[mysqld]
# Default Homebrew MySQL server config
# Only allow connections from localhost
bind-address = 127.0.0.1
	
datadir=/volumes/safe/sql

[client]
port=3306

Connecting to MySQL from R (without ODBC)

See https://github.com/r-dbi/RMariaDB

install.packages('RMariaDB', repos="https://cloud.r-project.org")

Install mysql-connector-c from Homebrew; this was tricky and I had to brew unlink mysql before it would work, and then brew link mysql --overwrite afterwards. Still not sure if this broke things

brew install mysql-connector-c

Then edit /usr/local/etc/my.cnf

[spotlight]
database=spotlight
user=steve
password=""

Useful links