首页 » MySQL » MySQL Backup/Restore tools 总结

MySQL Backup/Restore tools 总结

数据库存放着组织拥有的最重要的资产之一——数据。保持数据安全和不受破坏是关键任务。对于大多数应用程序来说,停机比数据丢失或损坏更痛苦。DBA的核心职责包括数据库的备份恢复,到了关键时刻非常考验方案的有效性,前两天一套几十T的oracle数据库,在vmware外挂两个存储设备,结果在扩容存储后,不知什么原因磁盘的文件头前5M左右都被清空,还好之前有备份,恢复了1天才搞定,可见oracle的RMAN备份还是完全可以信任。在MySQL开源数据库也存在一些备份工具,但存在一些差异,备份应根据恢复需求进行规划。这里简单整理。

数据库的备份需求

  • Online backup
  • Incremental Backup
  • Partial backup
  • Consistent Point in time Recovery
  • Roll Forward Recovery

备份方法

  • FULL
  • FULL + Incremental
  • Full + Incremental + Log
  • Slave Replication

–图片来自oracle

备份策略

–图片来自oracle

备份工具

  • Online backup( Physical backup )
    • MySQL Enterprise Backup   mysqlbackup
    • Percona XtraBackup
    • Mariabackup( for MariaDB )
  • Export/import ( Logcial backup )
    • mysqldump
    • mysql Shell
    • mysqlpump
    • mydumper
  • Standby Copy
    • MySQL Replication
  • Cold backup
    • offline, shutdown db server, OS copy files
  • file system volume managers snapshots
    • LVM  snapshot copy

物理备份比较

MySQL没有为社区版提供在线物理备份。您可以购买企业版MySQL Enterprise Backup或使用第三方工具。市场上最流行的第三方工具是XtraBackup(PXB) 。PXB和MySQL企业备份都为dba提供了高性能的在线备份解决方案,具有数据压缩和加密技术, 对比主流的工具 Percona XtraBackup vs MySQL Enterprise Backup,

MySQL Enterprise Backup

它可以在Linux、Windows、Mac和Solaris上运行。重要的是,它还可以将备份存储到磁带上,这通常是比写入磁盘更便宜的解决方案。直接写磁带支持与Veritas Netbackup、Tivoli Storage Manager和EMC network worker集成。

mysqlbackup 支持使用 MySQL 服务器的page tracking功能创建增量备份,通过此功能,mysqlbackup 会在 InnoDB 数据文件中查找自上次备份以来已修改的已更改页面,然后复制这些页面。一般来说,如果数据库中的大多数数据尚未修改,则使用页面跟踪的增量备份比 mysqlbackup 执行的其他类型的增量备份更快。另一方面,optimistic incremental backup, 仅扫描自上次备份以来已修改的 InnoDB 数据文件中的已更改页面,从而节省了一些不必要的扫描时间,并行备份绝对是mysqldump和Enterprise备份之间最大的区别之一。它通过多线程处理提高了性能。

  • support for myisam
  • high performace backup restore
    • backup >3x faster than mysqldump export (physical backup so fast)
    • restore >10x than mysqldump recovery
  • compressed backup
    • multi-level compression
    • save 70% or more of the storage required
    • unused block compression
  • fullbackup
  • incremental backup
  • partial abckups
  • point in time recovery
  • unlimited database size
  • cross-platform
  • Detect corruption
    • MySQL Enterprise Backup can check page checksums to ensure that your InnoDB log files and data are backed up correctly.

XtraBackup

Percona XtraBackup是一款非常流行的开源MySQL/MariaDB热备份软件,XtraBackup可能只能在Linux平台上运行,这无疑会阻碍那些在windows上运行的人。这里的解决方案可能是复制到运行在Linux上的从服务器,并从那里运行备份。

Percona 的 xtrabackup 是一种允许 DBA 对 InnoDB 数据库进行(虚拟)非阻塞快照的工具。它的工作原理是将数据文件从一个卷物理复制到另一个位置。您还可以通过网络将备份流式传输到将存储备份的单独备份主机。在复制数据时,它会密切关注 InnoDB 重做日志并记录在此期间发生的任何更改。最后,它会执行 FLUSH TABLES WITH READ LOCK(这就是我们使用“虚拟”一词的原因)并完成备份。由于最后一个锁,备份是一致的。如果您使用 MyISAM 表,xtrabackup 的影响更大,因为非事务表必须在 FTWRL 到位时通过网络复制 – 根据这些表的大小,可能需要一段时间。在此期间,不会在主机上执行任何查询。

 

Feature Percona XtraBackup MySQL Enterprise Backup
(InnoDB Hot Backup)
License

GPL

Proprietary

Price

Free

Included in subscription at $5000 per Server

Open source

chack-circle

ban-circle

Streaming and encryption formats

Open source

Proprietary

Supported MySQL flavors

Percona Server, MySQL, MariaDB

MySQL

Non-blocking InnoDB backups 1

chack-circle

chack-circle

Blocking MyISAM backups

chack-circle

chack-circle

Incremental backups

chack-circle

chack-circle

Full compressed backups

chack-circle

chack-circle

Incremental compressed backups

chack-circle

ban-circle

Fast incremental backups 2

chack-circle

chack-circle

Incremental backups with archived logs

chack-circle

ban-circle

Backup locks 8

chack-circle

chack-circle

Encrypted backups

chack-circle

chack-circle 3

Streaming backups

chack-circle

chack-circle

Parallel local backups

chack-circle

chack-circle

Parallel streaming backups

chack-circle

ban-circle

Parallel compression

chack-circle

chack-circle

Parallel encryption

chack-circle

chack-circle

Parallel apply-log

chack-circle

chack-circle

Parallel copy-back

chack-circle

chack-circle

Partial backups

chack-circle

chack-circle

Throttling 4

chack-circle

chack-circle

Point-in-time recovery support

chack-circle

chack-circle

Safe secondary backups

chack-circle

chack-circle

Compact backups 5

chack-circle

chack-circle

Buffer pool state backups

chack-circle

chack-circle

Individual tables export

chack-circle

chack-circle 6

Individual partitions export

chack-circle

ban-circle

Restoring tables to a different server 7

chack-circle

chack-circle

Data & index file statistics

chack-circle

ban-circle

InnoDB secondary indexes defragmentation

chack-circle

ban-circle

rsync support to minimize lock time

chack-circle

chack-circle

Improved FTWRL handling

chack-circle

ban-circle

Backup history table

chack-circle

chack-circle

Backup progress table

ban-circle

chack-circle

Offline backups

chack-circle

chack-circle

Backup to tape media managers

chack-circle

chack-circle

[1] InnoDBtables are still locked while copying non-InnoDBdata.
[2] Fast incremental backups are supported for Percona Server with XtraDB changed page tracking enabled.
[3] Percona XtraBackup supports encryption with any kinds of backups. MySQL Enterprise Backup only supports encryption for single-file backups.
[4] Percona XtraBackup performs throttling based on the number of IO operations per second. MySQL Enterprise Backup supports a configurable sleep time between operations.
[5] Percona XtraBackup skips secondary index pages and recreates them when a compact backup is prepared. MySQL Enterprise Backup skips unused pages and reinserts on the prepare stage.
[6] Percona XtraBackup can export individual tables even from a full backup, regardless of the InnoDB version. MySQL Enterprise Backup uses InnoDB 5.6 transportable tablespaces only when performing a partial backup.
[7] Tables exported with Percona XtraBackup can be imported into Percona Server 5.1, 5.5 or 5.6+, or MySQL 5.6+. Transportable tablespaces created with MySQL Enterprise Backup can only be imported to Percona Server 5.6+, MySQL 5.6+ or MariaDB 10.0+.
[8] Lock table for backup

小结

我们希望对MySQL备份方法的介绍,帮助您找到保护数据的解决方案。要记住的主要事情是,如果您不知道您的备份进程设计是否合理,请进行测试。只要您有一个能够满足业务工作备份恢复需求,就没有坏的设计方法,  哪怕使用逻辑表导出。只要记住不时地测试恢复,并确保数据库的备份可以及时恢复。

 

References
https://www.percona.com/mysql/software/percona-xtrabackup/feature-comparison
https://www.slideshare.net/slideshow/mysql-enterprise-backup-69523045/69523045

打赏

目前这篇文章还没有评论(Rss)

我要评论