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 数据库的备份和恢复操作。
特点:
-
备份数据和恢复速度很快,支持大型数据库和需要支持灾难级备份的场景。
-
主要针对的就是inodb类型的数据,备份过程不会锁表,适合数据库需要7*24小时运行的场景
-
支持增量备份、对备份数据进行加密、压缩等功能
-
向后兼容性差,需要根据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。
安装说明:
-
确定MySQL的版本信息
-
参考官方文档选择合适的XtraBackup版本
-
选择安装方式,可以源码安装、二进制安装还有包管理安装等。安装步骤文档写得很详细,具体参考文档。
使用 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")
![MySQL 数据备份](https://imgs.yssmx.com/Uploads/2023/12/749308-1.png)
选项说明:
-
--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支持在备份数据的时候将数据进行压缩,一次减少硬盘空间的占用。
前提:
-
不同版本使用的默认压缩算法和支持的压缩算法不同,具体需要根据版本号查看官方文档了解。
-
需要在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
![MySQL 数据备份](https://imgs.yssmx.com/Uploads/2023/12/749308-2.png)
数据恢复过程
1、数据解压缩
因为数据是被惊醒压缩了,所以需要先将数据进行解压缩。如果压缩的时候没有指定压缩算法,那么直接使用 --decompress
选项就会采用默认的压缩算法进行解压。
例如:将/data_bak/2023-12-11_C下的数据进行解压缩
sudo xtrabackup --decompress --target-dir=/data_bak/2023-12-11_C
![MySQL 数据备份](https://imgs.yssmx.com/Uploads/2023/12/749308-3.png)
2、处理备份数据
sudo xtrabackup --prepare --target-dir="/data_bak/2023-12-11_C"
![MySQL 数据备份](https://imgs.yssmx.com/Uploads/2023/12/749308-4.png)
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版本大于或等于这个版本的时候,注释中的内容会被执行。
![MySQL 数据备份](https://imgs.yssmx.com/Uploads/2023/12/749308-5.png)
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 会关闭当前的二进制日志文件并开始一个新的日志文件。
![MySQL 数据备份](https://imgs.yssmx.com/Uploads/2023/12/749308-6.png)
说明:
--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选项来加全局读锁,会锁定所有数据库的所有表。也会导致一个问题,数据量大时,可能会导致长时间无法并发访问数据库。文章来源:https://www.toymoban.com/news/detail-749308.html
mysqldump -u user_name -p --lock-all-tables --all-databases > backup.sql
如果支持备份单个数据库,可以用 -lock-tables
参数,这样只会锁定正在备份的数据库中的表。备份操作不会影响到服务器上其他数据库中的表。文章来源地址https://www.toymoban.com/news/detail-749308.html
mysqldump -u user_name -p --lock-tables database_name > backup.sql
到了这里,关于MySQL 数据备份的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!