Mysql：用户'root'@'localhost'的访问被拒绝

Mysql: Access denied for user 'root'@'localhost'

提问by Betlista

I know, it's really sad, but I'm not able to connect to MySQL database.

我知道，这真的很难过，但我无法连接到 MySQL 数据库。

I simply donwloaded and unzipped mysql-5.6.10-win32.zip from MySQL site.

我只是从 MySQL 站点下载并解压缩了 mysql-5.6.10-win32.zip。

I followed this tutorial, but I'm not able to connect to my MySQL database as root.

我遵循了本教程，但我无法以 root 身份连接到我的 MySQL 数据库。

I know it's not secure to connect as a root without the password, but I just need to do some test, so it's ok for me to do it now (and if I know how to connect as a root I know how to add another user too).

我知道在没有密码的情况下以 root 身份连接是不安全的，但我只需要做一些测试，所以我现在可以这样做（如果我知道如何以 root 身份连接，我知道如何添加另一个用户也）。

my.ini

我的.ini

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/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=c:\Programs\mysql-5.6.10-win32
datadir=c:\Programs\mysql-5.6.10-win32\data
port=3306
server_id=1


# 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 

[client]

port=3306

When I start mysqldI see:

当我开始时，mysqld我看到：

> mysqld --console --init-file=..\set_root_password.sql
2013-04-02 13:48:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-04-02 13:48:24 8600 [Note] Plugin 'FEDERATED' is disabled.
2013-04-02 13:48:24 8600 [Note] InnoDB: The InnoDB memory heap is disabled
2013-04-02 13:48:24 8600 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-04-02 13:48:24 8600 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-04-02 13:48:24 8600 [Note] InnoDB: CPU does not support crc32 instructions
2013-04-02 13:48:24 8600 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-04-02 13:48:24 8600 [Note] InnoDB: Completed initialization of buffer pool
2013-04-02 13:48:24 8600 [Note] InnoDB: Highest supported file format is Barracuda.
2013-04-02 13:48:24 8600 [Note] InnoDB: 128 rollback segment(s) are active.
2013-04-02 13:48:24 8600 [Note] InnoDB: Waiting for purge to start
2013-04-02 13:48:24 8600 [Note] InnoDB: 1.2.10 started; log sequence number 1626183
2013-04-02 13:48:24 8600 [Note] Server hostname (bind-address): '*'; port: 3306
2013-04-02 13:48:24 8600 [Note] IPv6 is available.
2013-04-02 13:48:24 8600 [Note]   - '::' resolves to '::';
2013-04-02 13:48:24 8600 [Note] Server socket created on IP: '::'.
2013-04-02 13:48:24 8600 [Note] Event Scheduler: Loaded 0 events
2013-04-02 13:48:24 8600 [Note] Execution of init_file '..\set_root_password.sql' started.
2013-04-02 13:48:24 8600 [Note] Execution of init_file '..\set_root_password.sql' ended.
2013-04-02 13:48:24 8600 [Note] mysqld: ready for connections.
Version: '5.6.10'  socket: ''  port: 3306  MySQL Community Server (GPL)

set_root_password.sqlcontains some commands I found in How to reset root password, and few others I hoped they help:

set_root_password.sql包含我在How to reset root password 中找到的一些命令，还有一些我希望它们有所帮助的命令：

UPDATE mysql.user SET Password=PASSWORD('pass') WHERE User='root';
grant all privileges on *.* to 'root'@'%';
grant all privileges on *.* to 'root'@'localhost';
grant all privileges on *.* to 'root@localhost';

SHOW GRANTS FOR 'root'@'%';

FLUSH PRIVILEGES;

but I'm not able to login still.

但我仍然无法登录。

To login I tried:

要登录我试过：

>mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

>mysql -u root --password=pass
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

>mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

>mysql -u root --password=
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

>mysql -u root --password=mysql
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

>mysql -u root mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

>mysqladmin --no-defaults -u root version
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

In some tutorial I read about mysqladmincommand but it requires root password too.

在一些教程中，我阅读了mysqladmin命令，但它也需要 root 密码。

I tried also empty password and mysql as default password and nothing wokrd.

我也试过空密码和 mysql 作为默认密码，没有 wokrd。

I tried also --skip-grant-tableswithout success.

我也试过--skip-grant-tables没有成功。

I do not have mysqld_safe in bin directory of MySQL home directory, so I couldn't try that.

我在 MySQL 主目录的 bin 目录中没有 mysqld_safe，所以我无法尝试。

I tried to read all questions here related to that issue, but I have no idea what I can try to yet...

我试图在这里阅读与该问题相关的所有问题，但我不知道我可以尝试什么......

I'm frustrated why it isn't as simple as "download & run". I do not want to download Win installer, because I do not want services started on Windows startup and other stuff copied in my system without control...

我很沮丧为什么它不像“下载并运行”那么简单。我不想下载 Win 安装程序，因为我不想在 Windows 启动时启动服务以及在我的系统中不受控制地复制其他东西...

采纳答案by Betlista

I just found, that I have to connect specifying -h and my PC name from ipconfig -all

我刚刚发现，我必须连接指定 -h 和我的 PC 名称来自 ipconfig -all

>ipconfig -all

Windows IP Configuration

   Host Name . . . . . . . . . . . . : PC1234
   Primary Dns Suffix  . . . . . . . :
   Node Type . . . . . . . . . . . . : Hybrid
   IP Routing Enabled. . . . . . . . : No
   WINS Proxy Enabled. . . . . . . . : No
   DNS Suffix Search List. . . . . . : tmdev

and

和

mysql -u root -h PC1234 --password=pass

works fine. Thanks for help. It's strange, but it's working now.

工作正常。感谢帮助。这很奇怪，但它现在正在工作。

edit:

编辑：

I had problems to connect with that configuration. I solved it by specifying bind-addressin my.ini:

我在连接该配置时遇到了问题。我通过bind-address在 my.ini 中指定来解决它：

bind-address=localhost