《MySQl数据库备份脚本.doc》由会员分享,可在线阅读,更多相关《MySQl数据库备份脚本.doc(4页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、1MySQl 数据库备份脚本数据库备份脚本#!/bin/bash#=#MySQL 定时备份#MySQL timed backup#=DUMP=/usr/bin/mysqldumpIPADDR=127.0.0.1PORT=3306USER=typechoPASSWD=569da4de2139687044bbDATABASE=typechoROOT_DIR=/data#-set-u-x$DUMP|exit 100OUT_DIR=$ROOT_DIR/mysql/mysql-newTAR_DIR=$ROOT_DIR/mysql/mysql-bak-listLOG_DIR=$ROOT_DIR/mysql
2、/mysql-log2LOG_DIR_file=$LOG_DIR/mysql-logDATE=date+%Y-%m-%dTAR_BAK=$DATABASE-bak-$DATE.tar.gzBAK_NAME=$DATABASE-bak-$(date-d#39;-5days#39;+%Y-%m-%d).tar.gz-d$OUT_DIR|mkdir-p$OUT_DIR-d$TAR_DIR|mkdir-p$TAR_DIR-d$LOG_DIR|mkdir-p$LOG_DIRcd$OUT_DIR|exit 200-n$OUT_DIR rm-rf$OUT_DIR/*|echo Failed to delet
3、e bufferfile$LOG_DIR_filemkdir-p$OUT_DIR/$DATE$DUMP-h$IPADDR-P$PORT-u$USER-p$PASSWD-databases$DATABASE|gzip$OUT_DIR/$DATE/$DATABASE-$DATE.sql.gzif$?-eq 0;thenecho-n$(date+%Y-%m-%d%H:%M:%S)The backup successful,$LOG_DIR_file#压缩cd$OUT_DIRtar-zcf$TAR_BAK$DATE/dev/nullecho-n Backup file packed3successfu
4、lly,$LOG_DIR_file|echo-nPackaging failure,$LOG_DIR_filemv$OUT_DIR/$TAR_BAK$TAR_DIRecho-n Backup file packagingsuccessfully moved to data directory successfully,$LOG_DIR_file|echo-n Move failure,$LOG_DIR_file#删除之前的备份rm-rf$TAR_DIR/$BAK_NAMEecho Backup succeeded 5 daysbefore deletion!$LOG_DIR_file|echo
5、 Backup failed 5 days beforedeletion。$LOG_DIR_fileelseecho$(date+%Y-%m-%d_%H:%M:%S)Backupfailure!$LOG_DIR_filefi注:如果备份报错mysqldump:Got error:1449:The user specified as a definer(abc%)does not exist when using LOCK TABLES因为 mysqldump 命令默认在导出时是要锁定表的,所以解决方式有两个。1、创建相关用户4grant all on*.*to abc#39;%#39;identified by#39;123456#39;2、在命令中加上-skip-lock-tables 这个参数$DUMP-h$IPADDR-P$PORT-u$USER-p$PASSWD-databases-skip-lock-tables$DATABASE$OUT_DIR/$DATE/$DATABASE-$DATE.sql分表备份脚本:如果表多建议使用分表备份。MySQL分表备份脚本:https:/
限制150内