How to access MySQL database using Perl

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
## 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 "<p>Here is a list of tables in the MySQL database $db.</p>";
while (@row= $sqlQuery->fetchrow_array()) {
my $tables = $row[0];
print "$tables\n<br>";
$rc = $sqlQuery->finish;

Save and upload above program.

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 14 comments so far... add one

CategoryList of Unix and Linux commands
Disk space analyzersdf duf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Modern utilitiesbat exa
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg glances gtop jobs killall kill pidof pstree pwdx time vtop
Searchingag grep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
14 comments… add one
  • Ron Lusk Sep 19, 2008 @ 21:59

    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.

  • Stone Man Mar 10, 2009 @ 4:48

    Hi Nixcraft,

    Thanks for this code but i got the following:

    – I copy the code and paste it in this file
    – And run chmod +x
    – Then run ./

    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.

    [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:
    cmd? q

    ANy suggestion on how to fix this?
    Thanks again!


  • medoix Nov 20, 2009 @ 7:33


    remove $rv = and $rc = they are not needed on those lines.

  • Anitha Dec 3, 2009 @ 5:15

    I have to synchronize bugzilla which uses mysql and HP quality centre which uses mssql .. i’m tryin to do this in perl.. As a fresher i’m not sure wat perl can do .. can anyone suggest me

  • Lina Oct 6, 2010 @ 23:33

    This really does work. After 3 days of searching this is the first script that works.

    THANK YOU!!!!!!!

  • Abby Dec 1, 2010 @ 7:34

    Works beautifully! Thank you :)

  • ahmed Sep 27, 2011 @ 8:48

    It’s working very well
    thank you.

  • UncaAlby Oct 27, 2011 @ 7:14

    Works like a champ!

    My only significant modifications were “use strict” which means adding “my” to declare all the variables.

    It would be nice if it also had an example of adding parameters to the prepared statement. I happened to look that up separately, which I’m adding to this comment in case someone else finds it useful.

    Example: list every row where the user_id starts with “a”.

    $sql = "select user_id from users where user_id like ?";
    $sth = $dbh->prepare($sql) or die(DBI->errstr);
    $sth->bind_param(1, "a%");
    $rv = $sth->execute or die(DBI->errstr);
    print "User ID's that start with 'a'\n";
    while (my @row = $sth->fetchrow_array()) {
      my $tables = $row[0];
      print "$tables\n";
    $rc = $sth->finish;

    Results (from my table):

    User ID’s that start with ‘a’

  • Cory Jun 27, 2012 @ 19:54

    Thank you!

  • slyft May 29, 2013 @ 11:49

    What extention should I use while saving the program? An does it need any compiler or IDM ? Thanks

  • pavan Jun 25, 2013 @ 6:53

    Thanks a lot,
    I have been searching for this, past 4 days.

    Can you please share script to connect excel file into perl.

    Thanks in Advance.

  • khinelay Aug 11, 2014 @ 13:32

    I’ve got an error when I installed the DBI::mysql via cpan.I am using ec2(ubuntu 14) and rds(mysql). In my ec2 I’ve already DBI via cpan. When I install DBI::mysql, I’ve got an error.
    cpan[1]> install DBI::mysql

    Reading '/home/ubuntu/.cpan/Metadata'
    Database was generated on Sun, 10 Aug 2014 14:06:13 GMT
    Warning: Cannot install DBI::mysql, don't know what it is.
    Try the command
    i /DBI::mysql/
    to find objects with matching identifiers.
    so i type i /DBI::mysql/ and I've got this message 
    nolock_cpan> i /DBI::mysql/                                                                                                                       
    Module  < Ambrosia::DataProvider::DBI::mysql (KNM/Ambrosia-0.010.tar.gz)
    Module  < AnyEvent::DBI::MySQL   (POWERMAN/AnyEvent-DBI-MySQL-1.0.5.tar.gz)
    Module  < AnyEvent::DBI::MySQL::db (POWERMAN/AnyEvent-DBI-MySQL-1.0.3.tar.gz)
    Module  < AnyEvent::DBI::MySQL::st (POWERMAN/AnyEvent-DBI-MySQL-1.0.3.tar.gz)
    Module  < AnyEvent::DBI::MySQL::st::ready (POWERMAN/AnyEvent-DBI-MySQL-1.0.3.tar.gz)
    Module  < Audio::DB::Adaptor::dbi::mysql (TWH/Audio-DB-0.01.tar.gz)
    Module  < Bio::DB::DBI::mysql    (CJFIELDS/BioPerl-DB-1.006900.tar.gz)
    Module  < Bio::DB::GFF::Adaptor::dbi::mysql (CJFIELDS/BioPerl-1.6.924.tar.gz)
    Module  < Bio::DB::GFF::Adaptor::dbi::mysqlace (CJFIELDS/BioPerl-1.6.924.tar.gz)
    Module  < Bio::DB::GFF::Adaptor::dbi::mysqlcmap (CJFIELDS/BioPerl-1.6.924.tar.gz)
    Module  < Bio::DB::GFF::Adaptor::dbi::mysqlopt (CJFIELDS/BioPerl-1.6.924.tar.gz)
    Module  < Bio::DB::SeqFeature::Store::DBI::mysql (CJFIELDS/BioPerl-1.6.924.tar.gz)
    Module  < CHI::t::Driver::DBI::mysql (JSWARTZ/CHI-Driver-DBI-1.24.tar.gz)
    Module  < Class::DBI::mysql      (TMTM/Class-DBI-mysql-1.00.tar.gz)
    Module  < Class::DBI::mysql::FullTextSearch (TMTM/Class-DBI-mysql-FullTextSearch-1.01.tar.gz)
    Module  < DBIx::Class::Fixtures::DBI::mysql (JJNAPIORK/DBIx-Class-Fixtures-1.001021.tar.gz)
    Module  < DBIx::Class::Schema::Loader::DBI::mysql (ILMARI/DBIx-Class-Schema-Loader-0.07041.tar.gz)
    Module  < DBIx::Class::Storage::DBI::mysql (RIBASUSHI/DBIx-Class-0.08270.tar.gz)
    Module  < DBIx::Class::Storage::DBI::mysql::backup (NGS/DBIx-Class-Storage-DBI-mysql-backup-0.04.tar.gz)
    Module  < DBIx::Skinny::Schema::Loader::DBI::mysql (NEKOYA/DBIx-Skinny-Schema-Loader-0.25.tar.gz)
    Module  < DBO::Handle::DBI::mysql (ABW/DBO-0.01.tar.gz)
    Module  < JAF::DBI::mysql        (GRISHACE/Apache-JAF-0.21.tar.gz)
    Module  < NoSQL::PL2SQL::DBI::MySQL (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
    Module  < NoSQL::PL2SQL::DBI::MySQL::Schema (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
    Module  < NoSQL::PL2SQL::DBI::MySQL::Schema::index (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
    Module  < NoSQL::PL2SQL::DBI::MySQL::Schema::index::column (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
    Module  < NoSQL::PL2SQL::DBI::MySQL::Schema::table (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
    Module  < NoSQL::PL2SQL::DBI::MySQL::Schema::table::column (TQISJIM/NoSQL-PL2SQL-1.21.tar.gz)
    Module  < ORM::Db::DBI::MySQL    (AKIMOV/ORM-0.85.1.tar.gz)
    Module  < ORM::Db::DBI::MySQLemTa (AKIMOV/ORM-0.85.1.tar.gz)
    Module  < RDF::Trine::Store::DBI::mysql (GWILLIAMS/RDF-Trine-1.009.tar.gz)
    Module  < SPOPS::DBI::MySQL      (CWINTERS/SPOPS-0.87.tar.gz)
    Module  < SQL::Translator::Parser::DBI::MySQL (FREW/SQL-Translator-0.11018.tar.gz)
    Module  < SimpleDBI::mysql       (ABBYPAN/SimpleDBI-0.01.tar.gz)
    34 items found
    • voiptesterchn Jun 5, 2015 @ 9:45
      use DBI;
      use strict;
      my $driver = "mysql"; 
      my $database = "TEST";
      my $dsn = "DBI:$driver:database=$database";
      my $userid = "BOOK";
      my $password = "BOOK123";
      my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
      print  "Database connected :$dbh\n";

      Here I am able to create a datbase after running the script .(
      like ./
      Please correct below script if its incorrect .But its not working .
      I want to write script which will ask to user to create database name ,user name and passowrd after running the script..(User can give the input from keyborad)
      like Please enter the Databse Name if not exists .

      use DBI;
      use strict;
      my $driver = "mysql"; 
      my $database =  ;
      my $userid = ;
      my $password = ;
      my $dsn = "DBI:$driver:database=$database";
      my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
      print  "Database connected :$dbh\n";
      $dbh->do("Create database  $database ");
      print  ("Datbase Created ");
  • Doug Marker Sep 3, 2015 @ 3:36

    This worked well for me. Very easy to follow. I too use ‘strict’ and had to add my to the various variables but other than that it was a great find.


    Doug Marker

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum