Saved

[prog]

I wish there was a nearby shrine to MySQL I could go visit. I'd like to go there and burn some incense or something.

In the middle of some pretty hairy DB work in order to make the revenue reports work, and I swear to you, all my guesses as to syntax for bizarro table relations that looked like they might work actually did work.

If this were Oracle, this would have taken me a week to figure out.

Comments

Now is the time to automate mysqldump

[taskboy3000.livejournal.com]

Jmac,

Now is the time to think about consistently backing up your database. The good news is this is pretty easy. Here's a redacted version of what I use in a number of places:

#!/bin/sh
# backup DB selectively

if [ "$1" == "-v" ];
then
  verbose=1
fi

db_user=p0w3rus3r
db_pass=s3cr3t
date=`date +"%Y%m%d"` # YYYYMMDD

dest=/lots/of/room/db_backups/
if [ ! -d $dest ];
then
    if [ "$verbose" ];
    then
       echo "**mkdir $dest"
    fi
    mkdir $dest
fi

# remove old backups
if [ "$verbose" ];
then
   echo "**Removing files older than two weeks in $dest";
fi
find $dest -name "*sql.gz" -ctime +7 -exec "rm" "-f" "{}" ";"


for db in "my" "favorite" "DBs";
do
  if [ "$verbose" ];
  then
    echo "**Backing up $db";
  fi

  mysqldump -u$db_user --add-drop-table $db > $dest/$db-$date.sql

  flag="";
  if [ "$verbose" ];
  then
      flag="-v";
      echo "**gzipping $db-$date.sql";
  fi

  gzip $flag $dest/$db-$date.sql
  rm -f $dest/$db-$date.sql
done;

This would be a welcomed addition to a crontab. You may want to adjust the prune period.

Rock, rock on.

[ahkond.livejournal.com]

yay table relationships (I just jump on opportunities to use this icon)

I do a lot of Microsoft SQL Server stuff on a daily basis, including supporting a variety of financial stuff, so I'm happy to try to help if you get stuck.