数据备份方式 物理备份Xtrabackup(XBK) 增备恢复
一、MySQL常用备份工具
- 逻辑备份方式
mysqldump(MDP)
mysqlbinlog
replication
mydumper
load data in file - 物理备份方式
MySQL Enterprise Backup(企业版)
Percona Xtrabackup(PBK,XBK) - 架构备份方式
-
使用 delete 误删数据怎么找回?
可以用 Flashback 工具通过闪回把数据恢复回来。
Flashback 恢复数据的原理是什么?
Flashback 恢复数据的原理是是修改 binlog 的内容,拿回原库重放,从而实现数据找回。 -
使用sed、grep从mysqldump中过滤出需要备份的信息。
备份方式:
- innodb表
采用快照备份的方式,开启一个独立的事物,获取当前最新的一致性快照,放临时表中,转换成SQL语句保存到sql文件中。 -
非innodb表
需要锁表备份,全局锁表,然后转换成sql语句保存到sql文件中。
二、Mysql数据备份
#mysql客户端
mysql
mysqladmin
mysqldump
1.备份的原因
1.备份就是为了恢复。
2.尽量减少数据的丢失(公司的损失)
2.备份的类型
1.冷备:停库,停服务,备份
2.热备:不停库,不停服务,备份
3.温备:不停服务,锁表(阻止数据写入),备份
#冷备份:
这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
#温备份:
这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
#热备份:
这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能。使用热备份时,系统仍可供读取和修改数据的操作访问。
3.备份的策略
1.全备:全部数据备份
2.增备:针对于上一次备份,将新数据备份
3.差异备份:基于上一次全备进行新数据的备份
4.备份方式
1)逻辑备份
#基于SQL语句的备份
1. binlog
2. into outfile
[root@db03 data]# vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
mysql> select * from world.city into outfile '/tmp/world_city.data';
3. mysqldump
4. replication
报错:
需要配置my.cnf,加入一条安全目录: secure-file-priv=/tmp
2)物理备份
#备份底层的数据文件
1.备份整个data数据目录
2.xtrabackup工具
三、物理备份Xtrabackup
- Percona Xtrabackup (采用perl语言编写)
0.安装
#上传文件包(2.4以后版本是针对mysql8.0版本)
[root@db03 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#下载epel源
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo
#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup
wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#安装
[root@db03 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#安装好后的命令
[root@db03 ~]# xtrabackup
[root@db03 ~]# innobackupex
1.安装最新版xbk
# rpm包下载位置
https://repo.percona.com/tools/yum/release/7/RPMS/x86_64/
# 安装依赖
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# 安装2.4.*版对应mysql5.7.*以下版本
yum install percona-xtrabackup-24 -y
# 安装8.0.*版对应mysql8.0.*版本
yum install percona-xtrabackup-80.x86_64 -y
无法安装情况:
# 1.先配置repo源
vi /etc/yum.repos.d/percona-original-release.repo
#
# This repo is managed by "percona-release" utility, do not edit!
#
[percona-release-x86_64]
name = Percona Original release/x86_64 YUM repository
baseurl = http://repo.percona.com/percona/yum/release/releasever/RPMS/x86_64
enabled = 1
gpgcheck = 1
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
[percona-release-noarch]
name = Percona Original release/noarch YUM repository
baseurl = http://repo.percona.com/percona/yum/release/releasever/RPMS/noarch
enabled = 1
gpgcheck = 1
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
[percona-release-sources]
name = Percona Original release/sources YUM repository
baseurl = http://repo.percona.com/percona/yum/release/$releasever/SRPMS
enabled = 0
gpgcheck = 1
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
# 2.再安装指定版本例如
yum install percona-xtrabackup-80.x86_64 -y
2.Xtrabackup备份原理
很好的参考文档:https://www.cnblogs.com/linuxk/p/9372990.html
# 非innodb表
对于非innodb表(比如myisam)是触发全局锁,直接锁表,cp数据文件,然后解锁,属于一种温备。
# innodb表
"支持热备,业务发生时影响最小的备份方式。
1.开启线程,checkpoint,将已经提交的数据页刷新到磁盘,记录一个LSN。
2.拷贝表相关的所有的文件(ibdata1,frm,ibd...)
3.备份期间产生新的数据变化的redo也会备份走。
4.结束拷贝后会再次统计LSN,看redo的位置点,记录binlog和gtid的位置信息。写入到一个专用文件Xtrabacker_chang_point
5.所有的备份文件统一存放到一个目录下。"
对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
3)备份时读取配置文件/etc/my.cnf(如果使用Xtrabackup备份,必须要配置datadir)
3.Xtrabackup全量备份
# Xtrabackup是服务器端工具,只能本地备份,不能远程备份.
# 前提条件:
数据库启动
能连接数据库
配置文件设置clinet标签:
[client]
socket=/tmp/mysql.sock
默认读取配置文件[mysqld]下的datadir
1)准备备份目录
[root@db03 ~]# mkdir /backup
2)备份(全备)
# 指定备份目录进行全备(会自动生成基于当前时间点的目录)
xtrabackup --defaults-file=/etc/my.cnf --socket=/var/lib/mysql/mysql.sock --user=root --password=123 --backup --target-dir=/backup/
#去掉时间戳进行手工指定目录备份
innobackupex --user=root --password=123 --no-timestamp /backup/full_`data +%F`
3)查看全备内容
[root@db03 ~]# ll /backup/full/
总用量 129052
-rw-r----- 1 root root 434 7月 23 08:51 backup-my.cnf
drwxr-x--- 2 root root 68 7月 23 08:51 dump
-rw-r----- 1 root root 79691776 7月 23 08:51 ibdata1
-rw-r----- 1 root root 52428800 7月 23 08:51 ibdata2
drwxr-x--- 2 root root 4096 7月 23 08:51 mysql
drwxr-x--- 2 root root 4096 7月 23 08:51 performance_schema
drwxr-x--- 2 root root 68 7月 23 08:51 row
drwxr-x--- 2 root root 20 7月 23 08:51 test
-rw-r----- 1 root root 21 7月 23 08:51 xtrabackup_binlog_info #记录binlog的位置点信息
-rw-r----- 1 root root 113 7月 23 08:51 xtrabackup_checkpoints #记录LSN信息,方便增量备份.
-rw-r----- 1 root root 483 7月 23 08:51 xtrabackup_info #记录本次备份的全面信息
-rw-r----- 1 root root 2560 7月 23 08:51 xtrabackup_logfile #redo-log
[root@db03 full]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 8417759
# 查看主从binlog点位
[root@db03 full]# cat xtrabackup_info
binlog_pos = filename 'mysql-bin-master.000004', position '594'
innodb_from_lsn = 0
innodb_to_lsn = 2812612
4.Xtrabackup全备恢复数据
数据恢复过程:
1. 必须先停库
2. 自动故障恢复redo前滚,undo回滚
xtrabackup --prepare --apply-log --target-dir=/backup/backup
3. 覆盖恢复数据
\cp -fr /backup/xbk/* /data/mysql/
4. 恢复权限和用户mysql
chown -R mysql.mysql /data/mysql
5. 启动数据库
模拟:
1)删除所有数据库
mysql> drop database dump;
mysql> drop database performance_schema;
mysql> drop database row;
mysql> drop database test;
2)停止数据库
[root@db03 ~]# systemctl stop mysqld.service
3)手动模拟CSR的过程
#将redo进行重做,已提交的写到数据文件,未提交的使用undo回滚,模拟CSR的过程
[root@db03 ~]# innobackupex --user=root --password=123 --apply-log /backup/full
4)恢复数据
1>方法一:
#移走原数据目录
[root@db03 mysql]# mv data data.back
#将全备的数据目录迁移回来
[root@db03 mysql]# cp -r /backup/full ./data
[root@db03 mysql]# chown -R mysql.mysql data
2>方法二:
#使用innobackupex恢复数据
[root@db03 mysql]# innobackupex --copy-back /backup/full/
[root@db03 mysql]# chown -R mysql.mysql data
5)启动数据库查看数据
#启动数据库
[root@db03 data]# systemctl start mysqld
[root@db03 data]# mysql -uroot -p123
mysql> show databases;
5.Xtrabackup增量备份
1.基于上一次备份进行增量
2.增量备份无法单独恢复,必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中
1)先全备
[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
2)写入新数据
[root@db03 ~]# mysql -uroot -p123
mysql> use dump
mysql> insert dump values(10000),(20000),(30000);
3)第一次增备
# 增量备份第一次
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2020-07-23 /backup/inc1
参数说明:
--incremental:开启增量备份功能
--incremental-basedir:上一次备份的路径
#验证
[root@db03 ~]# cat /backup/full/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 8417759
[root@db03 ~]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 8417759
to_lsn = 8419281
4)再次写入数据
[root@db03 ~]# mysql -uroot -p123
mysql> use dump
mysql> insert dump values(100000),(200000),(300000);
5)第二次增备
[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2
#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1636167
[root@db03 backup]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1636167
to_lsn = 1640828
[root@db03 backup]# cat /backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1640828
to_lsn = 1645877
6)再次写入数据
7)第三次增量备份
[root@db03 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3
6.Xtrabackup增量恢复数据
- 查看lsn位置点:
--redo-only参数就是保证了不做undo动作,只做redo动作,取消last_lsn与to_lsn之间的差距.
1)将全备执行redo
[root@db03 backup]# innobackupex --apply-log --redo-only /backup/full_2020-07-23
2)将第一次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full_2020-07-23
#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1640828 #该值本来是inc1的位置点
3)将第二次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full_2020-07-23
#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1645877 #该值本来是inc2的位置点
4)将最后一次增备执行redo和undo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3/ /backup/full_2020-07-23
#验证
[root@db03 backup]#
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = full-prepare d
from_lsn = 0
to_lsn = 1649869
5)将整体数据进行一次CSR
[root@db03 backup]# innobackupex --apply-log /backup/full_2020-07-23/
6)恢复数据
[root@db03 mysql]# mv data data.bak
[root@db03 mysql]# innobackupex --copy-back /backup/full_2020-07-23/
[root@db03 mysql]# chown -R mysql.mysql data
[root@db03 mysql]# systemctl start mysqld
7. Xtrabackup总结
1.增备:
优点:占用磁盘空间小,没有重复数据
缺点:恢复麻烦
2.全备:
优点:恢复只需一次
缺点:占用磁盘空间,每次全备都有重复数据