Cron job to backup Kayako owned DB?

[christinasc]

Anyone here have a suggested method to backup the kayako db on a hosted owned version (other than the option in the admin panel where you click on 'Generate backup')

I would like to make it a cron job if possible, however i don't have root access to the server on which the DB resides. I do believe there is mysql access. Does anyone have anything already scripted for this ?

Thanks much in advance.

[greengiant]

If there is remote mysql access you can use a copy of the mysqldump command on your backup machine via cron to build the backup.

Command would look like
mysqldump -c -p PASSWORD -u USERNAME -h HOSTNAME DBNAME > /path/to/backup/file.sql

[christinasc]

Awesome! Thanks - i'll try it.

[bear]

If you're allowed to run a shell script on your server, here's a dandy:
(Edit to fit your site details, and make sure paths to sh and mysqldump and all that are correct for your server)

#!/bin/sh

# This script will backup one or more mySQL databases
# and then optionally email them and/or FTP them

# This script will create a different backup file for each database by day of the week
# i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1)
# This is a trick so that you never have more than 7 days worth of backups on your FTP server.
# as the weeks rotate, the files from the same day of the prev week are overwritten.
#/bin/sh /home/user/directory/scriptname.sh > /dev/null
############################################################
#===> site-specific variables - customize for your site

# List all of the MySQL databases that you want to backup in here, 
# each seperated by a space
# If not run by root, only one db per script instance
databases="mydbname"

# Directory where you want the backup files to be placed
backupdir=/home/mydomain/backups

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=myuser --password=mypasswd"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip
uuencode=/usr/bin/uuencode
mail=/bin/mail

# Send Backup?  Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="mySQL Backup"
mailto="me@mydomain.com"

#===> site-specific variables for FTP
ftpbackup="y"
ftpserver="myftpserver.com"
ftpuser="myftpuser"
ftppasswd="myftppasswd"
# If you are keeping the backups in a subdir to your FTP root
ftpdir="forums"

#===> END site-specific variables - customize for your site
############################################################

# Get the Day of the Week (0-6)
# This allows to save one backup for each day of the week
# Just alter the date command if you want to use a timestamp
DOW=`date +%w`

# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ] 
then
   echo "Not a directory: ${backupdir}"
   exit 1
fi

# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
   $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${DOW}-${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
   rm -f ${backupdir}/${DOW}-${database}.sql.gz
   $gzip ${backupdir}/${DOW}-${database}.sql
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
   for database in $databases
   do
      $uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
      $mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu
   done
fi

# FTP it to the off-site server
echo "FTP file to $ftpserver FTP server"
if [ $ftpbackup = "y" ]
then
   for database in $databases
   do
      echo "==> ${backupdir}/${DOW}-${database}.sql.gz"
ftp -n $ftpserver <<EOF
user $ftpuser $ftppasswd 
bin
prompt
cd $ftpdir
lcd ${backupdir}
put ${DOW}-${database}.sql.gz
quit
EOF
   done
fi

# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit

[christinasc]

Wow! you really rock, bear! thanks a million.

[chimborazo]

HI, I really like that script, but I have no clou how this could be implemented on a Windows Server? Any ideas?

[Jamie Edwards]

Hi chimborazo,

You might like to do a Google search for "mysql backup php script" - there are several available on the web and involve uploading one PHP script and setting a system cron job ("scheduled task" in Windows) to execute this script every day, week and so on.

Documentation on how to set up a scheduled task in Windows Server 2003 is found in the manual: User manuals