Using MySQL on macOS Mojave via Homebrew - mhulse/mhulse.github.io GitHub Wiki

Install Homebrew

If it’s not already installed, install Homebrew:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Install MySQL

Check Homebrew’s default “formulae” in it’s main repository:

$ brew info mysql
mysql: stable 8.0.15 (bottled)
...

Looks good! Next, install MySQL:

$ brew install mysql
==> Downloading https://homebrew.bintray.com/bottles/mysql-8.0.15.mojave.bottle.tar.gz
...

The installation will output a bit of info; the most useful info being:

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

If all goes smoothly, you should be able to use Homebrew Services to manage mysql:

# Start the MySQL service at login with:
$ brew services start mysql
# Run the MySQL service but don't start it at login (nor boot) with:
$ brew services run mysql
# Stop the MySQL service with:
$ brew services stop mysql
# Restart the MySQL service with:
$ brew services restart mysql
# List all services managed by brew services with:
$ brew services list
# Remove all unused services with:
$ brew services cleanup

For example, load and start the MySQL service (at login):

$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

Note: The above command is equivalent to:

$ ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Next, check if the MySQL service has been loaded:

$ brew services list
Name      Status  User   Plist
mysql     started mhulse /Users/mhulse/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Verify the installed MySQL instance:

$ mysql -V
mysql  Ver 8.0.15 for osx10.14 on x86_64 (Homebrew)

Next, set a root password and configure other options:

$ mysql_secure_installation

Tip: If you don’t want a root password, run:

$ mysqladmin -u root -p<password> password ''

Now you can connect to your database like so (use password you created when running mysql_secure_installation above):

$ mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.15 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Bonus! Allow your macOS user to type mysql with no password (replace <user> for your login’s user name):

mysql -u root -p<password> << "EOF"
  FLUSH PRIVILEGES;
  CREATE USER '<user>'@'localhost' IDENTIFIED BY '';
  CREATE USER '<user>'@'%' IDENTIFIED BY '';
  GRANT GRANT OPTION ON *.* TO '<user>'@'localhost';
  GRANT GRANT OPTION ON *.* TO '<user>'@'%';
  GRANT ALL PRIVILEGES ON *.* TO '<user>'@'localhost';
  GRANT ALL PRIVILEGES ON *.* TO '<user>'@'%';
  FLUSH PRIVILEGES;
EOF

Lastly, we can create our on my.cnf and adjust some settings:

$ 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
...

Copy my.cnf to your how directory as a hidden file:

$ cp /usr/local/etc/my.cnf ~/.my.cnf

Next, adjust settings (you could also just open the file and manually make changes):

# Allow connections from anywhere, not just localhost:
$ sed -i '' 's/bind-address.*/bind-address = 0.0.0.0/' ~/.my.cnf
# Relax the checking for non-deterministic functions (add or update line):
grep -q '^log_bin_trust_function_creators' ~/.my.cnf \
  && sed -i '' 's/log_bin_trust_function_creators.*/log_bin_trust_function_creators = 1/' ~/.my.cnf \
  || sed -i '' '$ a\
log_bin_trust_function_creators = 1' ~/.my.cnf

Restart the Launch Agent:

$ brew services stop mysql
# OR:
# $ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ brew services start mysql
# OR:
# $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

That’s it! Use whatever tool you’d prefer for database management.

See also: MySQL basic, tricks and techniques

Reinstall or remove MySQL

Be aware: In case of reinstalling mysql, your root password remains the same.

You could simply run:

$ brew reinstall mysql

Or, if you want to uninstall everything and start fresh …

Make sure to backup your local databases (assuming MySQL is running):

# Create a backup of an entire Database Management System (DBMS):
$ mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p
# Back up a specific database. Replace db1 with the name of the database you want to back up:
$ mysqldump -u username -p db1 --single-transaction --quick --lock-tables=false > db1-backup-$(date +%F).sql

Uninstall:

$ brew services stop mysql
Stopping `mysql`... (might take a while)
==> Successfully stopped `mysql` (label: homebrew.mxcl.mysql)

$ brew remove mysql
Uninstalling /usr/local/Cellar/mysql/8.0.15... (267 files, 234.6MB)

Homebrew house cleaning:

$ brew cleanup
$ brew doctor

Unload previous MySQL auto-login (this file may not exist):

$ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Remove files (some of these paths may not exist):

$ sudo rm /usr/local/mysql
$ sudo rm -rf /usr/local/var/mysql
$ sudo rm -rf /usr/local/mysql*
$ sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
$ sudo rm -rf /Library/StartupItems/MySQLCOM
$ sudo rm -rf /Library/PreferencePanes/My*
$ rm -rf ~/Library/PreferencePanes/My*
$ sudo rm -rf /Library/Receipts/mysql*
$ sudo rm -rf /Library/Receipts/MySQL*
$ sudo rm -rf /private/var/db/receipts/*mysql*

Remove previous MySQL configuration (this file may not exist):

$ sudo nano /etc/hostconfig
# Remove the line MYSQLCOM=-YES-
# If file has not contents, just exit without saving (CTRL + X)

Couldn’t hurt to run:

$ brew doctor
$ brew update
$ brew upgrade
$ brew cleanup
$ brew cleanup --prune-prefix
$ brew doctor

Links

⚠️ **GitHub.com Fallback** ⚠️