mysql-5.7 备份与恢复篇

最新推荐文章于 2025-05-01 21:28:39 发布

weixin_47647077

最新推荐文章于 2025-05-01 21:28:39 发布

阅读量1.3k

点赞数 1

分类专栏： sk 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_47647077/article/details/118327562

版权

sk 专栏收录该内容

21 篇文章

订阅专栏

3.4、mysqldump（逻辑备份使用）

3.5、实现所有表的单独备份

3.6、模拟故障案例并恢复--mysqldump（重点）

3.6.1、数据目录全被删除恢复操作

3.6.2、单表被删除恢复操作

3.7、模拟故障案例并恢复--xtrabackup（重点）

3.7.1、xtrabackup安装

3.7.2、xtrabackup 介绍

3.7.3、xtrabackup的命令innobackupex使用

3.7.4、xtrabackup之备份、增量、恢复演示（重点）

1、备份设计

1）全备、增量、自动备份时间

2）备份巡检、备份是否存在与否、备份空间足够与否

3）定期演练备份恢复、季度或半年（要在测试库演练）

4）故障恢复（通过现有备份恢复数据）

5）迁移

2、备份类型

1）热备

在数据库正常业务时备份数据（只能是innodb）

2）温备

锁表备份，只能查询不能修改表（myisam）

3）冷备

停止数据库业务情况下备份数据

3、备份方式及工具介绍

3.1、备份工具

1）逻辑备份工具

基于SQL语句进行备份
mysqldump       *****
mysqlbinlog     *****

2）物理备份工具

基于磁盘数据文件备份
xtrabackup(XBK) ：percona 第三方   *****
MySQL Enterprise Backup（MEB）

3.2、逻辑备份有物理备份比较

1）mysqldump逻辑备份

优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间

缺点：
4.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB  =1024 PB =1000000 TB

2）xtrabackup(XBK)

优点：
1.类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
2.可读性差
3.压缩比低，需要更多磁盘空间
建议：
>100G<TB

3.3、备份策略

备份方式：
全备:全库备份，备份所有数据
增量:备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
备份周期:
根据数据量设计备份周期
比如：周日全备，周1-周6增量

3.4、mysqldump（逻辑备份使用）

1）通用参数：

-u  -p   -S   -h  -P    
本地备份:
mysqldump -uroot -p  -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p  -h 10.0.0.51 -P3306

2）常用参数：

1）导出全部数据库（--all-databases  , -A） 
     mysqldump -uroot -p -S /tmp/mysql.sock  -A >./full.sql
补充：（--set-gtid-purged=OFF和--set-gtid-purged=ON）
如果一个数据库开启了GTID，使用mysqldump备份的时候或者说是转储的时候，即使不是MySQL全库(所有库)备份，也会备份整个数据库所有的GTID号。
GTID是为了加强数据库的主备一致性、故障恢复和容错能力，mysqldump备份整个数据库用来做从库的话，那么GTID是必须的(一个MySQL主从复制是开启了GTID的场景下)。
但是如果仅仅是备份单个库或者是导入单个库到其它的数据库(也是开启了GTID)，那么GTID号有重复概率(GTID由UUID+顺序事务ID组成)，所以如果想在数据导入的时候不想导入另外一个数据库全部的GTID，那么可以使用`--set-gtid-purged=OFF`来禁止。

2）导出几个数据库、参数后面加数据库名（--databases,  -B）
    mysqldump -uroot -p -B world sk --set-gtid-purged=OFF >./test.sql

3）world数据库下的city,country表
    mysqldump -uroot -p world city country >./bak1.sql
    以上备份恢复时:必须库事先存在,并且ues才能source恢复
4）进入mysql命令模式后用source恢复数据表的使用
    mysql>use world;
    mysql>source 
    mysql> source /home/sk/mysqldump/student.sql

3）特殊参数：

1） -R            备份存储过程及函数
    --triggers  备份触发器
    -E             备份事件
    mysqldump -uroot -p -A -R -E --triggers >./full.sql

2）--flush-logs、-F  在备份开始时,刷新一个新binlog日志
    mysqldump -uroot -p  -A  -R --triggers -F >./full.sql

3)--master-data=2 
    以注释的形式,保存备份开始时间点的binlog的状态信息
    mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/back/world.sql
    [root@db01 ~]# grep 'CHANGE' /backup/world.sql 
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;
    功能：
    （1）在备份时，会自动记录，二进制日志文件名和位置号
    0 默认值
    1  以change master to命令形式，可以用作主从复制
    2  以注释的形式记录，备份时刻的文件名+postion号
    （2） 自动锁表
    （3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是    实现快照备份。

4)--single-transaction
    innodb 存储引擎开启热备(快照备份)功能       
    master-data可以自动加锁
    在不加--single-transaction ，启动所有表的温备份，所有表都锁定
    加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能
      备份必加参数
    mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-        gtid-purged=OFF >/data/backup/full.sql

5）--set-gtid-purged=auto（不了解的就尽量不要用）
    auto , on
    off 
    使用场景:
    1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
    mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
    2. auto , on:在构建主从复制环境时需要的参数配置
    mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

6）--max-allowed-packet=#（客户端备份大表需要这个参数）
    mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

     --max-allowed-packet=# 
    The maximum packet length to send to or receive from server.