1045 Error on MySQL 4.1.11 during Server Config Wizard

I’m installing a new MySQL database, but want to access the database remotely.  I have properly set up MySQL (4.1.11), Apache2 (2.0.54), PHP (5.04) and PHPMyAdmin (2.6.2) on Windows 2003.  All works fine from this one server!

However, when I rerun MySQL Server Instance Config Wizard, and modify the security settings to “enable root access from remote machines”, I get a 1045 error when I click on “execute” at the end of the wizard…

---
Connection error

The security settings could not be applied to the database because the connection has failed with the following error:

Error Nr. 1045
Access denied for user ‘root’@’localhost’ (using password: YES)

If a personal firewall……..    etc, etc.
---

I’m not running any domains or domain controlers, just single machines.  I’m trying to allow my home computer 123.234.12.34 to access the database which is on an IP of 111.22.123.234.

I found this thread saying this is a known bug…

http://www.webmasterworld.com/forum23/3290.htm

But can’t find any solutions to my problem.  Any help appreciated on this!  I’ve used MySQL a limited number of times before, but am not an expert with it by any means.  So it could be user error, it might be a bug.?.?
LVL 1
rebiesAsked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
Andrew,

from the commandline login to mysql server (machine running mysql server)..
login with root(any user who have grant privilege)

C:\mysql\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 434 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

(root@localhost) [(none)]>
(root@localhost) [(none)]> GRANT ALL ON *.* TO 'umesh'@'%' IDENTIFIED BY 'shastry123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.08 sec)

(root@localhost) [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.09 sec)

(root@localhost) [(none)]>

Now try loging using newly created user umesh with password shastry123(you should  create your own)

C:\mysql\bin>mysql -uumesh -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 435 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

(umesh@localhost) [(none)]>


Now you can connect to this server(mysql) from anywhere using umesh/shastry123 & have full access as root..


If you want to allow remote users to access this server via phpmyadmin... then edit the config.inc.php (this should be under ./<pathtophpmyadmin>/config.inc.php

Search for the below contents(also you can append).


i++;
$cfg['Servers'][$i]['host']            = 'this should be IP address of mysql server';
$cfg['Servers'][$i]['port']            = '';
$cfg['Servers'][$i]['socket']          = '';
$cfg['Servers'][$i]['connect_type']    = 'tcp';
$cfg['Servers'][$i]['extension']       = 'mysql';
$cfg['Servers'][$i]['compress']        = FALSE;
$cfg['Servers'][$i]['controluser']     = '';
$cfg['Servers'][$i]['controlpass']     = '';
$cfg['Servers'][$i]['auth_type']       = 'config';
$cfg['Servers'][$i]['user']            = 'umesh';
$cfg['Servers'][$i]['password']        = 'shastry123';
$cfg['Servers'][$i]['only_db']         = '';


Now try from remote machine ..

Hope This helps!
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Hi!

[TakenFromNet/Docs]
This simply means that your username or password are incorrect. If you have not changed the username, try using 'root' as the username and no password. If that works, then you are ok. Otherwise, you need to find the correct username / password combination OR set the proper permissions for that particular user to that database.

Also note that after changing security settings(added/updated users thru insert/update) you must run FLUSH PRIVILEGES command to reflect the chnages made..

If you have forgotten the root password then you may need to reset it..take a look at here

http://dev.mysql.com/doc/mysql/en/resetting-permissions.html


0
 
rebiesAuthor Commented:
ushastry:

That would not be the problem, because I have the 'user' and 'password' set in config.inc.php, and I am able to log in and do everything I want with phpMyAdmin.  So I know the username and password work.  I can also log in and do things via

mysql -u root -p

as well.  I'm only having problems using the MySQL Server Instance Config Wizard.  I guess I could probably allow remote access via phpMyAdmin or via the MySQL command line program, but I would have no idea how that is done.  I tried adding a user into the MySQL > User table with the "Host" of my IP address, and another with my ISP's host name, but that did not do the trick either.

Andrew
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
campbelcCommented:
By default mysql doesnt allow you to connect remotely to your databases. By default localhost is allowed. May be something as simple as updating the mysql user row.

use mysql
update mysql set hostname = '%' where username = 'root'
0
 
campbelcConnect With a Mentor Commented:
sorry its:

use mysql
update user set Host = '%' where User = 'root'
0
 
campbelcCommented:
How is that any different then what I had recommended?

Your creating a new user, I'm using the existing root user he was talking about that failed to connect from remote PCs.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
campbelc,

Comment from campbelc
Date: 05/11/2005 09:38AM IST

>>use mysql
>>update mysql set hostname = '%' where username = 'root'

U should have used mysql.user or simply user here..

Comment from campbelc
Date: 05/11/2005 09:40AM IST

Comment from ushastry
Date: 05/11/2005 09:40AM IST

CHeck the the posting time here...campbelc/ushastry  09:40AM...means we had posted at the same time..then how would I know that u have posted...

Also when u update directly the grant tables..you must do flush privileges..otherwise it won't work..




0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Any updates?
0
 
rebiesAuthor Commented:
Okay, thanks guys.  I apologize on not closing this topic earlier.  I had been so busy and it was not until 2 weeks later that I finally found a solution.  I'll split points as ushastry and campbelc both helped contribute to the answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.