Author Topic: mysql database on seperate machine (Read 3138 times)

captainwinky · « **on:** March 08, 2006, 04:07:22 pm »

heya,

trying to move my bots over to another machine, but i'd prefer to keep the mysql database on this computer. i have set up MySQL.conf to access this machine, but apon running the bot i get:

[Keine Verbindung zur Datenbank!] (Lost connection to MySQL server during query)
Warning: mysql_connect(): Lost connection to MySQL server during query in /home/****/BeBot_***********/MySQL.php on line 36

any help would be greatly appreciated

CW

Alreadythere · « **Reply #1 on:** March 08, 2006, 04:19:17 pm »

Did you grant the db user remote access from the new server? Pretty certain that you need to add another GRANT for non-local access.

captainwinky · « **Reply #2 on:** March 08, 2006, 04:27:13 pm »

hmm, i'm rather noobish to MySQL... well, very, not rather :p
i'll look into creating/allowing this GRANT. thanks

CW

Alreadythere · « **Reply #3 on:** March 08, 2006, 04:31:54 pm »

From the manual:

Quote

Give username full access to all tables from example.com:
grant all on *.* to [email protected] identified by 'password';

Give username full access to all tables from any hostname:
grant all on *.* to username@% identified by 'password';

Give username select access to all tables in database foo:
grant select on foo.* to username@hostname identified by 'password';

captainwinky · « **Reply #4 on:** March 08, 2006, 04:48:26 pm »

thanks for the link to the manual. couldn't remember where it was *hides*... feeling a little daft.
still having problems, but biggest issue at this point is it's been a while since i initially set up mysql, and now i can't remember how to do even the simplest of things :/
installing mysql-client on the other machine now to see if i am able to access my database so as to determine whether i have done something wrong with the bot-setup, or if i do need to set up mysql to allow connections from other computers.

CW

captainwinky · « **Reply #5 on:** March 08, 2006, 05:05:43 pm »

this is puzzling
trying to log into mysql using mysql-client, i get:

ERROR 2003 (HY000): Can't connect to MySQL server on '*****' (111)

the nearest info i can find to this suggests that the mysql server is not running on the host, but it definitely is up and running ?? :/

CW

MatHack · « **Reply #6 on:** March 08, 2006, 05:11:59 pm »

I have MySQL running on a different machine as my bots, and I'm certain it has something to do with your settings (either BeBot or MySQL ones, but I suspect MySQL).

All you have to do is run these 2 commands on your mysql-server:

Quote

GRANT ALL PRIVILEGES ON *.* TO 'username'@'clientmachinename' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

please include the ' around the words where they stand.

captainwinky · « **Reply #7 on:** March 08, 2006, 05:24:57 pm »

thanks. tried doing exactly that, but still same error :/

i also think it would seem to be a problem my mysql-server rather than the bot as i am getting the ERROR 2003 message while trying to use mysql-client from the other machine, rather than trying to access it with BeBot.

mysql seems quite happy with the input regarding 'grant'. i'm trying to log in from the other machine following the manual's instructions. very confused as to why it won't work.

CW

Alreadythere · « **Reply #8 on:** March 08, 2006, 05:26:56 pm »

Do you have any kind of firewall running on/between the hosts?

captainwinky · « **Reply #9 on:** March 08, 2006, 05:42:00 pm »

i have iptables on the mysql-server machine, but there are no rules set up at all.

the network server here has a firewall between us and the web, but there is no blocking of ports or the likes between local machines to my knowledge.

CW

captainwinky · « **Reply #10 on:** March 08, 2006, 05:57:01 pm »

oh, found the problem. took a bit of sifting through google (or a lot i should say).

Quote:
Embarrassingly, the trouble turned out not to be iptables at all; I DID
do it correctly. But mysql was set to accept connections only from
localhost, and I'd previously thought that mysql handled filtering by
where the connection came from only at the individual user level (e.g.
grant all on dbname.* to 'username'@'host' identified by 'password' will
only allow that user to connect from 'host'). Turns out there's also a
setting in my.cnf:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

I commented this out and restarted the mysql server and voila! remote
connections accepted.
End Quote.

thanks for the help

. i'd still be screwed without the 'grant' information.

CW