mysql备份脚本

-表名.sql #实例：backup/2016-08-25/uic-test.sql #测试数据还原实例：mysql -uroot -pxxx uic< backup/2016-08-25/uic-test.sql ###############脚本变量定义################################## MYSQL_HOME=/home/work/mysql MYSQL_DUMP=/home/work/mysql/bin/mysqldump BACKUP_HOME=/home/work/mysql/backup save_time_threshold=7 #备份天数 BACKUP_DATA_PATH=$BACKUP_HOME/$(date +%F) #RM_DATA_PATH=$BACKUP_HOME/$(date -d "-30 days" +%F) PASS=$(grep password /home/work/mysql/etc/root.cnf |sed s/[[:space:]]//g|awk -F‘=‘ {‘print $2‘}) USER=‘root‘ HOST=‘127.0.0.1‘ ###############函数部分####################################### function backupMysql(){ local db=$1 local table=$2 local dbdir=$3 local backfile="${dbdir}/${db}-${table}.sql" $MYSQL_DUMP --skip-lock-tables -h${HOST} -u${USER} -p${PASS} $db $table | gzip > ${backfile}.gz } ##########脚本逻辑部分######################################### ls $BACKUP_HOME &>/dev/null || mkdir $BACKUP_HOME chown work.work $BACKUP_HOME ls $BACKUP_DATA_PATH &>/dev/null || mkdir $BACKUP_DATA_PATH chown work.work $BACKUP_DATA_PATH find $BACKUP_HOME/ -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \; #if [ -d $RM_DATA_PATH ];then # rm -rf $RM_DATA_PATH #fi cd $BACKUP_DATA_PATH || exit echo "begin backup mysql" cmd="show databases" cnt=$(mysql -h${HOST} -u${USER} -p${PASS} -s -e "${cmd}") for db in $cnt do cmd="use $db;show tables" tablelist=$(mysql -h${HOST} -u${USER} -p${PASS} -s -e "${cmd}") for table in $tablelist do if [ "$table" == "project_files" ] ;then cmd1="use table;SELECT project_id,version,chunk,size from $table" mysql -h${HOST} -u${USER} -p${PASS} -s -e "${cmd1}" | gzip > ${BACKUP_DATA_PATH}/${db}-${table}_txt.gz else backupMysql "$db" "$table" "$BACKUP_DATA_PATH" fi done done

 

mysql备份脚本

标签：/etc/   mysql备份脚本   l数据库   awk   功能   dep   chown   cti   function