LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   I forget the mysql password and cant change it (https://www.linuxquestions.org/questions/linux-server-73/i-forget-the-mysql-password-and-cant-change-it-4175484038/)

saeeds255 11-09-2013 02:45 PM
I forget the mysql password and cant change it
 
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 (Post 5061480)
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 (Post 5061494)
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 (Post 5061754)
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 (Post 5061951)
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 (Post 5062043)
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 :D
quit;)


All times are GMT -5. The time now is 05:55 AM.