MySQL Backup to FTP and Email Shell Script for Cron v2.2

13 03 2011
It's been a while since I've publicly made updates to this script, but I did make some tweaks over the years that I'd like to share.

Here are some changes over the last version:

  • Delete old backups via FTP
  • Backup to multiple FTP servers
  • More efficient backups
  • Add time in filename (allows for multiple backups/day)
  • More verbose/better error detection

With that said, follow the link below to download the new version:
mysqlbackup-2.3.sh

You can view the previous version here.
If there's a feature that's missing that you'd like to see, leave a comment below.

Comments : 19 Comments »
Categories : Linux Luvin'
Trackbacks : No Trackbacks »

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)
13 03 2011
#1 Rob Ludlow (Reply)

Ameir, I'm very glad to read that you're keeping this AMAZING script updated. Here are some stats for you, and others that might be helpful and interesting:

1) I've been using your MySQL backup script for about 5 years!
2) I use it to backup about 14 of my most important DB's
3) My largest DB is 3 GB in size and compresses down to a 1gb file. NOTE: the special version you created allows me to ignore the search index tables... with those tables, my DB is 6.7 GB!
4) I backup all the DB's every day around 4:00 am EST without any problems
5) These backups have saved my butt about 30 times... I mean REALLY REALLY saved me, my businesses, not to mention all my tens of thousands of users!

I'm interested in updating my script for the regular backups that I do, but is there a chance you can create a skip tables version of this update for me?

Also, I've learned the hard way, "If it ain't broke, don't fix it!" That being the case, since your earlier version is working so amazingly well, do you think the pros of updating outweigh any potential cons?

Keep up the great work, and please shoot me an email when you have a second.
21 03 2011
#1.1 Ameir Abdeldayem (Reply)

Hey Rob! Wow, I'm glad to see that the script has gone so far for you. It's saved me plenty as well, but I'm definitely not pushing the amount of data you are; I'm happy to hear that this scales well.

There aren't many significant changes between this version and the previous one; if you don't need to back up to multiple FTP servers or delete FTP backups automatically, this version doesn't bring in anything too enticing.

Regardless, if you'd like for me to merge this version with the one I modified for you a while back, it shouldn't take too much effort. There aren't any cons with upgrading. Just let me know what you'd like to do and I can work at it in my free time.
13 03 2011
#2 Rob Ludlow (Reply)

Ameir, a question: I see in the new version you have "Delete old backups via FTP". With the old version, I keep 3 days locally and then I've been manually deleting backups over 30 days off the FTP site.

I only see one spot for "delete old files" and "DAYS=". Does this apply to local, FTP or both? If it applies to both, would it be possible to break it out so we could set different options for local and remote? Example:

Local Settings:
DELETE=y
DAYS=3

FTP Settings:
DELETE=y
DAYS=30
21 03 2011
#2.1 Ameir Abdeldayem (Reply)

It'd be great if I could do that, but I unfortunately can't think of a straightforward way to do it. I cheat a little bit here with the FTP deletions because I check for the "old" files on the local server, and blindly attempt to delete them from the FTP server. Those files might not even exist on the FTP server, but we try to delete them anyway.

In short, if you delete files off of the local server every 3 days, we'd only be able to delete them from the FTP server every 3 days. Does that make sense?
21 03 2011
#2.1.1 Rob Ludlow (Reply)

A bit over my head, but I think I understand. Maybe I should stick with your previous version since I really like having many more day backups on my remote server, but only keeping a few days locally.

Cheers!
24 03 2011
#3 Chris (Reply)

The script doesn't delete the mysqldump(.sql file) file after it gzip's it. Can you work this into the script?

Excellent script BTW!
24 03 2011
#3.1 Ameir Abdeldayem (Reply)

What operating system are you running on? I've tested this script on Debian and RedHat-based OSes, and the gzip included doesn't leave behind the uncompressed file. On one of my Ubuntu 10.04 servers, gzip -V gives version 1.3.12.
03 05 2011
#3.1.1 Thiyagarajan (Reply)

Dear Amier I have used your old and new version in our live servers. It's pretty good to take complete backup of mysql. But I've a problem only on FTP server. This is the error msg "invalid compressed data--crc error & invalid compressed data--length error" - while extracting the compressed .gz file. FYI: in local machine it gunzip properly. Please help us to rid of it.. Thank you..
22 05 2011
#3.1.1.1 Ameir Abdeldayem (Reply)

My guess is that the files are being transferred in ASCII mode and not binary, which is the default on my machines. Try this:

In the section that starts with "ftp -nv
25 06 2011
#4 Steve (Reply)

Hi Ameir,

Any chance you could make a tandem script, or modifications to this one, so that it can also make backups of files located on the server? I'm about to use this script to routinely backup my mysql database of my phpBB forums... however, those backups are worthless if I do not have a copy of the phpBB directory structure, with all of the included modifications to the phpBB source files!

Any help or guidance would be greatly appreciated!
24 07 2011
#4.1 Ameir Abdeldayem (Reply)

Hi Steve,

I wrote what you're looking for a while back and it still serves me well. Check out:
http://www.ameir.net/blog/archives/15-Folder-Backup-to-FTP-and-Email-Shell-Script-for-Cron-v2.html

Let me know how it works out for you.
01 08 2011
#5 Junaid (Reply)

Dear Ameir

Your script is great, i would like to backup some selected tables from one db, how could i do this?

Thanks
Junaid
29 10 2011
#5.1 Ameir Abdeldayem (Reply)

Junaid,

Please see this post and those surrounding it; it should resolve what you're looking for.

http://www.ameir.net/blog/archives/18-MySQL-Backup-to-FTP-and-Email-Shell-Script-for-Cron-v2.1.html#c1080
24 08 2011
#6 Rob (Reply)

Any chance of exporting it as csv?
29 10 2011
#6.1 Ameir Abdeldayem (Reply)

There's no native support, but you can do this:
http://christianriesen.com/2009/06/creating-a-mysql-dump-in-csv-format/

Just add those options to the line in the script that does the dump. Let me know how it works out.
13 11 2011
#7 Karl (Reply)

Thanks very much Ameir, this is just what I was looking for!
14 01 2012
#8 Donovan (Reply)

Great script, but I think I have found a small bug in 2.2......

The OLDDBS variable is only set if you have FTP=y. So if you have FTP=n and DELETE=y then the delete functionality will not work as OLDDBS is unset.
13 02 2012
#9 Ben (Reply)

Ameir,

I found your script on another site where a guy had made a few improvements to it. I've added it to Github because I never want to lose it - I use it a ton!! I still have your name on the project of course. Any chance you'd be willing to apply your latest updates to the copy of the script he modified?

Link: https://github.com/bkuhl/Smart-MySQL-Backup
13 02 2012
#9.1 Ameir Abdeldayem (Reply)

Ben,

Thanks for contacting me about this. I'm glad to hear that the script has been useful to you.

I'll devote some time this week to comparing my script to the one in Github and merge my changes wherever I can.

I'll ping you here once that is complete.

Add Comment


You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
BBCode format allowed

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA