centos5.6下mysql数据库定时备份

// 单个数据库备份

[root@localhost /]# vi mysql-backup.sh
db_user="root"
db_passwd="123456"
db_host="192.168.1.110"
db_name="ecshop"
# the directory for story your backup file.
backup_dir="/backup"
# date format for backup file (dd-mm-yyyy)
time="$(date +"%Y-%m-%d-%H-%M-%S")"
# mysql, mysqldump and some other bin's path
MYSQL="/usr/local/mysql/bin/mysql"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
GZIP="/bin/gzip"

$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db_name | $GZIP -9 > "$backup_dir/$db_name"_"$time.gz"

// 整个数据库备份

（只保留7天以内的备份数据，不备份数据表中的information_schema 和 performance_schema）

[root@localhost /]# vi mysql-backup.sh
db_user="root"

db_passwd="123456"

db_host="localhost"

# the directory for story your backup file.

backup_dir="/backup/"

# date format for backup file (dd-mm-yyyy)

time="$(date +"%H-%M-%S-%m-%d-%y")"

# 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"

MV="/bin/mv"

GZIP="/bin/gzip"

# 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/"

echo "Start to Backup...";

# get all databases
# don't backup information_schema、performance_schema
all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
all_db=${all_db//information_schema/};
all_db=${all_db//performance_schema/};

for db in $all_db

do

$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"

done

# delete the oldest backup

test -d "$backup_dir/backup.7/" && $RM -rf "$backup_dir/backup.7"

# rotate backup directory

for int in 6 5 4 3 2 1 0

do

if(test -d "$backup_dir"/backup."$int")

then

next_int=`expr $int + 1`

$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"

fi

done
echo "BackUp Success!"
exit 0;

运行数据库自动备份脚本

# chmod 700 mysql-backup.sh                   // 只允许管理员运行此脚本

# ./mysql-backup.sh

# ls –la /mysql-backup.sh

 

让备份脚本每天自动执行

# crontab –e

# 00 03 * * * /mysql-backup.sh                  // 每天凌晨3点执行

 

 

数据库出现问题后的恢复操作：

可以通过mysql管理工具直接将备份压塑包里的数据进行导入即可