MySQL DB Server Issues

RomeoS

RomeoS

RomeoS

Associate
Joined
8 Jul 2004
Posts
1,818
Location
London
I have a Apache2 webserver running on 10.0.0.102 (Running Windows 2003 Standard) and a MySQL DB Server on 10.0.0.70 (Running Windows 2000 SP4).

On the webserver (10.0.0.102), I have set up a forum that can be accessed via a no-ip domain. What I want is the forums DB to be installed on 10.0.0.70. I have enabled remote root access and made sure its running on port 3306 (netstat confirmed).

On forums install, I select MySQL version 5 and enter the IP instead of localhost and fill the username and password.

Regardless of what I try, I get "Can not connect to the Database".

What am I doing wrong? I seperated MySQL because its going to be used pretty heavily and now need a dedicated box for it.

MySQL version: 5.0.18
 
Have you unblocked all the necessary firewall ports? (3306 to/from .102 & .70)

Also, have you altered the permissions of the database user to allow connections from .102 (the default is localhost only)?
 
Hi mate,

Does port 3306 need access to the external world? Neither the Apache or the MySQL box have firewalls running, they sit behind the routers firewall.

How would I allow the DB user (root in this case) to accept connections from .102? During the MySQL config, I enabled "allow remote root access" which I would have thought ment user "root" could connect from .102?
 
From the 2K3 box, what happens if you telnet to port 3306 on the 2k box? Ie, start, run, cmd, 'telnet 10.0.0.70 3306'

If it works you should get a message showing you the MySQL Server version.

You can check where root can connect from if you do:

select Host,User from mysql.user where User="root";

If you don't get back a record listing either the IP/DNS name of the 2k3 box or a record with % (meaning anywhere) as the host, then root can't connect from the 2k3 box.

Personally I'd not connect to the DB as root from an application since root has control over everything, not just the DB you're working on. I'd do this:

grant all privileges on forumdb.* to [email protected] identified by 'password';

That way you have a user called forum_user who can connect from 10.0.0.102 only, using the password you specified. The user has full access to all the tables in the db called forumdb but no access to anything else. If you knew exactly what privs the forumdb user needed you could just grant it those.
 
burbleflop said:
From the 2K3 box, what happens if you telnet to port 3306 on the 2k box? Ie, start, run, cmd, 'telnet 10.0.0.70 3306'

If it works you should get a message showing you the MySQL Server version.
Click to expand...

Yup, get MySQL version> at the command prompt followed by some random characters, then disconnection.

You can check where root can connect from if you do:

select Host,User from mysql.user where User="root";

If you don't get back a record listing either the IP/DNS name of the 2k3 box or a record with % (meaning anywhere) as the host, then root can't connect from the 2k3 box.
Click to expand...

Dont have enough milliseconds to copy paste that before I get disconnected because of above problem.

Personally I'd not connect to the DB as root from an application since root has control over everything, not just the DB you're working on. I'd do this:

grant all privileges on forumdb.* to [email protected] identified by 'password';

That way you have a user called forum_user who can connect from 10.0.0.102 only, using the password you specified. The user has full access to all the tables in the db called forumdb but no access to anything else. If you knew exactly what privs the forumdb user needed you could just grant it those.
Click to expand...

Left it as root for now to eliminate any permission problems that may arrise, once its up and running i'll set up a limited user :)
 
RomeoS said:
Yup, get MySQL version> at the command prompt followed by some random characters, then disconnection.
Click to expand...

Ok, so no firewalls getting in the way and blocking the connection then.

RomeoS said:
Dont have enough milliseconds to copy paste that before I get disconnected because of above problem.
Click to expand...

I should have said, but actually do that on the MySQL server itself.
 
Yes, root can connect locally, but can root connect from 10.0.0.127? The SQL statement I posted earlier will tell you.

Using one of my machines as an example:
Code: 
mysql> select Host,User from mysql.user where User="root";
+-----------+------+
| Host      | User |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

That shows that root can only connect from localhost, but if I look at another account:
Code: 
mysql> select Host,User from mysql.user where User="paul";    
+-----------+------+
| Host      | User |
+-----------+------+
| %         | paul |
| localhost | paul |
+-----------+------+
2 rows in set (0.00 sec)

The 'paul' user can connect from anywhere because of the % in the first record.
 
So root is allowed to connect then. Is there any debugging you can enable in the forum software?

It might be worth installing some sort of MySQL client on the 2K3 machine and checking that you can connect from there.
 
You must log in or register to reply here.
Back
Top Bottom