CODEWITHSUNDEEP
mysqlperldbi
avi_m1968
avi_m1968

Reputation: 23

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

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 :-)

Upvotes: 2

Views: 3499

Answers (2)

ysth
ysth

Reputation: 98398

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.

Upvotes: 4

DVK
DVK

Reputation: 129423

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

Upvotes: 4

Related Questions