Automatic Back-Up? /MySQL

Posts: 4 · Posted: Fri Apr 13, 2007 8:09 pm

Anyone know of a utility that would automatically back-up the MySQL databases on my dedicated server? I've been doing it by hand with phpAdmin whenever I remember...

Posts: 112 · Posted: Fri Apr 13, 2007 11:23 pm

You could setup a cron job that runs the following every day:
#!/bin/sh

mysqldump -Q -u username -ppassword --all-databases > alldbbackup.sql

That'll dump all databases in one .sql file, so it's not the easiest to restore (it's also not compressed, although I'm pretty sure that's an option in mysqldump, or you could just gzip the alldbbackup.sql).

I wrote a quick and dirty script that does the job. It'll go through, dump all databases to their own .sql file, then tar and gzip them all up into one .tar.gz. So that this way you can easily extract the one .sql file to restore it.

Code:

#!/bin/sh

# MySQL Hostname
DBHOST='localhost'

# MySQL Username
DBUSER='root'

# MySQL Password
DBPASSWD='yourpass'

# Local Directory for Dump Files
LOCALDIR=/backups/sql
DUMPDIR=/backups/tmp
# user/group backups should be owned by
USERCHMOD='backups'

cd $LOCALDIR
SUFFIX=`date +%Y-%m-%d`
FILENAMETAR=$SUFFIX.sqlbackups.tar.gz

if [ ! -e $DUMPDIR]; then
mkdir $DUMPDIR
fi

DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`

for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAMESQL=$SUFFIX-mysql-$DATABASE.sql
mysqldump -u$DBUSER -h$DBHOST $DATABASE > $DUMPDIR/$FILENAMESQL
fi
done

cd $DUMPDIR
tar -czf $LOCALDIR/$FILENAMETAR *
rm -f $DUMPDIR/*
chmod 600 $LOCALDIR/$FILENAMETAR
chown -R $USERCHMOD.$USERCHMOD $LOCALDIR
exit 0

*notes*
$LOCALDIR is the directory the backups will be stored in (the final .tar.gz's)
$DUMPDIR is the temporary directory the .sql files will be in. When the script is done, this will be empty.
$USERCHMOD is the user that will own the backups when they've been completed. This user must exist on the system.

If it's a cPanel system, you can likely keep the password blank.
If it's a Plesk system, the root user should be changed to admin, and the password can be `cat /etc/psa/.psa.shadow`

I'm not saying this is the best or only way to do it. This isn't a script I wrote to be mass-distributed and used on all systems. It's literally just something I wrote for my personal system

Posts: 103 · Posted: Tue Apr 17, 2007 8:40 pm

Try MySQLYOG, the commercial version. It is inexpensive. You can setup schedules on your local PC or server and have the backups run as needed. You can also have a separate file per database table. We use this with HMS for a client who requires a nightly backup to their own server.

HTH

Posts: 2 · Posted: Tue Aug 25, 2009 4:13 pm

Few more pieces of information that I got from HMS support crew:

-- As a shared server user you can also have cron jobs.
-- Setting up cron jobs: http://www.webmasters-central.com/article-blog/tutorials/cron-tutorial-managing-cron-tab-or-cron-job-is-easy/

Posts: 310 · Posted: Thu Aug 27, 2009 6:19 pm

MySQL Administrator. I have it run remote and local backs every night at 10 PM. In my opinion its the best tool for scheduled backups.

Posts: 2 · Posted: Thu Nov 05, 2009 6:15 am

whitesites wrote:

MySQL Administrator. I have it run remote and local backs every night at 10 PM. In my opinion its the best tool for scheduled backups.

Thanks for the info!
Is this available for shared hosting plans? I don't see it in my Control Panel and don't know if HMS would let me install it.
Or do I need to have it installed on my own machine and have it connect to the DB? If so, where does the backup file get saved?

----
http://www.gracepok.com/

Posts: 310 · Posted: Thu Nov 05, 2009 6:29 am

YOu can download it at MySQL.com Its free.
you run it on your local machine, the backups will also be saved to your local machine. You don't need to install anything on the server.

Posts: 192 · Posted: Thu Nov 05, 2009 3:54 pm

I love the MySQL GUI Tools!

http://dev.mysql.com/downloads/

Here's an internal script we use for cron tasks:

Code:

#!/bin/bash
# MySQL Server Backup
###########################
# Number of days to keep backups
DAYS=7
# Credentials ::
BU_HOST="localhost"
BU_USER="username"
BU_PASS="P455w0rd"
# Where to put the backup
BU_PATH="/path/to/backups"

# Check for connectivity
TEST=`mysql -h "$BU_HOST" -u "$BU_USER" -p"$BU_PASS" -e "SHOW DATABASES"`

if [ ! $? = 0 ]; then
echo "Host does not appear to be responding."
echo "Try again later."
echo "`date -R`"
exit 1
else
for DB in `echo $TEST | cut -d"|" -f2` ; do
mysqldump -u $BU_USER -p$BU_PASS -h $BU_HOST --opt $DB | gzip > $BU_PATH/$DB-`date -I`.gz;
if [ $? -eq 0 ];
then
echo "Successfully backed up: $DB !";
fi
done
fi

# This bit finds files which have not changed in
# a week and deletes them.

find $BU_PATH -name *.gz -ctime +$DAYS -exec rm -f '{}' ';'

exit 0

You could save this as a text file, upload it to your server, and make it executable:

Code:

chmod +x filename

You can edit your crontab by logging into your web server using your customer account. Once logged in run:

Code:

crontab -e

Reference: http://linux.die.net/man/5/crontab