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
I have moved the development of this to the GitHub repository below:
https://github.com/ameir/mysqlbackup
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.
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.
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
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.
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?
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!
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!
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.
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..
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 <
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!
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.
Dear Ameir
Your script is great, i would like to backup some selected tables from one db, how could i do this?
Thanks
Junaid
Any chance of exporting it as csv?
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.
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
Thanks very much Ameir, this is just what I was looking for!
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.
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
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.
Hello! I’ve been using the 2.1 version of this script for a few years, and have been very happy with it. I just recently updated to the 2.3 version – thank you for all your hard work!
The new script does a count of bytes transferred, but I don’t see a setting / option to turn this on or off… it produces a rather verbose email on some of my larger databases. Is there a setting to turn it off?
Hi Geoffrey,
I should probably remove this by default, as it’s really only helpful when running the script interactively.
To turn off the byte count, you simply have to remove the line that says ‘tick’.
Let me know if you run into any issues.
-Ameir
I’m seeing the same issue – I do not want it to delete files off my FTP server, but it’s not deleting them from the local server, either.
I’m running the 2.3 version at the moment.
Thank you for all your work on this – it’s been a lifesaver!
Works fine – thank you!
Or… never mind me, it’s working fine. For some reason, it seemed to take an extra day to kick in and clean up after itself.
Question – is it possible to set the script to clean up the local files, but not the remote ones? It’s good that it’s freeing up space on the server, but with 9 TB on my remote FTP server, I don’t need to delete as often, if at all. (Maybe different times for local and remote?)
I’m glad to hear it’s working out well for you.
Not deleting files on the remote FTP server is a piece of cake; simply remove the line “$REMOVE”. You can also remove the line starting with “REMOVE” (without the ‘$’), but it doesn’t help or hurt to leave it.
I hope that helps!
Been using the script for while now, thanks!
However, I started using the email function, but it spits out an error:
[code]Error sending message, child exited 75 (Deferred.).
/backup/mysqlbackup-2.3.sh: line 111: 13033 Done echo "$BODY"
13034 Segmentation fault | mutt -s "$SUBJECT" $EMAILS $ATTACH
ERROR: Your backup could not be emailed to you! [/code]
I had a similar problem in the past. It turned out to be that my backups were larger than Postfix allowed for a message.
An easy way to check is to telnet to your server on port 25 and type ‘ehlo localhost’. You should get a line with something like ’250-SIZE 10240000′.
If the size given is smaller than your combined backups, you’ll need to change your MTA’s setting; just make sure the recipient address can handle the filesize too.
Hi Ameir,
We have been using the script for sometime and works great for us. Recently I am having some trouble in its configuration due to change in our servers. My questions is can I use IP instead of host-name at
“# your MySQL server’s name
SERVER=xyz.com”
and If I am using IP then should I also provide this IP at HOST of Mysql server location.
#———————-MySQL Settings——————–#
# your MySQL server’s location (IP address is best)
HOST=localhost
Thanks
One of the best backup script I did ever use.
Helped me so much, thank you Amier!
Hi Shuja,
The SERVER variable is just for naming purposes (filename). The actual HOST variable needs to be the IP or hostname where your MySQL server is. I hope that helps!
Dear Ameir,
first of all thank you for this script, it’s really great!
Maybe it’s just a mistake I don’t see but I face an unpleasant issue: this script works perfectly when launched via ssh but doesn’t work when in crontab.
System is Ubuntu LTS server 64 bit edition; it seems that in “interactive” mode database gets backed up, while in “cron” mode it doesn’t, but I get no specific error or warning.
Thank you
D.
What are the minimum user permission require for this script? I have looked online and found various permissions necessary. I want to have the least amount as possible for the mysql dumps.
I have an EC2 instances connected to an RDS MySQL instance. I run a cron from a local server to run this mysql dump and it has started spiking the CPU and taking down the EC2 server that is connected to the MySQL RDS instance. I get a high volume of DB connections on the RDS but not sure how that is causing a the EC2 instance that is only connected to the RDS MySQL database to spike the CPU and take it down.
Thanks for any ideas related to permissions.
Hello, firstly, thanks for the script.It’s really cool.The script send email reports with backup.sql.gz file.I don’t want this.I just want to email report not sql.gz file.How can I do this, thanks.
This is a great question. I found the following that should be of use:
http://openconcept.ca/mysql_permissions_for_backup	
It would also be helpful to monitor processes as the backup is being taken to get some insight on what processes are consuming the most resources. For example, gzipping a large database dump can be quite intensive, and if you don’t have enough CPUs on your server, it can grind to a halt.
I’m glad the script is working out well for you.
To not send the dumps as an attachment, you can simply put a # in front of the line that starts with “ATTACH=”. You will then get the same email, but without the attachments.
Hope that helps!
I haven’t experienced this issue before; are you sure the script is being executed? It will help if you put “MAILTO=email@addre.ss” in your crontab so you can see what’s going on.
I’d recommend to use FULL paths in your crontab just to be safe, and ensure that the script is executable. You could also just try, e.g., “/bin/bash /full/path/mysqlbackup.sh”.
Let me know if you have any updates.
Thanks, it’s okay