Dev's Weblog

Problem:

I tried to upgrade the MySQL 5.1 to MySQL 5.5 and messed up the installation somehow ,so I deleted the data directory for mysql (/var/lib/mysql) and installed the MySQL 5.5 fresh using the RPMs but after the installation I was not able to login as a MySQL root user.

I was getting the following error.

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

Solution:

The only way to reset the root user password is start the MySQL in the safe mode where it will not ask for the password so that you can login and change the password of existing user.

I started the MySQL daemon in a safe mode using the following command.

#mysqld_safe --skip-grant-tables

Logged into the MySQL ,

#mysql -u root

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;

Database changed

mysql> select * from user;

0 row in set (0.00 sec)

So the mysql.user table was empty and because of that it was not allowing the root user to login.
So I had to insert the root account entry manually in order to allow root login.

mysql> INSERT INTO user VALUES ('localhost','root',password('oracle'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y', 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

After that entry , I was able to login as a root user.

mysql> select * from user \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *2447D497B9A6A15F2776055CB2D1E9F86758182F
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: NULL
 authentication_string: NULL
1 row in set (0.00 sec)