Setting the MySQL root user password on OS X

Once you've installed MySQL, you'll need to establish the "root" password. If you don't establish a root password, then, well, there is no root password, and you don't need a password to log in.

So, that being said, you need to establish a root password.

Using terminal enter the following:

Installation: Set root user password:

/usr/local/mysql/bin/mysqladmin -u root password NEW_PASSWORD_HERE

If you've made a mistake, or need to change the root password use the following:

Change root password:

cd /usr/local/mysql/bin/
./mysql -u root -p
> Enter password: [type old password invisibly]

use mysql;
update user set password=PASSWORD("NEW_PASSWORD_HERE") where User='root';
flush privileges;
quit

I solved this by:

1. Shutting down my MySQL server: mysql.server stop
2. Running MySQL in safe mode: mysqld_safe --skip-grant-tables
3. In another terminal, login with mysql -u root
4. In the same terminal, run UPDATE mysql.user SET authentication_string=null WHERE User='root';, then FLUSH PRIVILEGES; and then exit with exit;
5. Stop the safe mode server with mysql.server stop and then start the normal one; mysql.server start

Now you can set your new password with

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

None of the previous comments solved the issue on my Mac.

I used the commands below and it worked.

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 -u root

The methods mentioned in existing answers don't work for MySQL 5.7.6 or later. According the MySQL documentation, this is the recommended way.

B.5.3.2.3 Resetting the Root Password: Generic Instructions

MySQL 5.7.6 and later:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Reference: https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

If you can't remember your password, @radtek's answer worked for me except in my case I had set up MySQL using brew which meant that steps 1 and 2 of his answer had to be changed to:

1. /usr/local/bin/mysql.server stop

2. /usr/local/bin/mysqld_safe --skip-grant-tables

Note: the lack of sudo.

This is what exactly worked for me:

1. Make sure no other MySQL process is running. To check this do the following:

   • From the terminal, run this command:

     lsof -i:3306
     

     If any PID is returned, kill it using kill -9 PID

   • Go to System Preferences → MySQL → check if any MySQL instances are running, stop them.

2. Start MySQL with the command:

   sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
   

3. The password for every user is stored in the mysql.user table under columns User and authentication_string respectively. We can update the table as:

   UPDATE mysql.user SET authentication_string='your_password' where User='root'
   

I think this should work:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOURNEWPASSWORD'

(Note that you should probably replace root with your username if it isn't root.)

Stopping MySQL Server

sudo /usr/local/mysql/support-files/mysql.server stop

Starting MySQL in safe mode

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

Changing the root password

/usr/local/mysql/bin/mysql -u root

use mysql;
UPDATE user SET authentication_string=PASSWORD('NEW_PASSWORD') WHERE user='root';
FLUSH PRIVILEGES;
exit

Testing

Run /usr/local/mysql/bin/mysql -u root

Now enter the new password to start using MySQL.

macOS v10.14 (Mojave) and later with 5.7.26 installed from the Mac OS X DMG installer.

When attempting to use the UPDATE command posted by other users, it results in the following error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Copy the password that was presented to you by the installer, open a terminal, and do the following:

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YOURPASSWORDHERE';

Try this in a terminal:

/usr/local/bin/mysql_secure_installation

This workaround works on my laptop!

Mac with macOS v10.14.5 (Mojave).

MySQL 8.0.17 was installed with Homebrew.

• I run the following command to locate the path of MySQL

  brew info mysql

• Once the path is known, I run this:

  /usr/local/Cellar/mysql/8.0.17/bin/mysqld_safe --skip-grant-table

• In another terminal I run:

  mysql -u root

• Inside that terminal, I changed the root password using:

  update mysql.user set authentication_string='NewPassword' where user='root';

• and to finish I run:

  FLUSH PRIVILEGES;

And voilà, the password was reset.

References

To reference MySQL 8.0.15 + , the password() function is not available. Use the command below.

Kindly use

UPDATE mysql.user SET authentication_string='password' WHERE User='root';

You can manually turn-off MySQL on Mac, by clicking on  Apple menu and open System Preferences. Choose the “MySQL” preference panel, and then click on the “Stop MySQL Server” button to stop MySQL Server on Mac.

After you stop your MySQL, you'll need to follow these steps.

• You'll need to start MySQL in skip-grant-tables mode

  sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables
  

• In your terminal itself, enter this command to flush existing privileges

  /usr/local/mysql/bin/mysql mysql> FLUSH PRIVILEGES;
  

• Now you need to alter the user password

  mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
  
  mysql> exit
  

Then you can go to  Apple menu and open System Preferences. Choose the “MySQL” preference panel, then click on the “Stop MySQL Server” button to stop MySQL Server on Mac.

Finally you can again go to  Apple menu and open System Preferences. Choose the “MySQL” preference panel, then click on the “Start MySQL Server” button to start MySQL Server on Mac.

For MySQL 8

1. Shutdown MySQL server

• Go to System Preferences -> MySQL
• Click Stop MySQL Server button

2. Open two terminal [command-line] windows

3. In the first terminal window run the following:

mysqld_safe --skip-grant-tables

4. In the second terminal window do the following:

4.1. Login to MySQL

mysql -u root

4.2. Run the following in the MySQL prompt:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';

4.3. Exit MySQL

exit;

5. Go back to the first terminal window and shutdown mysqld_safe

5.1. Press CTRL + Z

5.2. Run the following command

mysqladmin -u root -p shutdown

5.3. Enter the new password you set in 4.2. when prompted.

6. Start MySQL Server [see 1.]

Much has changed for MySQL 8. I've found the following modification of the MySQL 8.0 "How to Reset the Root Password" documentation works with Mac OS X.

Create a temporary file, $HOME/mysql.root.txt, with the SQL to update the root password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<new-password>';

This uses mysql_native_password to avoid the Authentication plugin 'caching_sha2_password' cannot be loaded error, which I get if I omit the option.

Stop the server, start with an --init-file option to set the root password, and then restart the server:

mysql.server stop
mysql.server start --init-file=$HOME/mysql.root.txt
mysql.server stop
mysql.server start

mysqld_safe --skip-grant-tables
mysql -u root
UPDATE mysql.user SET authentication_string='yourpasswd' WHERE User='root';
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

I somehow need to do this every time my MacBook restarts.

$ export PATH=$PATH:/usr/local/mysql/bin

now,to make this permanent:
$ echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.bash_profile

next, start mysql in safe mode:
$ sudo mysqld_safe --skip-grant-tables;

If this does not work, go to System Preferences and stop MySQL server.
next, On the **other** terminal, you may use the below:

$ mysql -u root

mysql> USE mysql;

mysql> UPDATE mysql.user SET authentication_string=null WHERE 
User='root';

mysql> FLUSH PRIVILEGES;

mysql> exit;

$ mysql -u root
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 
caching_sha2_password BY 'yourpassword';

$ mysql -u root -p
Enter password: 
mysql> SELECT user();

next, start the mysql server in normal mode. and you're done with resetting your root password. this worked for mysql 8.0.17 ver. for me.
thanks to everyone on top, https://stackoverflow.com/questions/36099028/error-1064-42000-you-have-an-error-in-your-sql-syntax-want-to-configure-a-pa,

https://www.houseninetytwo.com/how-to-use-mysql-in-terminal-on-mac-os-high-sierra/#:~:text=You%20may%20have%20gotten%20something,%2Fmysql%2Fbin%2Fmysql.&text=It%20should%20execute%20the%20right,return%20your%20version%20of%20MySQL.