Setting the MySQL root user password on OS X

Question

I just installed MySQL on Mac OS X. The next step was setting the root user password, so I did this next:

1. Launch the terminal app to access the Unix command line.

2. Under the Unix prompt I executed these commands:

   $ cd /usr/local/mysql/bin
   $ ./mysqladmin -u root password 'password'
   

But, when I execute the command

$ ./mysql -u root, this is the answer:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 224
Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

I can get into the mysql command line without any password!

Why is this?

Answer 1

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)

Answer 2

This is what exactly worked for me:

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

    a.From the terminal, run this command:
          lsof -i:3306 
      If any PID is returned, kill it using kill -9 PID
    b. 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'
   

Answer 3

If you have forgot the MySQL root password, can’t remember or want to break in….. you can reset the mysql database password from the command line in either Linux or OS X as long as you know the root user password of the box you are on:

(1) Stop MySQL

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

(2) Start it in safe mode:

sudo mysqld_safe --skip-grant-tables

(3) This will be an ongoing command until the process is finished so open another shell/terminal window, log in without a password:

mysql -u root

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

In the UPDATE command above just replace the 'password' with your own new password, make sure to keep the quotation marks

(4) Save and quite

FLUSH PRIVILEGES;

\q

(5) Start MySQL

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

Answer 4

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 :)

Answer 5

If you forgot your password or want to change it to your mysql:

1. start your terminal and enter:

sudo su

2. Enter pass for you system
3. Stop your mysql:

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

4. Leave this window OPEN, run second terminal window and enter here:

mysql -u root

5. And change your password for mysql:

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

where "new_password" - your new pass. You don't need old pass for mysql.

6. Flush, quit and check your new pass:

FLUSH PRIVILEGES;

7. Close all windows and check your new pass for mysql. Good luck.

Answer 6

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