Geek Answers Handbook

Lost all privileges in MYSQL

I saw the same question in many forms on this site and in many other forums. However, I have tried all the solutions and still have the same result. I am pretty sure that I used to have root access on this MySQL server, and I could even use the phpMyAdmin tool to create databases among other queries. Now, when I go to phpMyAdmin and click on the databases, I am greeted:

enter image description here

If I use the command line tool, I can log in using:

mysql -u root -p

I will be asked to enter a password, and I will do it. When I try to do something else, say:

mysql> SELECT user, host FROM mysql.user;

I get the following:
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'

(http://benrobb.com/2007/01/15/howto-remote-root-access-to-mysql/), , MySQL . , :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

:

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

, ! , , , ...

- , - . .

Elshae

**** **** mysqld ! , :

/etc/init.d/mysql start
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

?

+5
4

, .

/etc/init.d/mysql start
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

:

ps aux | grep mysql

:

root     15265  0.0  0.1   3896  1284 pts/10   S    12:41   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    15302  1.6  2.8 132532 29600 pts/10   Sl   12:41   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root     15303  0.0  0.0   3008   608 pts/10   S    12:41   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root     16160  0.0  0.0   3120   708 pts/10   R+   12:42   0:00 grep mysql

:

kill -9 15265
kill -9 15302

MySQL :

/etc/init.d/mysql start

: https://help.ubuntu.com/community/MysqlPasswordReset :

/usr/sbin/mysqld --skip-grant-tables --skip-networking &

, :

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'yournewrootpassword' WITH GRANT OPTION;

, , , phpMyAdmin , . , , !

!

+2

MySQL ( UNIX):

/etc/init.d/mysqld stop

MySQL :

/usr/bin/mysqld_safe  --skip-grant-tables

mysql DB root

/usr/bin/mysql -uroot -p mysql

root

update mysql.user
set Select_priv='Y',
    Insert_priv='Y',
    Update_priv='Y',
    Delete_priv='Y',
    Create_priv='Y',
    Drop_priv='Y',
    Reload_priv='Y',
    Shutdown_priv='Y',
    Process_priv='Y',
    File_priv='Y',
    Grant_priv='Y',
    References_priv='Y',
    Index_priv='Y',
    Alter_priv='Y',
    Show_db_priv='Y',
    Super_priv='Y',
    Create_tmp_table_priv='Y',
    Lock_tables_priv='Y',
    Execute_priv='Y',
    Repl_slave_priv='Y',
    Repl_client_priv='Y',
    Create_view_priv='Y',
    Show_view_priv='Y',
    Create_routine_priv='Y',
    Alter_routine_priv='Y',
    Create_user_priv='Y',
    Event_priv='Y',
    Trigger_priv='Y' 
where User='root';

MySQL .

+6

, reset root mysqladmin:

> mysqladmin password yournewrootpassword

, root- , :

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'yournewrootpassword' WITH GRANT OPTION;
0

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; , , ...

GRANT, ... ACCESS ...

0

More articles:


All Articles