MySQL Backup

mysql-backup.sh

  #!/bin/sh
  #
  # This script automates a call to mysqldump
  # and sends the output to a file in a backup
  # directory. The script is set up to keep
  # seven days of history.
  #
  # Before you can run this script you must
  # set up a MySQL user that can perform the
  # backup. This user must have permission to
  # SELECT and LOCK TABLES. The user should not
  # be permitted to access MySQL in any way other
  # than through the local socket. Here's how the
  # user should be created:
  #
  # GRANT SELECT,LOCK TABLES ON *.* TO 'SomeUser'@'localhost' IDENTIFIED BY 'SomePassword'
  # FLUSH PRIVILEGES;
  #
  # This script should be owned by root and only
  # root should be able to read, write, and
  # execute it. (i.e., chmod 700)
  #
  
  BACKUPUSR=backup
  BACKUPPWD="password"
  BACKUPDIR=/opt/db/backup
  MYSQLDUMP=/usr/bin/mysqldumpdb
  GZIP=/bin/gzip
  DAYOFWEEK=`/bin/date +%u`
  
  # dump out all the databases
  ${MYSQLDUMP} -u ${BACKUPUSR} --password=${BACKUPPWD} --opt \\
      --all-databases > ${BACKUPDIR}/mysql_databases.${DAYOFWEEK}.sql
  
  # compress the output file
  ${GZIP} -f ${BACKUPDIR}/mysql_databases.${DAYOFWEEK}.sql