Setting the MySQL root user password on OS X

Question

Try the command FLUSH PRIVILEGES when you log into the MySQL terminal. If that doesn't work, try the following set of commands while in the MySQL terminal

$ mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD("NEWPASSWORD") WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

Change out NEWPASSWORD with whatever password you want. Should be all set!

Update: As of MySQL 5.7, the password field has been renamed authentication_string. When changing the password, use the following query to change the password. All other commands remain the same:

mysql> UPDATE user SET authentication_string=PASSWORD("NEWPASSWORD") WHERE User='root';

Update: On 8.0.15 (maybe already before that version) the PASSWORD() function does not work, as mentioned in the comments below. You have to use:

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

If you don't remember the password you set for root and need to reset it, follow these steps:

1. Stop the mysqld server, this varies per install
2. Run the server in safe mode with privilege bypass

sudo mysqld_safe --skip-grant-tables;

3. In a new window connect to the database, set a new password and flush the permissions & quit:

mysql -u root

For MySQL older than MySQL 5.7 use:

UPDATE mysql.user SET Password=PASSWORD('your-password') WHERE User='root';

For MySQL 5.7+ use:

USE mysql;

UPDATE mysql.user SET authentication_string=PASSWORD("your-password") WHERE User='root';

Refresh and quit:

FLUSH PRIVILEGES;

\q

4. Stop the safe mode server and start your regular server back. The new password should work now. Worked like a charm for me :)

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

The instructions provided in the mysql website is so clear, than the above mentioned

1. $ sudo /usr/local/mysql/support-files/mysql.server stop
2. $ sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables
3. /usr/local/mysql/bin/mysql
4. mysql> FLUSH PRIVILEGES;
5. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
6. mysql> exit or Ctrl + z
7. $ sudo /usr/local/mysql/support-files/mysql.server stop
8. $ sudo /usr/local/mysql/support-files/mysql.server start
9. /usr/local/mysql/support-files/mysql -u root -p
10. Enter the new password i.e MyNewPass

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

1. Stop the mysqld server.

   • Mac OSX: System Preferences > MySQL > Stop MySQL Server
   • Linux (From Terminal): sudo systemctl stop mysqld.service

2. Start the server in safe mode with privilege bypass

   • From Terminal: sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

3. In a new terminal window:

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

4. This will open the mysql command line. From here enter:

   • UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root';

   • FLUSH PRIVILEGES;

   • quit

5. Stop the mysqld server again and restart it in normal mode.

   • Mac OSX (From Terminal): sudo /usr/local/mysql/support-files/mysql.server restart
   • Linux Terminal: sudo systemctl restart mysqld

For the new Mysql 5.7, for some reason the bin commands of Mysql aren't attached to the shell, you have to do:

1. Restart the Mac after the installation.

2. Start Mysql:

   System Preferences > Mysql > Start button

3. Go to Mysql install folder in the terminal:

   $ cd /usr/local/mysql/bin/

4. Access to Mysql:

   $ ./mysql -u root -p

and enter the initial password given to the installation.

4. In Mysql terminal change password:

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