MySQL ERROR 1045 (28000): Access denied for user解决方法

最新推荐文章于 2024-07-31 10:29:06 发布

ChiFaVane

最新推荐文章于 2024-07-31 10:29:06 发布

阅读量1.4k

点赞数

文章标签： mysql 数据库配置

本文链接：https://blog.csdn.net/chifavane/article/details/50507286

版权

最近，自己在捣鼓MySQL数据相关知识，在了解其发展史和相关介绍后，准备在自己的电脑上安装MySQL数据库练练手，因此去MySQL官网上下载了目前最新版的WINDOWS平台解压版客户端。

系统：Windows 7 x64

MySQL数据库版本：mysql-5.7.10-winx64.zip

解压版MySQL安装教程如链接：http://blog.csdn.net/chifavane/article/details/22523601（WIN7解压版）

作者参考教程安装完最新版的MySQL数据库后，启动MySQL服务居然报错了！

问题重现：

F:\MySQL\bin>mysql -u root -p
Enter password: ******
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

根据数据库错误提示在网上搜索资料解决，方法如下：

编辑MySQL安装目录下的my.ini配置文件，在[mysqld]下增加skip-grant-tables，样例如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = F:/MySQL
datadir = F:/MySQL/data
bind-address = 127.0.0.1
# port = .....
# server_id = .....
# config
skip-grant-tables


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

保存my.ini配置文件，重启MySQL服务：

F:\MySQL\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

F:\MySQL\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

重新登录MySQL数据库，操作步骤如下：

登录MySQL数据库

F:\MySQL\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

使用mysql数据库

mysql> use mysql
Database changed

更改mysql数据库的用户密码

mysql> update user set password = password("server") where user = "root";
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

如果在更新mysql用户密码时出现 ERROR 1054 (42S22): Unknown column 'password' in 'field list' 错误时，是因为新版MySQL数据库（ MySQL-5.7.10 ）用户表（user）的password列改为authentication_string.
查询user表的列如下：

mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

因此，修改mysql的用户密码SQL语句如下：

mysql> update user set authentication_string=password("server") where user="root";
Query OK, 1 row affected, 1 warning (0.24 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

PS：server为你需要设置的密码。

最后，把my.ini配置下的skip-grant-tables的配置项删除，并保存my.ini文件，然后重启MySQL服务。

F:\MySQL\bin>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。


F:\MySQL\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


F:\MySQL\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

至此，MySQL数据安装完成，并且能正常使用！

最后，感谢网上的各位大咖给出的资料给作者参考使用！