use --login-path=local in perl DBD::mysql

Question

mysql supports passwordless login using stored local authentication credentials in a file named .mylogin.cnf (see here for more details).

for example:

mysql --login-path=local

My questions is: how to do this in perl using DBD::mysql?

Answer 1

DBD::mysql uses the MySQL C API, which doesn't appear to support login paths. As an alternative, you can use an option file and the mysql_read_default_file and mysql_read_default_group options in your connect call:

use strict;
use warnings 'all';

use DBI;

my $dsn = 'DBI:mysql:' .
          ';mysql_read_default_group=local' .
          ';mysql_read_default_file=/path/to/config';

my $dbh = DBI->connect($dsn, undef, undef, {
    PrintError => 0,
    RaiseError => 1
});

Your option file should look something like this:

[local]
host     = localhost
database = foo
user     = myuser
password = mypassword

[remote]
host     = remote.example.com
database = foo
user     = myuser
password = mypassword

Note that unlike .mylogin.cnf (the file used by --login-path), regular option files are not encrypted. This isn't as big of a problem as it might sound. The main benefit of encryption is that it prevents you from accidentally exposing your credentials, e.g. when viewing the file, but it doesn't provide unbreakable security. You can still protect your option file by making sure it's not world-readable, excluding it from version control, etc.