CentOS6.5下修改MySQL密码

背景

 

        说起这篇博文，就不得不提笔者12月初的一次出差。一天今天找笔者交代“咱有个外包的项目下周四你可能得出一趟差”，然后我还是做我之前的开发，就在去的前一天，我终于见到了项目的开发者。了解了一下需求之后，感觉什么问题都没有就出发了。

   

        哪知这次出差就是个坑啊！跟我说的环境都是好了，结果JDK版本不对，Mysql密码错了，IP设置不对，没有考虑到服务器的无法连接外网……

   

        这篇博客就先讲讲如何修改MySQL密码吧

 

        MySQL密码修改

 

 

        1． 首先确认服务器出于安全的状态，

 

        即是是没有人能够任意地连接MySQL数据库。因为在重新设置MySQL的root密码的期间，MySQL数据库完全出于没有密码保护的状态下，其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对外的端口封闭，并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态。最安全的状态是到服务器的Console上面操作，并且拔掉网线。

 

        2．修改MySQL的登录设置：

 

 

# vi/etc/my.cnf

 

        在[mysqld]的段中加上一句：skip-grant-tables

 

        保存并且退出vi。

       

         3．重新启动mysqld

 

#/etc/init.d/mysqld restart

 

 

 

        4．登录并修改MySQL的root密码

 

 

 

        

        5．将MySQL的登录设置修改回来

 

 

# vi/etc/my.cnf

 

 

 

        6．重新启动mysqld

 

 

#/etc/init.d/mysqld restart

 

 

 

        总之这次出差收获挺大的，虽然在培训的时候出现了这样那样的问题（毕竟之前一个没有看过产品，就在培训的当天早晨花了一个小时体验一下系统。中午吃饭的时候做了个PPT下午就直接演示+培训了。这样的出差虽然有点惊险，但是正是这些问题替公司检验了笔者的能力。

 

ERROR 1820 (HY000): You must reset your password using&n

 (2016-03-18 01:06:50)

转载▼

分类： 测试开发

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');

 

自启动

 

cp mysql.server /etc/init.d/mysql

chmod +x /etc/init.d/mysql

chkconfig --add mysql

reboot试试看

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

13.7.1.1 ALTER USER Syntax

ALTER USER user_specification [, user_specification] ... user_specification: user PASSWORD 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 the Password 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 specify CURRENT_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.