检查mysql备份_Mysql数据检查备份脚本

#!/bin/bash

user=root

dateDIR=$(date -d "yesterday" +"%y-%m-%d")

mkdir -p /data/backup/mysql/data/$dateDIR

date +"%Y-%m-%d-%H:%M:%S"  >/data/backup/mysql/data/$dateDIR/MyISAMcheck.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/MyISAMcheck.log

date +"%Y-%m-%d-%H:%M:%S"  >/data/backup/mysql/data/$dateDIR/MyISAManalyze.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/MyISAManalyze.log

date +"%Y-%m-%d-%H:%M:%S"  >/data/backup/mysql/data/$dateDIR/MyISAMoptimize.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/MyISAMoptimize.log

date +"%Y-%m-%d-%H:%M:%S"  >/data/backup/mysql/data/$dateDIR/InnoDBcheck.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/InnoDBcheck.log

date +"%Y-%m-%d-%H:%M:%S"  >/data/backup/mysql/data/$dateDIR/InnoDBanalyze.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/InnoDBanalyze.log

date +"%Y-%m-%d-%H:%M:%S"  >/data/backup/mysql/data/$dateDIR/InnoDBoptimize.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/InnoDBoptimize.log

#刷新缓存中的日志

mysqladmin -u$user -p123456 flush-logs

for db in $(mysql -u$user -p123456 -e "show databases" | grep -ve "Database" -ve "mysql" -ve "test" -ve "information_schema")

do

#数据库中每个表的存储引擎

mysql -u$user -p123456 -e "show table status" -D "$db" | sed '1d' | awk '{print $1" "$2}' > table-engine_check.txt

#根据每个表不同的存储引擎，进行修复和优化

while read table engine

do

if [ "$engine" == "MyISAM" ] ; then

mysqlcheck -u$user -p123456 -C -F -f --auto-repair --tables "$db" "$table" >> /data/backup/mysql/data/$dateDIR/MyISAMcheck.log ;

mysql -u$user -p123456  -e "ANALYZE NO_WRITE_TO_BINLOG TABLE $db.$table;" |awk 'END{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10}'>> /data/backup/mysql/data/$dateDIR/MyISAManalyze.log;

mysql -u$user -p123456  -e "OPTIMIZE NO_WRITE_TO_BINLOG TABLE $db.$table;"|awk 'END{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10}'>>/data/backup/mysql/data/$dateDIR/MyISAMoptimize.log;

elif [ "$engine" == "InnoDB" ] ; then

mysqlcheck -u$user -p123456 -C -F -f --auto-repair --tables "$db" "$table" >> /data/backup/mysql/data/$dateDIR/InnoDBcheck.log ;

mysql -u$user -p123456  -e "ANALYZE NO_WRITE_TO_BINLOG TABLE $db.$table;" |awk 'END{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10}'>> /data/backup/mysql/data/$dateDIR/InnoDBanalyze.log;

mysql -u$user -p123456  -e "OPTIMIZE NO_WRITE_TO_BINLOG TABLE $db.$table;"|awk 'END{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10}'>> /data/backup/mysql/data/$dateDIR/InnoDBoptimize.log;

fi

done < table-engine_check.txt

#针对每个库进行锁表然后备份；将备份后的文件打包压缩到指定目录

mysqldump -u$user -p123456 --default-character-set=utf8 --opt --lock-all-tables ${db}| gzip -9> /data/backup/mysql/data/$dateDIR/${db}_${dateDIR}.sql.gz

done

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/MyISAMcheck.log

date +"%Y-%m-%d-%H:%M:%S"  >>/data/backup/mysql/data/$dateDIR/MyISAMcheck.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/MyISAManalyze.log

date +"%Y-%m-%d-%H:%M:%S"  >>/data/backup/mysql/data/$dateDIR/MyISAManalyze.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/MyISAMoptimize.log

date +"%Y-%m-%d-%H:%M:%S"  >>/data/backup/mysql/data/$dateDIR/MyISAMoptimize.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/InnoDBcheck.log

date +"%Y-%m-%d-%H:%M:%S"  >>/data/backup/mysql/data/$dateDIR/InnoDBcheck.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/InnoDBanalyze.log

date +"%Y-%m-%d-%H:%M:%S"  >>/data/backup/mysql/data/$dateDIR/InnoDBanalyze.log

echo "***************************************" >>/data/backup/mysql/data/$dateDIR/InnoDBoptimize.log

date +"%Y-%m-%d-%H:%M:%S"  >>/data/backup/mysql/data/$dateDIR/InnoDBoptimize.log

rm -rf table-engine_check.txt

#删除7天以前的全备份文件和目录

find /data/backup/mysql/data/* -mtime +6 -type d -exec rm -rf {} \;>/dev/null 2>&1