[报错:访问终端localhos不允许远程访问]ERROR 1044 (42000): Access denied for user 'root'@'localhost'

当前主机复制远程mysql库数据时报错:

#mysqldump -h远程主机 -uroot -p远程mysql密码 -P3306 --default-character-set=utf8 --all-databases | mysql -h127.0.0.1 -uroot -p -P3306

 ERROR 1044 (42000): Access denied for user ‘root‘@‘localhost‘

解决办法: 给localhost开放权限

#mysql -uroot -p

输入密码

#查看各root账号的权限:

mysql> select current_user() from dual;                                                                                                                         
+----------------+                                                                                                                                              
| current_user() |                                                                                                                                              
+----------------+                                                                                                                                              
| root@localhost |                                                                                                                                              
+----------------+                                                                                                                                              
1 row in set (0.00 sec)                                                                                                                                         
                                                                                                                                                                
mysql> select host,user from user where user=‘root‘;                                                                                                            
ERROR 1046 (3D000): No database selected                                                                                                                        
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,user from user where user=‘root‘;                                                                                                            
+-----------+------+                                                                                                                                            
| host      | user |                                                                                                                                            
+-----------+------+                                                                                                                                            
| localhost | root |                                                                                                                                            
+-----------+------+                                                                                                                                            
1 row in set (0.00 sec)                                                                                                                                         
                                                                                                                                                                
mysql> show grants for root@‘localhost‘;                                                                                                                        
+---------------------------------------------------------------------+                                                                                         
| Grants for root@localhost                                           |                                                                                         
+---------------------------------------------------------------------+                                                                                         
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ WITH GRANT OPTION |                                                                                         
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION        |                                                                                         
+---------------------------------------------------------------------+                                                                                         
2 rows in set (0.00 sec)                                                                                                                                        
                                                                                                                                                                
mysql> show grants for root@‘127.0.0.1‘;                                                                                                                        
ERROR 1141 (42000): There is no such grant defined for user ‘root‘ on host ‘127.0.0.1‘                                                                          
mysql> show grants for root@‘%‘;                                                                                                                                
ERROR 1141 (42000): There is no such grant defined for user ‘root‘ on host ‘%‘                                                                                  
mysql> show grants for root@‘xxx.xxx.xxx.xxx‘;                                                                                                                  
ERROR 1141 (42000): There is no such grant defined for user ‘root‘ on host ‘xxx.xxx.xxx.xxx‘ 
 
如上所示,root@localhost账号没有访问权限及WITH GRANT OPTION选项,关于WITH GRANT OPTION选项,如果想让授权的用户,也可以将这些权限授予给其他用户,需要选项 WITH GRANT OPTION 。也就是说有这个选项就可以将权限传递给第三方。这也是上面root@localhost用户给其它用后授权报错的原因,如果以 root@127.0.0.1登录(此账号拥有WITHGRANT OPTION选项),创建用户并授权就不会有这个错误,如下所示:

 #给IP-xxx.xxx.xxx.xxx赋予远程访问权限:

 # mysql -h127.0.0.1 -u root

(需先设置密码:
mysql>  set global validate_password_length=6;                                                                                                                  
Query OK, 0 rows affected (0.00 sec)                                                                                                                            
                                                                                                                                                                
mysql>  set global validate_password_policy=0;                                                                                                                  
Query OK, 0 rows affected (0.00 sec)                                                                                                                            
                                                                                                                                                                
mysql> set password for root@localhost = password(‘123456‘);                                                                                                    
Query OK, 0 rows affected, 1 warning (0.00 sec)    )
设置密码的直接输入:
# mysql -host 127.0.0.1 -u root -p

>grant all privileges on *.* to root@"127.0.0.1" identified by "密码" WITH GRANT OPTION;

 

#生效设置:

>FLUSH PRIVILEGES;

 



 

[报错:访问终端localhos不允许远程访问]ERROR 1044 (42000): Access denied for user 'root'@'localhost'

上一篇:MySQL支持的字符集


下一篇:安装python的mysqlclient==1.4.6报错