Saturday, February 2, 2013

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

MySQL 5.6 added comprehensive password security mechanism how password and internally handled and encrypted.
One major feature is auto encrypted password generation when mysql first time installed on your system. you can find the encrypted password in /root/.mysql_secret. Once you are into mysql using encrypted password you will get this error until unless root password not rest using :
SET PASSWORD = PASSWORD('new_password');

[root@SM-Couple1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10

Copyright (c) 2000, 2013, 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 databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> use mysql
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> show grants;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement


Now reset the pssword:

mysql> SET PASSWORD = PASSWORD('root');
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>


Another way to reset password is to run /usr/bin/mysql_secure_installation

9 comments:

  1. I set it after fresh installation

    but it is again and again showing in error logs..:(

    ReplyDelete
  2. it should not be like that... anyway
    restart mysql and verify again, also paste mysql error logs here...

    ReplyDelete
  3. Hi to every body, it's my first visit of this web site; this blog consists of remarkable and really fine information for visitors.

    Here is my webpage; calories burnt walking

    ReplyDelete
  4. Wonderful article! We will be linking to this particularly great
    content on our site. Keep up the good writing.

    Look at my web page: Trabajo orquestas

    ReplyDelete
  5. great........... it really worked for me........thanks

    ReplyDelete
  6. That solved my mysql problem, thanks! BTW, some spammy comments seem to have snuck into your blog post. :(

    ReplyDelete