Topic: mysql cron bkups

[ted]

Is there any way to setup a cron job to do a nightly backup of my database?  I tried mysqlhotcopy, but I got a whole bunch of perl errors.
Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/5.6.0/i386-linux /usr/lib/perl5/5.6.0 /usr/lib/perl5/site_perl/5.6.0/i386-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) at /usr/bin/mysqlhotcopy line 8.

[Jason Miller]

Why not use the mysqldump tool that also ships with mysql and is used by the e-smith backup facility?

Here's how I backup the database for my primary website.  Below is a script called make_backups which I stored in /etc/cron.daily so that it will run every day:

-------------------------
#!/bin/sh

mysqldump [database_name] > /home/e-smith/files/primary/files/sql/[database_name].sql
-------------------------

-> replace [database_name] with your database name and obviously put the files whereever you would rather place them.  It assumes root is running the cron, but you could also pass it parameters for the dbuser and dbpassword (see man mysqldump)

I like it because its simple and effective.  If you have more than one database, you could also back them up in the same script.

Sorry, I'm not familiar with mysqlhotcopy.

Regards,

Jay

[Alexie]

Good ide.But remember to incl Password if you use that.!!

-------------------------
#!/bin/sh

mysqldump -p[password] [database_name] > /home/e-smith/files/primary/files/sql/[database_name].sql
-------------------------

[Jason Miller]

Yep.  

I was thinking of making it more specific [user name and password], but in this case it still works because cron.daily is run as root and therefore no password is required to access mysql or mysql administrative commands ...

Thanks for pointing that out.

Regards,

Jay