mysql login problem.

I seem to have a problem with loggin into mysql!
this morning, i tried to log into mysql like usual through phpMyAdmin-2.5.3 i got an error, says it couldn't authenticate me?
so i tried to log in directly using the command line. didn't work..
so i had to specify that i am root, then password..
that worked..
so now, i'm logged in..
then for some reason, i thought that i should change the password.. it was in encrypted format.. so it was illegible.. but when i changed it.. it didn't look like it was encrypted.. it was exactly the same as i had typed.

so i didn't think anything of it.. i thought it would update itself when i restarted..
now, it looks like the password that i typed in is the un-decrypted name? if you know what i mean..

and exited restarted mysqld and tried to log in again..
now i can't log in to mysql on the command line.. or any other way?
how can i go in and change the password back, or make it have no passwords??
anything?
what should i do?
cofeeextravaganzaAsked:
Who is Participating?
 
harpooliganConnect With a Mentor Commented:
A.4.2 How to Reset a Forgotten Root Password

If you never set a root password for MySQL, then the server will not require a password at all for connecting as root. It is recommended to always set a password for each user. See section 4.2.2 How to Make MySQL Secure Against Crackers.

If you have set a root password, but forgot what it was, you can set a new password with the following procedure:

   1. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:

shell> kill `cat /mysql-data-directory/hostname.pid`

      You must be either the Unix root user or the same user mysqld runs as to do this.
   2. Restart mysqld with the --skip-grant-tables option.
   3. Set a new password with the mysqladmin password command:

shell> mysqladmin -u root password 'mynewpassword'

   4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:

shell> mysqladmin -h hostname flush-privileges

   5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

   1. Take down and restart mysqld with the --skip-grant-tables option as described above.
   2. Connect to the mysqld server with:

shell> mysql -u root mysql

   3. Issue the following commands in the mysql client:

mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
    ->             WHERE User='root';
mysql> FLUSH PRIVILEGES;

   4. After this, you should be able to connect using the new password.
   5. You can now stop mysqld and restart it normally.
0
 
cwpConnect With a Mentor Commented:
Take a look at Appendix A of your MySQL manual:
=============
If you have set a root password, but forgot what it was, you can set a new password with the following procedure:

Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:
shell> kill `cat /mysql-data-directory/hostname.pid`

You must be either the Unix root user or the same user mysqld runs as to do this.
Restart mysqld with the --skip-grant-tables option.
Set a new password with the mysqladmin password command:
shell> mysqladmin -u root password 'mynewpassword'

Now you can either stop mysqld and restart it normally, or just load the privilege tables with:
shell> mysqladmin -h hostname flush-privileges

After this, you should be able to connect using the new password.

=============
Now, next time if you want to change the password in SQL queries, you should use PASSWORD('password') to encrypt your password (UPDATE user SET password=PASSWORD('password') WHERE user='root').
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.