AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL 备份总结

2022-05-26 379
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
日志服务 SLS，月写入数据量 50GB 1个月
简介： 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 版总结
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
rqbarzitsudte
目录
相关文章
源码星辰
|
3月前
|
存储 关系型数据库 MySQL
mysql数据库备份与恢复
MySQL数据库的备份与恢复是确保数据安全性和业务连续性的关键操作。
源码星辰
117 4
潇洒洒
|
1月前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下，实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
潇洒洒
95 3
最好zzz
|
1月前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下，MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务，我们可以每天自动执行数据库备份，从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
最好zzz
45 3
以山向海
|
6月前
|
存储 SQL 关系型数据库
关系型数据库mysql二进制日志辅助备份
【6月更文挑战第21天】
以山向海
143 2
sunrr
|
2月前
|
安全 关系型数据库 MySQL
MySQL用户备份
【10月更文挑战第2天】MySQL用户备份
sunrr
64 3
sunrr
|
2月前
|
存储 关系型数据库 MySQL
MySQL如何备份？
【10月更文挑战第2天】MySQL如何备份？
sunrr
149 3
游客mldfis24krfue
|
4月前
|
SQL 关系型数据库 MySQL
在Linux中，如何备份和恢复MySQL数据库？
在Linux中，如何备份和恢复MySQL数据库？
游客mldfis24krfue
395 2
hh真是个慢性子
|
4月前
|
SQL 数据可视化 关系型数据库
MySQL 备份可视化巡检系统
MySQL 备份可视化巡检系统
hh真是个慢性子
70 1
hh真是个慢性子
|
4月前
|
SQL 存储 关系型数据库
MySQL备份：mydumper 备份恢复工具生产实战
MySQL备份：mydumper 备份恢复工具生产实战
hh真是个慢性子
146 1
土木林森
|
4月前
|
安全 关系型数据库 MySQL
揭秘MySQL海量数据迁移终极秘籍：从逻辑备份到物理复制，解锁大数据迁移的高效与安全之道
【8月更文挑战第2天】MySQL数据量很大的数据库迁移最优方案
土木林森
781 17

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    如何在openGauss/PostgreSQL手动清理XLOG/WAL 文件?
  • 4
    MySql进阶使用之视图、索引（详解）
  • 5
    Reactive Spring实战——响应式MySql交互
  • 6
    mysql中的时间函数---运维常用
  • 7
    Linux命令：MySQL系列之四--MySQL管理创建CREATE表和索引
  • 8
    mysql--索引 （查询）
  • 9
    Mysql5.7使用DTS增量同步数据到MaxCompute
  • 10
    mysql索引优化
  • 1
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    81
  • 2
    Linux对MySQL语法校验
    90
  • 3
    【MySQL系列笔记】分库分表
    214
  • 4
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    163
  • 5
    服务器Linux系统配置mysql数据库主从自动备份
    215
  • 6
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    420
  • 7
    golang使用gorm操作mysql3，数据查询
    97
  • 8
    golang使用gorm操作mysql2
    67
  • 9
    golang使用gorm操作mysql1
    115
  • 10
    Hbase与MySQL对比，区别是什么？
    309

    • 相关课程

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

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • mongodb
  • mariadb
    • 下一篇
    DataWorks