数据库必知必会系列：数据库备份与恢复策略

发布于：2023-09-27 ⋅ 阅读:(86) ⋅ 点赞:(0)

作者：禅与计算机程序设计艺术

1.简介

数据库备份是数据安全、数据的完整性、数据一致性的重要保障。数据库备份主要分为物理备份和逻辑备份两种类型。其中物理备份是将整个数据库文件（或称作数据文件）保存到磁盘，并在需要时进行数据恢复；而逻辑备份则是对数据库中所存储的数据信息进行复制、转储、传输等操作，并保留原始数据及相关的备份副本，用来做后期的数据恢复。

本文将介绍数据库备份策略，包括全量备份、增量备份、分级备份、异地冗余备份等，并讨论不同场景下的恢复策略，如灾难恢复、主从切换恢复、业务连续性恢复等。文章结合实际例子详细阐述了备份策略、恢复策略，并给出了相应的示例代码，力求让读者通过阅读学习本文，掌握数据库备份和恢复的知识技能。欢迎广大读者一起加入讨论，共同探讨数据库备份策略与恢复策略相关的技术问题。

2.基本概念

2.1.备份的定义

数据备份，通常指的是对计算机系统或数据库中的数据进行备份，以保证数据的完整性、可用性、并提供长久的存档。其目的是确保在某个时间点之前数据的完整性不受损坏、不会丢失或被篡改。数据库备份策略一般分为全量备份、差异备份、定时备份、按需备份、完全恢复等。

  1. 全量备份：全量备份又称全备，是指把整个数据库的数据备份，将所有数据都完全备份下来，不仅包括表结构、数据记录、触发器、视图等对象，还包括如索引、日志、事务等系统配置信息。它的特点是占用较大的磁盘空间，且耗费较多的时间。全量备份可以支持灾难恢复、主从切换恢复、异地备份等需求。

  2. 增量备份：增量备份又称增备、复制备份或镜像备份，是指把自上次备份后发生的新的数据更新，或者自某一特定时间点起至现在的所有数据更新都备份下来，它解决了全量备份不能完全覆盖历史数据的问题，但是仍然存在数据空间利用率低的问题。它的特点是只需要记录更新的内容，不需要记录整个数据库，因此空间开销小，速度快。增量备份适用于数据量较大的情况，并且对数据的实时性要求不高。

  3. 分级备份：分级备份是指按照一定频率将不同的备份集中放在不同的地方进行保存。优点是可以根据需要灵活恢复，并节约磁盘空间，但也增加了运维成本，同时也会造成冗余备份，影响系统可用性。分级备份属于定期增量备份。

  4. 异地冗余备份：异地冗余备份是指将不同区域的同一份数据存储在不同的位置，这样可以实现数据的快速访问，并满足在区域内出现意外事件时的灾难恢复需求。它是实现跨区域容灾的一种常用方式。

  5. 日志备份：日志备份是指为了防止数据丢失导致系统崩溃，将数据库执行过的每一个SQL语句都记录在日志文件里面的备份方案。当发生系统故障时，可以使用日志文件来还原数据。日志备份是一个可选的备份策略。

2.2.恢复策略

数据恢复是一个复杂的过程，因为它涉及多个环节，比如硬件、操作系统、应用程序、网络通信等，必须考虑各种因素的影响，才能达到最佳效果。数据恢复策略一般分为以下几类：

  • 灾难恢复（Disaster Recovery）：发生严重破坏、灾难或环境突然变化，或者出现其他不可抗力因素导致数据库无法正常运行，需要通过有效的备份恢复数据。灾难恢复一般采用多种备份策略组合来降低风险，保证数据可用性和持久性。
  • 主从切换恢复（Failover Recovery）：由于主库的故障，导致从库接管其工作负载，主从切换后，从库接管数据库后，数据库需要恢复到最新状态，并同步主库的数据，以保证主从库之间的数据一致性。
  • 业务连续性恢复（Business Continuity Recovery）：在业务活动中断的情况下，需要提供业务连续性功能，比如支持查询、分析、报告等，业务连续性恢复一般采用日志备份+热备份的方式来实现。
  • 恢复测试恢复（Recovery Testing）：由于恢复测试可能会使生产环境产生严重的损害，所以一般需要设计比较严苛的测试环境，来尽可能避免损害数据的完整性和可用性。

3.备份策略介绍

3.1.全量备份策略

全量备份是指将数据库中所有的数据库对象、表数据、日志信息等都进行备份，包括数据库结构、数据、日志以及配置文件等信息。它的特点是存储大、耗时长，全量备份是最经济的备份方式，但是对性能有一定影响。一般情况下，全量备份的周期为一年一次，或每隔几个月执行一次。

3.1.1.演练：如何执行MySQL的全量备份？

演示环境：MySQL版本号为8.0，服务器安装路径为/usr/local/mysql，使用的用户为root，密码为password。

步骤：

  1. 执行备份命令mysqldump -u root -p --all-databases > /backuppath/full_backup$(date +%Y-%m-%d_%H.%M.%S).sql。

    mysqldump命令用于备份MySQL数据库，-u选项指定用户名为root，-p选项指定输入密码。--all-databases选项表示备份所有数据库，>前面指定输出文件的绝对路径，后面使用date +%Y-%m-%d_%H.%M.%S)获取当前时间戳，作为备份文件的名称。

  2. 将备份文件压缩为zip文件，命名为full_backup$(date +%Y-%m-%d_%H.%M.%S).tar.gz。命令如下:tar zcvf full_backup$(date +%Y-%m-%d_%H.%M.%S).tar.gz /backuppath/full_backup$(date +%Y-%m-%d_%H.%M.%S).sql。

  3. 将压缩后的备份文件上传至远程备份服务器，命令为:scp /backuppath/full_backup$(date +%Y-%m-%d_%H.%M.%S).tar.gz username@remotehost:/backuppath/。

  4. 将远程备份目录添加到crontab中，命令为:crontab -e，并追加内容：0 */2 * * * cd /backuppath/; tar czvf full_backup_date +%Y-%m-%d_%H.%M.%S.tar.gz *.sql。此命令每两个小时执行一次，自动打包压缩/backuppath/目录下所有*.sql文件，并打包压缩为名为full_backup_日期时间.tar.gz的文件，上传至远程备份服务器的/backuppath/目录下。

  5. 配置远程备份服务器的邮件通知功能，发送邮件提醒上传成功。

以上五步即完成了 MySQL 的全量备份。但以上方法有一个缺陷，如果主库发生异常关闭，则备份失败。为了保证备份的完整性，应配置mysqldump脚本的--single-transaction选项，该选项能够确保生成的备份不会由于执行COMMIT、ROLLBACK等操作导致数据的不一致性。另外，建议启用binlog，保证数据的完整性和一致性。

注意事项：

  1. 每个表的大小不同，使用show table status;查看每张表的大小。

  2. 如果数据库很大，可以使用--max-allowed-packet=N限制每个请求的数据包最大值，来减少网络传输消耗。

  3. 使用SHOW MASTER STATUS和SHOW SLAVE STATUS查看主库信息和从库信息。

# 查看每张表的大小
mysql> show table status;
+-----------------------+--------+---------+------------+-------...
| Name                  | Engine | Version | Row_format | Rows ...
+-----------------------+--------+---------+------------+-------...
| mytable               | InnoDB |      10 | Dynamic    |    999
+-----------------------+--------+---------+------------+-------...

# 查看数据库的大小
du -h --apparent-size /var/lib/mysql/* | sort -hr
67G    /var/lib/mysql/ibdata1   # ibdata1: innoDB buffer pool, 忽略即可
3.5G    /var/lib/mysql/test      # test库
2.1G    /var/lib/mysql/performance_schema  # performance schema
1.6G    /var/lib/mysql/mysql      # mysql库，忽略即可
1.6G    /var/lib/mysql/sys        # sys库，忽略即可
1.5G    /var/lib/mysql/mydb       # mydb库
68K    /var/lib/mysql/mysqlx.sock # mysqlx服务端socket文件，忽略即可

3.2.增量备份策略

增量备份是指只备份自上次备份后发生的新的数据更新，或者自某一特定时间点起至现在的所有数据更新，它解决了全量备份不能完全覆盖历史数据的问题，但是仍然存在数据空间利用率低的问题。因此，增量备份可以定期对数据进行备份，将存储空间和备份周期压到最低。增量备份有两种形式：基于时间的增量备份和基于事务的增量备份。

3.2.1.基于时间的增量备份

基于时间的增量备份是在设定的时间间隔内，按照指定的备份策略，进行一次备份，从而实现数据的增量备份。它通过时间点或时间段来标识增量，从而实现对数据的实时备份。增量备份的周期一般取决于业务量大小。例如，当业务量不大时，增量备份周期设置为每天执行一次；当业务量非常大时，增量备份周期设置为一周执行一次。

关键参数：

  1. innodb_flush_log_at_trx_commit: 设置为1或2，表示每次事务提交时立即刷新日志。这可以确保日志及时写入磁盘，并保证事务完整性和一致性。

  2. master_heartbeat_period: 设定两个Master之间的心跳间隔，避免由于Master长时间失联而发生单点故障。

  3. sync_binlog: 设置为1或2，表示每一个事务提交时都会强制执行fsync()操作，确保事务日志及时写入磁盘。

  4. expire_logs_days: 指定日志文件过期时间。默认值为0，表示日志文件永不过期。若要设置日志文件过期时间，则可以在server配置文件中设置，也可以在客户端连接数据库时，通过参数指定。

优化建议：

  1. 在使用InnoDB引擎的数据库中，应启用innodb_buffer_pool_dump_at_shutdown和innodb_fast_shutdown两个系统变量。innodb_buffer_pool_dump_at_shutdown参数控制着InnoDB缓冲池在关闭的时候是否执行缓存刷新操作。若设置为1，则在关闭过程中缓冲池的缓存刷新操作会被延迟到最后才执行，进一步加速数据库的关闭过程。innodb_fast_shutdown参数控制着是否直接关闭数据库进程，而无需等待缓冲池刷新操作完成。若设置为1，则直接关闭数据库进程，而无需等待缓存刷新操作完成。

  2. 对于使用InnoDB引擎的数据库，应通过工具分析表的碎片化程度，并采取措施清理碎片化。在执行备份任务时，建议通过锁表的方式，禁止对表进行任何修改，避免表的增删改操作造成日志的大量积累。

  3. 通过设定binlog_cache_size参数可以调整binlog的缓存大小，减少磁盘IO操作。

  4. 在使用mydumper工具备份MySQL数据时，可以通过设置-B参数指定分批大小，避免一次性备份太多的数据，导致备份时间过长。

3.2.2.基于事务的增量备份

基于事务的增量备份是指按照事务数量的增量进行备份，它可以保证数据的完整性和一致性，但是由于记录了每个事务的修改，因此占用大量的备份空间。一般情况下，基于事务的增量备份周期设置为半年或一年。

关键参数：

  1. transaction_write_set_extraction: 设置为'enabled'，开启事务的脏页写入集提取功能。这可以将事务中所做的修改记录到备份文件中，从而实现基于事务的增量备份。

优化建议：

  1. 对InnoDB引擎的数据库，应禁止收集二进制日志。这是为了减少备份空间占用。

  2. 在备份过程中，为了确保备份数据的完整性和一致性，应尽量不要执行DDL操作。

3.2.3.演练：如何实现MySQL的增量备份？

演示环境：MySQL版本号为8.0，服务器安装路径为/usr/local/mysql，使用的用户为root，密码为password。

步骤：

  1. 修改MySQL配置，打开日志记录功能，并调整配置参数。
[mysqld]
general_log = on
slow_query_log = on
long_query_time = 10
sort_buffer_size = 16M
read_buffer_size = 64k
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
key_buffer_size = 16M
innodb_buffer_pool_size = 16G
innodb_thread_concurrency = 16
innodb_lock_wait_timeout = 50
max_connections = 2000
query_cache_type = ON
tmp_table_size = 64M
net_read_timeout = 30
net_write_timeout = 60
interactive_timeout = 600
wait_timeout = 180
  1. 创建一个新的用户backupuser并赋予该用户相应权限。
CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';
  1. 为backupuser创建备份目录/backuppath/inc_backup。
mkdir -p /backuppath/inc_backup; chown backupuser:backupuser /backuppath/inc_backup; chmod 700 /backuppath/inc_backup; ls -ld /backuppath/inc_backup;
  1. 创建一个新的数据库temp_backup，并赋予该用户读取和导出权限。
CREATE DATABASE temp_backup; GRANT SELECT, EXPORT ON temp_backup.* TO 'backupuser'@'localhost'; FLUSH PRIVILEGES; USE temp_backup;
  1. 创建一个触发器，在临时数据库temp_backup中记录所有的修改事务。
DELIMITER $$
CREATE TRIGGER trigger_backup BEFORE INSERT, UPDATE, DELETE ON *.* FOR EACH ROW BEGIN
    SET @ignore = CONCAT(@ignore,',',OLD._row_id); /* Record the affected row IDs */
    IF (@@SESSION.gtids_next!= '') THEN
        SET @sqlstmt = CONCAT('SET @ignore=',IFNULL(@ignore,''));
    ELSEIF (INSERT) THEN
        SET @sqlstmt = CONCAT('REPLACE INTO binlog_info(event_time,gtid)',
                              'VALUES (NOW(),CONCAT(\'-\',NOW()))');
    ELSEIF (DELETE) THEN
        SET @sqlstmt = CONCAT('REPLACE INTO binlog_info(event_time,gtid)',
                              'VALUES (NOW(),CONCAT(\'-\',NOW()))');
    ELSEIF (UPDATE) THEN
        SET @sqlstmt = CONCAT('REPLACE INTO binlog_info(event_time,gtid)',
                              'VALUES (NOW(),CONCAT(\'-\',NOW()))');
    END IF;
    PREPARE stmt FROM @sqlstmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
  1. 在临时数据库temp_backup中创建一个表binlog_info，用于记录备份的开始时间戳和对应的GTID。
CREATE TABLE binlog_info (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, gtid TEXT);
  1. 初始化备份任务，将所有修改的事务记录到binlog_info表中。
FLUSH BINARY LOGS; ALTER TABLE binlog_info DISABLE KEYS; RESET @@SESSION.GTID_NEXT; SELECT @@GLOBAL.gtid_executed AS executed, NOW(); REPLACE INTO binlog_info(gtid) VALUES ('-'.concat(substring(@@GLOBAL.gtid_executed,position(',)' IN @@GLOBAL.gtid_executed)+2)); COMMIT; ALTER TABLE binlog_info ENABLE KEYS; FLUSH BINARY LOGS; SHOW BINLOG EVENTS IN \'mysql-bin.\' WHERE @@global.gtid_executed IS NOT NULL AND GTID_SUBTRACT(\'<\',@@global.gtid_executed,\'>\' ) >= \'>\' LIMIT 100000,1;
  1. 使用mydumper工具备份所有修改的事务到/backuppath/inc_backup/temp_backup_日期时间.sql文件中。
mydumper -h localhost -u backupuser -P password -t temp_backup --rows 1000000 -o /backuppath/inc_backup/temp_backup_`date +'%Y-%m-%d_%H.%M.%S'`.sql --database temp_backup --threads 16 --compress 10;
  1. 清除临时数据库temp_backup中的数据和表。
DROP DATABASE temp_backup;
  1. 配置mysqldump脚本，以便在备份时自动使用--single-transaction选项，以保证备份数据的一致性。
sed -i "s/^\-\-hex\-blob/\-\-single-transaction/" /usr/local/mysql/bin/mysqldump

以上五步即完成了 MySQL 的增量备份。

4.应用场景与建议

4.1.场景一：主从同步差异备份

一般来说，主从同步备份的目的是为了实现数据的双向同步，以保证主库与从库的数据始终保持一致，避免主库出现问题时，数据丢失。因此，一般情况下，从库的数据恢复均采用逻辑备份方式。但是，当主库中的数据发生变更时，如果采用逻辑备份，就会出现同步延迟，导致数据不一致。这时，可以考虑进行增量备份。

在这种情况下，可以针对从库进行全量备份，然后再进行增量备份。具体流程如下：

  1. 从库执行全量备份，保存为full_backup_slave.sql文件。
  2. 从库执行增量备份，保存为inc_backup_slave.sql文件。
  3. 从库执行全量恢复，恢复到full_backup_slave.sql文件的备份状态。
  4. 从库执行增量恢复，恢复到full_backup_slave.sql文件后的最新状态。

4.2.场景二：业务连续性恢复

业务连续性恢复的目标是确保业务持续运行，即使发生意外的系统故障或者业务事件。因此，必须确保系统可以从备份中恢复，以保证数据的完整性和可用性。在这种情况下，可以先进行增量备份，然后再进行全量备份。具体流程如下：

  1. 根据需要设置备份策略，如每日、每周备份。
  2. 每次备份前检查数据库连接情况，确保备份顺利结束。
  3. 执行增量备份，保存为inc_backup_primary.sql文件。
  4. 检查备份结果，确认备份顺利结束。
  5. 执行全量备份，保存为full_backup_primary.sql文件。
  6. 检查备份结果，确认备份顺利结束。
  7. 停止应用，将原有的数据库拷贝，重命名为old_database。
  8. 拷贝备份的full_backup_primary.sql文件到原有数据库所在主机。
  9. 将拷贝的full_backup_primary.sql文件导入到原有数据库。
  10. 启动应用，进入正常的业务流水线。

4.3.场景三：异地备份

当系统发生全球性故障时，需要考虑数据的本地冗余备份。在这种情况下，可以考虑异地冗余备份。异地冗余备份就是将数据分别备份到多个站点，以实现数据的快速访问。具体流程如下：

  1. 根据业务需求确定数据需要备份的级别，如仅仅备份核心数据、备份全部数据。
  2. 根据备份站点的距离选择备份的策略。
  3. 配置网络冗余以实现异地备份。
  4. 登录各个备份站点，并将数据备份到各自的服务器上。
  5. 配合监控中心，定时检测备份站点的健康状况，并进行自动切换。

4.4.总结

本文详细介绍了数据库备份策略的相关知识，并提供了两套简单实用的备份策略和恢复策略的参考示例。希望通过阅读本文，读者能够了解数据库备份策略的基本理念、策略原理和应用场景，并灵活运用备份策略来保障数据库的安全和可用。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布