MySQL backup/dump perl/script with cron

[denn88]

I was looking for a good backup script (perl) that I can use to create dump files of my MySQL database. I found a good script that does just that (can even email you a .tar.gz of the backup) but it doesn't work on my Panther Mac.

This is the perl script I found: MySQL Backup 3.1 you can download it here. I changed the settings so that it works with my passwords an my correct dirs. All looks good but where it says:

use DBI;

... it stops. I'd like to use this script with cron, so my mysql databases are backuped every day.

I'd works kinda like this: It uses the the mysqldump command (mysql) to export the db to a text file, later on it .tar.gzip's the file and adds the date of the dump file to it. If you want it can email or ftp it somewhere.

Anyone have any suggestions or other apps./scripts that would work somewhat the same? I just want a .txt dump file of every db. I'd like it to find/export all my datbases, even If I add one sometimes. I'd like a dump file of every mysql database in a text file.

If cron could handle the daily backups, I'd be very happy...

[hayne]

Quote: Originally posted by denn88 All looks good but where it says: use DBI; ... it stops.

It sounds like you haven't installed the Perl DBI module. See http://dbi.perl.org/

[acme.mail.order]

DBI is a Perl module that you apparently don't have installed, or it's a different name. . Contact the script author or dig around in the volumnious perl resources on the net.

If your databases are reasonably static, writing a script to dump/tar/zip them is trivial, and making cron do it daily adds about one minute to the programming time.

The script you've got seems to have everything including the kitchen sink, and decent documentation to boot. Might look at it for my own server.

[black0]

Acme is right, just doing what you are trying to do is practically trivial. There is no need to get all fancy. This is the script I use to do exactly what you are talking about:

#!/bin/bash
backupdir=*****
backupfile=`date +backup-%d-%m-%y_%H.sql`
tarfile=mysqlbackup.tar
cd $backupdir
mysqldump --opt -A -h ***** --password=***** -u root > $backupfile
gunzip "$tarfile".gz
tar rf $tarfile $backupfile &> /dev/null
gzip $tarfile
rm $backupfile

This dumps the DB, puts the datestamp right into the name and inserts it into a tar file. The only difference from what it sounded like you are trying to do is that it uses one tar file to store all of my backups rather than a tar file per backup. (the ***** should, of course, be filled in with the appropriate values)

[georgeocrawford]

Have a look at automysqlbackup - it's a shell script with some nice features:

• Backup mutiple MySQL databases with one script. (Now able to backup ALL databases on a server easily. no longer need to specify each database seperately)
  
• Backup all databases to a single backup file or to a seperate directory and file for each database.
  
• Automatically compress the backup files to save disk space.
  
• Can backup remote MySQL servers to a central server.
  
• Runs automatically using cron or can be run manually.
  
• Can e-mail the backup log to any specified e-mail address instead of "root". (Great for hosted websites and databases).

It also does a nice rotation of Daily, Weekly and Monthly backups by default.

[denn88]

Nice code, could work for me. Does the '-A' or '-h' cause MySQL to dump all tables to the same .sql file? (not so smart) or seperate .sql files? I'd like separate files of every database. I don't use this terminal command, I use CocoaMySQL to manage my db's and do dumps sometimes.

But it's what I'm looking for! What do I fill in after the '-h' flag? A root user? localhost? The second is the password... so far so good... and does this command dumps all db's?

Thanx so far...

[black0]

The -h is the same as the -h in the mysql command line tool; i.e., it allows you to specify the host. The -A does indeed dump all databases (this is appropriate for my particular server). My tables are small enough that I can easily cut and paste out the particular table I want to restore in the rare instance that I actually need to fix something.

One certainly could use a loop to dump each DB individually to its own file. The primary change to the mysqldump line would be to remove the -A and to put the name of the DB after 'root'.

[denn88]

Hi all, I found 'georgeocrawford's' script very usefull. It now backups my MySQL databases very nice. The setup of the script is pretty simple and works great. Still have to wait for the weekly and monthly backups...

But I still like to test things using a simpler script or a smaller script, custom made perhaps... Maybe I'll try to tweak the script so that it creates single .sql files of every db, all in the same folder, no tar.gz. Because I would like that instead.

All I wanted was script that just backuped all my MySQL databases and can be used with cron. So I don't have to worry about it at all. The script suggested by 'black0' would work, but still needed to manually enter the db names. Yes I could dive into Unix and come up with a loop script, but I'm not a programmer...

So thanx all for the support... I'll stick with the script by 'georgeocrawford' for the time being... Thanx. One other thing: Ever heard of the free app: 'CronniX'? If you do a lot of cron jobs and still have to edit the cron tab yourself using the terminal, it can save you alot of time. It's a nice cocoa app. can even edit the system/root cron tab. Works for me...

[vonleigh]

This is what I have in my /etc/daily.local:

echo "Rotating MySQL backups:"

for i in /Users/mysql/mysql_backup.sql; do
    if [ -f "${i}" ]; then
        echo " $i"
        if [ -x /usr/bin/gzip ]; then gzext=".gz"; else gzext=""; fi
        if [ -f "${i}.4" ]; then mv -f "${i}.4" "${i}.5"; fi
        if [ -f "${i}.3" ]; then mv -f "${i}.3" "${i}.4"; fi
        if [ -f "${i}.2" ]; then mv -f "${i}.2" "${i}.3"; fi
        if [ -f "${i}.1" ]; then mv -f "${i}.1" "${i}.2"; fi
        if [ -f "${i}.0" ]; then mv -f "${i}.0" "${i}.1"; fi
        if [ -f "${i}" ]; then 
                mv -f "${i}" "${i}.0"; 
        fi
        touch "${i}" && chmod 640 "${i}"
    fi
done

# dump new backup
echo "Dumping new MySQL backup."
/usr/local/bin/mysqldump -h localhost -u BACKUP_USER -pPASSWORD\
 --all-databases > /Users/mysql/mysql_backup.sql

Very simple, just rotates everything them dumps a new backup, in this way i get up to four days if I need it for any reason, you can make it more days if you want. You can also add something in /etc/weekly.local or monthly.local to have even older backups.

BACKUP_USER is a special user that only has permission to read all databases, but can't write to them (for a tiny bit added security, since the password goes in plain text in the command after -p). Those are the only two values you have to change to make it work for you. Maybe also the location of your backups if it's different from /Users/mysql.


v