brew install mysql on macOS

Question

I'm trying to setup up MySQL on mac os 10.6 using Homebrew by brew install mysql 5.1.52.

Everything goes well and I am also successful with the mysql_install_db.
However when I try to connect to the server using:

/usr/local/Cellar/mysql/5.1.52/bin/mysqladmin -u root password 'mypass'

I get:

/usr/local/Cellar/mysql/5.1.52/bin/mysqladmin: connect to server at 'localhost' 
failed error: 'Access denied for user 'root'@'localhost' (using password: NO)'

I've tried to access mysqladmin or mysql using -u root -proot as well,
but it doesn't work with or without password.

This is a brand new installation on a brand new machine and as far as I know the new installation must be accessible without a root password. I also tried:

/usr/local/Cellar/mysql/5.1.52/bin/mysql_secure_installation

but I also get

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Cantrip

See also: apple.stackexchange.com/a/199747/152271 ... it's not brew install, but it worked for me when none of the answers here did – Doctorate

FWIW I used mysql -u root and it worked here :| – Ludovika

Answer 1

I think one can end up in this position with older versions of mysql already installed. I had the same problem and none of the above solutions worked for me. I fixed it thus:

Used brew's remove & cleanup commands, unloaded the launchctl script, then deleted the mysql directory in /usr/local/var, deleted my existing /etc/my.cnf (leave that one up to you, should it apply) and launchctl plist

Updated the string for the plist. Note also your alternate security script directory will be based on which version of MySQL you are installing.

Step-by-step:

brew remove mysql

brew cleanup

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

rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

sudo rm -rf /usr/local/var/mysql

I then started from scratch:

1. installed mysql with brew install mysql

2. ran the commands brew suggested: (see note: below)

   unset TMPDIR
   
   mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
   

3. Start mysql with mysql.server start command, to be able to log on it

4. Used the alternate security script:

   /usr/local/Cellar/mysql/5.5.10/bin/mysql_secure_installation
   

5. Followed the launchctl section from the brew package script output such as,

   #start
   launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
   
   #stop
   launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
   

Note: the --force bit on brew cleanup will also cleanup outdated kegs, think it's a new-ish homebrew feature.

Note the second: a commenter says step 2 is not required. I don't want to test it, so YMMV!

Bicentennial

Great answer, thanks! One thing to add: Though it is mentioned in the answer, I missed it and in so doing lost about 6 hours trying to get set up on Lion OSX. Make absolutely sure that you delete the old mysql directory at /usr/local/var/mysql if you have a previous version of MySql before you reinstall. Otherwise you will not be able to log in as root initially to set the password and you will spend a lot of time yelling at your computer. – Gnomic

go into ~/Library/LaunchAgents to see what the .plist file for mysql is actually called - in my case it was installed by homebrew so you need to modify the above uninstall procedure. – Phrygian

Ya, the unload and rm ~/Library/LaunchAgents/ steps really matter so make sure you delete the proper .plist file. I tried 7 times until I did that right. – Edie

If you're transitioning from MAMP, like I was, and using the MySQL Ruby Gem, I needed to also remove the MySQL gem before I could get the login to work. I ran rvm implode, then started the brew install and I was able to run the mysql_secure_installation – Batsheva

Because this thread is old, the "launchctl unload" line above is now wrong. The file homebrew installs is not longer called "com.mysql.mysqld.plist", it is called "homebrew.mxcl.mysql.plist". The line should now read "launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist" – Electrophone

I have` [~/Library/LaunchAgents]$ ls com.adobe.ARM.202f4087f2bbde52e3ac2df389f53a4f123223c9cc56a8fd83a6f7ae.plist com.mysql.mysqld.plist com.facebook.videochat.ava.plist homebrew.mxcl.postgresql.plist` under ` ~/Library/LaunchAgents` but unloading them gives launchctl: Couldn't stat("com.mysql.mysqld.plist"): No such file or directory – Diplomatics

I had to add --explicit_defaults_for_timestamp But then I still get 48617 [ERROR] /usr/local/opt/mysql/bin/mysqld: unknown option '--skip-bdb' – Milksop

@Diplomatics ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents – Tricolor

mysql_install_db can now be omitted, brew creates the server automatically in the right directoy. – Nasturtium

This works for mariadb, too (with appropriate changes). Thanks! – Kurys

if 'brew cleanup' doesn't do the trick, try 'brew cleanup -s' – Saturant

step 2 was definitely required for me – Petr

upon using mysql_install_db: [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize. – Arletha

It gave me the error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Doing mysql.server start solved the problem for me. – Bennink

mysql_install_db --verbose --user=whoami --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp mysql_install_db: [ERROR] unknown variable 'tmpdir=/tmp' 2018-12-03 15:59:37 [ERROR] Unrecognized options – Canthus

Boom! Yesterday I simply ran brew install mysql but was having trouble getting it to run. All I had to do was mysql.server start. Thanks!! – Scumble

What do I do about getting the error "mysql_install_db: command not found"? – Hexavalent

@ Nic Scozzaro: I had to link mysql manually with [brew link --force [email protected]] in order for bash to see any mysql commands. – Ora

FATAL ERROR: Could not find my_print_defaults – Ngo

Error: invalid option: --force – Uriah

Answer 2

Here are detailed instructions combining getting rid of all MySQL from your Mac then installing it The Brew Way as Sedorner wrote above:

Remove MySQL completely per The Tech Lab

• ps -ax | grep mysql
• stop and kill any MySQL processes
• 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*
• launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
• edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
• 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*
• sudo rm -rf /tmp/mysql*
• try to run mysql, it shouldn't work

---

Brew install MySQL per user Sedorner from this StackOverflow answer

• brew doctor and fix any errors

• brew remove mysql

• brew cleanup

• brew update

• brew install mysql

• unset TMPDIR

    mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp # whoami is executed inline
  

• mysql.server start

• run the commands Brew suggests, add MySQL to launchctl so it automatically launches at startup

mysql should now work and be running all the time as expected

Godspeed.

Dasheen

I also got bitten by doing all of the above without removing /tmp/mysql.sock first. – Vincenza

Thanks, +1. Couple comments: you say to sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist, then a few steps later you attempt to launchctl unload that file...but it won't be there, because you deleted it. Also, brew install mysql ran the mysql_install_db step for me, no need to do it twice. – Middleman

@CorySimmons does this mean I should use my own name, or root in the whoami part of your commands? – Mechelle

Confirmed - whoami is my name (I used root). This worked great. – Mechelle

Note that /etc/hostconfig does not exist on Yosemite and beyond (superuser.com/questions/850974/…) – Dirty

This worked for me also, I was having 'Plugin is not installed' issues when I tried mysql -u root. The only step that seemed to not work for me was mysql_install_db --verbose --user=whoami--basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp due to unrecognised options. – Quire

mysqld -initialize --verbose --user=whoami --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp use this for El Capitan then it should work – Boatswain

I had an issue with mysql -uroot returning a ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Solved by mysql -u root -p – Cloistral

It looks that brew install mysql runs 'mysqld --inititalize` automatically now. So, the only thing you would need to do is: brew services start mysql. – Homy

I have a question: is there any chance to hook up my old databases to this new install?? :/ – Homy

Make a dump of your db then import it into the fresh install. – Dasheen

running <code>mysqld -initialize --verbose --user=whoami --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp</code> or <code>mysql_install_db --verbose --user=whoami --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp # whoami is executed inline</code> returned errors Here's a screenshot to the errors: prnt.sc/LfN1JVR1T3nC – Fullfaced

Answer 3

Had the same problem. Seems like there is something wrong with the set up instructions or the initial tables that are being created. This is how I got mysqld running on my machine.

If the mysqld server is already running on your Mac, stop it first with:

launchctl unload -w ~/Library/LaunchAgents/com.mysql.mysqld.plist

Start the mysqld server with the following command which lets anyone log in with full permissions.

mysqld_safe --skip-grant-tables

Then run mysql -u root which should now let you log in successfully without a password. The following command should reset all the root passwords.

UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES;

Now if you kill the running copy of mysqld_safe and start it up again without the skip-grant-tables option, you should be able to log in with mysql -u root -p and the new password you just set.

Clearness

This worked for me! I started guessing the password before I tried this too and the password ended up being 'password'. So try that. – Roer

Launching mysqld_safe without the grant tables worked for me. However, the UPDATE did not work since I do not have any records on select * from mysql.user; Since we are already logged in as root, we can simply insert one for root by running GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY 'whatever'; FLUSH PRIVILEGES; – Baleful

I was using the stop service/script, and it kept restarting. Was that because I never unloaded the LaunchAgent? (doh) – Wring

What works for me was something similar to what you make. In the part that you updated the password of the user, I had to make: use mysql; update user set authentication_string=password('1111') where user='root'; As in: https://mcmap.net/q/57845/-mysql-user-db-does-not-have-password-columns-installing-mysql-on-osx – Puberulent

Answer 4

Homebrew

1. First, make sure you have homebrew installed
2. Run brew doctor and address anything homebrew wants you to fix
3. Run brew install mysql
4. Run brew services restart mysql
5. Run mysql.server start
6. Run mysql_secure_installation

Trona

Answer 5

If brew installed MySQL 5.7, the process is a bit different than for previous versions. In order to reset the root password, proceed as follows:

sudo rm -rf /usr/local/var/mysql
mysqld --initialize

A temporary password will be printed to the console and it can only be used for updating the root password:

mysql.server start
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'my-new-password';" | mysql -uroot --password=TEMPORARY_PASSWORD

Cumulation

I can see the temp password generated but when I try to run mysql, I get: ERROR! The server quit without updating PID file (/usr/local/var/mysql/XXXXX.local.pid). and when I do mysql -u root, the password is not accepted. – Quid

I successfully installed mysql 5.7.9 using the dmg installer which prompted me a window that it generated a temp password for me. And now mysql is working correctly. Too bad I had to spend huge amount of time just to get it working on a brand new Macbook. :| – Quid

@forloop 1. ps -ef | grep mysql 2. kill -9 pid 3. mysql.server start – Petit

for me, above reseting/altering of password did not work. I had to log into mysql using temporary password then run alter command 1. mysql -u root -p <temp-password> 2. ALTER USER 'root'@'localhost' IDENTIFIED BY 'New-Password'; – Participial

Answer 6

Okay I had the same issue and solved it. For some reason the mysql_secure_installation script doesn't work out of the box when using Homebrew to install mysql, so I did it manually. On the CLI enter:

mysql -u root

That should get you into mysql. Now do the following (taken from mysql_secure_installation):

UPDATE mysql.user SET Password=PASSWORD('your_new_pass') WHERE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'
DROP DATABASE test;
FLUSH PRIVILEGES;

Now exit and get back into mysql with: mysql -u root -p

Foldboat

This solution is not working for me because mysql -u root is not taking me to mysql prompt. It says Access denied for user 'root'@'localhost' (using password: NO) – Operose

Did you have a previous install of MySQL on that machine? – Foldboat

Answer 7

I had the same problem just now. If you brew info mysql and follow the steps it looks like the root password should be new-password if I remember correctly. I was seeing the same thing you are seeing. This article helped me the most.

It turned out I didn't have any accounts created for me. When I logged in after running mysqld_safe and did select * from user; no rows were returned. I opened the MySQLWorkbench with the mysqld_safe running and added a root account with all the privs I expected. This are working well for me now.

Irresolution

Answer 8

If mysql is already installed

Stop mysql completely.

1. mysql.server stop <-- may need editing based on your version
2. ps -ef | grep mysql <-- lists processes with mysql in their name
3. kill [PID] <-- kill the processes by PID

Remove files. Instructions above are good. I'll add:

1. sudo find /. -name "*mysql*"
2. Using your judgement, rm -rf these files. Note that many programs have drivers for mysql which you do not want to remove. For example, don't delete stuff in a PHP install's directory. Do remove stuff in its own mysql directory.

Install

Hopefully you have homebrew. If not, download it.

I like to run brew as root, but I don't think you have to. Edit 2018: you can't run brew as root anymore

1. sudo brew update
2. sudo brew install cmake <-- dependency for mysql, useful
3. sudo brew install openssl <-- dependency for mysql, useful
4. sudo brew info mysql <-- skim through this... it gives you some idea of what's coming next
5. sudo brew install mysql --with-embedded; say done <-- Installs mysql with the embedded server. Tells you when it finishes (my install took 10 minutes)

Afterwards

1. sudo chown -R mysql /usr/local/var/mysql/ <-- mysql wouldn't work for me until I ran this command
2. sudo mysql.server start <-- once again, the exact syntax may vary
3. Create users in mysql (http://dev.mysql.com/doc/refman/5.7/en/create-user.html). Remember to add a password for the root user.

Doctorate

Answer 9

TL;DR

MySQL server might not be running after installation with Brew. Try brew services start mysql or just mysql.server start if you don't want MySQL to run as a background service.

Full Story:

I just installed MySQL (stable) 5.7.17 on a new MacBook Pro running Sierra and also got an error when running mysql_secure_installation:

Securing the MySQL server deployment.

Enter password for user root: 
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Say what?

According to the installation info from Brew, mysql_secure_installation should prompt me to... secure the installation. I figured the MySQL server might not be running and rightly so. Running brew services start mysql and then mysql_secure_installation worked like a charm.

Energy

Answer 10

Here is an update for MySQL 5.7

bash --version
GNU bash, version 4.4.12(1)-release (x86_64-apple-darwin17.0.0)
Copyright (C) 2016 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later 

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

#========================================
brew --version
Homebrew 1.7.6
Homebrew/homebrew-core (git revision eeb08; last commit 2018-09-27)
Homebrew/homebrew-cask (git revision c9f62; last commit 2018-09-27)

#========================================
mysql --version
mysql  Ver 14.14 Distrib 5.7.23, for osx10.13 (x86_64) using  EditLine wrapper

#========================================
system_profiler SPSoftwareDataType
Software:

    System Software Overview:

      System Version: macOS 10.13.3 (17D47)
      Kernel Version: Darwin 17.4.0
      Boot Volume: Macintosh HD
      Boot Mode: Normal
      Computer Name: EdisonMacHomeBj
      User Name: Edison (edison)
      Secure Virtual Memory: Enabled
      System Integrity Protection: Disabled
      Time since boot: 6 days 23:13

brew remove [email protected]
brew cleanup
mv /usr/local/var/mysql /usr/local/var/mysql.bak
brew install [email protected]
rm -rf /usr/local/var/mysql

#========================================
mysqld --initialize
2018-09-28T04:54:06.526061Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-09-28T04:54:06.542625Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2018-09-28T04:54:07.096637Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-09-28T04:54:07.132950Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-09-28T04:54:07.196824Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 87cf2f10-c2da-11e8-ac2d-ba163df10130.
2018-09-28T04:54:07.224871Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-09-28T04:54:07.366688Z 0 [Warning] CA certificate ca.pem is self signed.
2018-09-28T04:54:07.457954Z 1 [Note] A temporary password is generated for root@localhost: kq3K=JR8;GqZ

#========================================
mysql_secure_installation -uroot -p"kq3K=JR8;GqZ"
mysql_secure_installation: [Warning] Using a password on the command line interface can be insecure.

Securing the MySQL server deployment.


The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Sherr

when copy all database from "mysql.bak" it's not working :( – Bereft

Answer 11

Just to add something to previous answers - When upgrading from MySql 5.6 to MySql 8.0, I followed the steps provided here to make a clean uninstall, yet I got following errors

2019-11-05T07:57:31.359304Z 0 [ERROR] [MY-000077] [Server] /usr/local/Cellar/mysql/8.0.18/bin/mysqld: Error while setting value 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2019-11-05T07:57:31.359330Z 0 [ERROR] [MY-013236] [Server] The designated data directory /usr/local/var/mysql is unusable. You can remove all files that the server added to it.
2019-11-05T07:57:31.359413Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-11-05T07:57:31.359514Z 0 [Note] [MY-010120] [Server] Binlog end

Took me some time to figure it out. Found a clue here: https://discourse.brew.sh/t/clean-removal-of-mysql/2251

So, the key to my problem was removing /usr/local/etc/my.cnf file after uninstall. After that one last step, MySql finally started working.

Ockeghem

Answer 12

Try by giving Grant permission Command of mysql

Dizen

the problem is that i can't access the mysql server at all after the installation by brew. I can start it, but it won't let me in as root with or without password. (therefore i can't create other users or manage permissions.. or have I not understood you right? – Cantrip

Answer 13

I had the same issue after I tried to restart mysql.

I use the following two aliases in my .profile for convenience

alias mysql-stop='launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist'
alias mysql-start='launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist' 

After stoping mysql and then trying to restart I experienced the issue you were having. I looked into the launchctl load and it was reporting a “nothing found to load” error.

After a quick search I found this..

http://www.daveoncode.com/2013/02/01/solve-mac-osx-launchctl-nothing-found-to-load-error/

So I updated me mysql-start alias as follows

alias mysql-start='launchctl load -w -F ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist'

This solved my issue which may be useful for you.

Murex

Brew now supports a services command, so these can be replaced with brew services stop mysql and brew services start mysql respectively. – Idelson

Answer 14

None of the above answers (or any of the dozens of answers I saw elsewhere) worked for me when using brew with the most recent version of mysql and yosemite. I ended up installing a different mysql version via brew.

Specifying an older version by saying (for example)

brew install mysql56

Worked for me. Hope this helps someone. This was a frustrating problem that I felt like I was stuck on forever.

Subtropics

Yes, this will work, but if you wanted to install MySQL 5.7 (as currently done by brew install mysql), you could follow the directions in my answer https://mcmap.net/q/86160/-brew-install-mysql-on-macos – Cumulation

Answer 15

The "Base-Path" for Mysql is stored in /etc/my.cnf which is not updated when you do brew upgrade. Just open it and change the basedir value

For example, change this:

[mysqld]
basedir=/Users/3st/homebrew/Cellar/mysql/5.6.13

to point to the new version:

[mysqld]
basedir=/Users/3st/homebrew/Cellar/mysql/5.6.19

Restart mysql with:

mysql.server start

Tracheotomy

Answer 16

Try solution I provided for MariaDB, high change that it works with MySQL also:

MacOSX homebrew mysql root password

In short, try to login with your username! not root.

Try same name as your MacOS account username, e.g. johnsmit.

To login as root, issue:

mysql -u johnsmit

Flyte

Answer 17

deleting /opt/homebrew/var/mysql did the job. Apparently there were mysql data (including password) so all this manipulations described above were to no avail.

Audley

This the path absolute or relative? Absolute path needs to have leading slash – Recti