MySQL Root Login

[requiemsallure]

PS C:\Program Files\MySQL\MySQL Server 5.6\bin> ./mysqld --init-file='C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql-init.txt'
***********************************mysql-init.txt***************************************
update user set password='password' where user='root'; flush privileges;

****************************************************************************************
PS C:\Program Files\MySQL\MySQL Server 5.6\bin> ./mysql --user=root -p
Enter password: ********
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I can access the server with --safe --skip-grant-tables enabled, but that does not do me any good... I cannot figure
out what I am doing wrong and I have checked in a few different places. All lead back to the same thing.
the procedure that I am using.

Does anyone know where I am going wrong?

 

[Alabalcho]

I think you should set the password with Password=PASSWORD("newpassword") command

update user set Password=PASSWORD('new-password') where user='root';

 

[requiemsallure]

I think you should set the password with Password=PASSWORD("newpassword") command

update user set Password=PASSWORD('new-password') where user='root';

You are correct, this will also work. From my understanding the string you gave will encrypt the password. If you perform it the way I have it in my statement it will appear in plain text. I can change the password just fine. my issue is that even though I have changed the password my grant tables do not seem to be loading correctly, wrong, or not at all.

 

[Pinhedd]

I think you should set the password with Password=PASSWORD("newpassword") command

update user set Password=PASSWORD('new-password') where user='root';

You are correct, this will also work. From my understanding the string you gave will encrypt the password. If you perform it the way I have it in my statement it will appear in plain text. I can change the password just fine. my issue is that even though I have changed the password my grant tables do not seem to be loading correctly, wrong, or not at all.

The password must be digested (MD5 hash I believe) and salted using MySQL's internal PASSWORD() function. You cannot have the password stored in plain text

 

[Pinhedd]

Also, as a side note please be aware that MySQL has an order of precedence with respect to user@host pairs. Each can have its own password. Wildcard hosts, such as 'root'@'%' are lowest in priority, and I'm not even certain if there is a wildcard host for root by default, local login may be all that is allowed.

 

[requiemsallure]

Also, as a side note please be aware that MySQL has an order of precedence with respect to user@host pairs. Each can have its own password. Wildcard hosts, such as 'root'@'%' are lowest in priority, and I'm not even certain if there is a wildcard host for root by default, local login may be all that is allowed.

This may be so, however I know that it will set the password in plain text without the Password() option. I had deleted all other MySQL accounts for root at that time.

Since then I have found the solution to this issue. I had previously installed MySQL on this machine before and when I uninstalled it I didn't make sure the uninstaller removed everything completely. I can only assume that the previous grant tables or something to this affect were causing it to fail even though my server showed the change in the password. when re-installed MySQL again (5th or 6th time... ) I made sure to clean the registry and delete any lingering folders and files related to MySQL before I started. After this the server worked as expected.

Thanks for your recommendations.

 

[Pinhedd]

Also, as a side note please be aware that MySQL has an order of precedence with respect to user@host pairs. Each can have its own password. Wildcard hosts, such as 'root'@'%' are lowest in priority, and I'm not even certain if there is a wildcard host for root by default, local login may be all that is allowed.

This may be so, however I know that it will set the password in plain text without the Password() option. I had deleted all other MySQL accounts for root at that time.

Since then I have found the solution to this issue. I had previously installed MySQL on this machine before and when I uninstalled it I didn't make sure the uninstaller removed everything completely. I can only assume that the previous grant tables or something to this affect were causing it to fail even though my server showed the change in the password. when re-installed MySQL again (5th or 6th time... ) I made sure to clean the registry and delete any lingering folders and files related to MySQL before I started. After this the server worked as expected.

Thanks for your recommendations.

MySQL uses an internal MySQL database to manage its own configuration. User authentication is contained within a table named 'user' within a database named 'mysql'. It has many fields, among them is one named Password which is 41 characters in length.

If the user that you are currently logged in as has permission to act upon this table you may modify it using standard SQL queries. The query

update user set password='password' where user='root'

simply sets the password field to read "password" for any record in which the user field is equal to "root". This is not sufficient, because the password that is transmitted for authentication is not transmitted in cleartext. It is hashed by the connecting client and the server compares this hash to mysql.user.password; if it matches, the user is authenticated. The actual password text that is entered by the user in order to authenticate the client is never seen by the server, so entering the actual password text into the password field is not only pointless, it's a major security risk.

The query

update user set password=PASSWORD('password') where user='root'

first converts the string 'password' into a fixed-width hash representation using the PASSWORD() function which is then stored in the password field. If the password is not common, it cannot be reversed if the database is somehow compromised.