"root" was not even allowed to perform a select on mysql database's user table.
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select super_priv, grant_priv from mysql.user where user ='root';
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'
mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
ERROR 1142 (42000): UPDATE command denied to user 'root'@'localhost' for table 'user'
mysql> quit
Bye
mysqld stop &
start mysql in safe mode with grant table skipped.
mysqld_safe --skip-grant-tables &
On another terminal
[root@baja ~]# ps -ef|grep mysql
root 5577 5489 0 11:24 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 5730 5577 1 11:24 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root 5755 5535 0 11:25 pts/2 00:00:00 grep mysql
[root@baja ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select super_priv, grant_priv from mysql.user where user ='root';
+------------+------------+
| super_priv | grant_priv |
+------------+------------+
| N | Y |
| Y | Y |
| Y | Y |
| Y | Y |
| N | N |
| Y | N |
+------------+------------+
6 rows in set (0.00 sec)
mysql> select host, super_priv, grant_priv from mysql.user where user ='root';
+---------------+------------+------------+
| host | super_priv | grant_priv |
+---------------+------------+------------+
| localhost | N | Y |
| baja | Y | Y |
| 127.0.0.1 | Y | Y |
| ::1 | Y | Y |
| % | N | N |
| xxx.xxx.xxx.x | Y | N |
+---------------+------------+------------+
6 rows in set (0.00 sec)
mysql> update user set super_priv='Y' where user ='root';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 6 Changed: 2 Warnings: 0
mysql> select host, super_priv, grant_priv from mysql.user where user ='root';
+---------------+------------+------------+
| host | super_priv | grant_priv |
+---------------+------------+------------+
| localhost | Y | Y |
| baja | Y | Y |
| 127.0.0.1 | Y | Y |
| ::1 | Y | Y |
| % | Y | N |
| xxx.xxx.xxx.x | Y | N |
+---------------+------------+------------+
6 rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@baja ~]#
On another terminal kill -9 of the mysql pid and start it back up like normal
mysqld start &
On another terminal
mysql -u root -p
[root@baja ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select super_priv, grant_priv from mysql.user where user ='root';
+------------+------------+
| super_priv | grant_priv |
+------------+------------+
| Y | Y |
| Y | Y |
| Y | Y |
| Y | Y |
| Y | N |
| Y | N |
+------------+------------+
6 rows in set (0.01 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host, super_priv, grant_priv from user where user ='root';
+---------------+------------+------------+
| host | super_priv | grant_priv |
+---------------+------------+------------+
| localhost | Y | Y |
| baja | Y | Y |
| 127.0.0.1 | Y | Y |
| ::1 | Y | Y |
| % | Y | N |
| xxx.xxx.xxx.x | Y | N |
+---------------+------------+------------+
6 rows in set (0.00 sec)
In summary
mysqld stop &
mysqld_safe --skip-grant-tables &
mysql -uroot -p
use mysql
<<look at the "user" table and making sure both the super_priv, and grant_priv for the localhost row has "Y" on both. If one of them missing, perform an update to rectify the privileges.
mysql stop &
mysql start &
<<<you should be able to perform whatever with root now>>>
No comments:
Post a Comment