-
Notifications
You must be signed in to change notification settings - Fork 10
Using MySQL on macOS Mojave via Homebrew
If it’s not already installed, install Homebrew:
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"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 startIf 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 cleanupFor 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.plistNext, 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.plistVerify 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_installationTip: 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.cnfNext, 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.cnfRestart 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.plistThat’s it! Use whatever tool you’d prefer for database management.
See also: MySQL basic, tricks and techniques
Be aware: In case of reinstalling mysql, your root password remains the same.
You could simply run:
$ brew reinstall mysqlOr, 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).sqlUninstall:
$ 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.plistRemove 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