use DBI;
use DBD::mysql; #although not used in this example
[download]

option 1: From what I understood from the docs, the most portable way of accessing a list of existing mySQL databases was to use the following:

my @installed_databases = DBI->data_sources('mysql');
[download]

This method works fine for MySQL and on my machine, until I started installing the script on another machine with the same architecture.Then I began to get privilege errors and I couldn't figure out why. I knew from the docs that this statement only works for the localhost access, but it still should have worked. Finally I discovered the reason: because you can't specify a user and password with this method, it defaults to the user running the perl program. if you specified a MySQL root password in your grant table, then you can't run your perl program as root (or any user with a localhost entry with a password in your grant table for that matter). You need to run the program as any user without a password.

ADVANTAGES: Supposed to be most portable to other database engines. Also, easiest option if your program is a one-time solution that will live just on your machine.

DISADVANTAGES:Can't specify host, so only good for default localhost. Can't specify user and password, so you essentially can't run the perl program as a user that has a password in the MySQL grant table, including root.

option 2: Ok, so the next recommended option was to go with a supported, but less portable way:

my $drh=DBI->install_driver('mysql');
my @installed_databases= $drh->func($HOST,'_ListDBs');
[download]

This install_driver method comes straight out of the DBI docs. I used it because at first I didn't know it was possible to connect to MySQL without specifying a database. It works fine, but it had the same problem as above, in that you couldn't run the program as a user that had a password in the MySQL grant table, eg. as root.

ADVANTAGES: You can specify host.

DISADVANTAGES: Not portable to other database engines? Can't specify user or password, so you essentially can't run the perl program as a user that has a password in the MySQL grant table, including root.

option 3: So at this point I found a way to list the databases where I could specify my username and password....by using the command line:

my @installed_databases = `mysqlshow --user=$USERNAME --password=$PASS
+WORD`;
[download]

which worked fine,and you could even specify a --host tag if you needed. (By the way, those are `back quotes`, not 'single quotes', if you've never used them before). It didn't matter if you ran the program as root or any other user. However,the problem with this was that it would definitely not be portable to other database engines that I would try in the future.

ADVANTAGES: You can easily specify host, user, password. You can test output right away from the command line.

DISADVANTAGES: Definitely NOT portable to other database engines.

option 4: But after a post to Perl Monks, I learned something important from a reply: you do not have to specify a database when using the DBI connect method - something which is never mentioned in the docs or book. So I go back to option 2, but modify it like this:

my $dbh=DBI->connect("DBI:mysql:host=$HOST",$USER,$PASSWORD);
my @installed_databases= $dbh->func('_ListDBs');
[download]

You can leave out the host=$HOST to default to localhost (but leave the trailing colon after mysql, i.e. 'DBI:mysql:').
Works fine. Portable to other databases engines? I'm not sure, but I'm sure it is more portable than option 3.

ADVANTAGES: You can easily specify host, user, password. And a database, once you have an existing one.

DISADVANTAGES: May not be portable to other database engines?

Comments, corrections, and other options are welcome. If none, then I hope this just helps someone in the future.

Update: Summarized options with advantages/disadvantages.