Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

mysql login problem.

Posted on 2003-11-12
2
Medium Priority
?
4,086 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:cofeeextravaganza
2 Comments
 
LVL 3

Assisted Solution

by:cwp
cwp earned 80 total points
ID: 9737480
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
 

Accepted Solution

by:
harpooligan earned 260 total points
ID: 9737739
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question