How do I direct DBI::mysql to a particular mysql instance

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

I am working in a small cluster with two instances of MYSQL. One MYSQL is used by the cluster head node for keeping track of the compute nodes. We installed another instance of MYSQL (different version,different path, different port) for general use. Our efforts to use DBI::mysql to connect to the general are not working. Some code

#!/usr/bin/perl -w
use strict;
use DBI;
my $host = 'localhost:3309';
my $db = 'fdb';
my $db_user = 'first';
my $db_password = 'firstpasswd';
my $dbh = DBI->connect("dbi:mysql:$db:$host",
                       "$db_user",
                       "$db_password") 
                       or die "Couldn't connect to database: ".DBI->er
+rstr;
[download]

whereas

/path/to/user/mysql/bin/mysql -u first -p
[download]

works fine. That is the user and password are in fact correct. Users can access the database with regular MYSQL commands. The PERL script above returns "Couldn't connect to database: Access denied for user 'first'@'localhost (using password:YES)" which is what happens when I enter the wrong password in the mysql command line case. So, I think the DBI::MYSQL, as I am using it, is trying to connect to the /usr/bin/mysql not to the /path/to/user/mysql/ which is where I want it to go. How do I tell DBI::mysql which mysql to connect to?

Comment on How do I direct DBI::mysql to a particular mysql instance Select or Download Code

Replies are listed 'Best First'.
Re: How do I direct DBI::mysql to a particular mysql instance by pc88mxer (Vicar) on Jul 18, 2008 at 21:26 UTC
When you run: `mysql -u first -p` [download] you are accessing mysql through the Unix domain socket (i.e. `/var/run/mysqld/mysqld.sock`), not a TCP socket (i.e. port 3306). Here's how to connect in each of these cases: `# connect via the Unix domain socket my $dsn = "DBI:mysql:database=$db"; my $dbh = DBI->connect($dsn, ...); # connect via a TCP socket my $dsn = "DBI:mysql:database=$db;host=localhost;port=3309"; my $dbh = DBI->connect($dsn, ...);` [download] For more details, see the DBD::mysql documentation.	[reply] [d/l] [select]
Re^2: How do I direct DBI::mysql to a particular mysql instance by hazards (Initiate) on Jul 21, 2008 at 17:56 UTC
Folks, I thank you for the in sights. I attempted the suggested fixes with the following modified code. `#!/usr/bin/perl -w use strict; use DBI; my $port=3309; my $host = '127.0.0.1'; my $db = 'udb'; my $username = 'usr'; my $password = 'passwd'; my $dsn = "DBI:mysql:database=$db;host=$host;port=$port"; my $dbh = DBI->connect("$dsn", "$username", "$password") or die "Couldn't connect to database: ".DBI->er +rstr;` [download] Which is,I think, the suggested TCP socket case using the loopback address for the localhost yet in my hands I still see: `Couldn't connect to database: Access denied for user 'usr'@'localhost' + (using password: YES)` [download] What to try now?	[reply] [d/l] [select]
Re: How do I direct DBI::mysql to a particular mysql instance by bradcathey (Prior) on Jul 18, 2008 at 21:16 UTC
Hey hazards, can you put your code in <code> tags? —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply]