Auto-backup Your MySQL DBs Daily/weekly/monthly

miCRoSCoPiC^eaRthLinG

Nov 5 2005, 04:22 PM

Hi folks,
I just came across this cool shell script called automysqlbackup which takes on certain parameters such as your user/pass/host address/database names and performs automatic daily/weekly/monthly backups of your databases as configured.

The script is ready to run and need very little configuration - only in the top part:

QUOTE

#=====================================================================
#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================

# Username to access the MySQL server e.g. dbuser
USERNAME=dbuser

# Username to access the MySQL server e.g. password
PASSWORD=password

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="DB1 DB2 DB3"

# Backup directory location e.g /backups
BACKUPDIR="/backups"

# Mail setup
# What would you like to be mailed to you?
# - log : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
MAILCONTENT="files"

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE="4000"

# Email Address to send mail to? (user@domain.com)
MAILADDR="user@domain.com"

# ============================================================
# === ADVANCED OPTIONS ( Read the doc's below for details )===
#=============================================================

# List of DBBNAMES for Monthly Backups.
MDBNAMES="mysql $DBNAMES"

# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)
DBEXCLUDE=""

# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes

# Separate backup directory and file for each DB? (yes or no)
SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=6

# Choose Compression type. (gzip or bzip2)
COMP=gzip

# Command to run before backups (uncomment to use)
#PREBACKUP="/etc/mysql-backup-pre"

# Command run after backups (uncomment to use)
#POSTBACKUP="/etc/mysql-backup-post"

- where you set your mysql access params and database list. Next you simply put this script on your CrobTab and forget about it. It's a real cool tool to have and I believe since most of you can access your hosting accounts using the Java based SSH Client in your cPanel, setting this up wouldn't be any problem at all. Even other wise, you can set the parameters and upload it via FTP and then add it to your cron table using cPanel.

Have fun

and any questions about configuring it - most welcome.

Attached .txt file contains the script. Simply rename it to automysqlbackup.sh and chmod +0775
or,
alternatively it can be directly obtained from SourceForge.Net @ http://sourceforge.net/projects/automysqlbackup/

Click to view attachment

Comment/Reply (w/o sign-up)

warbird

Nov 5 2005, 05:41 PM

Wow, very cool M^e, I don't work much with MySql yet, but I'm planning to use it for my next. (can't tell you what site, top secret

) Anyway, this is gonna be useful for me. Thanks for the code. One tiny thing. Could you tell us the source please? I wanna look at it, maybe it got some more interesting scripts.

-=jeroen=-

Comment/Reply (w/o sign-up)

miCRoSCoPiC^eaRthLinG

Nov 5 2005, 05:56 PM

Check out that SourceForge link. That's where I found it while browsing SourceForge. This guy wrote just one script I think, although you might find others in similar categories. Just browse the sourceforge archives. I'll list if I come across any more.

Comment/Reply (w/o sign-up)

yordan

Nov 6 2005, 05:31 PM

Does this script work at astahost too ? Or do you need to have console access in order to fire it ?

Comment/Reply (w/o sign-up)

ruben

Nov 6 2005, 09:50 PM

Err read the last part of m^es post again. Yes it is compatible with Astahost, you have to set it up as a cronjob, he explained how.

Sorry for double posting but I was having some issues configuring this and I need help with it.
1. Is daily backup considered resource exploitation? What's the limit?
2. Which path do I have to specify in the CronTabs in order to let it find the file to be executed? I tried /home/ruben/etc/cron.daily and etc/cron.daily, neither worked, I get Emails like this

QUOTE

#
#: bad interpreter: No such file or directory

to my astahost mail account.
3. Would it be easier to set it up using SSH, now that I have it?
If it's only a path issue, probably not, but there is also the writing permission thing, etc.
4. What does the script do when the database files get to big for email? Split em or not send them at all?
Since I never got it to work, I was wondering, what I'll get.

Thanks!

Comment/Reply (w/o sign-up)

saint-michael

Dec 22 2005, 11:18 PM

interesting find you found m^e this would be a good script for people running forums.

but on ruben questions 4th one in partical you can set up the size of the email that you think will best fit with what you have you Mysql dbs doing.

CODE

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE="4000"

QUOTE

2. Which path do I have to specify in the CronTabs in order to let it find the file to be executed? I tried /home/ruben/etc/cron.daily and etc/cron.daily, neither worked, I get Emails like this

I believe the script makea s complete backups the dbs that you want to list

CODE

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="DB1 DB2 DB3"

the question i have are you loading hte script through phpmyadmin on are you putting it in your file manager to be run?

Comment/Reply (w/o sign-up)

ruben

Dec 24 2005, 10:27 AM

Thanks for trying to answer my questions, but you misunderstood me in every aspect.
I know that I can set a max. attachment size, but what will the script do as soon as the database exceeds the limit? Stop sending emails or start splitting them?

I know, that the script is supposed to make database backups. The question asked was: Which path do I have to specify in the crontab in order for it to find the cronjob file?
If I have my cronjob named "cron.daily" in the folder home/ruben/etc/, how do I have to specify the path in the crontab? I've tried various (home/ruben/etc/cron.daily, /etc/cron.daily, etc/cron.daily), but neither worked (once there was a permission problem first, but then it didn't find it again).

To answer your question:
You specify the variables in the script, then you save it in some not-publicly accessable folder on your website like /home/saint-michael/etc/
(the file name/ending is not important). Then you specify the file to be called in the crontab. And THEN you get to all my problems.

Comment/Reply (w/o sign-up)

Shadow X

Dec 25 2005, 04:08 PM

Very useful...thanks so much for that script miCRoSCoPiC^eaRthLinG. I always forget to backup my MySQL databases, and now that I have this code...I don't have to!

Comment/Reply (w/o sign-up)

soccerdvl18

Dec 26 2005, 12:24 AM

this stuff is very useful for me right now, but im having a lil trople finding things right now but that doesnt matter. thanx a lot of i was lookin how to bak up mySQL database