MacOSX homebrew mysql root password

Question

For some reason MySQL stopped giving access for root. Uninstalled and reinstalled with Homebrew. Fresh install, fresh tables but when I enter

mysql -u root -p

I get this error:

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

I reinstalled MySQL five times but it is still asking for a password. How do I fix this?

Answer 1

None of these worked for me. I think i already had mysql somewhere on my computer so a password was set there or something. After spending hours trying every solution out there this is what worked for me:

$ brew services stop mysql
$ pkill mysqld
$ rm -rf /usr/local/var/mysql/ # NOTE: this will delete your existing database!!!
$ brew postinstall mysql
$ brew services restart mysql
$ mysql -uroot

all credit to @Ghrua

Answer 2

Just run this command (where NEWPASS is your password):

$(brew --prefix mysql)/bin/mysqladmin -u root password NEWPASS

I have had the same error and fixed it this way.

Answer 3

In case you have inadvertently set and forgot the root password, and you don't want to wipe all your databases and start over because you are lazy and forgot to have a back up solution in place, and you are using a fairly recent Homebrew install (Winter 2013), here are steps to reset your password for MySQL.

Stop the currently running MySQL instance

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

Now start mysql by hand skipping the grant tables and networking

$(brew --prefix mysql)/bin/mysqld_safe --skip-grant-tables --skip-networking

Note that if when you run echo $(brew --prefix mysql) and it does not respond as "/usr/local/opt/mysql" in bash, you will need to adjust the path accordingly.

Once you have done this, you now should have a running, unprotected MySQL instance up.

Log in and set the password

mysql -u root

At the prompt, enter the following MySQL command to set a new password for the effected user.

mysql> update mysql.user set password=PASSWORD('new_password_here') WHERE user='root';

If all went to plan it should say:

Query OK, 1 row affected (0.02 sec)
Rows matched: 4  Changed: 1  Warnings: 0

Exit out of the MySQL prompt.

mysql> exit
Bye

Stop server:

mysqladmin -u root shutdown

Now, lets put back the launch daemon so we have our MySQL at the ready again:

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

Congratulations. You've just reset your mysql root password. Pour yourself a coffee and get a backup solution in place!

Answer 4

I had the same problem a couple days ago. It happens when you install mysql via homebrew and run the initialization script (mysql_install_db) before starting the mysql daemon.

To fix it, you can delete mysql data files, restart the service and then run the initialization script:

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
rm -r /usr/local/var/mysql/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
unset TMPDIR
mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

Answer 5

Got this error after installing mysql via home brew.

So first remove the installation. Then Reinstall via Homebrew

brew update
brew doctor
brew install mysql

Then restart mysql service

 mysql.server restart

Then run this command to set your new root password.

 mysql_secure_installation

Finally it will ask to reload the privileges. Say yes. Then login to mysql again. And use the new password you have set.

mysql -u root -p

Answer 6

If you run

brew install mariadb
...
brew services start mariadb
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb)

 $(brew --prefix mariadb)/bin/mysqladmin -u root password newpass
/usr/local/opt/mariadb/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost''

also login with root account fails:

mariadb -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

then default admin user is created same name as your MacOS account username, e.g. johnsmit.

To login as root, issue:

mariadb -u johnsmit
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.4.11-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> exit
Bye

Bonus: to change current or other user pass you can use mysqladmin command:

$(brew --prefix mariadb)/bin/mysqladmin -u arunas password 'newsecret'

but this does not affect localhost for some reason, but should work for app login.

Or use native MySQL change user password SQL, which explicitly specifies host, in my case 'localhost' account of the user:

mariadb -u arunas
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.5.9-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> ALTER USER 'arunas'@'localhost' IDENTIFIED BY 'newsecret';
Query OK, 0 rows affected (0.006 sec)

MariaDB [(none)]> exit
Bye

Now let's try to login without password:

mariadb -u arunas
ERROR 1045 (28000): Access denied for user 'arunas'@'localhost' (using password: NO)

you see login failed, thus now we need specify the need of password:

mariadb -u arunas -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.5.9-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> exit
Bye

Happy usage!

Answer 7

Since the question was asked/answered long time ago, those top answers do not work for me. Here's my solution, in 2020.

Background: Fresh mysql/mariadb installed by homebrew.

Problem: The password for root is not empty and unknown.

The fix:

1. mysql -u YOUR-SYSTEM-USERNAME -p
2. The password is empty (press enter)
3. ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW-ROOT-PASSWORD';
4. FLUSH PRIVILEGES;

The reason:

1. Homebrew will create a user with root privileges named by the current MacOS username.
2. it has no password
3. Since it has all privileges, just reset the root password with that user.
4. The initial password for root was randomly generated.

Answer 8

This worked for me:

sudo mysql -u root

Answer 9

I've just noticed something common to most of the answers here, and confirmed on my fresh install. It's actually obvious if you look at the recommendations to run mysqladmin -u root without -p.

There is no password.

Brew sets mysql up with just a root user and no password at all. This makes sense, I guess, but the post-install Caveats don't mention it at all.

Answer 10

1. go to apple icon --> system preferences
2. open Mysql
3. in instances you will see "initialize Database"
4. click on that
5. you will be asked to set password for root --> set a strong password there
6. use that password to login in mysql from next time

Hope this helps.

Answer 11

Try with sudo to avoid the "Access denied" error:

sudo $(brew --prefix mariadb)/bin/mysqladmin -u root password NEWPASS

Answer 12

This worked for me. Hopefully this works for you too!!! Follow them.

brew services stop mysql
pkill mysqld
# NB: the following command will REMOVE all your databases!
# Make sure you have backups or SQL dumps if you have important data in them already.
rm -rf /usr/local/var/mysql/
brew services restart mysql

mysql -uroot
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

mysql -u root 
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password 
BY'YOUR_PASS_WORD!!!!';

Answer 13

I had this problem on a fresh install on Mac. I installed MariaDB with:

brew install mariadb 

Then started the service:

brew services start mariadb

I was unable to run 'mysql_secure_installation' as it prompted for the root password. Then I noticed in the install output:

mysql_install_db --verbose --user=jonny --basedir=/usr/local/Cellar/ ....

So I tried logging in as the username specified in the mysql_install_db output and was successful e.g.

mysql -u jonny

Then at the mysql prompt if you want to set a password for the root user:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('ToPsEcReT');

Answer 14

Running these lines in the terminal did the trick for me and several others who had the same problem. These instructions are listed in the terminal after brew installs mysql sucessfully.

mkdir -p ~/Library/LaunchAgents

cp /usr/local/Cellar/mysql/5.5.25a/homebrew.mxcl.mysql.plist ~/Library/LaunchAgents/

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

/usr/local/Cellar/mysql/5.5.25a/bin/mysqladmin -u root password 'YOURPASSWORD'

where YOURPASSWORD is the password for root.

Answer 15

Check that you don't have a .my.cnf hiding in your homedir. That was my problem.

Answer 16

This worked for me for MAC https://flipdazed.github.io/blog/osx%20maintenance/set-up-mysql-osx

Start mysql by running

brew services start mysql

Run the installation script

mysql_secure_installation

You will be asked to set up a setup VALIDATE PASSWORD plugin. Enter y to do this.

Select the required password validation (Doesn’t really matter if it is just you using the database)

Now select y for all the remaining options: Remove anon. users; disallow remote root logins; remove test database; reload privileges tables. Now you should receive a message of

All done!

Answer 17

So, in case someone has the same situation and configuration as I had and is also about to go mad - this worked for me.

After a long story I had a brew-installed MariaDB which kept automatically restarting when I killed its process (this was brew's doing), which had a root password, which I did not know.

$ brew services list

This shows something like:

mariadb started jdoe /path/to/homebrew.mxcl.mariadb.plist

Stop the MySQL server with:

$ brew services stop mariadb

Then start it again without the root user (and not using brew):

$ mariadbd --skip-grant-tables &

Here, mysql_secure_installation did not work for me because of the --skip-grant-tables, and it would not work without the --skip-grant-tables because it needed the password (which I did not have).
Trying $(brew --prefix mysql)/bin/mysqladmin -u root password hunter2 only returned strange errors and did nothing; $(brew --prefix mariadb)/bin/mysqladmin -u root password hunter2 also didn't work, gave different errors, and suggestions that did not work for me.

But you can log into mysql now without credentials: $ mysql

Here, the old method of updating the user table for root doesn't work because "Column 'Password' is not updatable".
The new method uses alter user BUT only works after you have done flush privileges; so do that first.
Then:
MariaDB [(none)]> alter user 'root'@'localhost' identified by 'hunter2';
(MariaDB [(none)]> is the MySQL prompt here)
Then do flush privileges; again.
Exit the MySQL client.

Now as far as brew is concerned, MariaDB is still not running, and so use $ ps aux | grep -i mariadb to find the pid and $ kill -9 <pid> it.
Then use $ brew services start mariadb to start it again.

Answer 18

I stumbled across this too and the solution was unironically to simply run this:

mysql

Answer 19

The default password when you install mysql via brew is root try this, it worked for me

mysql -uroot -proot

Answer 20

Terminal 1:

$ mysql_safe

Terminal 2:

$ mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

Answer 21

Iam using Catalina and use this mysql_secure_installation command and now works for me:

$ mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): << enter root here >>

i enter root as current password

OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

and do the rest

Answer 22

For me, mysql was setup with a root user and no password. I wanted to be able to login as my current user and not require the -u root bit. I used the following command to setup a super user:

mysql -u root -e "CREATE USER '$USER'@'localhost';"
mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO '$USER'@'localhost';"
mysql -u root -e "flush privileges;"

Any value for $USER will work. I personally concatenated all the above with a semicolon but reformatted to make it hopefully easier for all to read.

Answer 23

Followed the article from @Roman Escart. I guess the key is to use '$brew link --force [email protected]' https://medium.com/macoclock/setup-mysql-in-a-specific-version-on-macos-35d8ad89c699

Answer 24

Use init file to start mysql to change the root password.

brew services stop mysql
pkill mysqld
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'newRootPass';" > /tmp/mysql-init
$(brew --prefix mysql)/bin/mysqld --init-file=/tmp/mysql-init

Your root password is now changed. Make sure to shutdown server properly to save password change. In new terminal window execute

mysqladmin -u root -p shutdown

and enter your new pass.

Start your service and remove the init file

brew services start mysql
rm /tmp/mysql-init

Tested on mysql version 8.0.19

Answer 25

What is the easiest way to run Mysql on a Mac ?

My solution was to install MAMP.

https://www.mamp.info/en/mac/