Mysql8备份的两种方式

Mysql8备份的两种方式

xtrabackup，docker镜像

xtrabackup.tar

脚本附件

wsbak.sh

tarrmmysql.sh

第一种方法：

备份前提
vim my.cnf

[mysqldump]

user=root
password=123213213!


#备份脚本
#!/bin/bash
base_path='/data/mysql8/bak/'
log_path=$(date -d yesterday +"%Y%m")
evday=$(date -d yesterday +"%Y%m%d")

mkdir -p $base_path/$log_path/$evday

/data/mysql8/bin/mysqldump -h 127.0.0.1 --databases jdc_jiche> /data/mysql8/bak/jdc_jiche$evday.sql

if [ $? -ne 0 ];
then
echo "请等待"
sleep 3
/data/mysql8/bin/mysqldump -h 127.0.0.1  --databases jdc_jiche> /data/mysql8/bak/jdc_jiche$evday.sql
sleep 3
mv $base_path/jdc_jiche$evday.sql  $base_path/$log_path/$evday/jdc_jiche$evday.sql
else
mv $base_path/jdc_jiche$evday.sql  $base_path/$log_path/$evday/jdc_jiche$evday.sql
fi


第二种方法：
准备工作，下载我的tar包
1. docker load -i xtrabackup.tar
2. 创建docker-compose.yaml
version: '2'
services:
  xtrabackup8:
    image: centos7.8_xtrabackup8:v1
    hostname: 8643cfdab609
    environment:
      PATH: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
      TZ: Asia/Shanghai
    stdin_open: true
    volumes:
    #设定数据数据存储的目录，对应持久化
    - /home/dbdata/mysql/data/xfy/data/:/var/lib/mysql
    #设定备份的目录持久化
    - /dbbak/bak/:/dbbak/bak/
    tty: true
    logging:
      driver: json-file
    ipc: private
    command:
    - /bin/bash
    labels:
      org.label-schema.license: GPLv2
      org.opencontainers.image.title: CentOS Base Image
      org.opencontainers.image.vendor: CentOS
      org.label-schema.schema-version: '1'
      org.label-schema.build-date: '20200504'
      org.label-schema.name: CentOS Base Image
      org.opencontainers.image.licenses: GPL-2.0-only
      org.label-schema.vendor: CentOS
      org.opencontainers.image.created: '2020-05-04 00:00:00+01:00'
#启动安装xtrabackup容器
3. docker-compose up -d

#备份脚本
#!/bin/bash
docker exec -i 6dda2984d2ea xtrabackup   --host=192.168.18.13 --port=33109  --user=root  --password=123213213!  --backup  --target-dir=/dbbak/bak/bak_$(date "+%Y%m%d%H%M%S") --no-server-version-check


#日志清除脚本
#!/bin/bash
set -x
set -e

last_day=`date --date='1 days ago' +%F`

tar_day=1
remove_day=1800

projectLogPath=/dbbak/bak/


#删除压缩的日志的记录
function rmCompressionLog(){
    if [ -d /mnt/newsqlbak/sql_log ];then
        cd /mnt/newsqlbak/sql_log
        for CompressionLog in `find ./ -name "*.log" -ctime +${remove_day} -type f`;do
                    rm -rf $CompressionLog
            done
        fi
}

#参数1是path
function gzipLog() {
    for log in `find ./  -name "bak_*"  -type d `;do
		echo "tar zcf $1 $log"
		tar zcf $log.tar.gz $log && rm -rf $log
	done
}

#参数1是path
function rmLog() {
    for tarlog in `find ./ -name "*.tar.gz" -mtime +${remove_day} -type f`;do
                echo "rm mysql bak data  $tarlog"
                rm -rf $tarlog
        done
}

cd $projectLogPath

gzipLog

rmLog

rmCompressionLog
#############################################end#############################


#crontab 
#定时任务
0 1 * * * sh /dbbak/wsbak.sh &>/dbbak/sql_log/backmysql-log.$(date +\%Y\%m\%d\%H\%M\%S).log

#日志清除计划
30 5 * * * sh /dbbak/tarrmmysql.sh &> /dbbak/sql_log/rmmysqldata-log.$(date +\%Y\%m\%d\%H\%M\%S).log