connecting to MySQL on a different machine via DBI

indapa has asked for the wisdom of the Perl Monks concerning the following question:

Hi, Sorry if this question in answered somewhere in the documentation...but I'm trying to connect to a MySQL database which is on another machine. When I connect to MySQL via the command line I type the following and it works fine:

> mysql -h rat -u indapa -p
[download]

But if I want to connect to my database in Perl via DBI with the following code:


#!/user/bin/perl -w
use strict;
use DBI;

my $dsn = 'dbi:mysql:genedb';
my $user = 'indapa';
my $password = '*****';

my $dbh = DBI->connect($dsn,$user,$password) or die $DBI:errstr, "\n";
[download]

I get the following error when I run it:

DBI connect('aindap','aindap',...) failed: Can't connect to local MySQ
+L server through socket '/var/lib/mysql/mysql.sock' (2) at .//exon_qu
+ery.pl line 31
Can't connect to local MySQL server through socket '/var/lib/mysql/mys
+ql.sock' (2)
[download]

I know there is a way to do this, but I can't seem to figure it out. Your help is appreciated.

Comment on connecting to MySQL on a different machine via DBI Select or Download Code

Replies are listed 'Best First'.
Re: connecting to MySQL on a different machine via DBI by matija (Priest) on Mar 03, 2004 at 22:49 UTC
Your dsn should include the data on the host (and port, if it's not the default port), like this: `$dsn='DBI:mysql:database=genedb;host=some.host.com;port=3306';` [download]	[reply] [d/l]
Re: connecting to MySQL on a different machine via DBI by bart (Canon) on Mar 03, 2004 at 22:58 UTC
You have to make the connect string more elaborate, the simple "just the database name" won't do any more. The normal format is a set of name/value pairs coupled with an =" sign, and the pairs separated by a semicolon. Their order of appearance is irrelevant. So, try something like `my $dsn = 'dbi:mysql:database=genedb;host=rat';` [download] (as per the example that can be found here.) p.s. You were right, it is in the docs.	[reply] [d/l]
Re: connecting to MySQL on a different machine via DBI by jockel (Beadle) on Mar 04, 2004 at 15:35 UTC
Hello indapa, You also have to allow a user to connect from that computer. Read about GRANT in the MySQL manual. /jocke	[reply]