MySQL password issues (mac OS X Lion)

Question

I am trying to set the root password for MySQL in the terminal in Mac OS X Lion, and I am having issues. Every time I use the line:

/usr/local/mysql/bin/mysqladmin -u root password ******  (where ****** is the password I want to set)

I receive the error:

/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

I have not set a password previously, so I do not know why I am receiving this error.

are you changing old password or creating password first time? — Kashif Khan, Dec 16 '11 at 22:57

score 37 · Answer 1 · edited May 08 '16 at 17:15

37

Here is the procedure to reset password of root user.

1) Stop mysql (Kill mysql process or run following command)

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

2) Start it in safe mode

sudo mysqld_safe --skip-grant-tables

3) Open another terminal and run the following command (Keep last terminal open)

mysql -u root

4) Run the following command with suitable new password on the mysql console

For MySQL 5.7+:

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

For earlier versions:

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

5) mysql > FLUSH PRIVILEGES;

6) Quit from both terminals and open new terminal and connect to mysql with root user and new password

mysql -uroot -p

edited May 08 '16 at 17:15

BMW

509
3
15

answered Jun 12 '14 at 10:10

minhas23

9,291
3
58
40

I get this error after I type in 1): "ERROR! MySQL server PID file could not be found!" – user124384 Jun 21 '15 at 23:29
1

FYI: on newer mysql versions the `password` field has been renamed to `authentication_string`. – Raul Rene Sep 30 '15 at 13:43
@minhas2 Yours is the only solution that has worked after 2 hours of frustration. Thanks :) – Death Metal Nov 06 '15 at 22:11
getting error unknown column password – Rahul Apr 03 '16 at 12:58
I deleted the folder "sudo rm -rf /usr/local/mysql" and installed mysql again, after the installation is generated a new password – Willem Jul 13 '17 at 14:42

score 23 · Answer 2 · edited Jan 28 '12 at 19:28

23

Type:/usr/local/mysql/bin/mysqladmin -u root -p password

When it asks for a password, not not enter anything, just hit enter. It will then ask you to enter new password, then confirm. Finished.

edited Jan 28 '12 at 19:28

Mark Hall

53,938
9
94
111

answered Jan 28 '12 at 17:55

Huge

231
1
3

Works like a charm (Mac OS X Lion) – Brajeshwar Mar 07 '12 at 15:48
27

Not working for me. Still: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' – EZDC Jul 26 '12 at 04:08

score 6 · Answer 3 · answered Dec 16 '11 at 22:58

6

for creating password first time use

mysqladmin -u root password NEWPASSWORD

updating old password use

mysqladmin -u root -p'oldpassword' password newpass

Source = http://www.cyberciti.biz/faq/mysql-change-root-password/

answered Dec 16 '11 at 22:58

Kashif Khan

2,615
14
14

2

really? after what I just typed? You give the same answer? It's not WORKING! I want to create a password for the first time. The most progress I've gotten is I've typed: /usr/local/mysql/bin/mysqladmin -u root -p password and then I am prompted with Enter Password: .... however, it won't allow me to type (no type appears in the terminal for the enter password line). Then when I try to type it and hit enter it now gives me the error: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' – user1072337 Dec 17 '11 at 00:27
1

it seems you have created password and mysql is asking you to login with that password. i assume you dont remember the password since you created by mistake. you need to reset this password. visit this link on how to reset password for mysql http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html – Kashif Khan Dec 17 '11 at 01:11
Following the 'Unix' instructions, I cannot find the .pid file on my system. Am I missing something here? – user1072337 Dec 17 '11 at 02:02
I found the file, but when I type in: shell> kill `cat /mysql-data-directory/(my host_name).pid ...into terminal, I get the following error: – user1072337 Dec 17 '11 at 02:33
cat: /mysql-data-directory/(my host_name).pid: No such file or directory -bash: shell: command not found – user1072337 Dec 17 '11 at 02:34
i dont have mac os to help you directly....check this link http://serverfault.com/questions/67111/reset-mysql-5-root-password-on-mac-os-x – Kashif Khan Dec 17 '11 at 02:41

score 3 · Answer 4 · answered Jun 25 '15 at 03:23

3

I'm on Yosemite, but on Maverick has the same problem. - Fix the 2002 socket error first if you haven’t done

sudo mkdir /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

An then try

sudo /usr/local/mysql/bin/mysqladmin -u root -p password

answered Jun 25 '15 at 03:23

Ricardo Cruz

43
1
6

Thanks for this answer man! You can't imagine how much this helped me. I found two solutions for my problems in one answer. – M1X Sep 04 '15 at 23:53

score 1 · Answer 5 · answered Dec 10 '12 at 14:55

1

cd /usr/local/mysql/bin

./mysqladmin -u root password root

answered Dec 10 '12 at 14:55

Donald Derek

2,529
2
23
17

score 1 · Answer 6 · answered Aug 03 '17 at 18:43

To set your root password after installing:

Ensure MySQL is not running, stop it if you ran it.
On a Terminal do sudo mysqld_safe --skip-grant-tables to run MySQL bypassing the authentication.
Inside mysql do FLUSH PRIVILEGES;
Then reset by SET PASSWORD FOR root@'localhost' = PASSWORD('password'); where 'password' is your chosen password.
FLUSH PRIVILEGES; one more time

score 1 · Answer 7 · answered Dec 16 '11 at 22:57

1

That isn't how you set the password. That is how you login to mysql.

If you don't have a password set, you login using:

mysql -u root

and that will get you to the MySQL command line.

Instead of doing that just use the following to change the password.

mysqladmin -u root password NEWPASSWORD

answered Dec 16 '11 at 22:57

daemonofchaos

795
4
13

when I type in just mysqladmin -u root password NEWPASSWORD into terminal, I receive the line: -bash: mysqladmin command not found – user1072337 Dec 16 '11 at 23:35
1

and what you just typed, Kayak, is EXACTLY what I typed in the question, I continue to receive the error, it won't let me set a password using that command – user1072337 Dec 16 '11 at 23:57
when I use mysqladmin -u root -p'oldpassword' password newpass I receive the same error message, however, it now says "using Password:YES" – user1072337 Dec 17 '11 at 00:45

score 0 · Answer 8 · answered Feb 10 '15 at 05:29

0

This fix it for me: After this is going to ask you for your Super User password and then for the new password.

sudo /usr/local/mysql/bin/mysqladmin -u root -p password

answered Feb 10 '15 at 05:29

Max

1

I just get the error again after doing this. – user124384 Jun 21 '15 at 23:32

score 0 · Answer 9 · answered Mar 20 '16 at 16:55

You want to reset or set a new root password?

Steps for MySQL 5.7.5 and earlier:

Open a Terminal Window:

Stop mysql server if it is running:

sudo /usr/local/mysql/support-files/mysql.server stop
Restart it with the --skip-grant-tables option:

sudo mysqld_safe --skip-grant-tables

Open another Terminal Window:

Type: mysql -u root
Reset root password (i.e. for localhost)

UPDATE mysql.user SET authentication_string = PASSWORD(‘MyNewPass’), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost'; FLUSH PRIVILEGES;

Now your password for root@localhost is MyNewPass. You can change it:

Close all Terminal Windows. Open a new Terminal Window and type:

mysqladmin -u root -p'MyNewPass' password '123456'

Now your root password is 123456

You should see below warning message, you can ignore it if you are changing your root@localhost password:

mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
Check if your root password is successfully changed:

mysql -u root -p'123456' -e 'show databases;'

Output:

mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+

score 0 · Answer 10 · answered Feb 07 '17 at 06:04

This is working on macOS Sierra

step 1: open terminal

step 2: paste the following command

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

Your terminal should show this :

     Users-MacBook-Air:~ usr$ sudo /usr/local/mysql/support-files/mysql.server restart

Password: ERROR! MySQL server PID file could not be found!

Starting MySQL .. SUCCESS!

Step 3: Make sure SQL server is running

Step 4: Open the MYSQL working and click the local instance

Step 5: type old password and new password

Willem · Answer 11 · 2017-07-19T11:57:11.940

0

You will lose all databases

step 1: open terminal

step 2: delete folder mysql sudo rm -rf /usr/local/mysql

step 3: installed mysql again, after the installation is generated a new password

edited Jul 19 '17 at 11:57

answered Jul 13 '17 at 14:49

Willem

1,304
1
8
7

score -2 · Answer 12 · edited Dec 17 '18 at 01:33

-2

Step 1:

mysql -u root -p

It will ask Enter password:

Welcome to the MySQL monitor.

You can check default databases

mysql> show databases;
mysql> show tables;

edited Dec 17 '18 at 01:33

bunbun

2,595
3
34
52

answered Dec 16 '18 at 18:03

user10798253

1

1

This is not the correct answer to the question. The question is not about how to login into mysql which you have provided the command `mysql -u root -p` but how to change the user password with `mysqladmin` – PinoSan Dec 16 '18 at 18:26

MySQL password issues (mac OS X Lion)

12 Answers12

Linked