Mysql登陆root用户权限问题

问题原因：后台程序启动一直报链接数据库失败
linux登陆命令行登陆mysql，直接报没有权限

mysql  -u root -p

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

尝试使用127.0.0.1 IP登陆，报连接失败

mysql -h 127.0.0.1 -u root -p

报错 ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)

开启安全模式并重启数据库

vim /etc/my.cnf
skip-grant-tables

登陆成功后，查询用户权限

use mysql
select user,host,grant_priv from mysql.user where user='root';

**

发现user用户缺少localhost关联的数据，创建一个关联localhost的root用户**

INSERT INTO user SET User='root',Host='localhost',ssl_cipher='',x509_issuer='',x509_subject='';

新版本mysql的password字段有更改。

update mysql.user set authentication_string=password('yourpassword') where User='root'

老版本则

update user set password=password("654321") where user="root";

赋予权限，刷新权限

use mysql;
update user set 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='localhost';
flush privileges;

select user,host,grant_priv from mysql.user where user='root';

修改root用户密码

 UPDATE user SET Password=PASSWORD('123456') where USER='root';

然后关闭安全模式并重启数据库

vim /etc/my.cnf
#skip-grant-tables

查询root用户是否有未开的权限

SELECT Host,User,Select_priv ,Insert_priv ,Update_priv ,Delete_priv ,Create_priv ,Drop_priv ,Reload_priv ,Shutdown_priv ,Process_priv ,File_priv ,Grant_priv ,References_priv ,Index_priv ,Alter_priv ,Show_db_priv ,Super_priv ,Create_tmp_table_priv ,Lock_tables_priv ,Execute_priv ,Repl_slave_priv ,Repl_client_priv ,Create_view_priv ,Show_view_priv ,Create_routine_priv ,Alter_routine_priv ,Create_user_priv ,Event_priv ,Trigger_priv ,Create_tablespace_priv ,ssl_type  FROM mysql.user  where User="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',Create_tablespace_priv='Y' where Host='%';

flush privileges;

到此处问题解决