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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cwpCommented:
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
harpooliganCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.