mysql backup issue - zero size file and automatic backup

	Forum Index > glFusion > glFusion Support
mysql backup issue - zero size file and automatic backup

Printable Version

By: mlapl1 (offline) on January 30 2010 10:14 AM (Read 467 times)

mlapl1

I have two questions:

(a) When I create a backup file, it is created in the correct directory but is of zero size.

I am running my site under linux.
The path for mysqldump is defined properly
I do NOT have any mysqldump options specified - I removed the -Q option that was present by default. I assume that the appropriate parameters are passed to mysqldump by glfusion.

(b) Is there any way to automate the backup so that I do not have to initiate it manually?

Thank you for your help.

Andrew

Active Member

Group Comfort
Level:: +1

Status: offline

Registered: 10/06/09
Posts: 175

By: lgarner (offline) on January 30 2010 11:59 AM

lgarner

I am running my site under linux.

Does this mean your server? If so, then it should work. If it's hosted, then mysqldump might not work (GoDaddy is one that doesn't allow it).

For automatic backup, you can just add a line to your cronab, or something similar. This is assuming that a) you're hosting yourself, or b) your host allows mysqldump and offers scheduled tasks. If not, they might allow remote MySQL access (HostGator does, for example). You can add your home public IP to your host's configuration and run a scheduled task from your home system. An added benefit is that you now have an offsite backup.

You'll need to provide the credentials, unless the user running the cron job can access the db with no password. I put the credentials in ~/.my.cnf, like so:

PHP Formatted Code
[mysqldump]

password=mysql_password_here

For my use, I run a daily job that backs up all databases and keeps a week's worth.

PHP Formatted Code
#!/bin/sh

DAYSTOKEEP=7

DATE=`date +%s`

BACKDIR=/media/disk/mysql

OLDDIR=`expr $DATE - \( $DAYSTOKEEP \* 86400 \)`

CREDENTIALS="--defaults-file=/root/.my.cnf"

if [ ! -d $BACKDIR/$DATE ]; then

    mkdir -p $BACKDIR/$DATE

fi

for DIR in $BACKDIR/*

do

    if [ -d $DIR ]; then

      DIRNEW=`echo $DIR | awk -F / '{print $5}'`

        if [ $DIRNEW -lt  $OLDDIR ]; then

           rm -fr $DIR

        fi

    fi

done

for database in `mysql $CREDENTIALS -B -e "show databases" | grep -v Database`

do

        if [ -d $BACKDIR/$DATE ]; then

                mysqldump $CREDENTIALS --databases -q $database |gzip > $BACKDIR/$DATE/$database.sql.gz

        else

            echo "Could not backup the `hostname` database" | mail -s `hostname` myself@my_email.com

            exit 1

        fi

done

If all else fails, you might just have to do it yourself periodically via phpMyAdmin or other tool.

Admin

Group Comfort
Level:: +35

Status: offline

Registered: 06/10/08
Posts: 882

By: mlapl1 (offline) on January 30 2010 13:28 PM

mlapl1

Hi and thank you so much for the advice.

I run my own dedicated server with root access - so I have all necessary permissions - but the backup still does not work. I can backup the database separately from glfusion but I thought it might be simpler to do it through glfusion - especially as I thought that glfusion might have had an automatic backup available like some systems I use (e.g. e107 which emails you the backup file).

I guess I can use a shell script, but it would be nice to have it all there - and every day I am discovering something new about glfusion - that's good as it has great features.

Cheers
Andrew

Active Member

Group Comfort
Level:: +1

Status: offline

Registered: 10/06/09
Posts: 175