(28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

解决Python MySQL连接失败问题

最新推荐文章于 2024-02-04 11:00:00 发布

转载最新推荐文章于 2024-02-04 11:00:00 发布 · 763 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/kerrycode/p/7597258.html

本文介绍了一种在Python脚本中遇到的MySQL连接错误，详细分析了问题的原因在于root用户的认证方式不同导致的权限问题，并给出了修改配置文件解决该问题的方法。

在一台测试服务器测试Python脚本时，执行Python脚本时报如下错误：

主要错误信息为“operation the sql fail!1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)”。部分测试脚本如下所示，如下所，mysql.connector.connect的host为127.0.0.1 其它账号信息做了脱敏处理.

   def record_server_info():
       try:
           server_ip = get_host_ip();
    
           server_name= str.strip(get_host_name());
    
           server_system=platform.system();
    
           linux_dis =platform.linux_distribution();
           os_version =''
           os_version = ' '.join(linux_dis)
    
           os_bitinfo = platform.architecture()
           os_bit =os_bitinfo[0][0:2]
    
           processor = str.strip(commands.getoutput("cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c"))
    
           cpu_slot= commands.getoutput("cat /proc/cpuinfo | grep 'model name' | sort | uniq | wc -l");
    
           cpu_core =  multiprocessing.cpu_count();
    
           processor_core =int(cpu_core)/int(cpu_slot);
    
           memory = get_physical_memory()
    
           disk_space = get_disk_info()["capacity"]/1024/1024;
     
    
           dbcon = mysql.connector.connect(
               host='127.0.0.1',
               user='root',
               passwd='123456',
               database='mysql'
           )
           cursor = dbcon.cursor()
           sql_tex =( "insert into db_server_info("
                      "factory_cd      ,"
                      "server_name     ,"
                      "server_ip       ,"
                      "server_type     ,"
                      "server_system   ,"
                      "is_production   ,"
                      "os_version      ,"
                      "os_patch        ,"
                      "os_bit          ,"
                      "processors      ,"
                      "cpu_slot        ,"
                      "processors_core ,"
                      "memory          ,"
                      "disk_space      ,"
                      "server_purpose)  "
                      "values(%(factory_cd)s,%(server_name)s,%(server_ip)s,%(server_type)s,%(server_system)s,%(is_production)s,%(os_version)s,%(os_patch)s,%(os_bit)s,%(processors)s,%(cpu_slot)s,%(processors_core)s,%(memory)s,%(disk_space)s,%(server_purpose)s)")
           data={'factory_cd':factory_cd, 'server_name':server_name, 'server_ip':server_ip,"server_type":server_type,"server_system":server_system,"is_production":is_productin,"os_version":os_version, "os_patch":'',"os_bit":int(os_bit), "processors":processor,"cpu_slot":cpu_slot, "processors_core":processor_core,"memory":memory,"disk_space":disk_space,"server_purpose":''}
    
           cursor.execute('truncate table db_server_info')
           dbcon.commit()
           cursor.execute(sql_tex, data)
           dbcon.commit()
    
       except mysql.connector.Error as e:
           print('operation the sql fail!{0}'.format(e))
       finally:
           cursor.close;
           dbcon.close;
  

mysql -u root -p 测试登录MySQ发现没有问题，但是一检查发现，如果不指定host的情况下，当前用户为root@localhost，而root@'127.0.0.1'的密码为空。

   mysql> select current_user();
   +----------------+
   | current_user() |
   +----------------+
   | root@localhost |
   +----------------+
   1 row in set (0.00 sec)
    
   mysql> show grants for root@'localhost';
   +---------------------------------------------------------------------------------------------------------------------------+
   | Grants for root@localhost                                                                                                 |
   +---------------------------------------------------------------------------------------------------------------------------+
   | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*9B67EF98D94F2B81011D6D2CEDE4' 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';
   +---------------------------------------------------------------------+
   | Grants for root@127.0.0.1                                           |
   +---------------------------------------------------------------------+
   | GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
   +---------------------------------------------------------------------+
   1 row in set (0.00 sec)
    
   mysql> 
  

立即检查my.cnf的配置，发现可能某次测试时，设置了参数 skip-name-resolve，后面忘记取消了。由于启动mysqld时使用了'--skip-name-resolve'参数，此种情况下由于不做域名解析，127.0.0.1和localhost对mysql数据库来讲，是不同的主机，而root@'127.0.0.1'为空密码，所以Python脚本执行时，访问MySQL报错“(28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)”

转载于:https://www.cnblogs.com/kerrycode/p/7597258.html