mysql数据库备份（完整+增量）

最新推荐文章于 2024-06-22 15:26:37 发布

爬也要爬着前进

最新推荐文章于 2024-06-22 15:26:37 发布

阅读量185

点赞数

分类专栏： mysql 文章标签：数据库 mysql adb

本文链接：https://blog.csdn.net/JackNiMaBaBa/article/details/132269633

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

main.yml

[admin@ansible-001 mysql-backup]$cat  tasks/main.yml 
- name: Copy Backup Scripts
  copy:
    src: "{{ item }}"
    dest: /home/app/shell/
    owner: root
    group: root
    mode: 0600
  loop:
    - Mysql_FullBak.sh
    - Mysql_DailyBak.sh

- name: Touch DB Passwd.txt
  copy:
    content: 'Sdaa@12345'
    dest: /home/app/shell/.pw.txt
    owner: root
    group: root
    mode: 0600

- name: Add Cron Mysql Full Backup
  cron: 
    name: Mysql Full Backup
    weekday: "7"
    minute: "0"
    hour: "2"
    job: sh /home/app/shell/Mysql_FullBak.sh
    user: root
    
- name: Add Cron Mysql Daily Backup
  cron:
    name: Mysql Daily Backup
    weekday: "1-6"
    minute: "0"
    hour: "3"
    job: sh /home/app/shell/Mysql_DailyBak.sh
    user: root

Mysql_FullBak.sh

[admin@ansible-001 files]$cat Mysql_FullBak.sh
#!/bin/bash

DBUser="root"
DBPass=$(cat /home/app/shell/.pw.txt)
FileDate=$(date +%Y%m%d)
BakDir=/Backup
FileName=${FileDate}.sql
LogFile=${BakDir}/bak.log

if [ ! -d ${BakDir} ];then
  mkdir -p ${BakDir}
fi

Begin=$(date +"%Y-%m-%d %H:%M:%S")

/home/app/mysql/bin/mysqldump -u${DBUser} -p${DBPass} --quick --events --routines --triggers --flush-logs --master-data=2 --all-databases | pigz -p 8 > ${BakDir}/${FileName} && BakState=$(echo OK) || BakState=$(echo Failure) 

End=$(date +"%Y-%m-%d %H:%M:%S")

cat >> ${LogFile} << EOF
--------xxcar mysql full bakcup --------------
Start Time：${Begin}
End   Time: ${End}
Back State: ${BakState}

EOF

3.Mysql_DailyBak.sh

[zhaowei@ansible-001 files]$cat Mysql_DailyBak.sh
#!/bin/bash

DBUser="usopp"
DBPass=$(cat /home/app/shell/.pw.txt)
MysqlDir=/home/app/mysql
DataDir=${MysqlDir}/data
BinDir=${MysqlDir}/bin
BinIndex=${DataDir}/mysql-bin.index
BakDir=/Backup
BakDirDaily=${BakDir}/daily
LogFile=${BakDir}/bak.log

${BinDir}/mysqladmin -u${DBUser} -p${DBPass} flush-logs
Counter=$(wc -l ${BinIndex} | awk '{print $1}')
NextNum=0
Incre=()

if [ ! -d ${BakDirDaily} ];then
  mkdir ${BakDirDaily}
fi
   

for i in `cat ${BinIndex}`
do 
  base=`basename $i`
  NextNum=`expr $NextNum + 1`
  if [ $NextNum -lt $Counter ];then
    dest=${BakDirDaily}/$base
    if ( ! test -e $dest );then
       cp ${DataDir}/$base ${BakDirDaily}
       Incre[${#Incre[@]}]=$base
    fi
   fi
done

echo -e "\n---xxcar mysql Incremental Backup ---" >> $LogFile
for i in ${Incre[@]}
do
 echo $i >> $LogFile
done
echo  backup time:`date +"%Y-%m-%d %H:%M:%S"`  >> $LogFile