Solved

mysql login problem.

Posted on 2003-11-12
2
4,080 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Assisted Solution

by:cwp
cwp earned 20 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 65 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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