自动备份数据库

#!/bin/sh

#定义数据库连接字段

db_user=username

db_passwd=passwd

#定义备份目录，以及备份命令

backup_dir=/opt/bak/local/mysql

date=`date +"%Y%m%d"`

db_host=127.0.0.1

#检查备份目录是否存在，若没存在则创建

test ! -d "$backup_dir/mysql_$date" && mkdir "$backup_dir/mysql_$date/"

#列出所有数据库

#all_db="$(mysql -u $db_user  -p$db_passwd -Bse 'show databases')"

all_db="$(mysql -u $db_user -p$db_passwd -Bse 'show databases')"

echo $date >> /opt/bak/mysqllog.txt

#循环备份数据库

for db in $all_db

do

$backup_dir/mysql_$date/$db.sql

         mysqldump -u $db_user -h $db_host -p$db_passwd --single-transaction $bd > $backup_dir/mysql_$date/$db.sql

        zip -r $backup_dir/mysql_$date/$db.zip $backup_dir/mysql_$date/$db.sql

        rm -f $backup_dir/mysql_$date/$db.sql

done