MySQL的备份和恢复
  DcpJeOZ6VzTX 2023年11月02日 16 0

binlog存在问题

1）企业中的binlog很大，位置点不好找 grep

2）企业中所有数据不会只在一个binlog中 配合全备（mysqldump）

3）想要恢复的库，中途有其他库表操作，如何只截取指定库的

binlog作用 永远只是为了恢复增量数据产生的

备份的原因

1）备份就是为了恢复。 2）尽量减少数据的丢失（公司的损失）

运维工作的核心简单概括就两件事: 

1）第一个是保护公司的数据.

2）第二个是让网站能7*24小时提供服务(用户体验)。

mysql备份的类型

冷备份: （停库，维护页，进行备份）

这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

温备份: （不停库，但是备份时锁表）

这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

热备份: （不停库，不锁表，直接备份）

这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

拓展：

rsync：

1）冷备：停机，停服务进行备份

2）热备：不停机，不停服务，直接备份

备份的方式

逻辑备份

基于SQL语句的备份

  • binlog
  • mysqldump
  • into outfile
mysql> select * from world.city into outfile '/tmp/world_city.data';
可以cat查看/tmp/world_city.data
  • replication

MySQL的备份和恢复_MySQL备份

物理备份

基于数据文件进行备份
-直接打包datadir
	# 停库打包数据目录
	# scp给另一台
	# 解压，授权为mysql权限
	
-Xtrabckup（percona）

备份策略

一般公司是每天一次全备，每小时一次增备 全量备份 增量备份 差异备份

MySQL逻辑备份工具mysqldump

-u:指定用户
-p：指定密码
-h：指定主机
-s：指定scoket文件
-P：指定端口

所有库备份

# -A:备份所有库
# --all-databases:备份所有库

[root@db01 data]# mysqldump -uroot -p123 -A > /tmp/full.sql

## 备份文件中注释
-- 注释内容
/* 注释内容 */

## MySQL恢复数据，是覆盖形式的
drop table
create table
insert
（想要看备份出来的内容可以vim看，cat不便捷）

MySQL的备份和恢复_MySQL备份_02

备份单个库

# -B 备份单个库，指定库名（备份库和表，可以备份库和表，但不能直接指定表，前面要接库）
[root@db01 ~]# mysqldump -uroot -p123 -B hhh > /tmp/full.sql
# -B 备份多个库
[root@db01 ~]# mysqldump -uroot -p123 -B 库名1 库名2 库名3 > /tmp/full2.sql

# 不加选项直接指定库名（是备份表）
[root@db01 ~]# mysqldump -uroot -p123 hhh > /tmp/hhh.sql
# 不加选项，单表备份 [mysql库下的user表]
[root@db01 ~]# mysqldump -uroot -p123 mysql user > /tmp/mysql.sql
# 不加选项多表备份
[root@db01 ~]# mysqldump -uroot -p123 库名 表名1 表名2 > /tmp/mysql2.sql

MySQL的备份和恢复_MySQL备份_03

备份时刷新binlog

# -F:备份数据，并且刷新binlog
[root@db01 ~]# mysqldump -uroot -p123 -B mysql hhh zls test -F > /tmp/mysql4.sql
## 缺陷：有多少数据库，刷新出来多少binlog

MySQL的备份和恢复_MySQL备份_04

打点备份参数

# --master-data=0|1|2
0是关闭，是不开启打点备份的功能
1是备份一个change master语句，不被注释
2是备份一个change master语句，被注释

[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 > /tmp/full4.sql

change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='mysql-bin.000013',
master_log_pos=13138;

MySQL的备份和恢复_MySQL备份_05

快照备份

--single-transaction：不锁表备份，需要指定一个快照，否则永远备份不完
[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /tmp/full_hot.sql

拓展选项

-R:备份MySQL的函数
--triggers：备份MySQL的触发器

----下面这俩千万不要用----
-d：只备份表结构
-t：只备份数据

备份语句

进阶：
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full_hot.sql
||
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_hot.sql.gz
||
#全备终极版本:#
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_$(date +%F).sql.gz

mysqldump的恢复

# 需要恢复的数据截取，导出之后开始恢复

# 先进入数据库，不记录二进制日志
mysql> set sql_log_bin=0;
#库内恢复操作
mysql> source /backup/full.sql
# 打开记录二进制日志
mysql> set sql_log_bin=1;

#库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql

注意：
1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）
3）mysqldump是以覆盖的形式恢复数据的


物理备份

percona官网：https://www.percona.com

全量备份

增量备份

差异备份


安装xtrabackup

# 安装包在qq里
[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

# 官网下载再yum
[root@db01 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

# 直接yum安装官网下载的URL
[root@db01 ~]# yum install -y https://downloads.percona.com/downloads/PerconaXtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

xtrabackup是老版本命令：会锁表（温备）
innobackupex是新版本命令：针对innodb存储引擎进行备份，不会锁表（热备）

备份方式（物理备份）
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份时读取配置文件/etc/my.cnf

inobackupex命令基础选项

命令：innobackupex
选项：
--user：指定用户名
--password：指定密码
--socket：指定socket文件
--host：指定主机IP
--port：指定端口

--apply-log：模拟CSR
--copy-back：恢复数据

--incremental：开启增量备份
--incremental-basedir：指定上一次备份的目录
--no-timestamp：不需要时间戳
--redo-only：只做redo

innobackupex全备

# 全备（需要在配置文件指定socket，然后重启数据库）
[root@db01 backup]# innobackupex --user=root --password=123 /backup/

# 全备目录下的一些文件
[root@db01 backup]# ll /backup/2023-08-03_08-53-33/
-rw-r----- 1 root root       21 Aug  3 08:53 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Aug  3 08:53 xtrabackup_checkpoints
-rw-r----- 1 root root      464 Aug  3 08:53 xtrabackup_info
-rw-r----- 1 root root     2560 Aug  3 08:53 xtrabackup_logfile      // redo log


[root@db01 2023-08-03_08-53-33]# cat xtrabackup_binlog_info    （ 类似于--master-data=2 打点文件）
mysql-bin.000004        120



undo：原数据
commit：标签
lsn：日志版本号
txid：事务提交号

[root@db01 2023-08-03_08-53-33]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1730489
last_lsn = 1730489
compact = 0
recover_binlog_info = 0


[root@db01 2023-08-03_08-53-33]# cat xtrabackup_info 
uuid = 2e9db7fd-3198-11ee-9a9b-000c2923970d
name = 
tool_name = innobackupex
tool_command = --user=root --password=... /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.50-log
start_time = 2023-08-03 08:53:36
end_time = 2023-08-03 08:53:38
lock_time = 0
binlog_pos = filename 'mysql-bin.000004', position '120'
innodb_from_lsn = 0
innodb_to_lsn = 1730489
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

全备恢复

# 全备恢复
1）前提：被恢复的目录是空的        (把数据目录移走)
2）前提：被恢复的数据库的实例是关闭的  停库恢复

## 1.先停库
[root@db01 ~]# /etc/init.d/mysqld stop

## 2.清空被恢复的目录(实际生产中最好是把它移走，不然binlog会被删掉)
[root@db01 ~]# mv /app/mysql/data /tmp/data

# 3.先手动CSR，将redo中的数据，重做一遍，然后将undo中的数据，回滚一遍
[root@db01 ~]# innobackupex --apply-log /backup/2023-08-03_08-53-33/

# 4.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/2023-08-03_08-53-33/

# 5.授权
[root@db01 backup]# chown -R mysql.mysql /app/mysql/data

innobackupex增量备份

###模拟生产中如何全备###
模拟情景：每天一次全备，每小时一次增备（比如说在2023-08-03上午十点（这里的第四次增备）某个库被删了）
# 1.全备 （全部过程中会自动生成/backup/full-2023-08-03目录）
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full-$(date +%F)
[root@db01 backup]# cat full-2023-08-03/xtrabackup_checkpoints
[root@db01 backup]# cat full-2023-08-03/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 56926023
last_lsn = 56926023
compact = 0
recover_binlog_info = 0


# 2.第一次增量备份
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-2023-08-03/ /backup/inc1-$(date +%F-%H)
[root@db01 backup]# cat inc1-2023-08-03-15/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 56926023
to_lsn = 56942921
last_lsn = 56942921
compact = 0
recover_binlog_info = 0

--------这个时间段插入了表hhh-------------

# 3.第二次增备（如果不想在命令行加--user和--password就加在配置文件里的[client]下指定，这个不用重启数据库）
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc1-2023-08-03-15/ /backup/inc2-$(date +%F-%H)
[root@db01 backup]# cat inc2-2023-08-03-15/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 56942921
to_lsn = 57029035
last_lsn = 57029326
compact = 0
recover_binlog_info = 0


# 4.第三次增备
[root@db01 backup]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc2-2023-08-03-15/ /backup/inc3-$(date +%F-%H)
[root@db01 backup]# cat inc3-2023-08-03-15/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 57029035
to_lsn = 57035013
last_lsn = 57035013
compact = 0
recover_binlog_info = 0
------------这个时间段删了库---------------

xtrabackup增量备份恢复

1）基于上一次备份进行增量 2）增量备份无法单独恢复，必须基于全备进行恢复 3）所有增量必须要按顺序合并到全备当中

------#恢复----------------

# 1.停库
[root@db01 backup]# /etc/init.d/mysqld stop

# 2.清空data目录
[root@db01 ~]# mv /app/mysql/data /tmp/data

# 3.先模拟CSR
1）全备模拟CSR，只做redo，不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full-2023-08-03/
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_checkpoints 
backup_type = log-applied（状态发生了改变）
from_lsn = 0
to_lsn = 56926023
last_lsn = 56926023
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_binlog_info 
mysql-bin.000003	1355



2）第一次增备合并到全备的时候，只做redo，不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1-2023-08-03-15/ /backup/full-2023-08-03/
[root@db01 backup]# cat /backup/inc1-2023-08-03-15/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 56926023
to_lsn = 56942921
last_lsn = 56942921
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/inc1-2023-08-03-15/xtrabackup_binlog_info 
mysql-bin.000003	5955


3）第二次增备合并到全备的时候，只做redo，不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2-2023-08-03-15/ /backup/full-2023-08-03/
[root@db01 backup]# cat inc2-2023-08-03-15/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 56942921
to_lsn = 57029035
last_lsn = 57029326
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat inc2-2023-08-03-15/xtrabackup_binlog_info 
mysql-bin.000003	45070

4）第三次增备（最后一次增备）合并到全备的时候，redo和undo都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3-2023-08-03-15/ /backup/full-2023-08-03/
[root@db01 backup]# cat inc3-2023-08-03-16/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 57029035
to_lsn = 57035013
last_lsn = 57035013
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat inc3-2023-08-03-16/xtrabackup_binlog_info 
mysql-bin.000003	48750


5）最后全部合并完成后，把全备redo和undo都做一遍
[root@db01 backup]# innobackupex --apply-log /backup/full-2023-08-03/
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 57035013
last_lsn = 57035013
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/full-2023-08-03/xtrabackup_binlog_info 
mysql-bin.000003	48750


# 4.恢复数据
[root@db01 backup]# innobackupex --copy-back /backup/full-2023-08-03/

# 5.给数据目录授权
[root@db01 backup]# chown mysql.mysql -R /app/mysql/data

# 6.重启数据库
[root@db01 backup]# /etc/init.d/mysqld restart 

# 7.截取binlog
1）全备恢复后——drop database prod之前
起始位置点：48750
[root@db01 backup]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb 
mysql-bin.000003	48750
结束位置点：57119
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv /opt/data/mysql-bin.000003|grep -i 'drop database prod' -C 10
截取binlog
[root@db01 data]# mysqlbinlog --start-position=48750 --stop-position=57119 /opt/data/mysql-bin.000003 >/tmp/qb.sql

2）drop database prod之后——binlog结尾（停库之前）

# 在新环境里恢复
把截取的binlog导到新环境
-#进入数据库先关binlog
mysql> set sql_log_bin=1;
-#开始恢复
mysql> source /tmp/qb.sql
-#打开binlog
mysql> set sql_log_bin=0;
-在导回老库，或改代码

使用 Xtrabackup + 增量 + binlog

在企业中全备备份的名字可以自己根据公司备份次数要求去命名，一天备份一次或是一小时备份一次就是full_$(date +%F)或full_(date +%F-%H)

innobackupex差异备份

基于全备新增数据进行备份

###模拟生产中如何差异备份###
# 1.全备
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full
[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 57059063
last_lsn = 57059063
compact = 0
recover_binlog_info = 0


# 2.第一次差异备份
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi1-$(date +%F-%H)
[root@db01 backup]# cat chayi1-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 57059063
to_lsn = 57066393
last_lsn = 57066683
compact = 0
recover_binlog_info = 0

# 3.第二次差异备份
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi2-$(date +%F-%H)
[root@db01 backup]# cat chayi2-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 57059063
to_lsn = 57086385
last_lsn = 57086385
compact = 0
recover_binlog_info = 0

# 4.第三次差异备份
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/chayi3-$(date +%F-%H)
[root@db01 backup]# cat chayi3-2023-08-03-17/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 57059063
to_lsn = 57091193
last_lsn = 57091193
compact = 0
recover_binlog_info = 0



------#恢复----------------
## 恢复数据
[root@db01 backup]# /etc/init.d/mysqld stop
[root@db01 backup]# mv /app/mysql/data /opt/data

## 模拟CSR
1）全备只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full/
[root@db01 backup]# cat full/xtrabackup_checkpoints 
backup_type = log-applied   (状态变了)
from_lsn = 0
to_lsn = 57059063
last_lsn = 57059063
compact = 0
recover_binlog_info = 0


2）将最后一次差异备份合并到全备中，redo和undo都做
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/chayi3-2023-08-03-17/ /backup/full/

3）全备redo和undo都做
[root@db01 backup]# innobackupex --apply-log /backup/full/


# 恢复
[root@db01 backup]# innobackupex --copy-back /backup/full/

# 授权
[root@db01 backup]# chown -R mysql.mysql /app/mysql/data

# 截取binlog
[root@db01 data]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb 
mysql-bin.000001	14426


[root@db01 backup]# mysqlbinlog --base64-output=decode-rows /opt/data/mysql-bin.000001 |grep -i 'drop database prod' -C 5
46350

[root@db01 backup]# mysqlbinlog --start-position=14426 --stop-position=46350 /opt/data/mysql-bin.000001 > /tmp/chayi.sql


# 在新环境里恢复
把截取的binlog导到新环境
-#进入数据库先关binlog
mysql> set sql_log_bin=1;
-#开始恢复
mysql> source /tmp/chayi.sql
-#打开binlog
mysql> set sql_log_bin=0;
-在导回老库，或改代码
【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载，内容版权归原作者所有。本网站的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@moduyun.com
上一篇： MySQL主从复制 下一篇： MySQL存储引擎
  1. 分享：
最后一次编辑于 2023年11月08日 0

暂无评论

推荐阅读
  DcpJeOZ6VzTX   2023年11月02日   17   0   0 MySQL备份
DcpJeOZ6VzTX
DcpJeOZ6VzTX
作者其他文章 更多
Docker安装和部署

2023-11-13

Docker镜像操作

2023-11-13

Docker图形化界面

2023-11-13

Docker容器操作

2023-11-13

手动制作镜像

2023-11-13

Docker自动构建镜像

2023-11-13

Dockerfile起zabibix、wordpress、CICD

2023-11-13

Docker网络

2023-11-13

Harbor

2023-11-13

单机编排工具:docker-compose

2023-11-13

最新推荐 更多
docker 工作目录下overlay目录清理办法

2023-12-23

docker mount 挂载文件夹

2023-12-23

docker kafa 挂载

2023-12-23

docker install mysql5

2023-12-23

docker image 下载

2023-12-23

docker idea 插件如何使用

2023-12-23

docker file yum 模块

2023-12-23

docker expose配置多个端口

2023-12-23

docker desktop 设置网络

2023-12-23

docker desktop Mac界面中文

2023-12-23

docker daemon 重启

2023-12-23

docker container 添加hosts

2023-12-23

docker compose 运行 jar

2023-12-23

docker build npm install

2023-12-23

docker alpine怎么启动

2023-12-23

cuda 宿主机 docker

2023-12-23

centos docker 安装pip

2023-12-23

centos docker command not found

2023-12-23

centos8 dockercompose zabbix

2023-12-23

apache activemq docker

2023-12-23

常用标签
数据数据数据java数据linuxlinuxpythonCentOSredis区块链技术区块链字符串Java信息系统MySQL资讯python项目管理mysql教程MySQL