随着业务的不断增长,在mysqld上的数据势必会越来越多,那么只有备份单个库的备份脚本就不再适用实际的生产环境,因此就对以前写的备份脚本重新写了下(http://jim123.blog.51cto.com/4763600/1846301),当然因为数据量的增大,磁盘空间也要节约使用,那么就可以通过压缩效率极高bzip2来对备份的数据进行进一步的压缩数据,当然如果系统中没有bzip2的话,需要安装,这个安装可以通过rpm的前端工具yum或者apt-get等安装或者是直接安装源码包都可以,这个比较简单在这里就不再做过多的赘述了,脚本如下:
#!/bin/bash #masterbak.sh version3.0 chmod 700 #writer jim #00 03 * * * /usr/local/scripts/master_dbbak.sh #history #2016.11.19 #2016.12.3 export datetime=`date +"%Y%m%d"` export base_bindir="/usr/local/mysql/bin" export mysql_socket="/tmp/mysql.sock" export mysql_password="youpassword" #当然也可以在系统的家目录下加.my.cnf在里面的[mysql]和[mysqldump]下写上备份的账户密码 export datadir="`$base_bindir/mysql -S $mysql_socket -p$mysql_password -Bse "show variables like 'datadir';" |awk '{print $2}'`" export mysql_backup="/data/backup/mysql_backup" export masterbakdir="$mysql_backup/masterbakdir" export backup_log="$masterbakdir/logs" ########################设定要备份的库################################ export exclude="information_schema|performance_schema|mysql" export all_databases=`mysql -Bse "show databases;"|egrep -v "$exclude"` if [ ! -e "$backup_log/"$datetime"" ];then mkdir -p $backup_log/"$datetime" fi #########################记录主库库状态################################# $base_bindir/mysql -S $mysql_socket -p$mysql_password -e "select now();show master status\G" |tee -a $backup_log/"$datetime"/masterstat_${datetime}.log ###########################开始备份##################################### echo "============================ start mysqldump `date`" | tee -a $backup_log/"$datetime"/masterstat_${datetime}.log for i in $all_databases do $base_bindir/mysqldump --allow-keywords -S $mysql_socket -p$mysql_password -RE --add-locks --opt $i > $masterbakdir/$i-$datetime.sql echo "`date` $i backup success" |tee -a $backup_log/"$datetime"/masterstat_${datetime}.log done if [ $? -eq 0 ];then echo "============================ end mysqldump `date +"%Y-%m-%d %H:%M:%S"`"|tee -a $backup_log/"$datetime"/masterstat_${datetime}.log #导出过程函数过程及事件 else echo "`date` mysqldump backup fail"|tee -a $backup_log/"$datetime"/masterstat_${datetime}.log fi #########################进行数据压缩####################################### /usr/bin/bzip2 -z $masterbakdir/*.sql ####################删除7天前的备份数据及30天前的log################# find $masterbakdir -type f -ctime +7 -name "*.bz2" -exec rm -vf {} \; >> $backup_log/free-${datetime}.log find $backup_log -type f -ctime +7 -name "free-*" -exec rm -vf {} \; find $backup_log -type d -ctime +30 -name "*" -exec rm -vrf {} \; exit 0
转载于:https://blog.51cto.com/jim123/1875684