mysql root用户登录后无法查看数据库全部表

可能是把root@localhost用户删掉了。

首先停掉mysql服务,在/etc/my.cnf中添加 skip-grant-tables,同时可以添加skip-networking选项来禁用网络功能,防止这时其他人通过网络连接到数据库

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0skip-grant-tables #添加这一行  

启动服务,进入数据库

MariaDB [(none)]> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A#查看有没有root@localhost用户,没有就用update添加MariaDB [mysql]> select host,user,password from user;+-----------------------+------+-------------------------------------------+| host | user | password |+-----------------------+------+-------------------------------------------+| % | LEE | *E6CC90B878B948C35E92B003C792C46C58C4AF40 || % | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 || localhost | root | || localhost.localdomain | | || 127.0.0.1 | root | |+-----------------------+------+-------------------------------------------+5 rows in set (0.00 sec)#给root@localhost上密码MariaDB [mysql]> UPDATE user SET password=PASSWORD(‘1‘) WHERE user=‘root‘ AND host=‘localhost‘;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0#把相应的权限附上MariaDB [mysql]> update 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‘, -> `Create_tablespace_priv` = ‘Y‘ -> where user=‘root‘ and host=‘localhost‘;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0#更新数据库MariaDB [mysql]> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)  

在之后退出数据库,把刚才改的配置文件还原回来,重启数据库就好了。

 

相关文章