备案控制台
探索云世界
开发者社区 数据库 文章 正文

MySQL 备份总结

2022-05-26 324
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： MySQL 备份总结

MySQL 备份总结


一、BinLog 二进制日志


1.BinLog 日志内容

2.BinLog 日志文件的表现形式

3.BinLog 日志文件查看相关 MySQL 命令

4.MySQL 二进制文件读取工具

5.二进制日志格式

6.备份类型

7.根据备份整个数据还是变化数据

8.MySQL 最常用的三种备份工具


二、使用 LVM-Snapshot 工具备份


1.准备 LVM 卷，并将 MySQL 数据迁移到 LVM 卷上


三、使用 XtraBackup 工具备份


1.原理

2.安装 XtraBackup 工具

3.完全备份与恢复

4.增量备份与恢复


一、BinLog 二进制日志


1.BinLog 日志内容


  1. 引起 MySQL 服务器改变的任何操作（增 删 改）
  2. 复制功能依赖于此日志（MySQL 主从）
  3. Slave 服务器通过 Master 服务器的二进制日志完成主从复制，在执行之前保持于中继日志 relay log 中。
  4. Slave 服务器通常可以关闭二进制日志以提升性能。


2.BinLog 日志文件的表现形式


1）默认在 /usr/local/mysql/data/ 目录下，存在 mysql-bin.000001 mysql-bin.000002 的二进制文件


[root@MySQL ~]# sed -i '/\[mysqld]/a log-bin=mysql-bin' /etc/my.cnf
[root@MySQL ~]# sed -i '/\[mysqld]/a server-id=1' /etc/my.cnf
[root@MySQL ~]# head -3 /etc/my.cnf


image.png


什么时候二进制日志会重新生成：


  1. 重启 MySQL 服务：systemctl restart mysqld
  2. 在 MySQL 数据库中：flush logs;
  3. 根据 max_binlog_size 设置 MySQL 二进制文件最大值，达到最高值会自动创建。


[root@MySQL ~]# systemctl restart mysqld
[root@MySQL ~]# ls -l /usr/local/mysql/data/ | grep 'mysql-bin'


image.png


  • 注意：如果需要删除二进制日志时，切勿直接删除二进制文件，这样会使 MySQL 管理混乱。


3.BinLog 日志文件查看相关 MySQL 命令


1）查看正在使用的二进制日志文件


mysql> show master status;


image.png


2）手动滚动二进制日志文件


mysql> show binary logs;


image.png


3）显示所有的二进制日志文件


mysql> show binary logs;

image.png


4.MySQL 二进制文件读取工具


格式：mysqlbinlog [参数] log-files


有以下四种参数选择：


  • --start-position：用来指定二进制日志文件的起始位置；--stop-position：用来指定二进制日志文件的结束位置。
  • --start-datetime：用来指定二进制日志文件的起始日期；--stop-datetime：用来指定二进制日志文件的结束日期。
  • 使用 mysqlbinlog 工具来对 MySQL 进行增量备份：MySQL 增量备份与恢复


5.二进制日志格式


  • 由 bin_log_format={ statement | row | mixed } 定义（5.7 版本之前是 statement，5.7 版本之后是 row）


1）statement：


  • 基于语句，记录生成数据的语句。
  • 缺点：如果当插入信息为函数时，有可能不同时间点执行结果不一样。如 insert into test values(CURRENT_DATE())


2）row：


  • 基于行数据，缺点在于，有时候数据量会过大。


3）mixed：


  • 混合模式，由 MySQL 自行决定何时使用 statement 和 row 模式。


查看当前二进制日志记录格式


mysql> show variables like 'binlog_format';


image.png


  • 如果想要永久修改需：sed -i '/\[mysqld]/a binlog_format=模式' /etc/my.cnf 来进行修改。


6.备份类型


1）根据备份时，MySQL 服务是否在线


  • 冷备：停掉 MySQL 这个服务，读写操作都不能进行。
  • 温备：全局施加共享锁，只能读不能写。
  • 热备：不停掉 MySQL 这个服务，读写照样进行。


2）根据备份时的接口


  • 物理备份（Physical Backup）：直接复制数据文件，打包归档。简单来说就是对 MySQL 的数据存放路径进行打包。
  • 逻辑备份（Logical Backup）：把数据抽取出来保存在 SQL 脚本中，mysqldump 就属于逻辑备份。


各自特点：


  • 物理：不需要额外工具，直接归档命令即可，但是跨平台能力比较差；如果数据量超过几十个 G，则适用于物理备份。
  • 逻辑：导入方便，直接读取 SQL 语句即可；逻辑备份恢复时间慢，占用空间大；无法保证浮点数的精度；恢复完数据库后需要重建索引。


7.根据备份整个数据还是变化数据


1）完全备份：每次对数据进行完整的备份，即对整个数据库的备份（是差异备份与增量备份的基础）


  • 优点：备份与恢复操作简单方便。
  • 缺点：数据存在大量的重复；占用大量的空间；备份与恢复时间长。


2）差异备份：


  • 备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完全备份起，备份数据量越来越大。
  • 恢复数据时，只需要恢复上次的完全备份与最近的一次差异备份。


3）增量备份：只有那些在上次完全备份或增量备份后被修改的文件才会被备份。


  • 优点：占用空间小，备份速度快。
  • 缺点：如果中间某次的备份数据损坏，将导致数据丢失。


8.MySQL 最常用的三种备份工具


1）mysqldump：通常为数据小的情况下备份；单线程恢复比较慢。


  • InnoDB：热备，温备。
  • MyISAM，Aria：温备。


2）xtrabackup（通常用 innobackupex 工具）：备份 MySQL 大数据；属于物理备份，速度快。


  • InnoDB：热备，增量备份。
  • MyISAM：温备，不支持增量备份，只有完全备份。


3）lvm-snapshot：接近于热备的工具，因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁；


  • 使用 cp tar 等工具进行物理备份；
  • 备份和恢复速度较快。
  • 很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上尤其如此。


两者对比如下：


image.png


二、使用 LVM-Snapshot 工具备份


基于快照备份注意事项：


  1. 事务日志跟数据日志必须在同一个卷上。
  2. 创建快照卷之前，要请求 MySQL 的全局锁；在快照创建完成之后释放锁。
  3. 请求全局锁完成之后，做一次日志滚动；做二进制日志文件及位置标记（手动进行）


1.准备 LVM 卷，并将 MySQL 数据迁移到 LVM 卷上


1）创建分区


[root@localhost ~]# fdisk -l /dev/sdb


image.png


2）创建 PV、VG、LVM，并格式化


[root@localhost ~]# pvcreate /dev/sdb1 /dev/sdb2 /dev/sdb3
[root@localhost ~]# vgcreate test /dev/sdb1 /dev/sdb2 /dev/sdb3
[root@localhost ~]# lvcreate -L +10G -n zhangsan test
[root@localhost ~]# mkfs.xfs /dev/test/zhangsan


image.png


3）创建目录，并挂载逻辑磁盘


[root@localhost ~]# mkdir /var/zhangsan
[root@localhost ~]# mount /dev/test/zhangsan /var/zhangsan/
[root@localhost ~]# df -hT


image.png


4）确认服务处于关闭状态，然后将数据迁移到 /var/zhangsan


[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# tar -cf - . | tar xf - -C /var/zhangsan/


5）重新挂载 zhangsan 到 MySQL 数据库的主目录 /usr/local/mysql/data


[root@localhost ~]# umount /var/zhangsan/
[root@localhost ~]# mount /dev/test/zhangsan /usr/local/mysql/data/


6）审核权限并启动服务


[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p123123
mysql> show master status\G
mysql> flush logs;
mysql> flush tables with read lock;
[root@localhost ~]# lvcreate -L +4G -s -n mysql /dev/test/zhangsan 
[root@localhost ~]# mysql -uroot -p123123 -e "unlock tables;"


7）开启 MySQL 的二进制日志，并重启 MySQL 服务


[root@localhost ~]# sed -i '/\[mysqld]/a log-bin=mysql-bin' /etc/my.cnf
[root@localhost ~]# sed -i '/\[mysqld]/a server-id=1' /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld


mysql> create database Coco;
mysql> use Coco;
mysql> create table test(ID int);
mysql> insert into test values(1),(2),(3);
[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/


模拟数据库故障


[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# umount /dev/test/zhangsan


恢复


[root@localhost ~]# mount /dev/test/zhangsan /usr/local/mysql/data/
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p123123 -e "select * from Coco.test;"


image.png


三、使用 XtraBackup 工具备份


Percona XtraBackup（简称 PXB）是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQL、Percona Server 和 MariaDB，并且全部开源。


1.原理


  • 通信方式：2 个工具之间的交互和协调是通过控制文件的创建和删除来实现的。
  • 主要文件有：xtrabackup_suspended_1 xtrabackup_suspended_2 xtrabackup_log_copied


备份时 xtrabackup_suspended_2 协调这两个工具进程过程如下：


  1. ib 在启动 xb 进程后，会一直等 xb 备份完 InnoDB 文件，方式就是等待 xb_suspended_2 这个文件被创建出来。
  2. xb 在备完 InnoDB 数据后，就在指定目录下创建出这个文件，然后等这个文件被 ib 删除。
  3. ib 检测到文件 xb_suspended_2 被创建出来后，就继续往下走。
  4. ib 在备份完非 InnoDB 表后，删除 xb_suspended_2 这个文件，这样就通知 xb 可以继续了，然后等待 xb_log_copied 被创建。
  5. xb 检测到 xb_suspended_2 文件删除后，就可以继续往下了。


备份过程：


  • innobackupex 在启动后，会先 fork 一个进程，来启动 xtrabackup 进程，然后等待 xtrabackup 备份完 InnoDB 数据文件；
  • 当 xtrabackup 备份完时，会通过创建文件来通知 innobackupex 进程，接着 innobackupex 进程会去备份非 InnoDB 数据文件。


image.png


  • Redo 文件：是存储引擎层（InnoDB）生成的日志，主要为了保证数据的可靠性。
  • Ibd 文件：InnoDB 引擎开启的表空间，用来存储表的数据和索引。


2.安装 XtraBackup 工具


[root@MySQL ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5
[root@MySQL ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm
[root@MySQL ~]# tar xf percona-xtrabackup2.4.5-Linux-x86_64.tar.gz -C /usr/src/
[root@MySQL ~]# cd /usr/src/percona-xtrabackup-2.4.5-Linux-x86_64/
[root@MySQL percona-xtrabackup-2.4.5-Linux-x86_64]# cp bin/* /usr/bin/


XtraBackup 中主要包含两个工具：


xtrabackup（Perl 脚本）：


  • 用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互。

innobackupex（C/C++ 编译的二进制）：


  • 用来备份非 InnoDB 表的，同时会调用 xtrabackup 命令来备份 InnoDB 表；
  • 还会和 MySQL Server 发送命令来进行交互，如加读锁 ftwrl、获取位点 show slave status 等。


简单来说就是 innobackupex 在 xtrabackup 之上做了一层封装。


image.png


3.完全备份与恢复


[root@MySQL ~]# innobackupex --user=root --password=123123 /backups


查看备份数据


[root@MySQL ~]# ls /backups/
[root@MySQL ~]# ls -l /backups/2021-05-19_22-31-36/


image.png


注解：


  • backup-my.cnf：备份用到的配置选项信息文件。
  • xtrabackup_binlog_info：MySQL 当前正在使用的二进制日志文件和此时二进制日志文件时间的位置信息文件。
  • xtrabackup_checkpoints：备份的类型、状态和 LSN 状态信息文件。
  • xtrabackup_logfile：备份的日志文件。


删除原有的数据


[root@MySQL ~]# rm -rf /usr/local/mysql/data/*


合并数据，使数据文件处于一致性的状态


[root@MySQL ~]# innobackupex --apply-log /backups/2021-05-19_22-31-36/


恢复数据


[root@MySQL ~]# innobackupex --copy-back /backups/2021-05-19_22-31-36/


[root@MySQL ~]# ls -l /usr/local/mysql/data/
[root@MySQL ~]# chown -R mysql:mysql /usr/local/mysql/data/


image.png


总结：


  1. innobackupex 完全备份，并指定备份目录路径。
  2. 在恢复前，需要使用 --apply-log 参数先进行合并数据文件，确保数据的一致性要求。
  3. 在恢复时，直接使用 --copy-back 参数进行恢复，需要注意的是，在 my.cnf 中要指定数据文件目录的路径。


4.增量备份与恢复


[root@MySQL ~]# innobackupex --user=root --password=123123 /backups


  • 注意：增量备份前提是需要有完全备份（因为上面已经做过了，所以不再配置）


[root@MySQL ~]# mysql -uroot -p123123
mysql> create database Coco;
mysql> use Coco;
mysql> create table test(ID int);
mysql> insert into test values(1),(2),(3);


使用 innobackupex 进行增量备份


[root@MySQL ~]# innobackupex --user=root --password=123123 \
--incremental /backups/ \
--incremental-basedir=/backups/2021-05-19_23-00-37/


查看备份数据


[root@MySQL ~]# ll /backups/
[root@MySQL ~]# cat /backups/2021-05-19_23-00-37/xtrabackup_checkpoints
[root@MySQL ~]# cat /backups/2021-05-19_23-23-45/xtrabackup_checkpoints


image.png


删除数据库中所有数据


[root@MySQL ~]# rm -rf /usr/local/mysql/data/*


合并全备数据目录，确保数据的一致性


[root@MySQL ~]# innobackupex --apply-log --redo-only /backups/2021-05-19_23-00-37/


将增量备份数据合并到全备数据目录当中


[root@MySQL ~]# innobackupex --apply-log --redo-only /backups/2021-05-19_23-00-37/ \
--incremental-dir=/backups/2021-05-19_23-23-45/


image.png


恢复数据


[root@MySQL ~]# innobackupex --copy-back /backups/2021-05-19_23-00-37/


[root@MySQL ~]# chown -R mysql:mysql /usr/local/mysql/data/
[root@MySQL ~]# mysql -uroot -p123123 -e "select * from Coco.test;"


image.png


总结：


  1. 增量备份需要使用 --incremental 参数来指定需要备份到哪个目录，使用 incremental-dir 参数来指定全备目录。
  2. 进行数据备份时，需要使用 --apply-log redo-only 参数来先合并全备数据目录数据，确保全备数据目录的一致性。
  3. 再将增量备份数据使用 --incremental-dir 参数来合并到全备数据当中。
  4. 最后通过全备数据来进行恢复数据，注意，如果有多个增量备份，需要逐一合并到全备数据当中，再进行恢复。


文章标签：
云数据库 RDS MySQL 版
日志服务
C++
Perl
数据采集
SQL
关系型数据库
索引
MySQL
数据库
存储
大数据
关键词：
云数据库 RDS MySQL 版备份
云数据库 RDS MySQL 版总结
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
rqbarzitsudte
目录
相关文章
warmhearted
|
3月前
|
关系型数据库 MySQL 数据库
rds备份与恢复
rds备份与恢复
warmhearted
57 3
亚丁号
|
3月前
|
关系型数据库 MySQL 数据库
Python tk dos命令备份mysql数据库
Python tk dos命令备份mysql数据库
亚丁号
25 0
疯狂的猿
|
3月前
|
存储 关系型数据库 MySQL
mysql数据库如何做到定期备份
mysql数据库如何做到定期备份
疯狂的猿
289 2
北海。
|
4月前
|
存储 关系型数据库 MySQL
MySQL库的操作『增删改查 ‖ 编码问题 ‖ 备份与恢复』
MySQL库的操作『增删改查 ‖ 编码问题 ‖ 备份与恢复』
北海。
51 0
vohelon
|
2月前
|
SQL 关系型数据库 MySQL
mysql怎么备份
mysql怎么备份
vohelon
193 7
憧憬blog
|
3月前
|
存储 关系型数据库 MySQL
利用Xtrabackup进行mysql增量备份和全量备份
利用Xtrabackup进行mysql增量备份和全量备份
憧憬blog
193 0
程序三两行
|
15天前
|
SQL 存储 关系型数据库
mysql数据库备份与恢复
mysql数据库备份与恢复
程序三两行
46 0
左边的天堂
|
2月前
|
关系型数据库 MySQL Linux
Linux环境下定时备份mysql数据库
Linux环境下定时备份mysql数据库
左边的天堂
28 0
vohelon
|
2月前
|
存储 关系型数据库 MySQL
mysql怎么备份
mysql怎么备份
vohelon
21 7
warmhearted
|
2月前
|
监控 容灾 安全
规划阿里云RDS跨区迁移并构建容灾与备份策略
规划阿里云RDS（Relational Database Service）跨区迁移并构建容灾与备份策略
warmhearted
111 2

热门文章

最新文章

  • 1
    超1/3中国500强企业都在用的「汇联易」，为什么选用阿里云RDS？
  • 2
    MySQL全局库表查询准确定位字段
  • 3
    MySQL学习（三）
  • 4
    安装Docker&镜像容器操作&使用Docker安装部署MySQL，Redis，RabbitMQ，Nacos，Seata，Minio
  • 5
    在Python Web开发过程中：数据库与缓存，MySQL和NoSQL数据库的主要差异是什么？
  • 6
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 7
    【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（8.0版本升级篇）
  • 8
    MySQL数据库，可以使用二进制日志（binary log）进行时间点恢复
  • 9
    MySQL学习（五）
  • 10
    MySQL学习（七）
  • 1
    下次老板问你MySQL如何优化时，你可以这样说，老板默默给你加工资
    16
  • 2
    一台MySQL数据库启动多个实例
    7
  • 3
    MySQL学习手册（第一部分）
    31
  • 4
    使用Navicate连接Mysql过程详解
    5
  • 5
    MySQL-Proxy实现MySQL读写分离提高并发负载
    7
  • 6
    windows安装MySQL5.7教程
    6
  • 7
    MySQL Cluster集群安装及使用
    12
  • 8
    :“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versi
    6
  • 9
    MySQL字段的时间类型该如何选择？千万数据下性能提升10%~30%🚀
    23
  • 10
    【MySQL实战笔记】07 | 行锁功过：怎么减少行锁对性能的影响？-02 死锁和死锁检测
    18

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）