You don't need to move anything. Let the MySQL engine deal with the db files.

In tutorials there is the answer to your question.
Check Reading from a database for details.

If you've read Dubois's book, I wonder what you'd need more, but here goes: (untested)

#!/usr/bin/perl -w
use strict;
use DBI;
my $database = 'test';
my $username = 'username';
my $password = 'secret';
my $dbh=DBI->connect("dbi:mysql:$database",
$username,$password,
{RaiseError=>1}) or die "can't\n";

#if you get this far, you're connected
# and you can list the tables in your db

print "$_\n" for $dbh->tables;

$dbh->disconnect();
[download]

Update

Did you set a user and password in MySQL, or are you running as root without a password? (default out-od-the box).

If that's the case, you should authorize a new user with a password:

use mysql;
set password=password('rootpassword');
update user set password=password('rootpassword') 
where user='root';
delete from user where password='';
grant all privileges on testdatabase.* to kiat 
identified by 'yourpassword';
flush privileges;
[download]

Then log in as 'kiat' (mysql -u kiat -p) and you should be able to use that db, and also the script should work.

For more info, see the appropriate docs.

Thanks, mahi! I saved your code as test2.cgi and ran it from the browser. I don't see any output. Is it supposed to produce any output? I installed mysql quite a while ago and I'm not even sure if I had set a username and password. Is there a way to check?

Kiat,


Don't try to run it from the browser (i.e. via CGI) yet! Does it work from the command-line first? You have to take things one step at a time. So, first you need to figure out: can you connect to the mysql db manually, what password and user-name have you setup, etc.. This may not be a PERL problem at all! Also, if you don't know the user/password you may seriously want to consider reinstalling MySQL again so that you know exactly how it is set up.

Hope it helps!
-Tats

Here is a short bit of code to try and see if everythings working as it should, you'll have to change to your needs...

#!/usr/bin/perl -w
use strict;
use DBI;

my $dbtype = "mysql";
my $dbname = "mydb";
my $dbuser = "username";
my $dbpass = "userpassword";

my $dbh = DBI->connect("DBI:${dbtype}:${dbname}", "$dbuser", "$dbpass"
+) || die $DBI::errstr;

my $sql = "SELECT foo, bar FROM mytable";
my $sth = $dbh->prepare($sql);
$sth->execute() || die $DBI::errstr;

while (my ($foo, $bar) = $sth->fetchrow()) {
  print "Column foo contains: $foo\n";
  print "Column bar contains: $bar\n";
}

$sth->finish();
$dbh->disconnect() || warn $DBI::errstr;
[download]

Hopefully that will get you started.
barrd

Thanks, barrd! I saved your code as sql_test.cgi and ran it off the browser but I don't see any output. What could be wrong? Do I've to activate mysql or something?

No problem, first off see rnahi's excellent reply/update above about setting up a username/password. And if you used your browser to activate the script then a blank page is what you would expect as the script is designed to be run from the command line... sorry, I should have pointed that out. It was for test purposes only to ensure that you could get "something" out of the MySQL DB.

barrd

This will give you an advantage of not having the user/pass in multiple locations which will make your life easier should you ever have to change the database password and you can put this file somewhere other than your www directory with your cgi scripts and make it only readable by www and root users.

I outline how we did this here

I should point out that this is not a security cure-all, rather it is a bit more secure than having your user/pass in your cgi's and certainly more convienant.

This isn't Perl, but here goes...

An ISP that allows you to use MySQL almost certainly will give you login access to the database server WITHOUT giving you access to the physical location on their server where the database is located. So, you won't be able to "upload the database files" anywhere... the best you'll be able to do is export your data from your local tables into a portable (tab or pipe-delimited format), upload that to your ISP, and then connect to your MySQL account and use SQL like LOAD DATA INFILE to import your table data from the flat file to the ISP's database.

For more details on how to do this (and, in fact, to learn just about everything there is to know about MySQL) check out the MySQL online manual.

Gary Blackburn
Trained Killer

Another way is to write a small DBI script that connects to both DB's: your local one, and the internet one. The script then reads from your own local, and inserts everything into the other one.

The advantage is that you can use it anytime once you've written it once, and adjust it to work for any table you like.