Create backup problem 2

[chigley]

Hi,

I am tired, have had the most awful day with hardware dying on me at every turn (IPOD touch wireless sucks btw) (I know the worlds smallest Violin is playing) but I really have had a very very bad day. I wanted to set a backup routine on my MySQL database (for CRM data) and I go through the screens until I get to the schedule backup button, when it tells me I need to create profile. Sadly none of the screens mention profiles, A N Y W H E R E, and some extensive googling yields sfa.

Can anyone give me a hint. How do I create the profile it wants to use for the backup schedule?

OS = Ubuntu
MySQL 5.1

Very down at heel

Thanks

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[TonyGroves]

If it was me, I wouldn't bother with any GUI/profiles/whatever. I'd write a shell script to do the backup, and add an entry to /etc/crontab to run it regularly.

Cheer up, at least it's not Windows!

 

[Kozusnik]

Here's the backup script I use. It works great. It stores the hot copy locally (for me) in /

http://www/db/YYYYMMDD

with a timestamp on the filename in case you run it multiple times per day. Only change the section after "# Backup databases" to suit your needs. You can move the gzipped tarballs off of the server after they're created. Ideally, this would only run on your replication server, if you have one.

Here you go...

Edit the crontab
$ crontab -e

and add the line
22 * * * * /path/to/backup_db 30

That will run the job at 10:00 pm and delete the backup data from 30 days ago. Don't forget to make the shell script executable.

$ chmod 700 backup_db

#!/bin/sh

# Input: backup_db n (e.g. backup_db 31)

# Check that the input is valid.
# There should be exactly 1 argument.
if [ $# -ne 1 ]; then
  echo Error: $0 invalid usage.
  echo Usage: $0 n
  exit 1
fi

# The argument should be an integer.
n=`expr $1 + 0 2> /dev/null`
if [ $? -ne 0 ]; then
  qnbad=0
elif [ $n -lt 0 ]; then
  qnbad=0
else
  qnbad=1
fi
if [ $qnbad -eq 0 ]; then
  echo Error: n must be a positive integer.
  echo Usage: $0 n
  exit 1
fi

# Set the current month day and year.
month=`date +%m`
day=`date +%d`
year=`date +%Y`
ymonth=`date +%m`
yday=`date +%d`
yyear=`date +%Y`
hour=`date +%H`
min=`date +%M`

# Add 0 to month. This is a
# trick to make month an unpadded integer.
month=`expr $month + 0`
ymonth=`expr $ymonth + 0`
# Subtrace n from the current day.
yday=`expr $yday - 0`
day=`expr $day - $n`

# While the day is less than or equal to
# 0, deincrement the month.
while [ $day -le 0 ]
do
  month=`expr $month - 1`

  # If month is 0 then it is Dec of last year.
  if [ $month -eq 0 ]; then
    year=`expr $year - 1`
    month=12
  fi

  # Add the number of days appropriate to the
  # month.
  case $month in
    1|3|5|7|8|10|12) day=`expr $day + 31`;;
    4|6|9|11) day=`expr $day + 30`;;
    2)
      if [ `expr $year % 4` -eq 0 ]; then
        if [ `expr $year % 400` -eq 0 ]; then
          day=`expr $day + 29`
        elif [ `expr $year % 100` -eq 0 ]; then
          day=`expr $day + 28`
        else
          day=`expr $day + 29`
        fi
      else
        day=`expr $day + 28`
      fi
    ;;
  esac
done

# While the day is less than or equal to
# 0, deincrement the month.
while [ $yday -le 0 ]
do
  ymonth=`expr $ymonth`

  # If ymonth is 0 then it is Dec of last year.
  if [ $ymonth -eq 0 ]; then
    yyear=`expr $yyear`
    ymonth=12
  fi

  # Add the number of days appropriate to the
  # month.
  case $ymonth in
    1|3|5|7|8|10|12) yday=`expr $yday + 31`;;
    4|6|9|11) yday=`expr $yday + 30`;;
    2)
      if [ `expr $yyear % 4` -eq 0 ]; then
        if [ `expr $yyear % 400` -eq 0 ]; then
          yday=`expr $yday + 29`
        elif [ `expr $yyear % 100` -eq 0 ]; then
          yday=`expr $yday + 28`
        else
          yday=`expr $yday + 29`
        fi
      else
        yday=`expr $yday + 28`
      fi
    ;;
  esac
done
if [ $day -le 9 ] ; then
  day="0"$day
fi
if [ $month -le 9 ] ; then
  month="0"$month
fi
if [ $yday -le 9 ] ; then
  yday="0"$yday
fi
if [ $ymonth -le 9 ] ; then
  ymonth="0"$ymonth
fi
# Create a directory for today CCYYMMDD
mkdir /[URL unfurl="true"]www/db/$yyear$ymonth$yday[/URL]

# Backup databases
mysqlhotcopy --user=username --password=password database1 /[URL unfurl="true"]www/db/temp[/URL]
tar -cf /[URL unfurl="true"]www/db/$yyear$ymonth$yday/db1$hour$min.tar[/URL] /[URL unfurl="true"]www/db/temp[/URL]
gzip /[URL unfurl="true"]www/db/$yyear$ymonth$yday/db1$hour$min.tar[/URL]
rm -Rf /[URL unfurl="true"]www/db/temp/*[/URL]

mysqlhotcopy --user=username --password=password database2 /[URL unfurl="true"]www/db/temp[/URL]
tar -cf /[URL unfurl="true"]www/db/$yyear$ymonth$yday/db2$hour$min.tar[/URL] /[URL unfurl="true"]www/db/temp[/URL]
gzip /[URL unfurl="true"]www/db/$yyear$ymonth$yday/db2$hour$min.tar[/URL]
rm -Rf /[URL unfurl="true"]www/db/temp/*[/URL]

# Clean up directory from n days ago
rm -Rf /[URL unfurl="true"]www/db/$year$month$day/*.tar[/URL]
rm -Rf /[URL unfurl="true"]www/db/$year$month$day/*.tar.gz[/URL]
rmdir /[URL unfurl="true"]www/db/$year$month$day[/URL]

exit 0

Mark

 

[chigley]

Thanks for your help guys. I think you got it sorted

Mark, have a star!

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[chigley]

I tried this script and it fails at the mysqlhotcopy line with

Invalid db.table name 'sugarcrm.sugarcrm`.`accounts' at /usr/bin/mysqlhotcopy line 859.

The table does exist though. It is the first table in the list so I guess it then aborts.

Any ideas?


Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[chigley]

Apparently mysqlhotcopy is frowned on, and the preferred method is

mysqldump --user=foo--password=bar--tab=/home/charlie/sugarcrm/backup/tmp --opt sugarcrm

Except now I get

mysqldump: Got error: 1: Can't create/write to file '/home/charlie/sugarcrm/backup/tmp/accounts.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

It creates one file in the tmp directory

accounts.sql

and then it must fail and abort.

mysqlhotcopy apparently has a bug in the perl script that causes it to double up on databasename.databasename when referencing tables - so I had high hopes for mysqldump - but alas its just as broke.

What am I doing wrong?

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[Hondy]

Great script, works a treat and saved me loads of time, have a star and a boost up to the top for other people to find

 

[Kozusnik]

I should mention mysqlhotcopy only works on Linux. I don't think it works at all for Windows. Not that this is your problem.

Mysqlhotcopy copies the binary database files. The only way to restore is to shut down mysql move/copy the backup files to the mysql database directory (/var/lib/mysql/databasename), chown to mysql, and restart mysql.

Mysqldump creates a sql text file that can be imported while the server is running. It takes longer and your my.cnf (or php.ini if using something like phpmyadmin) has to be able to handle the file size.

Make sure the directories exist and maybe try
"mysqldump --opt --user=foo --password=bar sugarcrm > accounts.sql" from the command line. There is another option for --tables, but I've never used it.

Mark