[mysql] MacOSX homebrew mysql root password

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?

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');

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.

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

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

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

mysql -uroot -proot

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.

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

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

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

mysql

This worked for me:

sudo mysql -u root

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.

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!

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.

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

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.

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

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!

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!!!!';

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

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

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

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.

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