【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（备份+恢复篇）（一）https://developer.aliyun.com/article/1471055
MySQL的备份方式
逻辑备份
逻辑备份是通过将MySQL数据库中的数据（包括库表）以SQL语句的形式直接输出或生成备份文件的方式进行备份，其中使用mysqldump命令实现。
逻辑备份具有如下特点
优点：
- 操作简单、方便和可靠。
- 备份的数据可以在不同的平台、版本、软件和操作系统之间迁移和恢复。
- 可实现对分库分表的备份。
缺点：
- 备份速度比物理备份慢。
- 恢复效率相对较低。
常用的工具包括
- mysqldump：MySQL官方自带的最常用逻辑备份工具，可实现对分表分库的备份。
- mydumper：一个在GPL许可下发布的高性能MySQL备份和恢复工具集。
逻辑备份在以下企业应用场景中适用：
- 数据量不是特别大的场景，例如备份前的数据不超过30GB。这个参考值主要考虑备份效率与管理员使用复杂度间的平衡。
然而，当存在跨版本、跨软件的升级或数据迁移时，物理备份通常无法使用。此时，通常会使用mysqldump工具进行数据处理，例如：
- 恢复数据到测试库。
- 通过执行SQL语句人为删除数据。
- 在主从复制中使用。
案例：备份多个表
可以使用以下语法来备份多个表：
bash
复制代码
mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名
例如，备份库1中的表1、表2和表3可以使用以下命令：
bash
复制代码
mysqldump -u 用户名 -p 库1 表1 表2 表3 > 库1.sql
同样地，备份库2中的表1、表2和表3可以使用以下命令：
bash
复制代码
mysqldump -u 用户名 -p 库2 表1 表2 表3 > 库2.sql
备份一个或多个表的语法如下：
bash
复制代码
mysqldump [选项] 数据库名 [表名……]
备份一个或多个库的语法如下：
bash
复制代码
mysqldump [选项] –databases 数据库名……
备份所有数据库的语法如下：
sql
复制代码
mysqldump [选项] –all-databases
以上是备份多个表和多个库的语法和用法。你可以根据实际需求进行相应的调整和使用。
压缩和解压备份
以下是压缩备份和解压备份的代码示例：
压缩备份：
bash
复制代码
# mysqldump -B --master-data=2 dadong | gzip > /opt/t.sql.gz
这将使用mysqldump工具备份数据库dadong并将结果压缩为/opt/t.sql.gz文件。
解压备份：
bash
复制代码
# zcat t.sql.gz > t1.sql
这将解压缩/opt/t.sql.gz文件并将结果保存为t1.sql文件。另外，你还可以使用以下命令直接解压缩并删除源文件：
bash
复制代码
# gzip -d t.sql.gz
这会将t.sql.gz解压缩为t.sql文件，并删除原始的压缩文件。
请确保在操作过程中，确认目标路径和文件名与你需要的一致，并根据需要调整命令行中的数据库名称和文件路径。
mysqldump重要参数说明
下面是对mysqldump命令中一些重要参数的说明：
- -B, --database:
这个参数会在备份的数据中包含建库（create）以及"use库"的语句。可以同时备份多个库，只需要在参数后面直接添加多个库名。使用-B参数可以在数据库恢复时自动创建建表语句。
- -A, --all-databases:
备份所有的数据库。
- -F, --flush-logs:
刷新binlog日志，生成新的binlog文件。在将来进行增量恢复时，可以从这个新的binlog文件开始。如果备份多个库，每个库都会刷新一次binlog。如果只想刷新一次binlog，可以加上--lock-all-tables或--master-data参数。
- -x, --lock-all-tables:
在备份过程中，对所有数据库的表执行全局读取锁定操作，同时禁用-single-transaction和--lock-tables参数的功能。
- -l, --lock-tables:
将所有的表锁定为只读。
- --single-transaction:
备份InnoDB引擎的数据表时，通常会启用此选项，以获得一致性的数据快照备份。这个选项将设置备份会话的隔离级别为可重复读，并将整个备份过程放在一个事务中，以确保备份期间不会看到其他连接会话中已提交的数据。也就是说，备份开始时刻的数据是什么样的，备份结果就是什么样的。相当于对表进行加锁备份数据。但是，此参数允许备份期间进行写入操作，与使用-x参数锁定表不同，后者在备份期间不允许写入操作。启用此参数会关闭--lock-tables选项。
- -R, --routines:
备份存储过程和函数数据。
- -d, --no-data:
只备份库表结构（以SQL语句形式），不包含行数据。
- -t, --no-create-info:
只备份表内行数据（以SQL语句形式），不包含表结构。
- -T, --tab=name:
将库表和数据分离成不同的文件，行数据以纯文本形式存储，表结构以SQL语句形式存储。默认情况下，这个功能在5.6版本中是没有被授权的，需要修改my.cnf文件的参数。
- --master-data={1|2}:
在备份结果中包含binlog日志文件名及对应的binlog位置点（即"change master"命令）。--master-data=2会记录备份时刻的二进制日志位置，并将其注释掉。而--master-data=1则不会注释掉。
- --triggers:
备份触发器数据。
- --compact:
只显示少量有用的输出，适用于学习和测试环境调试。
single-transaction
当使用mysqldump的--single-transaction选项备份InnoDB表时，会开启一个事务并将整个备份过程放入其中。这样做可以确保在备份过程中不会看到其他连接已提交的数据变更。换句话说，在备份开始时刻的数据状态就是备份结果中的数据状态，就好像表被锁定一样。但需要注意的是，这个选项允许在备份期间进行数据写入，与使用-x选项锁定表并禁止写入的情况不同。
以下是一个操作示例：
bash
复制代码
# mysqldump -B --master-data=2 --single-transaction dadong | gzip > /opt/all.sql.gz
物理备份
冷备份
一种常见的物理备份方法是使用cp、rsync、tar、scp等复制工具将MySQL数据文件复制成多份。然而，直接复制的方式备份会引起数据丢失，因为在备份期间数据仍可以进行写入操作。
另外，在恢复数据库时，对新数据库的路径和配置也有要求，通常需要与原数据库的配置保持一致（包括版本、路径和配置选项），以确保数据正常恢复。通常情况下，人工停库或锁库后再进行物理复制是保证备份期间数据一致性的方法。然而，在生产环境中，通常不允许停机或锁表，除非事先申请停机或锁表时间。因此，使用传统的Linux命令拷贝工具进行冷备份是一种粗放的方式，应尽量避免使用。在进行大规模数据库迁移时，先停机然后进行物理迁移是一种高效的解决方案。
热备份
除了直接通过Linux命令行复制MySQL数据文件，还存在一些第三方的开源或商业物理热备份工具，如xtrabackup。使用这些工具可以实现物理全备份和增量备份。热备份是在不影响用户体验的情况下进行的备份方法。这些工具通常提供了更灵活和可靠的备份机制，可以确保数据的一致性，并能够有效地进行增量备份，减少备份时间和存储空间的占用。
物理备份的特点
物理备份与逻辑备份相比，具有以下优点和缺点。在企业中，根据需求，可以互补使用这两种备份方法。
优点
- 速度快：物理备份通过直接复制数据文件，备份过程简单高效，可以快速完成备份任务。
- 效率高：由于物理备份是直接复制数据文件，不需要解析数据，备份和恢复的效率较高。
缺点：
- 不容易跨平台、跨版本、跨软件、跨操作系统：物理备份的数据文件格式通常与特定的数据库版本、软件和操作系统相关，无法直接在其他平台或软件上进行恢复操作。
- 恢复过程复杂：由于物理备份涉及到数据库的路径、配置等细节，恢复时需要确保路径和配置与原数据库一致，这增加了恢复的复杂度。
- 使用较复杂：物理备份可能需要借助第三方工具或命令行操作，相对于逻辑备份而言使用起来可能更复杂一些。
物理备份常用工具：
在Linux环境下，常用的物理备份工具有以下几种：
- cp：是一个命令行工具，用于复制文件和目录。可以通过将数据库文件复制到备份目录来进行物理备份。
- tar：是一个常用的压缩和打包工具，在备份时可以使用tar将数据库文件打包到备份文件中进行备份。
- xtrabackup：是一个基于InnoDB存储引擎的开源热备份工具。与传统的冷备份工具不同，xtrabackup可以在数据库运行时进行备份，不需要停止数据库服务。
优化建议
- 对于总数据量超过30GB的数据库，可以考虑使用xtrabackup进行热备份，以提高备份的效率。
- 对于不希望影响数据库服务的情况，可以选择在数据库的从库上进行备份。在备份时，可以停止SQL线程从应用中读取数据，然后使用cp或tar等工具将数据库文件打包备份。这种方案称为冷备份，可以保证备份过程不影响数据库的正常服务。
MySQL数据库恢复
在进行数据库恢复时，需要注意以下事项：
- 字符集关联：字符集的正确设置对于数据恢复至关重要。如果字符集设置不正确，恢复的数据可能会出现乱码问题。确保在恢复过程中，使用正确的字符集配置，以避免乱码问题的发生。
- 使用MySQL命令和source命令进行恢复：MySQL命令和source命令是常用的数据库恢复工具。它们的原理是将备份文件中的SQL语句重新执行到数据库中，从而实现数据恢复的过程。在使用这些命令时，需要确保备份文件的SQL语句正确无误，并且执行过程中没有错误或中断。
优化建议
- 在进行数据库恢复之前，确保备份文件的字符集与目标数据库的字符集一致。这样可以避免因字符集不匹配而导致的数据乱码问题。
- 在使用MySQL命令或source命令恢复数据库时，建议先进行备份文件的验证，确保备份文件完整且没有损坏。可以使用checksum等工具对备份文件进行校验，以确保数据的完整性。
- 在进行数据恢复之前，建议先在测试环境或备份数据库中进行恢复测试，以确保恢复过程的顺利进行，并且恢复后的数据符合预期。
利用source命令恢复数据库
要通过source命令来恢复数据库，请按照以下步骤执行：
- 首先，进入MySQL数据库控制台。在命令行中输入以下命令并按Enter键：
css
复制代码
mysql -u root -p密码
这将使用root用户和密码登录到MySQL数据库。
- 登录成功后，使用以下命令切换到目标数据库：
perl
复制代码
mysql> use 数据库
将"数据库"替换为实际的目标数据库名称。
- 接下来，使用source命令来执行脚本文件。脚本文件可以是一个SQL文件或者是一个压缩包。如果是压缩包，需要在MySQL登录前进入压缩包所在的文件夹。
shell
复制代码
mysql> source back_db.sql
将"back_db.sql"替换为实际的脚本文件名称。
请注意，如果脚本文件不在当前路径下，需要提供脚本文件的完整路径。
通过以上步骤，您将能够使用source命令成功恢复数据库。记得在执行任何数据库恢复操作之前，确保先备份您的数据以确保安全。
MySQLDump恢复数据库
在执行使用gzip解压缩之前，请先确保已经备份了数据库以确保安全。以下是使用gzip解压缩的步骤：
- 备份数据库并创建测试数据。在终端中执行以下命令：
bash
复制代码
# mysqldump -B --master-data=2 dadong | gzip > /opt/backup.sql.gz
这将备份名为"dadong"的数据库并将其输出到/opt/backup.sql.gz文件中。
- 解压缩压缩包。在终端中执行以下命令：
bash
复制代码
# gzip -d /opt/backup.sql.gz
这将解压缩/opt/backup.sql.gz文件，并生成一个名为backup.sql的解压缩后的文件。
- 恢复数据库。在终端中执行以下命令：
bash
复制代码
# mysql </opt/backup.sql
这将使用mysql命令将backup.sql文件中的数据库恢复到MySQL中。
请注意，gzip命令的参数：
- -c 将输出写入标准输出，并保留原始文件。
- -d 解压缩压缩文件。
如果使用mysqldump导出数据库时指定了-B参数，则在恢复时无需特定库名。因为-B参数会生成包含"use backup"和"create database backup"的脚本，这类似于在恢复时执行"use backup"命令。
使用以上步骤，您将能够成功使用gzip解压缩并恢复数据库。请谨慎操作，并在执行任何数据库操作前先备份数据以确保安全。
System指令
注：如果不想退出MySQL，在执行恢复语句时可以使用系统命令。
在MySQL命令行中，执行以下命令将当前目录切换到/root目录:
sql
复制代码
mysql> system cd /root mysql> system mysqldump -B --master-data=2 --single-transaction -B backup | gzip > /opt/all.sql.gz
以上是执行在MySQL命令行中切换到指定目录、退出MySQL、确认当前目录以及进行数据库备份并压缩的步骤。使用"--single-transaction"参数可以确保备份过程中不会锁定表，提高备份的稳定性和一致性。
使用zcat读取压缩包数据（直接导入数据库）
sql
复制代码
zcat /opt/backup.sql.gz >/opt/backup.sql mysql </opt/backup.sql
下节预告
综上所述基本上完成了MySQL数据库的数据备份和恢复实现，下一节会面向物理备份的恢复实战，对于“【MySQL技术专题】「问题实战系列」深入探索和分析MySQL数据库的数据备份和恢复实战开发指南（xtrabackup恢复指南）”。
