MySQL备份脚本

#!/bin/bash

# db_user is mysql username

# db_passwd is mysql password

# db_host is mysql host

# -----------------------------

db_user="ari"

db_passwd="yuiopkjhr3bfcfarbhbjiu@"

db_host="localhost"

# the directory for story your backup file.

 backup_dir="/home/mybackup"

# date format for backup file (年-月-日_时:分:秒)

 time=`date +"(%Y-%m-%d)"`

# mysql, mysqldump and some other bin's path

MYSQL="/usr/local/mysql/bin/mysql"

MYSQLDUMP="/usr/local/mysql/bin/mysqldump"

MKDIR="/bin/mkdir"

RM="/bin/rm"

CP="/bin/cp"

GZIP="/bin/gzip"

KEEP_DAYS=30

# check the directory for store backup is writeable

 test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0

# the directory for story the newest backup

 test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"

# get all databases

all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"

for db in $all_db

    do

   $MYSQLDUMP -u $db_user -h $db_host -p$db_passwd  --single-transaction $db  >   "$backup_dir/backup.0/$time.$db.sql"

          cd "$backup_dir/backup.0/"

          tar -zcf $time.$db.tgz $time.$db.sql

          rm -f $time.$db.sql

    done

 find $backup_dir/backup.0/ -mtime +$KEEP_DAYS -exec rm {}\;

 exit 0;