[VPM] backup MySQL database with perl

Darren Duncan darren at darrenduncan.net
Tue Mar 24 22:18:38 PDT 2009


Jer A wrote:
> I am learning MySQL. I can manually backup use PHPmyAdmin.
> 
> what is an easy way to backup a database with perl (DBI), 
> programmatically, without knowledge of it's structure/contents (eg. 
> tables/records etc).
> 
> I would like to create some sort of scheduler for this as well.
> also, I wondering if backing it up as an email attachment is a good 
> thing as well?

I can't tell you the details so you'll have to look them up, but one simple 
solution is to just invoke the 'mysql' command-line utility that comes with the 
DBMS.  I have used a line like the following to upload records into a MySQL 
database using that utility:

   mysql --user=myuser --password=mypass mydbname < mysqlfilename

I assume you can use a similar one-liner to dump the contents of a MySQL 
database as a SQL text file.  At that point all your script has to do is put 
that file somewhere.  Or if your script invokes the shell, it can call 'mysql' 
directly and just feed it custom file names so then 'mysql' puts the file where 
you want.

That's by far the simplest solution, not to mention probably the fastest and 
most reliable.

That said, be careful that the exact version I showed you may be insecure 
because your db password would show up in the process list or shell history 
file.  There is probably another way to invoke 'mysql' where it would then 
prompt you for a password, but that would be more complicated to automate.

Don't bother with DBI etc in Perl unless you want to make all sorts of changes 
to your data in the backup process, or just do selective backups, etc.

-- Darren Duncan


More information about the Victoria-pm mailing list