Q. How do I access my MySQL database server using Perl programming language?
A. DBI is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql.
So you can connect Perl to your MySQL database server using the DBI Perl module. Here is small program.
Sample Perl code
Make sure you replace database name, MySQL server hostname, username and password according to your setup.
#!/usr/bin/perl -w use DBI; print "Content-type: text/html\n\n"; ## mysql user database name $db ="mysql"; ## mysql database user name $user = "vivek"; ## mysql database password $pass = "myPassword"; ## user hostname : This should be "localhost" but it can be diffrent too $host="localhost"; ## SQL query $query = "show tables"; $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass); $sqlQuery = $dbh->prepare($query) or die "Can't prepare $query: $dbh->errstr\n"; $rv = $sqlQuery->execute or die "can't execute the query: $sqlQuery->errstr"; print " <h3>********** My Perl DBI Test ***************</h3> "; print " Here is a list of tables in the MySQL database $db. "; while (@row= $sqlQuery->fetchrow_array()) { my $tables = $row[0]; print "$tables\n<br>"; } $rc = $sqlQuery->finish; exit(0);
Save and upload above program.
Want to read Linux tips and tricks, but don't have time to check our blog everyday? Subscribe to our daily email newsletter to make sure you don't miss a single tip/tricks. Subscribe to our weekly newsletter here!
- Email FAQ to a friend
- Printable version
- Rss Feed
- Last Updated: 9-19-08

{ 2 comments… read them below or add one }
I’ve looked at a lot of sample code trying to learn how to successfully get extract any information from a database and this has been the first that actually worked! Kudos. Very helpful.
Hi Nixcraft,
Thanks for this code but i got the following:
- I copy the code and paste it in this file checkdb.sh.
- And run chmod +x checkdb.sh
- Then run ./checkdb.sh
Then the following occured:
Name “main::rv” used only once: possible typo at /usr/bin/check.db line 25.
Name “main::rc” used only once: possible typo at /usr/bin/check.db line 42.
Content-type: text/html
********** My Perl DBI Test ***************
Here is a list of tables in the MySQL database mysql.
columns_priv
db
event
func
general_log
help_category
help_keyword
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
servers
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
[root@drbl-01 ~]#
Currently installed Perl Modules:
[root@drbl-01 DBD-mysql-4.010]# instmodsh
Available commands are:
l – List all installed modules
m – Select a module
q – Quit the program
cmd? l
Installed modules are:
DBD::mysql
DBI
Perl
cmd? q
ANy suggestion on how to fix this?
Thanks again!
Stone