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 |
|
Open source |
|
|
Streaming and encryption formats |
Open source |
Proprietary |
Supported MySQL flavors |
Percona Server, MySQL, MariaDB |
MySQL |
Non-blocking InnoDB backups 1 |
|
|
Blocking MyISAM backups |
|
|
Incremental backups |
|
|
Full compressed backups |
|
|
Incremental compressed backups |
|
|
Fast incremental backups 2 |
|
|
Incremental backups with archived logs |
|
|
Backup locks 8 |
|
|
Encrypted backups |
|
|
Streaming backups |
|
|
Parallel local backups |
|
|
Parallel streaming backups |
|
|
Parallel compression |
|
|
Parallel encryption |
|
|
Parallel apply-log |
|
|
Parallel copy-back |
|
|
Partial backups |
|
|
Throttling 4 |
|
|
Point-in-time recovery support |
|
|
Safe secondary backups |
|
|
Compact backups 5 |
|
|
Buffer pool state backups |
|
|
Individual tables export |
|
|
Individual partitions export |
|
|
Restoring tables to a different server 7 |
|
|
Data & index file statistics |
|
|
InnoDB secondary indexes defragmentation |
|
|
rsync support to minimize lock time |
|
|
Improved FTWRL handling |
|
|
Backup history table |
|
|
Backup progress table |
|
|
Offline backups |
|
|
Backup to tape media managers |
|
|
[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
对不起,这篇文章暂时关闭评论。