mysql root 没有任何权限

lifeison 2020-06-05

问题:

mysql root 没有任何权限

解决方案

1. add skip-grant-tables in /etc/my.conf

[~]# cat /etc/my.cnf|grep -i skip
skip-grant-tables

2. restart mysql

systemctl restart mysqld

3.增加权限

mysql> use mysql
Database changed
mysql> update user set Host=‘%‘,select_priv=‘y‘ where user=‘root‘;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update user set Host=‘%‘,insert_priv=‘y‘,update_priv=‘y‘,Alter_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‘,create_user_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‘ where user=‘root‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

4.  remove skip-grant-tables in /etc/my.conf

5.restart mysql

[~]# systemctl restart mysqld[~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.20 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mydb               || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)

相关推荐