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  背对背依靠  阅读(392)  评论(0编辑  收藏  举报