xtrabackup备份脚本-最佳实践-亲试ok

[root@localhost scripts]# cat xtra_full_bak.sh

#!/bin/bash

# 0 0 */2 * * sh /root/script/backupdb.sh

# author: Wang XiaoQiang 2016/09/29

#dbname='alldatabases'

dbuser='root'

dbpassword='123456'

backup_dir='/back'

backup_log='/back/logs/backup.log'

ymtime=`date +%Y-%m`

dtime=`date +%d`

wlog(){

  echo -e "`date "+%F %T"` DBname: $1 State: $2\n" >> $backup_log

}

[ ! -d $backup_dir/$ymtime ] && mkdir -p $backup_dir/$ymtime

for db in `echo "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','sys','performance_schema')" | mysql  -uroot -p123456 #--skip-column-names`

do

   database=${db}

done

cd $backup_dir/$ymtime && /usr/bin/innobackupex --host=127.0.0.1 --user=$dbuser --password=$dbpassword --no-timestamp ${database}  > /dev/null 2>&1

if [ $? -eq 0 ];then

  wlog $dbname 'Backup success.'

  dbakfile=${db}.$dtime.tar.gz

  tar zcf $dbakfile ${db} --remove-files

  [ $? -eq 0 ] && wlog $dbakfile 'Packaging success.' || wlog $dbakfile 'Packaging failed.'

else

  wlog $dbname 'Backup failed.'

fi

# End