6 - 数据备份与恢复｜innobackupex

2024-01-10

数据备份与恢复｜innobackupex

数据备份与恢复
- 数据备份相关概念
innobackupex 对数据做备份和恢复
- 增量备份与恢复

数据备份与恢复

数据备份相关概念

数据备份方式

物理备份：直接拷贝数据库目录（使用的系统命令 cp 或 tar）
逻辑备份：使用mysql服务提供的命令或安装第三方软件提供的命令，对已有的数据做备份。
数据备份策略
完全备份 : 备份所有数据
增量备份：备份新数据每次判断新数据和前一次备份数据比较
差异备份：备份新数据备份完全备份后新产生的数据每次判断新数据都是和完全备份的数据比较和第一次比
工作中使用的备份策略：
完全备份 + 差异备份

完全备份 + 增量备份

每周周一完全备份
每周周二～周日增量备份或差异备份

物理备份与恢复

优点备份恢复简单
缺点备份和恢复数据均需要停止数据库服务（若不停止服务也可以备份但会导致备份不完全）
仅仅 适用于同操作系统恢复数据
备份数据量大浪费存储空间
适合线下服务器的数据库备份数据量小的备份

# 拷贝数据库目录
cp -r /var/lib/mysql /bakdir/mysql.bak
# 打包压缩数据库目录
cd /var/lib/mysql/
tar -zcvf /bakdir/mysql.tar.gz ./*

恢复数据

# 停止服务 恢复数据
systemctl stop mysqld
# 清空
rm -rf /var/lib/mysql/*
# 使用备份文件恢复数据
cp -r /bakdir/mysql.bak/* /var/lib/mysql/
# 起服务之前 要更改所有者 所属组
chown -R mysql:mysql /var/lib/mysql/
# 启服务
systemctl start mysqld

逻辑备份（推荐）

使用mysql服务提供的备份命令对数据做备份和恢复
备份和恢复数据不需要停止服务，服务停止反而备份和恢复不了数据

备份命令格式：

]# mysqldump -uroot -p密码 数据库名 > /备份目录名/文件名.sql
数据库名的表示方式：
库名 表明 例如 tarena user # 对一张表做完全备份
-B 库名 例如 -B tarena # 备份一个库下的所有表
-B 库名1 库名1 例如 -B tarena mysql # 一起备份多个库的所有表
-A 或 --all-databases # 备份一台服务器上的所有数据

恢复命令格式

mysql -uroot -p密码 [库名]< /备份目录名/文件名.sql # 单张表必须写库名

mysqldump 备份时会锁表使用mysqldump备份文件恢复数据的时候也会锁表
锁表就是给表加锁，无论对表做select访问还是insert访问，都不允许
备份或恢复数据操作完成后会自动释放锁
所以 mysqldump备份时，要赶在访问量少的时候去备份

使用binlog日志文件实现对数据的时时备份‘

介绍：二进制日志 mysql服务日志文件的一种（错误日志文件、查询日志文件、man查询日志文件、binlog日志文件）
可用于数据的备份和恢复配置mysql主从同步的必要条件。
错误日志：

vim /etc/my.cnf
[mysqld]
log-error=/var/log/mysqld.log

binlog日志文件记录数据发生改变的

启用日志下图是没启用的

vim /etc/my.cnf
server_id=50 # 区分不同主机
log_bin

自定义日志文件位置

vim /etc/my.cnf
server_id=50 # 区分不同主机
# log_bin
log-bin=/mylog/db50 # 指定日志目录及名称

查看日志文件的内容

vim /mylog/db50.000001
mysqlbinlog /mylog/db50.000001

与binlog日志相关的管理命令
手动创建新的日志文件（默认是日志文件大于1G）

# 刷新日志
flush logs;
等效于
mysql -uroot -pNSD2107...a -e 'flush logs'

在这里插入图片描述
只要重启服务就会生成新的日志文件

systemctl restart mysqld
mysqldump -uroot -pNSD2107...a --flush-log -B tarena > /root/tarena.sql

删除已有的binlog日志文件
	使用sql命令

show master status; # 查看正在使用的日志
show binary logs; # 查看已有的日志文件
purge master logs to "db50.000005"; #  删除日志文件

使用日志恢复数据

使用日志恢复所有数据（所有日志文件命令）

mysqlbinlog /目录名/日志文件名 | mysql -uroot -p密码

使用日志恢复部分数据（执行日志文件记录的指定访问的sql命令）

mysqlbinlog 选项 /目录名/日志文件名 | mysql -uroot -p 密码
# 时间范围选项
起始时间 --start-datetime=""
结束时间 --stop-datetime=""
# 偏移量范围选项
起始偏移量 --start-position=偏移量数字
结束偏移量 --stop-position=偏移量数字

# 查看日志文件当前格式
show variables like "binlog_format";

在这里插入图片描述

修改日志文件记录sql命令的格式

vim /etc/my.cnf
[mysql]
server_id=50
log_bin=db50
binlog_format="mixed" # 指定日志格式

实验环境

mysql> reset master;
mysql> show master status;
create database db51;
create table db51.user(name char(5),class char(7));
insert into db51.user values("a","b");
insert into db51.user values("a","b");
insert into db51.user values("a","b");

# 查看日志
show master status;

使用binlog日志文件恢复数据
在这里插入图片描述

mysql -uroot -pNSD2107...a -e "select * from db51.user"

准备环境

show master status;
delete from db51.user;
select * from db51.user;
show master status;
insert into db51.user values("f","f");
insert into db51.user values("e","e");
show master status;

------------
scp /mylog/db50.000001 root@192.168.4.51:/opt/
在host51执行日志里 记录的指定范围的命令 恢复数据
	第一步 查看要执行的sql对应起始时间或起始偏移量
	mysqlbinlog /opt/db50.000001
	第二步 指定偏移量的访问 恢复数据
	mysqlbinlog --start-position=2019 --stop-position=2435 /opt/db50.000001 | mysql -uroot -pNSD2107...a
	第三步 查看数据是否恢复成功
在host50 使用binlog日志恢复删除的数据

mysql > show binlog events in "db50.000001";

在这里插入图片描述

innobackupex 对数据做备份和恢复

在线热备不锁表适合生产环境下备份业务。恢复数据时必须清空数据库目录（rm -rf /var/lib/mysql/*）
安装

yum -y install libev percona-xtrabackup

数据完全备份与恢复（对数据安全备份也不锁表）

# 备份数据命令格式
innobackupex -uroot -p密码 /备份目录名 --no-timestamp 
--np-timestamp 可选项 不用系统的日期做存放备份文件的目录名
准备恢复数据
	innobackupex --apply-log /备份目录名
拷贝数据
	innobackupex --copy-back /备份目录名
# 恢复数据命令操作
1.停止数据库服务
2.清空数据库目录
3.准备恢复数据
4.拷贝数据
5.修改数据库目录的所有者所属组为mysql
6.启动数据库服务
7.管理员root用户登陆服务查看数据

实验

mysql > create table db51.user2 select * from db51.user;
mysql > db51;
mysql > show tables 

# innobackupex -uroot -pNSD2017...a /allbak --no-timestamp

在这里插入图片描述

# 恢复数据命令操作
1.停止数据库服务
systemctl stop mysqld
2.清空数据库目录
rm -rf /var/lib/mysql/*
3.准备恢复数据
innobackupex --apply-log /allbak/
4.拷贝数据
innobackupex --copy-back /allbak/
5.修改数据库目录的所有者所属组为mysql
chown -R mysql:mysql /var/lib/mysql
6.启动数据库服务
systemctl start mysqld
7.管理员root用户登陆服务查看数据
mysql -uroot -pxxx -e 'select * from db51.'

使用完全备份文件恢复一张表的数据
在这里插入图片描述

删除表空间（表名.ibd 用来存放表记录的文件）
就是吧数据库目录下没有记录的表名.idb文件删除
```
alter table 库名.表名 discard tablespace;
```
导出表信息 (生成备份目录下备份数据的信息文件)

innobackupex --apply-log --export 数据库完全备份目录

拷贝表心中文件到数据库目录下
修改表信息文件的所有者及组用户

chown mysql:mysql /var/lib/mysql/db51/user2.*

导入表空间

alter table db51.user2 import tablespace;

rm -rf /var/lib/mysql/db51/user2.cfg
rm -rf /var/lib/mysql/db51/user2.exp

查看表记录

select * from db51.user2;

增量备份与恢复

增量备份是备份上次备份后新产生的数据，所以在执行增量备份之前，必须得先有一次备份
增量备份数据命令格式

]# innobackupex -uroot -p密码 --incremental /目录名 --incremental-basedir=/目录名 --no-timestamp
--incremental 备份新数据并定义新数据存放的目录名
--incremental-basedir 备份新数据参考的备份目录名

增量恢复数据命令格式

--incremental-dir 增量备份数据存放的目录名
innobackupex --apply-log --redo-only /首次备份目录名 --incremental-dir=/目录名 # 准备恢复数据

使用增量备份文件恢复数据的具体步骤

停止数据库服务

systemctl stop mysqld

rm -rf /var/lib/mysql/*

准备恢复数据(告诉程序从什么位置开始备份)

innobackupex --apply-log --redo-only /fullbak/

合并数据（合并的次数要增量备份的次数一样并且合并的顺序也要与增量备份的顺序一致）

innobackupex --apply-log --redo-only /fullbak/ --incremental-dir=/newldir # 合并第一次增量备份数据

在这里插入图片描述
5. 恢复数据（合并后的数据拷贝到数据库目录下）

innobackupex --copy-back /fullbak

修改数据库目录的所有者和组用户为mysql

chown -R mysql:mysql /var/lib/mysql

启动服务

systemctl start mysqld

查看数据
案例
周一执行完全备份

innobackupex -uroot -pxxxx /fullbak --no-timestamp

周二执行增量备份

innobackupex -uroot -pxxx --incremental /newdir --incremental-basedir=/fullbak --no-timestamp

周三执行增量备份

innobackupex -uroot -pxxx --incremental /newdir2 --incremental-basedir=/newdir --no-timestamp
如果做差异备份 参考的目录应该是/fullbak

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库

adb