Perl DBI DBD::mysql get the databases name from mysql server

Question

I have a production server that creates different databases everyday in a MySql server.

i need to write a perl script that would run at the end of the day and do the following.

connect to the mysql server and get the list of all the names of the databases it has. then connect to each of them and check the data that was stored.

the only thing i wasn't able to do with DBI and DBD::mysql was read the database names from the mysql server.

i wasn't able to find any DBI function that would work without connecting to a database first.

any help/example/URL would be very helpful :-)

In Sybase, there's a standard "master" database where info like that is stored. E.g. for your purpose master..sysdatabases. Not sure about MySql — DVK
– DVK, Commented Dec 6, 2010 at 13:56

ysth · Accepted Answer · 2010-12-06 23:15:45Z

4

use DBI;
use strict;
use warnings;
my $dbh = DBI->connect('dbi:mysql:information_schema', $ENV{'USER'}, $ENV{'PASSWD'}, { 'RaiseError' => 1 } );
my $databases = $dbh->selectcol_arrayref('show databases');

use Data::Dumper;
print Dumper $databases;
__END__
$VAR1 = [
      'information_schema',
      'mysql',
      'your_database_1',
      'your_database_2'
    ];

Only databases authorized to the user you connect with will be shown.

edited Dec 6, 2010 at 23:15

answered Dec 6, 2010 at 16:05

ysth

99.1k6 gold badges126 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

avi_m1968 Over a year ago

THANK YOU :-) that worked , i have added it to my code and it works like a charm :-) thank you again.

DVK · Accepted Answer · 2010-12-06 13:59:39Z

4

Try using INFORMATION_SCHEMA database - that database name always exists and thus can be hard-coded into DBI.

To be more specific, the equivalent to SHOW DATABASES is:

SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA

answered Dec 6, 2010 at 13:59

DVK

130k33 gold badges219 silver badges337 bronze badges

Collectives™ on Stack Overflow

Perl DBI DBD::mysql get the databases name from mysql server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related