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.

Re: Now is the time to automate mysqldump

[prog.livejournal.com]

Thanks. But we actually already back up the database (and everything else) by regularly uploading the entire hard drive to other computers!

Re: Now is the time to automate mysqldump

[taskboy3000.livejournal.com]

That's great, except when you need to restore the DB fast or only some of the tables. Use mysqldump for faster, more flexible restores. I've had to do this a fair amount.

Otherwise, you'll either need to copy the binary *ISM and index tables, which can be problematic when dealing with different architectures.

Having the SQL is really helpful, since you can always edit those commands to rebuild tables selectively.

Anyway, good luck.

Re: Now is the time to automate mysqldump

[daerr.livejournal.com]

We actually do a mysqldump every hour. Can't just backup the binary versions of the database without shutting it down, and who wants that? :)

My trick, which I haven't done with Volity yet, is to keep snapshots of the last 15 days of important databases, stored using MyISAM compressed tables. Doesn't take up too much space and gives live access to older data.

[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.