MySQL 数据备份

MySQL 实现物理备份

物理备份： 在文件系统层面，直接拷贝数据库相关文件（数据文件、索引文件和日志文件等）完成备份

在对MySQL数据进行备份的时候，主要备份以下内容：

  • 用户信息： 例如MySQL的账号信息，账号的权限等。这些都是存放再mysql这个数据库中的。

  • 业务数据： 相关的业务数据库

  • 配置文件： MySQL服务的相关配置

手动拷贝数据

将mysql服务停止后，将整个数据目录进行拷贝或者打包，例如利用tar、cp、rsync等工具

例如：

sudo systemctl stop mysql.service

tar -czf /var/lib/mysql_bak.tar.gz  /var/lib/mysql

xtrabackup 工具

可以借助一些物理备份工具来实现，例如xtrabackup就可以实现MySQL的物理数据备份。

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

xtrabackup 介绍

xtrabackup 是 Percona 开发的一个开源数据库物理备份工具。主要用来执行MySQL、Percona Server 和 MariaDB 数据库的备份和恢复操作。

特点：

  1. 备份数据和恢复速度很快，支持大型数据库和需要支持灾难级备份的场景。

  2. 主要针对的就是inodb类型的数据，备份过程不会锁表，适合数据库需要7*24小时运行的场景

  3. 支持增量备份、对备份数据进行加密、压缩等功能

  4. 向后兼容性差，需要根据MySQL具体版本来选择特定版本的xtrabackup

xtrabackup向后兼容性差的原因：

xtrabackup是一个物理备份工具，通过直接操作数据库文件和日志来备份数据，这也造成了对数据库的内部结构（如数据字典、redo log 格式）高度敏感。

当这些内部结构在数据库的新版本中发生变化时，XtraBackup 需要更新以适应这些更改，所以在选择版本时候，需要先确定MySQL版本信息，在根据官方文档选择适用的版本。

官方文档：https://docs.percona.com/percona-xtrabackup/innovation-release/

版本说明：

xtrabackup 版本号由两部分组成，主版本号 + 构建版本号

  • 主版本号确定了当前版本适用于哪个大版本的MySQL

  • 此间版本号表示当前版本的发版信息

现在xtrabackup主要有三个大的版本，分别对应三个版本的MySQL

  • xtrabackup 2.4：支持MySQL5.6和MySQL5.7

  • xtrabackup 8.0：支持MySQL8.0

  • xtrabackup 8.1：支持MySQL8.1

xtrabackup 8.0版本说明

  • XtraBackup 8.0.3 和8.0.4：基于MySQL 8.0.13开发而来，其中8.0.3版本时先行版，不适用于生产环境，8.0.4是正式版本，可用于生产环境。

  • XtraBackup 8.0.5 ：2019年4月发布，引入了MySQL8.0.14的新语法，

  • XtraBackup 8.0.6：2019年9月发布，引入了对 MyRocks 的支持

  • XtraBackup 8.0.7：2019年8月发布，可以对包含加密系统表空间的数据库进行备份

  • XtraBackup 8.0.8，8.0.9，8.0.10，8.0.11：优化部分功能，修复某些BUG

  • XtraBackup 8.0.12：支持对 MySQL 8.0.20 及更高版本的数据进行备份，前面的版本不支持与 MySQL 8.0.20 及更高版本的数据库进行工作

  • XtraBackup 8.0.13：已使用最新的 MySQL 8.0.20 进行了测试，并且修复了一些BUG

  • XtraBackup 8.0.22：从这个版本开始， 用了新的版本命名规则， XtraBackup的构建版本和MySQL的次要版本号保持一致。所以从后面的版本中，需要根据mysql的版本来选择 XtraBackup 的版本。

  • XtraBackup 8.0.23，8.0.25，8.0.26：修复漏洞。优化功能

  • XtraBackup 8.0.28：优化日志记录和其它一些功能，增加了对KMS组件的支持

  • XtraBackup 8.0.29：添加了新的重做日志类型，以支持算法行为的变话，主要是针对MySQL 8.0.29 而优化的，前面的版本就不支持MySQL 8.0.29

  • XtraBackup 8.0.30：增加了对 Zstandard （ZSTD） 压缩算法的支持，压缩效率更高。优化了其它一些功能

  • XtraBackup 8.0.31：启用qpress压缩算法，zstd作为默认的压缩算法。优化了一些功能

  • XtraBackup 8.0.32，8.0.33：基于MySQL 8.0.32 开发而来，前面的版本不支持MySQL 8.0.32了。8.0.33是对8.0.32的优化。

  • XtraBackup 8.0.34，8.0.35：这个版本 允许备份MySQL 8.0.35 及更高版本。8.0.35是优化了8.0.34的部分功能，修复了一些BUG。

安装说明：

  1. 确定MySQL的版本信息

  2. 参考官方文档选择合适的XtraBackup版本

  3. 选择安装方式，可以源码安装、二进制安装还有包管理安装等。安装步骤文档写得很详细，具体参考文档。

使用 xtrabackup

实现完整备份（全备份）

XtraBackup 确实是一个物理备份工具，它直接在文件系统层面拷贝 MySQL 或 MariaDB 的数据文件。然而，它也需要与 MySQL 数据库服务器建立连接，原因如下：

  • 获取数据库配置：XtraBackup 需要知道 MySQL 的一些关键配置（如 datadir、innodb_data_home_dir、innodb_log_group_home_dir 等），这些配置信息帮助 XtraBackup 确定需要备份的文件和日志位置。

  • 锁定表：在某些情况下（MySAM），XtraBackup 需要对数据库执行读锁定，以确保在备份过程中数据的一致性。

  • 记录日志位置：XtraBackup 需要记录备份开始时的二进制日志位置，这对于点时间恢复（PITR）是必要的。

连接到 MySQL 的参数与 mysql 命令行客户端的参数选项一致。

  • --user：指定连接到 MySQL 的用户名。

  • --password：指定用户的密码。

  • --port：指定 MySQL 的端口，其默认值为 3306。

  • --host：指定 MySQL 的地址，默认为 localhost。

  • --socket 或 -S：当 host 设置为 localhost 时，可以使用这个参数指定 socket 文件的位置。

数据备份过程

例如：mysql数据目录是/var/lig/mysql,需要将MySQL的所有数据备份到/data_bak/下,备份目录以为当前日期命名。

sudo xtrabackup --backup --host="192.168.14.17" --user="root" --password="eHIGH2014" --port=3306 --datadir="/var/lib/mysql" --target-dir=/data_bak/$(date +"%F")

选项说明：

  • --datadir：指定MySQL的数据目录

  • --target-dir：指定数据的存放目录

  • --backup：表示执行一个备份操作

数据恢复过程

1、处理备份数据

在使用 XtraBackup 对 InnoDB 数据库进行备份时，InnoDB 存储引擎会继续向 redo 日志文件写入新的数据更改。因此，在备份过程结束时，备份的数据文件（即数据库的物理文件）与 redo 日志文件之间可能会存在不一致性。

这种不一致是由于在备份进行期间，数据库仍在处理新的事务所导致的。为了确保备份数据的一致性和完整性，需要对备份数据进行特殊处理，即执行 xtrabackup --prepare命令。

此过程包括以下关键步骤：

  • 重放日志：此步骤涉及将备份期间累积在 redo 日志中的所有未提交事务应用到备份数据文件中。这一步骤确保了在备份时间点上所有的数据更改都被正确应用，从而保证数据的完整性。

  • 回滚未完成的事务：对于备份过程中正在进行但未完成的事务，XtraBackup 会进行回滚操作，以维护数据库的一致性。这样可以确保恢复时的数据库状态与备份时刻的状态一致。

  • 创建或调整系统表空间文件：如果需要，XtraBackup 会创建或调整一些 InnoDB 的系统表空间文件，以确保备份的数据库在恢复时可以正常启动和运行。

例如：将备份到/data_bak/2023-12-11的备份数据进行处理

sudo xtrabackup --prepare --target-dir=/data_bak/2023-12-11
2、还原备份数据

1、停止MySQL服务

sudo systemctl stop mysql.service

2、清空MySQL数据目录

root@ubuntu:/var/lib# mv mysql mysql-bak

root@ubuntu:/var/lib# mkdir mysql

root@ubuntu:/var/lib# chown mysql:mysql mysql

3、将备份数据拷贝到MySLQ数据目录

可以直接通过 通过cp、rsync等命令手动复制备份目录下的所有文件到MySQL数据目录下。也可以通过 XtraBackup的 --copy-back 或 --move-back 选项从备份目录将数据恢复到原始 MySQL 数据目录。

sudo xtrabackup --copy-back  --target-dir=/data_bak/2023-12-11   --datadir=/var/lib/mysql

4、更改文件权限

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

5、服务启动测试

sudo systemctl start mysql.service

实现备份时压缩数据

在进行数据备份的时候，如果备份数据过大，很容易将硬盘空间沾满，xtrabackup支持在备份数据的时候将数据进行压缩，一次减少硬盘空间的占用。

前提：

  1. 不同版本使用的默认压缩算法和支持的压缩算法不同，具体需要根据版本号查看官方文档了解。

  2. 需要在Linux系统上提前安装相关的工具，例如例如从XtraBackup 8.0.34版本开始，默认的压缩算法就是 ZSTD，所以需要提前安装好 zstd这个工具。

数据备份过程

1、安装压缩工具

根据特定的版本查看官方文档，确定支持的压缩算法，然后在Linux系统上安装对应的系统工具。

例如：使用 xtrabackup8.0.35，默认使用zstd算法，就需要安装zstd工具

sudo apt install zstd -y
2、数据备份

备份数据的时候，加上 --compress 选项就会使用默认的算法对数据进行压缩。

例如：mysql数据目录是/var/lig/mysql,需要将MySQL的所有数据备份到/data_bak/下,备份目录以为当前日期命名。

sudo xtrabackup --backup  --compress --host="192.168.14.17" --user="root" --password="eHIGH2014" --port=3306 --datadir="/var/lib/mysql" --target-dir=/data_bak/$(date +"%F")_C

数据恢复过程

1、数据解压缩

因为数据是被惊醒压缩了，所以需要先将数据进行解压缩。如果压缩的时候没有指定压缩算法，那么直接使用 --decompress 选项就会采用默认的压缩算法进行解压。

例如：将/data_bak/2023-12-11_C下的数据进行解压缩

sudo xtrabackup --decompress --target-dir=/data_bak/2023-12-11_C
2、处理备份数据
sudo xtrabackup --prepare --target-dir="/data_bak/2023-12-11_C"
3、还原备份数据

1、停止MySQL服务

sudo systemctl stop mysql.service

2、清空MySQL数据目录

root@ubuntu:/var/lib# mv mysql mysql-bak

root@ubuntu:/var/lib# mkdir mysql

root@ubuntu:/var/lib# chown mysql:mysql mysql

3、将备份数据拷贝到MySLQ数据目录

可以直接通过 通过cp、rsync等命令手动复制备份目录下的所有文件到MySQL数据目录下。

也可以通过 XtraBackup的 --copy-back 或 --move-back 选项从备份目录将数据恢复到原始 MySQL 数据目录。

sudo xtrabackup --copy-back  --target-dir=/data_bak/2023-12-11_C   --datadir=/var/lib/mysql

4、更改文件权限

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

5、服务启动测试

sudo systemctl start mysql.service

实现增量备份

增量备份和差异备份的区别：

  • 增量备份： 备份上一次备份后发生变化的数据。

  • 差异备份：** 备份上一次的完全备份后发生变化的数据文件

MySQL增量备份时基于 InnoDB 存储引擎的日志序列号（LSN）实现，LSN 是一个在 InnoDB 中不断增长的数字，记录了数据库中每次写操作的位置。

xtrabackup 使用 LSN 来确定自上次备份以来哪些数据发生了变化，从而只备份这些更改的部分。

数据备份过程

1、创建完整备份

必须要创建一次完整备份，否则后面的增量备份都没意义，这个全备份后续增量备份的基础。全备份会记录当时的 LSN 值和数据库的相关信息。

sudo xtrabackup --backup --host="192.168.14.17" --user="root" --password="eHIGH2014" --port=3306 --datadir="/var/lib/mysql" --target-dir=/data_bak/$(date +"%F")
2、创建增量备份

增量备份将仅包含自上次备份（无论是全备份还是之前的增量备份）以来发生变化的数据。

也就是说：第一次增量备份备份的是完整备份后发生变化的数据，第二次增量备份是第一次增量备份后发生变化的数据。第N次增量备份是第 N-1 次备份后发生变化的数据。

# 第一次增量备份
sudo xtrabackup --backup --incremental-basedir=/data_bak/2023-12-11  --host="192.168.14.17" --user="root" --password="eHIGH2014" --port=3306  --target-dir=/data_bak/$(date +"%F")_incre1

# 第二次增量备份
sudo xtrabackup --backup --incremental-basedir=/data_bak/2023-12-12_incre1  --host="192.168.14.17" --user="root" --password="eHIGH2014" --port=3306  --target-dir=/data_bak/$(date +"%F")_incre2

选项说明：

  • --incremental-basedir 选项指定了前一个备份的目录。这个目录可以是全备份或另一个增量备份的目录。通过比较 --incremental-basedir 中的数据与当前数据库的状态，来确定自上次备份以来哪些数据发生了变化。

  • --target-dir 选项指定增量备份文件的存放位置

数据还原过程

1、处理全备份数据

需要先准备全备份的数据文件，使其可以接收增量备份

sudo xtrabackup --prepare --apply-log-only --target-dir=/data_bak/2023-12-11
2、处理增量备份数据

按照顺序，分别将第一次增量备份、第二次增备份......，和处理过的全备份数据进行合并。

# 将第一个增量备份应用到全备份上
sudo xtrabackup --prepare --apply-log-only --target-dir=/data_bak/2023-12-11  --incremental-dir=/data_bak/2023-12-12_incre1

# 将第二个增量备份应用到全备份上
sudo xtrabackup --prepare  --target-dir=/data_bak/2023-12-11  --incremental-dir=/data_bak/2023-12-12_incre2
3、MySQL 数据还原

1、停止MySQL服务

sudo systemctl stop mysql.service

2、清空MySQL数据目录

root@ubuntu:/var/lib# mv mysql mysql-bak

root@ubuntu:/var/lib# mkdir mysql

root@ubuntu:/var/lib# chown mysql:mysql mysql

3、将备份数据拷贝到MySLQ数据目录

可以直接通过 通过cp、rsync等命令手动复制备份目录下的所有文件到MySQL数据目录下。也可以通过 XtraBackup的 --copy-back 或 --move-back 选项从备份目录将数据恢复到原始 MySQL 数据目录。

sudo xtrabackup --copy-back  --target-dir=/data_bak/2023-12-11   --datadir=/var/lib/mysql

4、更改文件权限

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

5、服务启动测试

sudo systemctl start mysql.service

MySQL 逻辑备份

逻辑备份：将数据库的数据和对象导出为标准 SQL 语句保存在文本文件中。通过重新执行这些SQL语句重现数据。

对于MySQL来说，最长用的逻辑备份工具就是mysqldump，mysqldump是mysql服务自带的一个工具，无需额外安装。mysqldump可以跨平台使用且使用简单，非常适用于小到中等规模的数据库。

默认情况下，mysqldump 工具将生成的 SQL 语句输出到标准输出（stdout）。如果直接运行 mysqldump 命令而不进行任何重定向，它会在命令行界面中显示 SQL 语句。

使用 mysqldump

备份数据库

备份整个数据库
mysqldump -u 用户名 -p 用户密码  database_name > backup.sql

通过这种方法备份整个数据库时，会生成一个包含所有表结构和数据的 SQL 文件，但不会包含 CREATE DATABASE 语句。即恢复数据的时候需要手动将对应的数据库创建好，然后再将数据导入到该数据库中。

如果需要生成一个带 CREATE DATABASE 语句 的sql文件，加一个 --databases 参数就可以了。

备份多个数据库

如果需要一次性备份多个指定的数据库，可以通过 mysqldump 的 --databases 选项来实现。还可以用 -B 参数，-B参数效果和 --databases 一样的。

mysqldump -u user_name -p password --databases db1 db2 db3 > backup.sql
备分所有数据库

如果想将所有数据库进行备份的话，通过 --all-databases 选项可以实现，也可以用 -A 参数，效果是一样的

mysqldump -u user_name -p password --all-databases > backup.sql

备份数据表

备份单个表

如果只想要备份单张表，只需要在数据库的后面指定表名就行了。

mysqldump -u user_name -p password database_name table_name > backup.sql
备份多个表

在数据库的后面指定多个表名就行了。

mysqldump -u user_name -p password database_name tb1 tb2 tb3 > backup.sql
只备份结构，不备份数据

仅备份数据库结构（例如，表定义、视图、存储过程等），但不包含任何实际数据，可以使用 --no-data 选项。

mysqldump -u user_name -p password --no-data database_name table_name > backup.sql
只备份数据，不备份结构

只想备份数据而不包括表结构，可以使用 --no-create-info 选项

mysqldump -u user_name -p password --no-create-info database_name table_name > backup.sql
备份表的一部分数据

如果需要备份一个数据表中的部分数据，通过 -where 选项来指定符合特定条件的数据。

例如：备份id < 1000 的数据

mysqldump -u user_name -p password --databases db1 tables1  --where="id < 1000" > users_backup.sql

备份存储过程和函数

使用 --routines 选项可以确保存储过程和函数被包含在备份中，也可以使用简写的-R参数。

mysqldump -u user_name -p --no-create-info --no-data --no-create-db --routines --skip-triggers database_name > routines_backup.sql

打开备份的sql文件，我们可以看到 /*!50003 ... */ 这是MySQL的一种特殊注释模式，称为“条件注释”或“版本控制注释”。这些注释内容实际上会被 MySQL 执行。

/*!50003 ，这里的50003表示该命令需要的最低 MySQL 版本为 5.00.03，MySQL版本大于或等于这个版本的时候，注释中的内容会被执行。

mysqldump 高级选项

在备份数据的过程中，可以添加一些选项来对备份进行优化：

优化备份速度

如果备份一个数据量很大的库或者表，mysqldump 默认会读取整个表到内存中，然后写入到备份文件。如果表非常大，这可能会消耗大量内存，并可能导致过度的内存使用甚至崩溃。

这个时候可以通过 --quick 选项，它是直接逐行读取数据并写入备份文件，显著减少了一次性内存需求。从而加快备份速度。

主从复制环境

默认情况下，备份数据文件的时候，是不会记录当前二进制日志位置的。使用 --master-data 时，mysqldump 会在 SQL 备份文件中添加一个 CHANGE MASTER TO 语句。

这个语句包含了备份时刻的二进制日志文件名和位置（log file position）。这对于设置复制非常重要，因为它指明了从服务器（slave）开始读取主服务器（master）二进制日志的起始点。

--master-data有两个值，默认值是2

  • --master-data=1：以非注释形式包含 CHANGE MASTER TO 语句

  • --master-data=2：使CHANGE MASTER TO 语句以注释形式添加，从而在不自动更改从服务器配置的情况下提供必要的信息。

--master-data 选项经常结合 --flush-logs 选项一起使用，--flush-logs 会在备份开始前刷新 MySQL 服务器的日志，包括二进制日志（binary log）。此时 MySQL 会关闭当前的二进制日志文件并开始一个新的日志文件。

说明：

--master-data 选项在 mysqldump 输出的 CHANGE MASTER TO 语句中主要包含 MASTER_LOG_FILE 和 MASTER_LOG_POS 参数，这些参数指明了二进制日志（binary log）的文件名和位置。

但是，这个语句并不包含完整的主服务器（master）配置，如 master_host、master_user、master_password 等。

所以这个参数主要用于记录备份时刻的二进制日志位置，这对于配置从服务器从正确的位置开始复制数据很重要，所以完整的数据配置可以自己手动修改这个sql文件实现。

innodb引擎表备份

使用 --single-transaction 选项，mysqldump 会在开始备份之前启动一个新的事务。所以备份会捕捉到事务开始时刻的数据库状态，并且在整个备份过程中保持这一状态，即使后续对数据库进行了更改。

mysql数据库备份

mysql这个数据库里面存放了mysql的用户和权限信息，如果想让备份文件被恢复到另一个 MySQL 服务器上后，用户和权限的更改立即生效。需要加 --flush-privileges 选项

添加这个选项后，会在 SQL 备份文件中添加一条 FLUSH PRIVILEGES; 语句。当这个备份文件被导入到 MySQL 服务器时，FLUSH PRIVILEGES; 语句会执行，从而重新加载权限表。

这确保了任何用户和权限的更改（如新用户的添加或权限的修改）会立即生效。这样就不用手动刷新权限了。

mysam引擎备份

现在一般用的都是innodb这个存储引擎，使用mysqldump这个工具的时候，innodb是支持热备份的，而mysam只支持温备份，即备份过程中能读不能写。

如果备份所有数据库，通过 --lock-all-tables 选项或者 -x选项来加全局读锁，会锁定所有数据库的所有表。也会导致一个问题，数据量大时，可能会导致长时间无法并发访问数据库。

mysqldump -u user_name -p --lock-all-tables --all-databases > backup.sql

如果支持备份单个数据库，可以用 -lock-tables 参数，这样只会锁定正在备份的数据库中的表。备份操作不会影响到服务器上其他数据库中的表。

mysqldump -u user_name -p --lock-tables database_name > backup.sql

posted on 2023-12-07 19:09  背对背依靠  阅读(176)  评论(0)  编辑  收藏  举报