Digital Hijinx  
 PortalFront Page   RegisterRegister / Log in  Chat  SearchSearch 

PHPBB / MYSQL HOSTED / NON-HOSTED AUTOMATED BACKUP SCRIPTS
The time now is Fri Sep 03, 2010 8:38 pm
All times are GMT - 7 Hours
Portal Index -> Forum Index -> Computer Hijinx -> PHPBB / MYSQL HOSTED / NON-HOSTED AUTOMATED BACKUP SCRIPTS

View all images in this topic :: View previous topic :: View next topic  
Author Message
retorq
'The Architect'


Joined: 14 Nov 2003
Posts: 4456
Location: AZ
Post #4371Posted: Thu Jul 29, 2004 12:27 pm 
OK boys and girls, here some stuff I put together for another PHPBB site I admin. It's a basic MYSQL/PHPBB database backup script. It'll actually backup anything but since I'm running PHPBB . . . thought it would help some of you since the default PHPBB backup is a complete joke. These are all std Unix / Linux cron / bash script files. They will not work on a Windows although there is no reason the theories wouldn't apply.

This cron script checks the database every 15 minutes, if you have CPanel access you can use this:
Code:
# /etc/cron.d/hf_check: crontab for automatic forum checking

# Check mySQL database every 15 minutes
58,13,28,43 *   * * *     root /root/hf_check.sh


This cron script backups the database every 15 minutes, again, you can use this in CPanel:
Code:
# /etc/cron.d/hf_sql_backup: crontab for automatic forum backups

# Backup mySQL database every 15 minutes
00,15,30,45 *   * * *     root /root/hf_backup.sh


This is the script that the check job calls, CPanel users might find that you do not have access to the mysqldump / mysqlcheck commands, you can still get your database backed up to another server if that's the case (see below). I had modified another SQL backup script I found to suit my needs. If you follow thru the script you'll see that you can define multiple hosts and databases to be backed up to the same file. That's not what I was after, hence the heavy editing. You'll notice this script will actually EMAIL me and other admins if I so tell it, that can be both very handy and bad. Some admins will freak out at the slightest warning but it's better then under reacting in my book. The email portion of the script may not work depending on your setup:
Code:
#!/bin/bash

DBHOST=localhost
BACKUPDIR=/root/hf_sql_backups
DBNAMES=helifreakdata
DATE=`date +%Y-%m-%d_`
TIME=`date +%R`
LOGFILE=`date +%A`
CHECKFILE='checkfile.txt'

# 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"

PATH=/usr/local/bin:/usr/bin:/bin

# Run command before we begin
if [ "$PREBACKUP" ]
        then
        $PREBACKUP
fi


# Hostname for LOG information
if [ "$DBHOST" = "localhost" ]; then
        HOST=`hostname`
else
        HOST=$DBHOST
fi

echo Check Start `date`>>"$BACKUPDIR/$LOGFILE"
echo ======================================================================>>"$BACKUPDIR/$LOGFILE"
# Normal Check
 echo Checking of Databases \( $DBNAMES \)>>"$BACKUPDIR/$LOGFILE"
 echo>>"$BACKUPDIR/$LOGFILE"
 mysqlcheck --user=DBUSERNAME --password=DBPASSWORD --host=$DBHOST $DBNAMES > "$BACKUPDIR/$CHECKFILE"

ERRORS=`cat "$BACKUPDIR/$CHECKFILE" | grep -ic 'error'`
WARNINGS=`cat "$BACKUPDIR/$CHECKFILE" | grep -ic 'warn'`

if [ $ERRORS -ne "0" ]; then
 echo TOTAL ERRORS FOUND: >> "$BACKUPDIR/$LOGFILE"; cat "$BACKUPDIR/$CHECKFILE" | grep -c 'error' >> "$BACKUPDIR/$LOGFILE"
 mail -s "HELIFREAKDATA ERRORS FOUND" stephen@digitalhijinx.com, retorq@digitalhijinx.com, stephen@mass-pc.com, retorq2001@yahoo.com < "$BACKUPDIR/$CHECKFILE"
 else echo NO ERRORS FOUND >> "$BACKUPDIR/$LOGFILE";
fi

if [ $WARNINGS -ne "0" ]; then
 echo TOTAL WARNINGS FOUND: >> "$BACKUPDIR/$LOGFILE"; cat "$BACKUPDIR/$CHECKFILE" | grep -c 'warning'  >> "$BACKUPDIR/$LOGFILE"
 mail -s "HELIFREAKDATA WARNINGS FOUND" stephen@digitalhijinx.com, retorq@digitalhijinx.com, stephen@mass-pc.com, retorq2001@yahoo.c
om < "$BACKUPDIR/$CHECKFILE"
 else echo NO WARNINGS FOUND >> "$BACKUPDIR/$LOGFILE";
fi

rm -f "$BACKUPDIR/$CHECKFILE"

echo Check End `date`>>"$BACKUPDIR/$LOGFILE"
echo ======================================================================>>"$BACKUPDIR/$LOGFILE"

# Run command when we're done
if [ "$POSTBACKUP" ]
        then
        $POSTBACKUP
fi

exit 0


This is the script that the backup job calls, it's very similar in structure to the check script. The check script was actually a modified backup script, again with some of the edits that I didn't need. If you wanted to get really fancy / efficient you could combine the scripts and put the check stuff in the PREBACKUP area and put the PERL script into the POSTBACKUP section, when I originally set this up the backup had to be up and running ASAP. Then later on we had backed up a bad DB and that's what prompted the check portion of the script. NONE of the backup scripts I have seen actually check the DB before it backs up. I willl more than likely combine the 2 scripts into one at some point in time, I'll update this when that time comes:
Code:
#!/bin/bash

DBHOST=localhost
BACKUPDIR=/root/hf_sql_backups
DBNAMES=helifreakdata
DATE=`date +%Y-%m-%d_`
TIME=`date +%R`
LOGFILE=`date +%A`

# 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"

PATH=/usr/local/bin:/usr/bin:/bin
OPT="--opt"             # OPT string for use with mysqldump ( see man mysqldump )

# Run command before we begin
if [ "$PREBACKUP" ]
        then
        $PREBACKUP
fi


# Hostname for LOG information
if [ "$DBHOST" = "localhost" ]; then
        HOST=`hostname`
else
        HOST=$DBHOST
fi

echo Backup Start `date`>>"$BACKUPDIR/$LOGFILE"
echo ======================================================================>>"$BACKUPDIR/$LOGFILE"
        # Normal Backup
                echo Backup of Databases \( $DBNAMES \)>>"$BACKUPDIR/$LOGFILE"
                echo>>"$BACKUPDIR/$LOGFILE"
                        mysqldump --user=DBUSERNAME --password=DBPASSWORD --host=$DBHOST $OPT $DBNAMES > "$BACKUPDIR/$DATE$TIME.sql"
                        gzip -f "$BACKUPDIR/$DATE$TIME.sql"
                echo Backup Information for $BACKUPDIR/$DATE$TIME.sql.gz>>"$BACKUPDIR/$LOGFILE"
                        gzip -l "$BACKUPDIR/$DATE$TIME.sql.gz" >> "$BACKUPDIR/$LOGFILE"
                echo ---------------------------------------------------------------------->>"$BACKUPDIR/$LOGFILE"

echo Backup End Time `date`>>"$BACKUPDIR/$LOGFILE"
echo ---------------------------------------------------------------------->>"$BACKUPDIR/$LOGFILE"

echo Total disk space used for backup storage..>>"$BACKUPDIR/$LOGFILE"
echo Size - Location>>"$BACKUPDIR/$LOGFILE"
echo `du -hs $BACKUPDIR`>>"$BACKUPDIR/$LOGFILE"
echo>>"$BACKUPDIR/$LOGFILE"
echo ======================================================================>>"$BACKUPDIR/$LOGFILE"

# Run command when we're done
if [ "$POSTBACKUP" ]
        then
        $POSTBACKUP
fi

perl /root/hf_transfer.pl "$DATE$TIME.sql.gz"

exit 0


This is the transfer perl script called at the very end (perl /root/hf_transfer.pl "$DATE$TIME.sql.gz"), this was something I wrote a while back when I was new to PERL. Basically I pass the file name to the perl script and then FTP that off to a server, if for some reason you aren't able to use the above scripts, this one probably won't help you much either. As you follow thru you can see there is no deletion of the old backups. This for a few reasons, first, it's not all that easy to do, second, I would have to do it on the remote FTP site thru the perl script and that would add to the complexity, the end user this was setup for needed something small, fast and easily understandable. Also you can not grep files name into a delete command using NET::FTP thru perl, not that I have been able to do anyways. Third, if its's 100% automated then I RARELY check up on it. This way I have to log into the server every other day or so and manually delete the old logs (only 7 logs exists, they keep appending) and backups. Same with the FTP server, I manually login and delete. That means I have to watch and look at what I am doing. It basically keeps me actively involved in the management of the servers, that's a good thing. If you are not actively deleting these backup files they could fill up your drive and crash your server, or you can get hit with some REALLY big overage charges from your hosting company. Just FYI the databases I am working with are just under 10MB zipped and just over 25MB zipped, we keep 24 hours worth of backups, and there are 4 per hour. You do the math:
Code:
use Net::FTP;

$file = "$ARGV[0]";

#opening a connection to the ftp and sending the files that match the $file variable
  $ftp = Net::FTP->new("FTP.SERVER.COM", Debug =>1 );
 die "Could not connect: $!" unless $ftp;
 $ftp->login('FTP LOGIN NAME', 'FTP LOGIN PASSWORD');
 $ftp->binary();

 $ftp->cwd('helifreak_sql_backups');
 $ftp->put("/root/hf_sql_backups/$file") || warn "Problem sending backup file . .";
 $ftp->quit();

exit();


Of course you'll want to go thru these scripts and change up the login names, passwords, server names and DB names. If you admin your own server and have full control, these scripts should work fine as it. If you are on a hosted server, these may not work at all. As always, I'm more than happy to help set these up and get them running. Let me know if you have any questions, suggestions, comments, etc.
 
retorq
'The Architect'


Joined: 14 Nov 2003
Posts: 4456
Location: AZ
Post #4372Posted: Thu Jul 29, 2004 12:46 pm 
Here are some hosted / CPanel scripts. First is the cron script I use, I use it here as a matter of fact, the only requirement really here is a simple .my.cnf file that passes the password to the mysqldump command so that I don't have to do it in my script:

Code:
rm -f digital_forums.sql.gz;mysqldump --opt digital_forums > digital_forums.sql; gzip digital_forums.sql


This is the .my.cnf file structure, I use one of these on all the server I admin:

Code:
[client]
# The following password will be sent to all standard MySQL clients
password="DBPASSWORD"


Some builds of MYSQL will need the double quotes, I've seen it require single quotes too, you may need to try both to get it to work properly. Your hosting company should know which one you need. The above will work on a hosted server with little or no hassle. The scripts below will require you to configure MYSQL to allow outside connections. It's usually not a huge security risk but if you are that worried you can skip these and go the local route. If you do decide to configure MYSQL to allow outside connections, you can specify an IP of a machine that is allowed to connect, handy if you know the server and it will always be the same server backing you up. To open MYSQL up you open the "MYSQL Databases" icon and down the bottom you specify a user name and below that you specify the machine that is allowed to connect. % will allow any machine to connect . . provided they provide the correct username and password of course.

Under the Cron job icon in CPanel you enter the above into the "Command to run:" box, then you tell it when to run it, I run this every hour on the hour. There is no FTPing of the script and it only keeps one backup. Something like this would allow multiple backups:

Code:
rm -f /root/helifreak_sql.txt.gz.bak;cp /root/helifreak_sql.txt.gz /root/helifreak_sql.txt.gz.bak;rm -f /root/helifreak_sql.txt.gz;mysqldump helifreakdata >> /root/helifreak_sql.txt;gzip /root/helifreak_sql.txt;perl /root/transfer.pl


Notice this one calls the FTP script at the end, it's easily adaptable. If you wanted more than 2 adding more rm/cp command is all you need. You could actually use the mv command and simplify it even more.

There is still the option of having your forums backed up remotely, I do that here as well:

Code:
05,35 *   * * *     root rm -f /root/backups/dh_mysqldump.sql.gz;mysqldump --host=www.digitalhijinx.com --user=DBUSERNAME --password=DBASSWORD --opt DBNAME >> /root/backups/dh_mysqldump.sql;gzip /root/backups/dh_mysqldump.sql


Above is a cron script that gets run every 30 minutes on the 5s, it remotely backs up this server, I have a few other forums that get backed up by the same server in the same fashion and it works beautifully. If for some reason it fails I automatically get an email to the root account telling me why it failed. Nothing else needed.
 
retorq
'The Architect'


Joined: 14 Nov 2003
Posts: 4456
Location: AZ
Post #4523Posted: Fri Aug 13, 2004 8:42 pm 
Here are some screens shots on how and where this info gets put for CPanel users, first access your Control Panel, down toward the bottom you SHOULD have something that says "CRON JOBS", click that.
IMAGE ATTACHED

You will then see the following, select "Standard".
IMAGE ATTACHED

In the sandard settings you will see something similar to the following:
IMAGE ATTACHED
You will want to fill in an email address where any failure reports will goto. Select the days and times you want this script to run, as you can see I run mine every hour on the hour. The code that will be entered into the "Command to run" box is here:
Code:
rm -f digital_forums.sql.gz;mysqldump --opt digital_forums > digital_forums.sql; gzip digital_forums.sql

You will need to edit the above to reflect YOUR database name, mine in this case is "digital_forums", you will replace all 4 instances of that. with your own database name.

Almost there, one last step. You need some way of providing your password to the mysqldump command, the way I do this is thru .my.cnf file in your home folder. You can do this thru CPanel or thru an SSH connection. Using CPanel goto your file manager:
IMAGE ATTACHED
Click on "Create a new File, the name of the file is ".my.cnf", the period in front of my is VERY important, don't forget it. It will be a text type document.

IMAGE ATTACHED
Insert the following contents in newly created file:
Code:
 [client]
# The following password will be sent to all standard MySQL clients
password="DBPASSWORD"

Obviously you will want to replace your password with DBPASSWORD.
 
retorq
'The Architect'


Joined: 14 Nov 2003
Posts: 4456
Location: AZ
Post #6868Posted: Tue Jan 18, 2005 10:54 am 
Here's the latest incase anyone is looking for this kinda stuff still.

Code:
#!/bin/bash

DBHOST=www.digitalhijinx.com
BACKUPDIR=/root/backups
DBNAMES=digital_forums
DATE=`date +%Y-%m-%d`
OLDDATE=`date +%Y-%m-%d -d "7 days ago"`

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

# Command run after backups (uncomment to use)
POSTBACKUP="rm -f $BACKUPDIR/$DBHOST-$OLDDATE.sql.gz"

PATH=/usr/local/bin:/usr/bin:/bin
OPT=""             # OPT string for use with mysqldump ( see man mysqldump )

# Run command before we begin
if [ "$PREBACKUP" ]
then
$PREBACKUP
fi

# Hostname for LOG information
if [ "$DBHOST" = "localhost" ]; then
HOST=`hostname`
else
HOST=$DBHOST
fi

# Normal Backup
mysqldump --user=DBUSERNAME --password=DBPASSWORD --host=$DBHOST $OPT $DBNAMES > "$BACKUPDIR/$DBHOST-$DATE.sql"
gzip -f "$BACKUPDIR/$DBHOST-$DATE.sql"

# Run command when we're done
if [ "$POSTBACKUP" ]
then
$POSTBACKUP
fi
exit 0


Basically it's the same as the 'old' bakup script with the addition of two things: the OLDDATE and POSTBACKUP variables. It will remove zipped archives that are 7 days old. Also I removed all the log file stuff since I never go thru it. If you wanted you could define the DBUSERNAME and DBPASSWORD with the rest of the variables, it doesn't really matter to me since this script is on my secure home server and no one has read access except the root user.
 
Display posts from previous:   
Jump to:  



digitalhijinx.com topic RSS feed

PageRank Checker

[ powered by: phpbb ]
[ server load: 2 page(s) served in previous 5 minutes ]
[ page generation time: 0.12s - sql queries: 15 - gzip disabled - debug on ]