I forget the mysql password and cant change it

saeeds255 · 11-09-2013, 02:45 PM

Hello,

I just set a password for mysql in Ubuntu webmin and when i enter that it returns error.

I try some methods but one of them was better.
I try this codes:

1. /etc/init.d/mysql stop (done without error)
2. mysqld_safe --skip-grant-tables & (done without error)
3. mysql -u root (connect to mysql)
4. use mysql;
update user set password=PASSWORD(“PASSWORD”)
where User=’root’;
flush privileges;
quit;

also step 4 done completely and i receive these massages :
"Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Bye"

finally I reset mysql with these two commands :
/etc/init.d/mysql stop
/etc/init.d/mysql start

but when i use the command "mysql -u root -p" to enter the passwort it says : "access denied for user root at localhost (using password: yes)"

why I cant enter now ? does anyone have solution or another method for this?

TB0ne · 11-09-2013, 03:17 PM

Quote:

Originally Posted by saeeds255

Hello,
I just set a password for mysql in Ubuntu webmin and when i enter that it returns error.

I try some methods but one of them was better. I try this codes:

1. /etc/init.d/mysql stop (done without error)
2. mysqld_safe --skip-grant-tables & (done without error)
3. mysql -u root (connect to mysql)
4. use mysql;
update user set password=PASSWORD(“PASSWORD”)
where User=’root’;
flush privileges;
quit;

also step 4 done completely and i receive these massages :
"Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Bye"

finally I reset mysql with these two commands :
/etc/init.d/mysql stop
/etc/init.d/mysql start

but when i use the command "mysql -u root -p" to enter the passwort it says : "access denied for user root at localhost (using password: yes)"
why I cant enter now ? does anyone have solution or another method for this?

You need to get a root shell (either "sudo -s" or "su - root"), then just type in "mysql".

saeeds255 · 11-10-2013, 03:19 AM

Quote:

Originally Posted by TB0ne

You need to get a root shell (either "sudo -s" or "su - root"), then just type in "mysql".

can you exactly say in witch step should I get a root shell ?
can i get root shell for mysql just with the "sudo -s" command ?

I just use this command for setting password : password=PASSWORD("");
then : select user , password , host from user;
and I saw that the password of all users is blank but when i want to enter mysql (after restarting mysql) i get this error :

"Re: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

why I cant login when I dont set any password ?

please help me, thanks a lot ...

TB0ne · 11-10-2013, 11:11 AM

Quote:

Originally Posted by saeeds255

can you exactly say in witch step should I get a root shell ?
can i get root shell for mysql just with the "sudo -s" command ?

Yes, that's why I said to Do exactly that before.

Quote:

I just use this command for setting password : password=PASSWORD("");
then : select user , password , host from user;
and I saw that the password of all users is blank but when i want to enter mysql (after restarting mysql) i get this error :

"Re: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)"

why I cant login when I dont set any password ?

please help me, thanks a lot ...

Re-read the first post. Do What it says

saeeds255 · 11-10-2013, 02:28 PM

Quote:

Originally Posted by TB0ne

Yes, that's why I said to Do exactly that before.

Re-read the first post. Do What it says

you know, my problem is just that I cant enter to a mysql that hasnt any password. in spite of it hasnt password but asks me to enter the password !!

how can I completely remove mysql with all of its data then install it again ?
I removed and reinstall it through wembin but the password didnt change.

TB0ne · 11-10-2013, 03:14 PM

Quote:

Originally Posted by saeeds255

you know, my problem is just that I cant enter to a mysql that hasnt any password. in spite of it hasnt password but asks me to enter the password !!

how can I completely remove mysql with all of its data then install it again ?
I removed and reinstall it through wembin but the password didnt change.

Again:

Type in "sudo -s"
Type in "mysql "

That's it. After that, Do What you'd like.

John VV · 11-10-2013, 03:30 PM

did you state you uninstalled mysql then reinstalled mysql

as i recall the defaults are
user = USER
password = PASSWORD

that is why it is very important to set them

munkz · 11-10-2013, 03:56 PM

I think your syntax may be a lil off.

1.Reload mysql with dropped privileges.

2. type "use mysq;"

3. Run the following :

Code:

FLUSH PRIVILEGES;
SELECT Host,User,Password FROM user WHERE User='root';   #<<<this is so you can compare and see what you have a root before the change. 
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *HUNK OF HASH SHOWS HERE VERSION 1        |
| 127.0.0.1 | root | *HUNK OF HASH SHOWS HERE VERSION 1        |
+-----------+------+-------------------------------------------+


SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');     #<<<not sure but I think you need to define where root will be connecting from with the ip. 
SELECT Host,User,Password FROM user WHERE User='root';   #<<<again, compare the fields to see if your SET/UPDATE took.
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *HUNK OF HASH SHOWS HERE VERSION 2        |
| 127.0.0.1 | root | *HUNK OF HASH SHOWS HERE VERSION 2        |
+-----------+------+-------------------------------------------+
FLUSH PRIVILEGES;
exit

Now, reload MySQL as normal and try it out. If that does not work, I think home depo has a sale on hammers this weekend.

saeeds255 · 11-11-2013, 01:57 AM

thanks my friends, I finally made it. I think its a good experience for everyone
so I want to share it :

1. ( sudo -s), then (mysql) doesn't work !

2. ( mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=PASSWORD(“PASSWORD”) where User=’root’;
flush privileges;
quit; ) it just change the root and other users passwords.

3. this way I could solve it :
( mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password'); ~~> I think this part is not needed but i use it.
flush privileges; ~~> this part is not needed i just add it for fun

quit