mysql全备份脚本速查

mysql全备份脚本

 

# 快捷备份方式
[root@nb scripts]# cat db.backup.sh 
#!/bin/bash
mysqldump -ubackup -pbackuppwd -P3306 -h192.168.65.128 -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384 --master-data=2 \
--single-transaction -B db1 --quick | gzip >/backup/db1_back_`date +%Y-%m-%d_%H_%M_%S`.sql.gz

 

# 边解压边倒入
# gunzip < mobile_billing-dump-2018070609.sql.gz | mysql

 

# 分开独立备份文件

mkdir -p /home/databak
cat >/home/databak/dbbbak.sh <<"EOF"
#!/bin/bash
. ~root/.bash_profile
### dump north db ###
cd /home/databak/

#ALL_DATABASE="$1_all_database-dump-`date +%Y%m%d%H`.sql.gz"
#data_filename="$1-dump-"`date +%Y%m%d%H`.sql.gz
#bakdir=$1
#if [ $2 = '3306' ] ; then
etcfile='/etc/my.cnf'
#else
#etcfile="/home/mysql_$2/my.cnf"
#fi
#if [ ! -e $bakdir ];then
#  mkdir $bakdir
#  fi
for database in `mysql -e "show databases\G"|grep Database |egrep -iv 'mysql|performance_schema|information_schema|sys'|awk -F: '{print $2}'`
do
  data_filename="$database-dump-"`date +%Y%m%d%H`.sql.gz
  mysqldump --defaults-file=$etcfile -f -hlocalhost -uroot --default-character-set=utf8 --master-data=2 --single-transaction --set-gtid-purged=OFF -R --triggers -q -B $database|gzip> /home/databak/${data_filename}
#get md5sum and file size

  data_filesize=`ls -l /home/databak/${data_filename}| awk '{print $5}'`
  data_filemd5=`md5sum /home/databak/${data_filename} | awk '{print $1}'`

  echo "${data_filemd5} ${data_filesize}" > /home/databak/${data_filename}.md5
done
### delete 30 day ago DB backup ###
  find /home/databak/ -mtime +30 -type f -name "*-dump-*.sql.gz*" | xargs rm -fr "{}" \;
  #find /home/databak/ -mtime +30 -type f -name "xtra*tar.gz*" | xargs rm -fr "{}" \;
  #find /home/databak/increbak/mysql -mtime +1 -type f -name "xtrainc*md5" |xargs rm -f "{}" \;
EOF

00 03 * * * /bin/bash /home/databak/dbbbak.sh