Solved

mysql login problem.

Posted on 2003-11-12
2
4,077 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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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