I'd Lost My MySQL Priviliges

[zAm]

Hello,
Guys .... i need help regarding mysql priviliges. somehow , i deleted all the priviliges of user root of mysql , now i am not able to create/edit any database .. can somebody tell me how would i get back my access . unfortunately , this happened with phpMyAdmin , i remove the all priviliges with phpMyAdmin .... please somebody help me .. i would be thankful to him . Thanks
Regards,
zAm (Lyarianz Internet Cable Network)

[LinuxFreaK]

Dear zAm,
Salam,

Use following commands

# service mysql stop
# mysqld_safe --skip-grant-tables --old-passwords --user=root &
# mysql

Code: Select all

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

Best Regards.

[zAm]

Hello,
LinuxFreak ...... well i execute the following command after stopping the mysqld service ..it shows me this error :
[root@lyarianz ~]# mysqld_safe --skip-grant-tables --old-passwords --user=root &
[4] 1500
[3] Exit 127 mysqld_safe --skip-grant-tables --old-passwords --user=root
[root@lyarianz ~]# -bash: mysqld_safe: command not found

what's that ??

[LinuxFreaK]

Dear zAm,
Salam,

its means your does not have mysqld_safe command or might be not available in your path.

# rpm -ql mysql-server | grep safe

or

# rpm -ql MySQL-server | grep safe

Best Regards.

[zAm]

Hello,
LinuxFreak ... when i execute that cmd 'rpm -ql mysql-server | grep safe' so i got this , it means that mysql-server & it's command mysqld_safe exists .... here it is .
[root@lyarianz ~]# rpm -ql mysql-server | grep safe
/usr/bin/safe_mysqld
so what could be else the problem ? i really wanted to make another databases but wish i could ........ huh ... ? anyway's ... thanks for your replies ......... what about that one now ?? Thanks ,
Regards,
zAm (Lyarianz Internet Cable Network)

[LinuxFreaK]

Dear zAm,
Salam,

# service mysql stop
# /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=root &
# mysql

Code: Select all

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

Best Regards.

[zAm]

Hello,
LinuxFreak , i execute your given commands , i got that error ... i am newbie in mysql database & linux too , hope you won't bother to help me out ..... here's what i got ...

[root@lyarianz ~]# /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=root &
[1] 10981
[root@lyarianz ~]# Starting mysqld daemon with databases from /var/lib/mysql
051214 01:30:33 mysqld ended


[1]+ Done /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=root

[root@lyarianz ~]# mysql
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
[root@lyarianz ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE mysql.user SET Password=PASSWORD('qwerty') WHERE User='root';
ERROR 1142: update command denied to user: 'root@localhost' for table 'user'
mysql>
what should i do now ??
Regards,
Lyarianz Internet Cable Network

[LinuxFreaK]

Dear zAm,
Salam,

I do not know what you are trying to do first you did not stoped mysql process and when you issue mysql -p it ask you root password and if you don't know your root password then you can not login into mysql...

But if you still wanna reset your MySQL root password. follow these steps...

# service mysql stop
# /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=mysql &
# mysql

Code: Select all

mysql> use mysql
mysql> update user set password=PASSWORD('qwerty') where user='root';

Best Regards.

[zAm]

Hello,
LinuxFreak ....... i am sorry that you don't understood my question properly ........... i remember the password , infact i can login to mysql with user root , but the problem is that i've lost my all priviliges of user root, like create,delete,edit databases. & i just wanted my priviliges back ...... i unfortunately remove my priviliges from phpMyAdmin , & now when i access phpMyAdmin so i got a error like No Privileges .....check this out
http://www.lyarianz.ukonline.co.uk/mysql.JPG
how can i get back my priviliges ....... anyway out .. i also tried to un-install mysql but it's show me a lot of errors of dependencies .... so i just couldn't ....... any way to get back my priviliges ... i had just this user root of mysql, not any user with all priviliges ...... there must be a way out , right ? Thanks for your kind replies ..... glad to see your replies always ....
Regards,
zAm (Lyarianz Internet Cable Network)

[lambda]

login as root, and type

Code: Select all

grant all on *.* to root@localhost identified by 'some-password' with grant option;

[zAm]

Hello,
lambda , thanks for your reply but this isn't working with me , check this out

Code: Select all

[root@lyarianz ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on *.* to root@localhost identified by 'some-password' with grant option;
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

any idea ? how to gain back my priviliges of mysql again ? Thanks
Regards,
zAm (Lyarianz Internet Cable Network)

[LinuxFreaK]

Dear zAm,
Salam,

Do same procedure using skip-grant-tables and it will solve your problem.

# service mysql stop
# /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=mysql &
# mysql

Code: Select all

mysql> grant all on *.* to root@localhost identified by 'some-password' with grant option;

# service mysql restart

Best Regards.

[zAm]

Hello,
LinuxFreak ..... still experiencing the same problem again & again ... here's what i've got in my bash prompt....

Code: Select all

[root@lyarianz ~]# service mysqld stop
Stopping MySQL:                                            [  OK  ]
[root@lyarianz ~]# /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=mysql &
[1] 12110
[root@lyarianz ~]# Starting mysqld daemon with databases from /var/lib/mysql
051216 22:01:12  mysqld ended


[1]+  Done                    /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=mysql
[root@lyarianz ~]# service mysqld start
Starting MySQL:                                            [  OK  ]
[root@lyarianz ~]# mysql -u --root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant all on *.* to root@localhost identified by 'some-password' with grant option;
ERROR 1045: Access denied for user: '@localhost' (Using password: NO)

Thanks
Regards,
zAm (Lyarianz Internet Cable Network)

[LinuxFreaK]

Dear zAm,
Salam,

Code: Select all

mysql> grant all on *.* to 'root'@'localhost' identified by 'some-password' with grant option;

Best Regards.

[zAm]

Hello,
LinuxFreak ... the same problem exists again with the cmd which u told me .................. any idea ??

Code: Select all

[root@lyarianz ~]# service mysqld stop 
Stopping MySQL:                                            [  OK  ] 
[root@lyarianz ~]# /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=mysql & 
[1] 12110 
[root@lyarianz ~]# Starting mysqld daemon with databases from /var/lib/mysql 
051216 22:01:12  mysqld ended 


[1]+  Done                    /usr/bin/safe_mysqld --skip-grant-tables --old-passwords --user=mysql 
[root@lyarianz ~]# service mysqld start 
Starting MySQL:                                            [  OK  ] 
[root@lyarianz ~]# mysql -u --root 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 3 to server version: 3.23.58 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> grant all on *.* to 'root'@'localhost' identified by 'some-password' with grant option;
ERROR 1045: Access denied for user: '@localhost' (Using password: NO)
mysql>