MySQL长短密码
今天批量搭建MySQL环境的时候,遇到长短密码问题,故就此问题总结一下长短密码。
介绍
1、长密码例子:
mysql> show grants for 'test'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+--------------------------------------------------------------------------------------------------------------+
2、短密码例子:
mysql> show grants for 'test'@'localhost';
+-------------------------------------------------------------------------------------+
| Grants for test@localhost |
+-------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '378b243e220ca493' |
+-------------------------------------------------------------------------------------+
3、对应的函数:
mysql> select Password('test');
+-------------------------------------------+
| Password('test') |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.00 sec) mysql> select old_password('test');
+----------------------+
| old_password('test') |
+----------------------+
| 378b243e220ca493 |
+----------------------+
4、old_passwords参数
可以动态地修改密码格式,old_passwords参数
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_old_passwords
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
问题引入
test用户之前对应的密码是短密码修改为长密码后,登录出现denied问题:
[root@typhoeus79 mysql_5580]# ./bin/mysql -utest -p'test' -P5580
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
[root@typhoeus79 mysql_5580]# mysql.5580
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 726
Server version: 5.5.8-log Source distribution Copyright (c) 2000, 2010, 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> show grants for 'test'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql> select Password('test');
+-------------------------------------------+
| Password('test') |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
重新授权之后,还是存在问题:
mysql> GRANT SELECT ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected (0.00 sec)
[root@typhoeus79 mysql_5580]# ./bin/mysql -utest -p'test' -P5580
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
起先以为是长短密码的问题,原来是没有flush privileges。记得之前说对于使用grant的授权是可以不用的flush的吗???
在flush privileges就可以了。