[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1050
  • Last Modified:

mysql access is denied. (urgent help !!!)

Hi, experts,

I'm new to myspl. I'm using mysql control center db tool to execute some command .
I just changed root access from localhost to %.

not sure if I did something worng. now I can't accress my db now?

I tried to access mydb in command line. when i input root password, the access is denied.

Please help, I hope my db table can be saved.

Thanks in advance.
0
lilyyan
Asked:
lilyyan
  • 38
  • 24
1 Solution
 
kupra1Commented:
what did you execute exactly? Paste the whole statement.
0
 
lilyyanAuthor Commented:
First I just add one user in command line to grant the privilege.

And it works well.

Then I just changed the root access from localhost to % by using mysql control center tool.

And it works fine too.

I think using another user is better, so I changed the root access back to localhost  by using mysql control center tool.

It seems is all what I did.

Then I try to log in as a root from command line, the access is denied.

0
 
kupra1Commented:
try closing the command line and opening again. after that try loggin on.
Also, if your MySQL Control Center is still open, go to the "user" table in the mysql database and see the enteries for the host. If possible, paste here.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
kupra1Commented:
also, don't forget to close your Control Center. There may be access violation due to different accesses.
0
 
lilyyanAuthor Commented:
Now the only user is: root@localhost:3306

Before this, there are other two users .
0
 
kupra1Commented:
you dont need to mention the port no.

It should be only 'root'@'localhost'
0
 
lilyyanAuthor Commented:
It seems that the program is still working. But the db can be connected only under the new usere i added.

it can not work under the user root / localhost access any more. i guess somehow i massed up the the user root and root@localhost:3306.

Is there some way to find the root password. Or I should add one by using the Control Cneter.

Well, i tried to log on from command line, after closing Control Center. It failed.
0
 
lilyyanAuthor Commented:
I just added one user called :

Name :    'root'@'localhost'

Host Name:  localhost

User Name: root

Password: mypassword

prot: 3306

When I try to test the connection under control center, the access is still denied.

0
 
kupra1Commented:
No, you can't retrieve the old password as it encrypted by MD5 algorithm which is only one-way algorithm. So, you better set the new password by executing the following.

grant all on *.* to 'root'@'localhost' identified by 'the_password_you_want_to_set';

Once you are successful doing this, go to the user table inside mysql database to see the password of the user 'root'. You should see your password set in the encrypted format in the password column.
0
 
lilyyanAuthor Commented:
Well, root@localhost:3306 is the name which appear in the user table. The user name is still root
0
 
kupra1Commented:
It should be like this:

Column           Value
Host                localhost            (no 3306)
User                root
0
 
kupra1Commented:
replace localhost:3306 by localhost in the host column.
0
 
lilyyanAuthor Commented:
Well, I can not access the sql commmand line now.

I changed the root password directly under control center, but still the connection is failed
0
 
kupra1Commented:
You shouldn't have changed the password directly. It requires the password in the encrypted format and not in the text format in the database. Now, it's a problem.
0
 
lilyyanAuthor Commented:
----------------------------------------------
Column           Value
Host                localhost            (no 3306)
User                root

----------------------------------------------

not sure the format you posted is using the control center.

i'm using My SQL Control Center 0.9.4-beta,
0
 
kupra1Commented:
were you able to execute the steps I mentioned above?
Should be like this:

| Host      | User | Password                                
| localhost | root | *C3F98080B4CCC9275CC2EBB9BF88D429B01062ED

No port no. Password is encrypted.
0
 
lilyyanAuthor Commented:
>>You shouldn't have changed the password directly. It requires the password in the encrypted format and not in the text format in the database. Now, it's a problem.

Maybe this is the problem. But the password uder control center is editable and it's not a text format. Even though I can input the password, but the password is in **** format.

0
 
lilyyanAuthor Commented:
>>were you able to execute the steps I mentioned above?

how to excute the command, may i ask, since i can not use commad line now

0
 
lilyyanAuthor Commented:
the prot number is the default option listed in the adding new user interface. user don't need to input
0
 
lilyyanAuthor Commented:
how to find the file which record the encrypted passowrd?
0
 
kupra1Commented:
Ok.. so we need to reset the password of root.
Follow these steps:
1. Stop your mysql service from the control panel.
2. Create a text file and place the following command within it on a single line:
           set password for 'root'@'localhost' = PASSWORD('newPassword');
Save the file with any name like C:\mysql-init.txt.
3.Open a dos prompt and execute the following:
mysqld-nt --init-file=C:\mysql-init.txt (if you have mysql less than 4.1)
mysqld-nt.exe --defaults-file="Path of your my.ini file" --init-file=C:\mysql-init.txt (if you have Mysql version 4.1+)
4. Stop the mysql server and restart it.
5. You should be able to connect now with the new password.


0
 
lilyyanAuthor Commented:
So Great! I will try your suggestion.

MySQL server is 4.1, should I still use mysqld-nt --init-file=C:\mysql-init.txt (if you have mysql less than 4.1) ?
0
 
lilyyanAuthor Commented:
Oh, I should use mysqld-nt.exe --defaults-file="Path of your my.ini file" --init-file=C:\mysql-init.txt (if you have Mysql version 4.1+).

0
 
lilyyanAuthor Commented:
Hi, kupra1

I got this error:
'mysqld-nt.exe' is not recognized as an internal or external command,
operable program or batch file.
0
 
kupra1Commented:
try executing by giving the full path e.g.

C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini" --init-file=C:\mysql-init.txt
0
 
lilyyanAuthor Commented:
C:\>C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt.exe --defaults-file="C
:\Program Files\MySQL\MySQL Server 4.1"--init-file=C:\mysql-init.txt
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
0
 
lilyyanAuthor Commented:
as suggested from step 1, i already stopped the mysql service from administrative tool in control panel
0
 
lilyyanAuthor Commented:
4. Stop the mysql server and restart it.

How to do this step, it means to stop the mysql service as posted in step 1?
0
 
lilyyanAuthor Commented:
when under C:\Program Files\MySQL\MySQL Server 4.1\bin, execute

mysqld-nt.exe --defaults-file="Path
of your my.ini file" --init-file=C:\mysql-init.txt

I got the following error:

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt.exe is not
a valid Win32 application
0
 
kupra1Commented:
do like this:
C:\>"C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt.exe"  --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini"  --init-file=C:\mysql-init.txt
0
 
lilyyanAuthor Commented:
The error is :
C:\Program Files\MySQL\MySQL Server 4.1\bin>mysqld-nt.exe --defaults-file="C:\Pr
ogram Files\MySQL\MySQL Server 4.1\my.ini"--init.txt
Access is denied.
0
 
lilyyanAuthor Commented:
Well, it's the same error:

C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt.exe is not
a valid Win32 application
0
 
kupra1Commented:
ok..
do the following:
1. Open a command prompt and set the classpath.

set classpath=%classpath%;C:\Program Files\MySQL\MySQL Server 4.1\bin;

2. Execute this at the command prompt:
mysqld-nt --skip-grant-tables -u root &

3. Open another command prompt and repeat step 1 in this window (not 2).
4. Execute this in the new command prompt.
mysql -u root
5. You will get the mysql command prompt.
6. At mysql prompt, execute this:
UPDATE mysql.user SET Password=PASSWORD('newpassword') where User='root';
7. Execute this finally;
flush privileges.
8.Now you should be able to connect using the new password.

0
 
lilyyanAuthor Commented:
i stoped the mysql service in prevous steps in contrl panel. now i tried to restart the service, but it's failed.

will this bw a problem.

will try above suggestion
0
 
lilyyanAuthor Commented:
Hi kupra1 ,

I tried up tp the second step
C:\>mysqld-nt --skip-grant-tables -u root &
'mysqld-nt' is not recognized as an internal or external command,
operable program or batch file.
0
 
kupra1Commented:
did you set the classpath first. If not, set it with giving the right path for your bin folder inside MYSQL folder. If it still does not work, use the full path same as last time.
0
 
lilyyanAuthor Commented:
Yeah , I set the classpath in dos command as well as in windows

set classpath=%classpath%;C:\Program Files\MySQL\MySQL Server 4.1\bin;
0
 
kupra1Commented:
ok.. use the absolute path then.
0
 
lilyyanAuthor Commented:
now under the directory : \Program Files\MySQL\MySQL Server 4.1\bin

i exected : mysqld-nt --skip-grant-tables -u root &

the error is C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt.exe is not
a valid Win32 application
0
 
lilyyanAuthor Commented:
really appreciate your attention :-)
0
 
kupra1Commented:
Go to your MySQL bin directory and check whether the mysqld-nt.exe is there or not. Also, try to execute without the .exe extension.
0
 
kupra1Commented:
do u have yahoo.. if yes add me as k_prabhakar1.. it will be faster there
0
 
lilyyanAuthor Commented:
the file mysqld-nt.exe is there.

and tried both : mysqld-nt --skip-grant-tables -u root &
mysqld-nt.exe --skip-grant-tables -u root &

still the same error.
0
 
lilyyanAuthor Commented:
i have a msn account, maybe i can try to add your user name under msn ?
0
 
lilyyanAuthor Commented:
hi, i tried to add your yahoo email address to the contact list. but it seems that you need .net password.
0
 
lilyyanAuthor Commented:
.net passport
0
 
kupra1Commented:
yea... and I dont have that. So, better do here.
0
 
lilyyanAuthor Commented:
could i know what's your local time now ?
0
 
kupra1Commented:
4:52 pm.. what is yours?
0
 
lilyyanAuthor Commented:
maybe i need re-copy a mysqld-nt.exe  file ?
0
 
kupra1Commented:
yea.. it seems that it has corrupted
0
 
lilyyanAuthor Commented:
6:54 pm ( central time) hehe :-)
0
 
lilyyanAuthor Commented:
Well, I don't want to consider reintall the mysql so far.
0
 
kupra1Commented:
ofcourse not. but your mysqld-nt.exe is corrupted and hence, is not runnable.
0
 
lilyyanAuthor Commented:
Hi kupra1,

I just  find that if I restall the mysql, there are several options, one is for for data repair, should I try this option ?
0
 
kupra1Commented:
But data repair will not repair your mysqld-nt.exe. ok.. tell me your email id. I will send you there. After that just replace it.
0
 
lilyyanAuthor Commented:
i will send a email to your yahoo account. appreciate your help. :-)
0
 
kupra1Commented:
@yahoo.co.in, not yahoo.com
0
 
lilyyanAuthor Commented:
i'm using this address: kupra_iitk_01@yahoo.com, you gave out in this site.

0
 
kupra1Commented:
ok..
0
 
lilyyanAuthor Commented:
did you receive my email ?
0
 
lilyyanAuthor Commented:
HI kupra1 ,

Really appreciate your suggestions. I restall the mysql db, and used your suggestions. Now the db has been recovered.

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.

  • 38
  • 24
Tackle projects and never again get stuck behind a technical roadblock.
Join Now