MySQL 数据备份
备份类型常见的备份有冷备份、温备份、热备份，还有什么物理备份、逻辑备份、增量备份、差异备份等等。
冷备份： 需要服务停止，在备份期间不能进行读和写操作。
温备份： 读操作可执行；但写操作不可执行
热备份： 读和写都可以正常进行，不影响数据备份
逻辑备份： 导出数据库中的数据和对象定义为标准 SQL 语句保存在文本文件中。这些 SQL 语句可以重新执行来恢复数据库。
物理备份： 直接复制数据库的文件，包括数据文件、索引文件和日志文件等。它是对数据库文件系统层面的直接拷贝。
MySQL 备份内容
用户信息： 例如MySQL的账号信息，账号的权限等。这些都是存放再mysql这个数据库中的。
业务数据： 相关的业务数据库
配置文件： MySQL服务的相关配置
MySQL 物理备份
一种方法是将mysql服务停止后，将整个数据目录进行拷贝或者打包，例如利用tar、cp、rsync等工具
还有一种方法就是利用专门的物理备份工具，例如xtrabackup就是一个非常好用的物理备份工具。
xtrabackup 介绍
xtrabackup是 Percona 开发的一个数据库物理备份工具，100%开源。主要用来执行MySQL、Percona Server 和 MariaDB 数据库的备份和恢复操作。
官方文档：https://docs.percona.com/percona-xtrabackup/innovation-release/
xtrabackup具有以下特点：
1、非常适合大型数据库以及需要快速备份还原和需要支持灾难级备份的场景。
2、支持增量备份、对备份数据进行压缩、数据加密等功能
3、xtrabackup主要是用来操作innodb这种存储引擎的数据，且整个过程是非阻塞的。在备份过程中，对数据库的读写操作可以继续进行，适合需要7*24小时运行的环境
4、正是因为xtrabackup是直接操作数据库文件和日志，所以对数据库的内部结构（如数据字典、redo log 格式）高度敏感。当这些内部结构在数据库的新版本中发生变化时，XtraBackup 需要更新以适应这些更改。并且高版本不能向下兼容低版本。
xtrabackup 安装
安装的时候需要先确认需要备份的数据库版本，然后再参考官方文档下载合适的 xtrabackup 版本才行。上面也说了，MySQL不同版本的文件结构有差别，所以造成不能高版本兼容低版本的问题。
例如：mysql的版本是5.7.x，那么就需要下载 2.4版本的xtrabackup，8.0版本的xtrabackup不能备份mysql5.7。
xtrabackup 备份数据
备份整个数据库
XtraBackup 是直接备份物理文件的工具，但是也需要连接到 MySQL。因为需要知道MySQL的一些配置（如 datadir、innodb_data_home_dir、innodb_log_group_home_dir 等），通过这些配置来确定需要备份的文件。
还有就是读取二进制日志位置来确保备份可以用于点恢复操作。连接MySQL的参数和mysql命令行工具是一样的。
1、创建备份目录
需要执行备份操作的过程中，具有对这个目录的读和写权限，所以可以直接以root用户身份执行。
mkdir /home/ehigh/mysql_bak2、执行备份操作
通过 --backup 选项可以执行全备份。
例如：
sudo xtrabackup --backup --host="192.168.15.12" --user="root" --password="eHIGH2014" --port=3306 --datadir="/var/lib/mysql" --target-dir="/home/ehigh/mysql_bak"3、数据恢复前的准备
通过 --prepare 选项让 xtrabackup完成一些数据恢复前的准备工作，因为执行物理备份时，XtraBackup 复制的是数据库文件的实时状态，包括那一刻的所有数据文件和日志文件。
这个状态下可能会有未完成的事务等等。通过这个操作可以将这些未完成的事务回滚。保证恢复时数据库的一致性。
而且执行这个操作是不会影响到现有数据的，因为这个操作仅针对备份的数据进行处理，只会处理备份目录中的备份文件，例如应用重做日志和回滚未完成的事务。
sudo xtrabackup --prepare --target-dir="/home/ehigh/mysql_bak"4、进行数据恢复
(1)停止服务。避免在恢复数据时发生数据损坏。
sudo systemctl stop mysql.service(2)清空数据目录。
sudo rm -rf /var/lib/mysql
mv /var/lib/mysql /var/lib/mysql-bak
mkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql (3)执行数据恢复
执行数据恢复一种方法是直接使用cp或者rsync工具将备份目录下的所有文件拷贝到mysql数据目录中，还有种方法就是通过 --copy-back 或 --move-back 选项从备份目录将数据恢复到原始 MySQL 数据目录。
这两个参数的区别如下：
[*]--copy-back：将备份数据复制回 MySQL 的数据目录。这个过程不会删除或更改备份目录中的原始备份文件
[*]--move-back：将备份数据移动（而非复制）回 MySQL 的数据目录，移动过后备份目录中就不存在备份文件了
sudo xtrabackup --copy-back--target-dir=/home/ehigh/mysql_bak--datadir=/var/lib/mysql（4）更改文件权限
将MySQL数据目录下的文件权限改为mysql
sudo chown -R /var/lib/mysql备份整个数据库并压缩数据
xtrabackup不同版本支持的压缩算法不同，具体信息可以看官方文档确认。通过 --compress选项即可再备份数据的时候，将数据进行压缩。
1、备份数据
例如：
sudo xtrabackup --backup --host="192.168.15.12" --user="root" --password="eHIGH2014" --port=3306 --compress --compress-threads=4 --datadir="/var/lib/mysql" --target-dir="/home/ehigh/mysql_bak_c"2、恢复数据前先解压缩
在备份数据的时候xtrabackup会通过自带的一些压缩算法库文件将文件进行压缩，但是解压的时候需要在系统上安装对应的工具才行。
https://img2023.cnblogs.com/blog/1519088/202312/1519088-20231207185908689-679062110.png通过 --decompress 选项即可完成解压
sudo xtrabackup --decompress --target-dir=/home/ehigh/mysql_bak_c3、恢复前的准备
通过 --prepare 选项完成恢复前的准备，具体操作和上面一样的
4、数据恢复
（1）停止MySQL服务
（2）清空MySQL数据目录
（3）使用 xtrabackup 恢复数据
（4）修改MySQL数据目录中文件的属主和属组
备份单个数据库
XtraBackup 备份单个数据库的操作比较特殊，因为 XtraBackup 主要设计用于备份整个 MySQL 服务器的。备份单个库用mysqldump就行了。
MySQL 逻辑备份
就是将数据库中的数据备份为SQL语句，将这些SQL语句存放在一个文件中，恢复数据的时候直接执行这个文件里面的SQL即可重现数据。
mysqldump
mysqldump是mysql服务自带的一个工具，mysqldump可以跨平台使用且使用简单，非常适用于小到中等规模的数据库。
默认情况下，mysqldump 工具将生成的 SQL 语句输出到标准输出（stdout）。如果直接运行 mysqldump 命令而不进行任何重定向，它会在命令行界面中显示 SQL 语句。
备份整个数据库
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版本大于或等于这个版本的时候，注释中的内容会被执行。
https://img2023.cnblogs.com/blog/1519088/202312/1519088-20231206162406884-2118219907.pngmysqldump 高级选项
在备份数据的过程中，可以添加一些选项来对备份进行优化：
1、优化备份速度：
如果备份一个数据量很大的库或者表，mysqldump 默认会读取整个表到内存中，然后写入到备份文件。如果表非常大，这可能会消耗大量内存，并可能导致过度的内存使用甚至崩溃。
这个时候可以通过 --quick 选项，它是直接逐行读取数据并写入备份文件，显著减少了一次性内存需求。从而加快备份速度。
2、主从复制环境：
默认情况下，备份数据文件的时候，是不会记录当前二进制日志位置的。使用 --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 会关闭当前的二进制日志文件并开始一个新的日志文件。
https://img2023.cnblogs.com/blog/1519088/202312/1519088-20231206164211756-1090448811.png说明：
--master-data 选项在 mysqldump 输出的 CHANGE MASTER TO 语句中主要包含 MASTER_LOG_FILE 和 MASTER_LOG_POS 参数，这些参数指明了二进制日志（binary log）的文件名和位置。
但是，这个语句并不包含完整的主服务器（master）配置，如 master_host、master_user、master_password 等。
所以这个参数主要用于记录备份时刻的二进制日志位置，这对于配置从服务器从正确的位置开始复制数据很重要，所以完整的数据配置可以自己手动修改这个sql文件实现。
3、innodb引擎表备份：
使用 --single-transaction 选项，mysqldump 会在开始备份之前启动一个新的事务。所以备份会捕捉到事务开始时刻的数据库状态，并且在整个备份过程中保持这一状态，即使后续对数据库进行了更改。
4、mysql数据库备份
mysql这个数据库里面存放了mysql的用户和权限信息，如果想让备份文件被恢复到另一个 MySQL 服务器上后，用户和权限的更改立即生效。需要加 --flush-privileges 选项
添加这个选项后，会在 SQL 备份文件中添加一条 FLUSH PRIVILEGES; 语句。当这个备份文件被导入到 MySQL 服务器时，FLUSH PRIVILEGES; 语句会执行，从而重新加载权限表。
这确保了任何用户和权限的更改（如新用户的添加或权限的修改）会立即生效。这样就不用手动刷新权限了。
5、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
来源:https://www.cnblogs.com/heyongshen/p/17874375.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]