[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