mysql备份和恢复以及常见的错误

wang_book

于 2024-10-25 17:43:08 发布

阅读量1.2k

点赞数 37

CC 4.0 BY-SA版权

分类专栏：常用方法(经验) 文章标签： mysql oracle 数据库安全架构

本文链接：https://blog.youkuaiyun.com/wang_book/article/details/143200130

常用方法(经验) 专栏收录该内容

9 篇文章

订阅专栏

在这里插入图片描述

备份数据库

备份数据库时，常见的备份方式有三种：

全量备份 (Full Backup)：

每次备份都会备份整个数据库。
优点：简单，恢复时只需要这份备份。
缺点：每次备份文件较大，备份和恢复的时间较长。
适合场景：如果数据库规模较小，或者不太频繁地进行数据更改。

增量备份 (Incremental Backup)：

只备份自上次备份以来发生变化的数据。
优点：备份时间短，备份文件小，占用磁盘空间少。
缺点：恢复时需要依赖多个备份文件，过程较为复杂。
适合场景：如果数据库比较大，且数据更改频繁，可以减少备份和存储压力。

差异备份 (Differential Backup)：

备份自上次全量备份以来的所有变化数据。
优点：备份文件较小，恢复比增量备份简单，只需要最近的全量备份和差异备份即可。
缺点：随着时间推移，差异备份的大小会增加。
适合场景：需要平衡备份效率和恢复速度的情况。

备份策略设计

针对每日备份，可以采用以下策略：

每日全量备份 + 每周增量备份：

每天做一次全量备份，确保可以快速恢复到某个时间点的数据。
每周进行增量备份，减少备份文件的大小。
优点：数据恢复灵活，同时减小备份文件的大小。

每天做差异备份：

每天做差异备份，并结合定期的全量备份（例如每月一次）。
优点：差异备份的大小不会像增量备份那样随时间推移而变大，恢复过程简单。
缺点：备份文件可能会逐渐变大，增加存储需求。

自动化备份方案

为了减少人工干预，建议使用自动化脚本进行备份。可以使用如下工具和方法：

使用 mysqldump 进行备份：
mysqldump 是 MySQL 提供的一个命令行工具，可以进行全量或增量备份。
配合 cron（Linux）或任务计划（Windows）进行定时自动备份。
示例：

# 每天凌晨 2 点执行全量备份
0 2 * * * /usr/bin/mysqldump -u root -p'password' database_name > /backup/db_full_$(date +\%F).sql

使用 Percona XtraBackup（适用于 InnoDB 存储引擎）：
这是一个开源的增量备份工具，专门用于 MySQL 和 MariaDB 的备份。它支持热备份（即不需要停机），并能做增量备份。
配合 cron 或其他定时任务工具进行自动化。
使用备份管理工具（如 MySQL Enterprise Backup）：
适合企业级用户，提供更高效的备份方式，支持增量、差异和全量备份。

备份存储策略

选择合适的备份存储位置以保证数据的安全性和可靠性：

本地备份：
将备份存储在本地磁盘或NAS中，恢复速度较快，但也面临硬件损坏的风险。
定期将备份数据复制到不同的物理存储位置。
云备份：
使用云服务（如 AWS S3、Google Cloud Storage、阿里云 OSS 等）进行远程备份，减少硬件风险，确保备份的高可用性。
云备份适合灾难恢复方案，特别是在本地发生故障时可以迅速恢复数据。
离线备份（如磁带、硬盘备份）：
将备份数据保存到物理介质中，以减少受网络攻击和硬件故障的影响。
不适合频繁备份，适合长期存档。

备份验证和恢复演练

备份只是保证数据安全的第一步，必须确保备份数据可以正确恢复。为此，应定期验证备份文件的完整性，并进行恢复演练：

验证备份文件完整性：定期检查备份文件是否损坏，尤其是对于压缩格式的备份。
定期恢复测试：每隔一段时间（例如每季度），进行一次恢复演练，确保在实际灾难发生时可以顺利恢复。

备份安全性

备份文件的安全性非常重要，避免备份数据被恶意篡改或丢失：

加密备份文件：在存储或传输备份文件时，使用加密技术保护敏感数据。
访问控制：限制对备份文件的访问权限，确保只有授权用户才能访问备份数据。
自动删除过期备份：根据保留策略，自动删除不再需要的旧备份，以节省存储空间。

备份恢复总结

每天的数据库备份可以通过全量备份、增量备份或差异备份相结合的方式实现。通过自动化备份工具、定期存储策略、备份验证及恢复演练，可以确保数据库的安全性和恢复能力。此外，备份文件的安全性也需要特别注意，以避免数据泄漏或丢失。

恢复时的一些错误

备份文件太大

错误信息

[报错SQL] Query 备份文件名 start
[ERR] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

解决方案

表示 MySQL 服务器收到的查询数据包超出了 max_allowed_packet 的设置限制。这通常发生在执行大型查询或导入大量数据时。

要解决这个问题，可以按照以下步骤调整 max_allowed_packet 的配置：

查找当前设置
你可以使用以下 SQL 查询来查看当前的 max_allowed_packet 设置：

SHOW VARIABLES LIKE 'max_allowed_packet';

max_allowed_packet 是 MySQL 中的一个系统变量，用于设置单个数据包（或消息）的最大大小。

临时调整 max_allowed_packet
如果你只需要临时调整该值（直到 MySQL 服务重启），可以执行以下命令：

SET GLOBAL max_allowed_packet=16777216;  -- 将大小设置为 16MB

选择临时调整：适合于偶尔需要处理大数据包的情况，或在测试阶段希望灵活调整配置时。

永久调整 max_allowed_packet
如果你希望在 MySQL 重启后保持新的设置，需要修改 MySQL 配置文件（通常是 my.cnf 或 my.ini）：

打开 MySQL 配置文件：
在 Linux 上通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf
在 Windows 上通常位于 MySQL 安装目录下的 my.ini
找到 [mysqld] 部分，如果没有，请添加：

[mysqld]
max_allowed_packet=16M  -- 或者其他合适的值，如 32M

保存文件并重启 MySQL 服务，以使更改生效。

选择永久调整：适合于持续需求较大的应用，确保每次数据库重启后都能正常工作，且简化管理。

重启 MySQL 服务
在 Linux 上，你可以使用以下命令重启 MySQL 服务：

sudo service mysql restart 
# 或者 二选一
sudo systemctl restart mysql

在 Windows 上，可以通过服务管理工具找到 MySQL 服务，然后重启。

注意事项

根据你的需要，可以将 max_allowed_packet 设置为更大的值，例如 32M 或 64M，但要注意，设置过大可能会影响服务器性能。
如果你正在处理大量数据导入，确保其他配置（如 innodb_buffer_pool_size）也适合你的数据量。
通过以上步骤，你应该能够解决 max_allowed_packet 超出限制的问题。
如果问题仍然存在，建议检查查询的具体内容，确保没有其他问题导致数据包过大。

表插入顺序问题导致无法插入子表记录

在数据库中，必须先插入父表中的记录，才能插入子表中的记录。因此，确保你在执行插入child表的 SQL 语句之前，已经成功插入了相应的 parent记录。

方法 1: 使用 --skip-extended-insert 选项
这个选项会让每个插入语句都单独一行，便于手动调整。但是这会使备份文件变得更加庞大。

mysqldump -u username -p --skip-extended-insert database_name > backup_file.sql
说明：这条命令将数据库 database_name 的所有数据导出到 backup_file.sql 文件中，每个插入语句独立一行。

方法 2: 手动调整备份顺序
将不同表分开备份，以控制它们的插入顺序。例如，先备份主表，再备份依赖于主表的从表。

# 先备份主表
mysqldump -u username -p database_name main_table > main_table_backup.sql

# 然后备份依赖的从表
mysqldump -u username -p database_name dependent_table > dependent_table_backup.sql

说明：这两条命令将分别备份 main_table 和 dependent_table，确保在恢复时从表在主表后插入。在恢复数据时，可以先恢复依赖关系少的表，再恢复依赖关系多的表。例如，先恢复 users 表，再恢复 orders 表（假设 orders 表依赖于 users 表）。

方法 3: 使用特定的顺序备份表
根据依赖关系按顺序备份表。

# 备份用户表
mysqldump -u username -p --no-create-info database_name users > users_backup.sql

# 备份订单表
mysqldump -u username -p --no-create-info database_name orders > orders_backup.sql

说明：users 表将被优先备份，因为 orders 表依赖于 users 表。
方法 4: 自定义备份脚本
编写一个 Bash 脚本，以特定顺序执行备份。

#!/bin/bash

# 定义数据库用户名和名称
DB_USER="username"
DB_NAME="database_name"

# 备份主表
mysqldump -u $DB_USER -p --no-create-info $DB_NAME main_table > backup_main_table.sql

# 备份从表
mysqldump -u $DB_USER -p --no-create-info $DB_NAME dependent_table > backup_dependent_table.sql

# 备份其他表
mysqldump -u $DB_USER -p --no-create-info $DB_NAME another_table > backup_another_table.sql

说明：这个脚本可以按照预设顺序备份多个表。保存为 backup_script.sh 后，通过 bash backup_script.sh 运行。

方法 5: 使用 InnoDB 外键约束
在恢复过程中禁用外键检查，以确保没有约束错误。

-- 禁用外键检查
SET FOREIGN_KEY_CHECKS=0;

-- 恢复主表
mysql -u username -p database_name < backup_main_table.sql

-- 恢复从表
mysql -u username -p database_name < backup_dependent_table.sql

-- 恢复其他表
mysql -u username -p database_name < backup_another_table.sql

-- 启用外键检查
SET FOREIGN_KEY_CHECKS=1;