mysql 物理备份 逻辑备份_mysql物理及逻辑备份shell脚本

#!/bin/bash

###mysql backup###

TS=$(date +%Y-%m-%d_%H-%M-%S)

Data_dir=/data3/data/mysql/

MySQL_bak_dir=/data1/mysql_bak/

Password="mysqlroot@wx2016"

cmd="mysql -uroot -p"

log_time="date +%Y%m%d_%H:%M:%S"

lock_dir=/var/lock/subsys/

Usage() {

echo "USAGE:$0 {physical_backup|logic_backup}"

}

write_log() {

content=$1

echo "`${log_time}` $content" >> /tmp/mysql_bak.log

}

function mysql_physical_backup() {

write_log "start innobackup"

/usr/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=mysqlroot@wx2016 ${MySQL_bak_dir}

sleep 5

writ_log "start apply log"

/usr/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=mysqlroot@wx2016 --apply-log ${MySQL_bak_dir}$TS

write_log "innobackup and apply log finished"

}

function mysql_logic_backup() {

db=`$cmd${Password} -e "show databases"|egrep -v "Database|information_schema|performance_schema|ts_db|aaa|mysql"`

for database in $db

do

write_log "mysqldump start ${database} backup"

tb=`$cmd${Password} -e "use ${database};show tables"|grep -v "Tables_in_${database}"`

for table in $tb

do

write_log "backup ${database} ${table}"

[ -d ${MySQL_bak_dir}mysqldump/$TS/${database}/ ] || mkdir -p ${MySQL_bak_dir}mysqldump/$TS/${database}

/usr/bin/mysqldump -uroot -p${Password} ${database} ${table} >${MySQL_bak_dir}mysqldump/$TS/${database}/${table}

done

write_log "mysqldump ${database} backup finished.."

done

}

main() {

while [ $# -ge 1 ]

do

mysql_bak_option="${1}"

case "${mysql_bak_option}" in

physical_backup)

mysql_physical_backup;

shift;;

logic_backup)

mysql_logic_backup;

shift;;

*)

Usage;

exit 1

esac

#mysql_logic_backup

done

}

(

flock -n 200

if [ $? -ne 0 ];then

echo "script is running..."

exit 2

fi

main "$@"

flock -u 200

)200>${lock_dir}mysql_bak.lock