Shell MySQL Backups - What's your solution?

I'm looking for a great solution to backup my MySQL databases - all of them. I'd like each one in at least it's own file. I need FTP ability later, but not right now. Right now I just need it to save on the same server that it's running on.

What do you use?

update

I suppose I should be clearer in what I'm asking for... I'm looking for a bash script that will utilize mysqldump to creat individual backup files for each database or an individual file for a table if the table is large enough.

From serverfault Webnet

• I use a simple Bash script that does a mysqldump and then a rsync (easily use ftp) to a another offiste machine.

  very simple yet it works!

  no need to take the database down either.

  http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

  Luc

  From Luma

• You can use this script:

  #!/bin/bash
  BACKUP_DIR="/opt/backup"
  MYSQL_USER=your_user
  MYSQL_PASS=your_pass
  MYSQL="$(which mysql)"
  MYSQLDUMP="$(which mysqldump)"
  GZIP="$(which gzip)"
  
  DB_LIST="$($MYSQL -u $MYSQL_USER -p$MYSQL_PASS -Bse 'show databases')"
  for db in $DB_LIST;
  do
      BACKUP_SUBDIR="$BACKUP_DIR/mysql_`date +%Y-%m-%d`"
      BACKUP_FILE="$BACKUP_SUBDIR/$db.gz"
      if [ ! -d $BACKUP_SUBDIR ]; then
          mkdir -p $BACKUP_SUBDIR
      fi
      if [ -f $BACKUP_FILE ]; then
          unlink $BACKUP_FILE
      fi
      $MYSQLDUMP -u $MYSQL_USER -p$MYSQL_PASS $db | $GZIP -9 > $BACKUP_FILE
  done
  

  Seishun : It will create a gzipped file for each database on your server in the backup directory. It's the script I am using on my server.

  Webnet : What permissions does /opt/backup/ need to have? Also, what settings do you recommend for Mysqldump and gzip? What should MYSQL be?

  Seishun : Definitely, you'll want the directory to be writable by the user that will set up the cronjob for backup. MYSQL="$(which mysql)" will contain the full path to the mysql executable on your system, through the use of 'which' command.
  From Seishun

• I've been using this AutoMySQLBackup script for my mysqldumps. Does daily, weekly, monthly backups and rotations. Quite handy.

  Joe : I use AutoMySQLBackup as well, from a remote server. That way, even if the MySQL server I'm backing up dies, I still have the data.
  From Lon

• This daily backup will create a backup of your db and it will create 4 day backups and rotate them. You can then backup that folder to a remote site.

  #!/bin/bash -x
  #
  # Marco Maldonado MySQL Backup
  # Version 1.0 September 9 2008
  # comments marco@penguincares.no-ip.org
  
  MYSQL=`which mysql`
  MYSQLDUMP=`which mysqdump`
  BACKUPS=/opt/backups
  dbs=`$MYSQL -u root -pYOURPASSWORD -Bse 'show databases'`
  
  for db in $dbs
  do
  rm -rf $BACKUPS/$db.3
  mv $BACKUPS/$db.2 $BACKUPS/$db.3
  mv $BACKUPS/$db.1 $BACKUPS/$db.2
  mv $BACKUPS/$db.0 $BACKUPS/$db.1
  #mkdir $BACKUPS/$db.0
  #$HOTCOPY $userpassword $db $BACKUPS/$db.0
  mysqldump -u root -pIndr@sN3t $db  > $BACKUPS/$db.0
  done
  
  

  Make sure you have a folder inside /opt and you call it backups

  From Marco