备份简要说明
- 物理备份采用xtrbackup (只实现全备逻辑)
- 逻辑备份采用mysqldump(分库分表)
- 脚本代码根据实际应用场景可选备份逻辑,并结合系统计划任务自动备份
#!/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