MySQL安装后出现的诡异的问题

最新推荐文章于 2022-03-26 22:03:45 发布

小阿小火苗

最新推荐文章于 2022-03-26 22:03:45 发布

阅读量546

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/jw_45840/article/details/51590022

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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

解决办法是：

（1）首先假定密码为空，然后登录试试，mysql -u root -p , 如果能登录，说明没有给root 设置密码，此时进入了MySQL的交互界面后，

SET PASSWORD = PASSWORD('这里输入你的新密码');

就解决了

下面是转过来的内容，转自：http://blog.sina.com.cn/s/blog_7d553bb50102w9rb.html

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

今天在MySql5.6操作时报错：You must SET PASSWORD before executing this statement解决方法，需要的朋友可以参考下

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

非常诡异啊，明明用密码登陆进去了，怎么还提示需要密码。
参考官方的一个文档，见http://dev.mysql.com/doc/refman/5.6/en/alter-user.html。如下操作后就ok了：

mysql> create database yan1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected (0.03 sec)

mysql> create database yan1;
Query OK, 1 row affected (0.00 sec)

也就是用mysql> SET PASSWORD = PASSWORD('123456');这句话重新设置一次密码！大爷的，真费劲啊。
这位仁兄也遇到了相同的问题。

最近新装好的mysql在进入mysql工具时，总是有错误提示:

# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
或者
# mysql -u root -p password 'newpassword'
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

现在终于找到解决方法了。本来准备重装的，现在不必了。
方法操作很简单，如下：
# /etc/init.d/mysql stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='root' or host='localhost';//把空的用户密码都修改成非空的密码就行了。
mysql> FLUSH PRIVILEGES;
mysql> quit # /etc/init.d/mysqld restart
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>

MySql5.6操作时报错：You must SET PASSWORD before executing this statement解决
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected (0.03 sec)
mysql> create database roger;
Query OK, 1 row affected (0.00 sec)
也就是用mysql> SET PASSWORD = PASSWORD('123456');这句话重新设置一次密码！

如果 MySQL 正在运行，首先杀之： killall -TERM mysqld。

运行mysqld_safe --skip-grant-tables &

如果此时不想被远程连接：mysqld_safe --skip-grant-tables --skip-networking &

使用mysql连接server

更改密码： update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';

*特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了

而是将加密后的用户密码存储于authentication_string字段

mysql> flush privileges;

mysql> quit;

修改完毕。重启

killall -TERM mysqld。

mysqld_safe &

然后mysql就可以连接了

但是此时操作似乎功能不完全，还要alter user...

alter user 'root'@'localhost' identified by '123';

网文说酱紫也可以：set password for 'root'@'localhost'=password('123');

自启动

<span style="word-wrap: normal; word-break: normal; line-height: 19.0221px; margin: 0px; padding: 0px; border: 0px; outline: 0px; font-size: 12.6814px; vertical-align: baseline; background: transparent;"><code style="margin: 0px; padding: 1px; border: 0px; outline: 0px; font-size: 12.0473px; vertical-align: baseline; color: inherit; font-family: 'Courier new', Courier, fixed, monospace; background-color: inherit;">cp mysql.server /etc/init.d/mysql</code></span>

<span style="word-wrap: normal; word-break: normal; line-height: 19.0221px; margin: 0px; padding: 0px; border: 0px; outline: 0px; font-size: 12.6814px; vertical-align: baseline; background: transparent;"><code style="margin: 0px; padding: 1px; border: 0px; outline: 0px; font-size: 12.0473px; vertical-align: baseline; color: inherit; font-family: 'Courier new', Courier, fixed, monospace; background-color: inherit;">chmod +x /etc/init.d/mysql</code></span>

<span style="word-wrap: normal; word-break: normal; line-height: 19.0221px; margin: 0px; padding: 0px; border: 0px; outline: 0px; font-size: 12.6814px; vertical-align: baseline; background: transparent;"><code style="margin: 0px; padding: 1px; border: 0px; outline: 0px; font-size: 12.0473px; vertical-align: baseline; color: inherit; font-family: 'Courier new', Courier, fixed, monospace; background-color: inherit;">chkconfig --add mysql</code></span>

reboot试试看

===========================

13.7.1.1 ALTER USER Syntax

ALTER USER user_specification [, user_specification] ... user_specification: userPASSWORD EXPIRE

The ALTER USER statement modifies MySQL accounts. An error occurs if you try to modify a nonexistent account.

To use ALTER USER, you must have the global CREATE USER privilege or the UPDATE privilege for the mysqldatabase. When the read_only system variable is enabled, ALTER USER additionally requires the SUPERprivilege.

Warning

ALTER USER was added in MySQL 5.6.6. However, in 5.6.6, ALTER USER also sets thePassword column to the empty string, so do not use this statement until 5.6.7.

Each account name uses the format described in Section 6.2.3, “Specifying Account Names”. If you specify only the user name part of the account name, a host name part of '%' is used. It is also possible to specifyCURRENT_USER or CURRENT_USER() to refer to the account associated with the current session.

For each account, ALTER USER expires its password. For example:

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

Password expiration for an account affects the corresponding row of the mysql.user table: The server sets the password_expired column to 'Y'.

A client session operates in restricted mode if the account password has been expired. In restricted mode, operations performed within the session result in an error until the user establishes a new account password:

mysql> SELECT 1; 

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

 mysql> SET PASSWORD = PASSWORD('new_password'); 

Query OK, 0 rows affected (0.01 sec) 

mysql> SELECT 1; 
+---+ | 1 | +---+ | 1 | +---+ 

1 row in set (0.00 sec)

As of MySQL 5.6.8, this restricted mode of operation permits SET statements, which is useful if the account password has a hashing format that requires old_passwords to be set to a value different from its default before using SET PASSWORD.

It is possible for an administrative user to reset the account password, but any existing sessions for the account remain restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.

Note

It is possible to “reset” a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password.