mysql backup

mysql 备份脚本

 

 

#!bin/bash

# media_manager > ad_reclaims

db_user="root"
db_passwd=""
db_host="127.0.0.1"
db_name="db"
db_table="group"

# the directory for story your backup file.
backup_dir="/alidata/dataBackup/"
#echo "Error: backup_dir is $backup_dir" && exit 0

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

time="$(date -d '-4 day' +"%Y-%m-%d")"

#echo "Error: back-name is $backup_dir$db_table-$time.sql" && exit 0

# mysql, mysqldump and some other bin's path
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/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/"

# 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 $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"
#done
t='2016-10-25';
-D 指定数据库
-B
-s silent 模式
-e 执行指定的SQL语句并退出
语句是一个变量时要用 EOF 包着
statement=" SELECT brandid FROM brand WHERE updatetime LIKE '$t%' LIMIT 1;";
max_id="$($MYSQL -u $db_user -h $db_host -p$db_passwd -D $db_name -Bs <<EOF
$statement
EOF
)";

直接接语句时要使用 -e 参数
#max_id="$($MYSQL -u $db_user -h $db_host -p$db_passwd -D $db_name -Bse ' SELECT `brandid` FROM `cx_brand` WHERE updatatime_auto LIKE $t%  LIMIT 1 ;')"
echo $max_id
exit;

mysqldump 命令如果要使用 where 条件需要用 -w 选项后面跟 条件
mysqldump --single-transaction -u $db_user -h $db_host -p$db_passwd $db_name $db_table -w "create_time like '$time%'" > $backup_dir$db_table-$time.sql

echo "Your database backup successfully completed"