MySQL备份

11 篇文章 0 订阅
4 篇文章 0 订阅

备份概述

备份是保证数据不丢失,或最小程度的丢失。

备份分类

按备份方法分类

  • 热备
    在线备份,数据库正常运行的状态下,读写正常。
  • 冷备
    离线备份,数据库关闭服务的状态下,读写失效。
  • 温备
    在线备份,数据库正常运行的状态下,读正常,写失效。

按备份文件分类

  • 逻辑备份
    逻辑备份是站在服务层面进行,是指由备份工具导出的文本文件。
  • 物理备份
    物理备份是站在系统层面进行,是指直接复制数据库的物理文件。

按备份内容分类

  • 完全备份
    对数据库进行一个完整备份。
  • 增量备份
    对数据库进行一个完整备份,后每次备份都基于上次备份的差异部分。
  • 差异备份
    对数据库进行一个完整备份,后每次备份都基于首次完备的差异部分。

备份实验

  • 备份内容
    数据、额外的数据(二进制日志和InnoDB的事务日志)、代码(存储过程和存储函数、触发器、事件调度器等)、服务器配置文件
[root@master_1 ~]# ls /var/lib/mysql/
aria_log.00000001  ibdata1      master_1_bin.000001  master_1_bin.index  mysql_upgrade_info
aria_log_control   ib_logfile0  master_1_bin.000002  multi-master.info   performance_schema
cr                 ib_logfile1  master_1_bin.000003  mysql
ib_buffer_pool     ibtmp1       master_1_bin.000004  mysql.sock

数据库:mysql、
  • 实验环境
    master_1:10.10.10.10
    master_2:10.10.10.11

tar备份

属于冷备,物理备份

  • 备份
  1. 停止数据库
  2. tar备份数据
  3. 启动数据库

使用tar备份master_1数据,通过nc传到master_2上恢复数据。

master_1

停止数据库
[root@master_1 ~]# systemctl stop mariadb.service

创建备份目录
[root@master_1 ~]# mkdir backup

master_1备份并发送

[root@master_1 ~]# tar -cf ./backup/`date +"%y%m%d%H%M%S"`-mysql.tar /var/lib/mysql/ && tar -cf - ./backup/ | nc 10.10.10.11 50

启动数据库
[root@master_1 ~]# systemctl start mariadb.service

master_2

接收监听
[root@master_2 ~]# nc -l 50 | tar -xf -

接收完成
[root@master_2 ~]# ls backup/
200222191809-mysql.tar
  • 恢复
  1. 停止数据库
  2. 清理环境
  3. 导入备份数据
  4. 启动数据库
停止数据库
[root@master_2 ~]# systemctl stop mariadb.service

清理环境
[root@master_2 ~]# rm -fr /var/lib/mysql/*

导入备份数据
[root@master_2 ~]# tar -xf ./backup/200222191809-mysql.tar -C /

启动数据库
[root@master_2 ~]# systemctl start mariadb.service  #注意此时的账号密码是master_1的

lvm备份

几乎属于热备,物理备份

  • 备份
  1. 加全局读锁
  2. 创建快照
  3. 解锁
  4. 挂载快照
  5. 备份数据
  6. 删除快照

使用lvm快照备份master_1数据,通过scp传到master_2恢复数据。

由于之前/var/lib/mysql/未单独挂载到一个lvm分区,首先完成一个数据迁移。

[root@master_1 ~]# vgs
  VG    #PV #LV #SN Attr   VSize   VFree
  cl      1   2   0 wz--n- <19.00g      0
  mysql   1   0   0 wz--n- <10.00g <10.00g

现有10G空闲vg(mysql),创建lv
[root@master_1 ~]# lvcreate -L 3G -n data mysql

格式化并挂载
[root@master_1 ~]# mkfs.xfs /dev/mysql/data
[root@master_1 ~]# mount /dev/mysql/data /mnt/

停止mysql并复制所有数据到挂载
[root@master_1 ~]# systemctl stop mariadb
[root@master_1 ~]# cp -ar /var/lib/mysql/* /mnt/

卸载并重新挂载到/var/lib/mysql/,并修改目录的属主属组
[root@master_1 ~]# umount /dev/mysql/data
[root@master_1 ~]# mount /dev/mysql/data /var/lib/mysql/
[root@master_1 ~]# chown -R mysql.mysql /var/lib/mysql/

启动mysql
[root@master_1 ~]# systemctl start mariadb

准备已经完成,可以开始备份。

加锁 && 快照 && 解锁
[root@master_1 ~]# echo "flush tables with read lock; system lvcreate -L 4G -n back -s /dev/mysql/data; unlock tables" | mysql

挂载快照
[root@master_1 ~]# mount -o ro,nouuid /dev/mysql/back /mnt/  #xfs挂载不支持UUID冲突

复制文件
[root@master_1 ~]# cd /mnt/
[root@master_1 mnt]# tar -zcf ~/backup/`date +%F`-mysql.tar.gz *

传输备份
[root@master_1 mnt]# scp /root/backup/2020-02-22-mysql.tar.gz root@10.10.10.11:~/backup/
  • 恢复
  1. 停止数据库
  2. 清理环境
  3. 导入数据
  4. 启动数据库
停止数据库
[root@master_2 ~]# systemctl stop mariadb.service

清理环境
[root@master_2 ~]# rm -fr /var/lib/mysql/*

导入数据
[root@master_2 ~]# tar -xf backup/2020-02-22-mysql.tar.gz -C /var/lib/mysql/

修改权限
[root@master_2 ~]# chown -R mysql.mysql /var/lib/mysql/

启动数据库
[root@master_2 ~]# systemctl start mariadb.service

mariabackup备份

属于热备,属于物理备份。支持全备、差备和增备。

由于MariaDB10.3.x以上版本的redo日志格式和之前不同了,xtrabackup不在支持。mariadb提供了一个开源备份软件mariabackup。

  • 安装
[root@master_1 ~]# yum -y install mariadb-backup

全备

使用mariabackup备份master_1数据,通过scp传到master_2恢复数据。并不需要特定发送,在远程主机执行备份指明host即可在远程主机备份。

  • 备份
创建全量备份
#备份时可在远程主机指明 --host 来实现远程备份
[root@master_1 ~]# mariabackup --backup --target-dir=./backup -S /var/lib/mysql/mysql.sock -u root -p ''
准备备份数据
[root@master_1 ~]# mariabackup --prepare --target-dir=./backup
传输备份数据
[root@master_1 ~]# cd backup/
[root@master_1 backup]# tar -zcf `date +%F`-mysql.tar.gz *
[root@master_1 backup]# scp 2020-02-23-mysql.tar.gz root@10.10.10.11:~/backup/
  • 恢复
停止mysql
[root@master_2 ~]# systemctl stop mariadb.service

清理环境
[root@master_2 ~]# rm -fr /var/lib/mysql/*

展开tar包
[root@master_2 ~]# tar -xf ./backup/2020-02-23-mysql.tar.gz -C /backup/ && rm -fr ./backup/2020-02-23-mysql.tar.gz

恢复数据
[root@master_2 ~]# mariabackup --copy-back --target-dir=./backup/ --datadir=/var/lib/mysql/ -u root -p ''

重置属主属组
[root@master_2 ~]# chown -R mysql.mysql /var/lib/mysql/

启动mysql
[root@master_2 ~]# systemctl start mariadb

增备

使用mariabackup备份master_1数据,通过scp传到master_2恢复数据。

  • 备份

全备:
时间2020-02-23

增备:
时间2020-02-24
时间2020-02-25

创建全量备份
[root@master_1 ~]# mkdir -p ./backup/fullbackup/
[root@master_1 ~]# mariabackup --backup --target-dir=./backup/fullbackup/`date +%F` -S /var/lib/mysql/mysql.sock -u root -p ''

第一次创建增量备份相对于全量备份
[root@master_1 ~]# mkdir -p ./backup/incbackup/
[root@master_1 ~]# mariabackup --backup --target-dir=./backup/incbackup/`date +%F` --incremental-basedir=./backup/fullbackup/2020-02-23/ -S /var/lib/mysql/mysql.sock -u root -p ''

第二次创建增量备份相对于第一次增量备份
[root@master_1 ~]# mariabackup --backup --target-dir=./backup/incbackup/`date +%F` --incremental-basedir=./backup/incbackup/2020-02-24/ -S /var/lib/mysql/mysql.sock -u root -p ''

[root@master_1 ~]# tree -d -L 2  backup/
backup/
├── fullbackup
│   └── 2020-02-23
└── incbackup
    ├── 2020-02-24
    └── 2020-02-25
    
传输文件
[root@master_1 ~]# tar -zcf ./backup.tar ./backup/
[root@master_1 ~]# scp ./backup.tar root@10.10.10.11:~/
  • 恢复
[root@master_2 ~]# tar -xf backup.tar

准备全量备份
[root@master_2 ~]# mariabackup --prepare --target-dir=./backup/fullbackup/2020-02-23/

准备增量备份
[root@master_2 ~]# mariabackup --prepare --target-dir=./backup/fullbackup/2020-02-23/ 
[root@master_2 ~]# mariabackup --prepare --target-dir=./backup/fullbackup/2020-02-23/ --incremental-dir=./backup/incbackup/2020-02-24
[root@master_2 ~]# mariabackup --prepare --target-dir=./backup/fullbackup/2020-02-23/ --incremental-dir=./backup/incbackup/2020-02-25

停止数据库
[root@master_2 ~]# systemctl stop mariadb

清理环境
[root@master_2 ~]# rm -fr /var/lib/mysql/*

恢复数据
[root@master_2 ~]# mariabackup --move-back --target-dir=./backup/fullbackup/2020-02-23/ --datadir=/var/lib/mysql/

修改权限
[root@master_2 ~]# chown -R mysql.mysql /var/lib/mysql/

启动数据库
[root@master_2 ~]# systemctl start mariadb

差备

使用mariabackup备份master_1数据,通过scp传到master_2恢复数据。

  • 备份

全备:
时间2020-02-23

差备:
时间2020-02-24
时间2020-02-25

创建全量备份
[root@master_1 ~]# mkdir -p ./backup/fullbackup/
[root@master_1 ~]# mariabackup --backup --target-dir=./backup/fullbackup/`date +%F` -S /var/lib/mysql/mysql.sock -u root -p ''

第一次创建差异备份相对于全量备份
[root@master_1 ~]# mkdir -p ./backup/diffbackup/
[root@master_1 ~]# mariabackup --backup --target-dir=./backup/diffbackup/2020-02-24 --incremental-basedir=./backup/fullbackup/2020-02-23/ -S /var/lib/mysql/mysql.sock -u root -p ''

第二次创建差异备份相对于全量备份
[root@master_1 ~]# mariabackup --backup --target-dir=./backup/diffbackup/2020-02-24 --incremental-basedir=./backup/fullbackup/2020-02-23/ -S /var/lib/mysql/mysql.sock -u root -p ''

[root@master_1 ~]# tree -d -L 2 ./backup/
./backup/
├── diffbackup
│   ├── 2020-02-24
│   └── 2020-02-25
├── fullbackup
│   └── 2020-02-23
└── incbackup
    ├── 2020-02-24
    └── 2020-02-25

传输数据
[root@master_1 ~]# tar -zcf ./backup.tar.gz ./backup/
[root@master_1 ~]# scp ./backup.tar root@10.10.10.11:~/
  • 恢复
[root@master_2 ~]# tar -xf backup.tar

准备全量备份
[root@master_2 ~]# mariabackup --prepare --target-dir=./backup/fullbackup/2020-02-23/

准备差异备份  #根据需要选择恢复对应时间点差异数据
[root@master_2 ~]# mariabackup --prepare --target-dir=./backup/fullbackup/2020-02-23/ 
[root@master_2 ~]# mariabackup --prepare --target-dir=./backup/fullbackup/2020-02-23/ --incremental-dir=./backup/incbackup/2020-02-25

停止数据库
[root@master_2 ~]# systemctl stop mariadb

清理环境
[root@master_2 ~]# rm -fr /var/lib/mysql/*

恢复数据
[root@master_2 ~]# mariabackup --move-back --target-dir=./backup/fullbackup/2020-02-23/ --datadir=/var/lib/mysql/

修改权限
[root@master_2 ~]# chown -R mysql.mysql /var/lib/mysql/

启动数据库
[root@master_2 ~]# systemctl start mariadb

mysqldump+binlog备份

使用mysqldump备份master_1数据,通过scp传到master_2恢复数据。

备份

[root@master_2 ~]# mysqldump --all-databases --master-data=1 --gtid --single-transaction -R -h10.10.10.10 -uroot -p1234 > `date +%F`.sql

--signle-transation		#innodb事务级别设置为REPEATABLE READ
--master-data=1			#记录binlog文件和位置,用于主从复制
--gtid					#mariadb中有效记录gtid_slave_pos,配合master-data使用

--master-data=1
"CHANGE MASTER TO MASTER_LOG_FILE='master_1_bin.000003', MASTER_LOG_POS=345;"

--gtid
"CHANGE MASTER TO MASTER_USE_GTID=slave_pos;"
"SET GLOBAL gtid_slave_pos='0-10-48';"

恢复

如果不关闭二进制日志,在恢复时会产生大量的二进制日志,所以应关闭恢复。

清理环境
[root@master_2 ~]# rm -fr /var/lib/mysql/*

启动数据库
[root@master_2 ~]# systemctl start mariadb

添加sql_bin_log=off到备份文件
[root@master_2 ~]# sed -i '1i set sql_bin_log=off;' 2020-04-01.sql

恢复数据
[root@master_2 ~]# mysql -uroot -p < 2020-04-01.sql

查看备份文件binlog位置
"CHANGE MASTER TO MASTER_LOG_FILE='master_1_bin.000002', MASTER_LOG_POS=377;"

准备binlog  #这里可以写入2020-04-01.sql文件中一同恢复
[root@master_2 ~]# mysqlbinlog --start-position=377 ./binlog/master_1_bin.000002 > tmp.sql
[root@master_2 ~]# mysqlbinlog ./binlog/master_1_bin.000003 ./binlog/master_1_bin.000004 >> tmp.sql

添加sql_bin_log=off到备份文件
[root@master_2 ~]# sed -i '1i set sql_bin_log=off;' tmp.sql

恢复数据
[root@master_2 ~]# mysql -uroot -p < tmp.sql

备份脚本

使用mariabackup工具完成定义间隔多少天一次全备,由crontab定义多久一次增备,同时删除超过3个周期以上的备份。

#!/bin/bash 
  
host=10.10.10.10
user=root
pass=""

backupDay=$1
backupDay=${backupDay:-7}

backup_dir=`pwd`/backup
[ -d $backup_dir ] || $(mkdir $backup_dir)

last_dir=$(ls -t $backup_dir 2> /dev/null | head -n 1)

last_timestamp=$(date -d "$last_dir" +%s)
current_timestamp=$(date -d "-$backupDay day" +%s)

if [ "$last_timestamp" -lt "$current_timestamp" -o -z "$last_dir" ] ; then

[ -d $backup_dir/`date +%F`/ ] || $(mkdir $backup_dir/`date +%F`)
[ -d $backup_dir/`date +%F`/full/ ] || $(mkdir $backup_dir/`date +%F`/full/)
[ -d $backup_dir/`date +%F`/incr/ ] || $(mkdir $backup_dir/`date +%F`/incr/)

fi

last_date_dir=$(ls -dt $backup_dir/* | head -n 1)
last_full_dir=$(ls -dt $last_date_dir/full/* 2> /dev/null | head -n 1)

[ -z $last_full_dir ] && $(mariabackup --backup --target-dir=$last_date_dir/full/`date +"%F@%T"`/ -h "$host" -u "$user" -p "$pass")

last_incr_dir=$(ls -drt $last_date_dir/incr/* 2> /dev/null | head -n 1)

[ -z "$last_incr_dir" -a -n "$last_full_dir" ] && $(mariabackup --backup --target-dir=$last_date_dir/incr/`date +"%F@%T"`/ --incremental-basedir=$last_full_dir -h "$host" -u "$user" -p "$pass")

[ -n "$last_incr_dir" ] && $(mariabackup --backup --target-dir=$last_date_dir/incr/`date +"%F@%T"`/ --incremental-basedir=$last_incr_dir -h "$host" -u "$user" -p "$pass")

[ -n $last_dir ] && $(ls -dt $backup_dir/* | awk "NR>3" | xargs rm -fr)
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值