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

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 temp 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, then restart the server:

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

Answer 2

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/support-files/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 start

8. /usr/local/mysql/support-files/mysql -u root -p

9. Enter the new password i.e MyNewPass

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

Answer 3

In the terminal, write mysql -u root -pand hit Return. Enter the current mysql password that you must have noted down. And set the password SET PASSWORD = PASSWORD('new_password');

Please refer to this documentation here for more details.

Answer 4

When I installed OS X Yosemite,I got problem with Mysql. I tried lot of methods but none worked. I actually found a quite easy way. Try this out.

1. First log in terminal from su privileges.

sudo su

2. stop mysql

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

3. start in safe mode:

sudo mysqld_safe --skip-grant-tables

4. open another terminal, log in as su privileges than, log in mysql without password

mysql -u root

5. change the password

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

6. flush privileges

FLUSH PRIVILEGES;

7. You are done now

Answer 5

For new Mysql 5.7 for some reason bin commands of Mysql not attached to the shell:

1. Restart the Mac after install.

2. Start Mysql:

   System Preferences > Mysql > Start button

3. Go to Mysql install folder in 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';

Answer 6

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