浪费了整整一下午时间,才解决------------->
出现的结果:
mysql无法登录,试了多种方法都无法
- 修改配置文件
#1.mysql所在的主机
vim /etc/my.cnf
#在[mysqld]中添加如下配置
skip-grant-tables
2.重新启动
systemctl restart mysqld.service
use mysql;
3.添加用户
insert into user(User,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('root','','','','');
4.更新这个用户(注意条件为where部分)
#我的mysql中有两个root用户(root@localhost, root@%)
update user set Host='localhost',select_priv='y', 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' and host='';commit;
5.此时root用户已经添加完成,这时 这个root用户的host字段为空,要加上localhost.
update user set host=’localhost’ where user=’root’ and host=’’;
6.修改密码
#注意mysql的版本,这里时mysql8.x
alter user 'root'@'localhost' identified with mysql_native_password by 'tonglucloud@123';
7.执行 show tables等命令出错,结果如下:
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
1) 查看拥有的权限
show grants for 'mysql.infoschema'@'localhost';
结果如上图,没有任何权限,
2) 解决方法
修改密码
mysql> CREATE USER ‘mysql.infoschema’@‘localhost’ IDENTIFIED BY ‘password’;
Query OK, 0 rows affected (0.00 sec)
#赋予权限
mysql> GRANT SELECT ON . TO mysql.infoschema
@localhost
;
Query OK, 0 rows affected, 1 warning (0.00 sec)
完美解决: