ZRM 2.1:备份MySQL分区表
MySQL 5.1通常可供生产使用。 MySQL 5.1的主要功能之一就是分区。 表分区可以帮助提高性能和可用性。 表可以根据范围(给定范围内的列值),列表(匹配一组值的列值),哈希(基于列值的用户定义哈希)或键(基于列值的预定义哈希函数)进行分区。 每个分区可以有不同的数据目录。
Zmanda Recovery Manager for MySQL(ZRM) 2.1版本支持MySQL 5.1GA版本。 ZRM提供了MySQL数据库的高效备份和恢复。 它可以执行逻辑,原始以及基于快照的备份。 ZRM 2.1支持存储快照(LVM和Solaris ZFS)作为备份映像,稍后可以转换为常规备份映像。
这样如何显示如何安装和配置ZRM 2.1来执行MySQL分区表的备份和恢复。
此示例使用在CentOS 4上运行的MySQL 5.1.30。MySQL
服务器具有包含MovieRatings
分区表的myisamnetflix
数据库。
*在服务器上安装MySQL 5.1.30。 从MySQL下载站点下载服务器和客户端映像。 应下载并安装以下rpms:
MySQL-client-community-5.1.30-0.rhel4
MySQL-server-community-5.1.30-0.rhel4
*按照说明创建默认的MySQL数据库并运行MySQL服务器。
*检查MySQL分区是否启用
mysql> SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
*在MovieID列上创建具有密钥分区的表,并使用数据填充表。
mysql> CREATE TABLE `MovieRatings` (
`MovieID` int(6) NOT NULL,
`CustomerID` varchar(10) NOT NULL,
`Rating` int(1) DEFAULT NULL,
`Date` date NOT NULL
) ENGINE=MyISAM PARTITION BY KEY (MovieID) PARTITIONS 3;
*从Zmanda下载页面下载ZRM 2.1 rpm。
*在/ etc / mysql-zrm / partition-test
目录下创建mysql-
zrm.conf。 partition-test
是备份集名称。
host="localhost" databases="myisamnetflix" password="boot12" user="root" compress=1 backup-mode=logical
*运行备份集分区设置
为mysql
用户的完整备份。 所有ZRM操作都应该以mysql
用户身份执行:
$ mysql-zrm-scheduler --now --backup-set partition-test --backup-level 0
schedule:INFO: ZRM for MySQL Community Edition - version 2.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
backup:INFO: ZRM for MySQL Community Edition - version 2.1
partition-test:backup:INFO: START OF BACKUP
partition-test:backup:INFO: PHASE START: Initialization
partition-test:backup:WARNING: Binary logging is off.
partition-test:backup:INFO: backup-set=partition-test
partition-test:backup:INFO: backup-date=20081125181119
partition-test:backup:INFO: mysql-server-os=Linux/Unix
partition-test:backup:INFO: backup-type=regular
partition-test:backup:INFO: host=localhost
partition-test:backup:INFO: backup-date-epoch=1227665479
partition-test:backup:INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 2.1
partition-test:backup:INFO: mysql-version=5.1.30-community
partition-test:backup:INFO: backup-directory=/var/lib/mysql-zrm/partition-test/20081125181119
partition-test:backup:INFO: backup-level=0
partition-test:backup:INFO: backup-mode=logical
partition-test:backup:INFO: PHASE END: Initialization
partition-test:backup:INFO: PHASE START: Running pre backup plugin
partition-test:backup:INFO: PHASE END: Running pre backup plugin
partition-test:backup:INFO: PHASE START: Flushing logs
partition-test:backup:INFO: PHASE END: Flushing logs
partition-test:backup:INFO: PHASE START: Creating logical backup
partition-test:backup:INFO: logical-databases=myisamnetflix
partition-test:backup:INFO: PHASE END: Creating logical backup
partition-test:backup:INFO: PHASE START: Calculating backup size & checksums
partition-test:backup:INFO: backup-size=160.44 MB
partition-test:backup:INFO: PHASE END: Calculating backup size & checksums
partition-test:backup:INFO: PHASE START: Compression/Encryption
partition-test:backup:INFO: compress=
partition-test:backup:INFO: backup-size-compressed=36.04 MB
partition-test:backup:INFO: PHASE END: Compression/Encryption
partition-test:backup:INFO: read-locks-time=00:01:27
partition-test:backup:INFO: flush-logs-time=00:00:00
partition-test:backup:INFO: compress-encrypt-time=01:23:07
partition-test:backup:INFO: backup-time=00:02:17
partition-test:backup:INFO: backup-status=Backup succeeded
partition-test:backup:INFO: Backup succeeded
partition-test:backup:INFO: PHASE START: Running post backup plugin
partition-test:backup:INFO: PHASE END: Running post backup plugin
partition-test:backup:INFO: PHASE START: Mailing backup report
partition-test:backup:INFO: PHASE END: Mailing backup report
partition-test:backup:INFO: PHASE START: Cleanup
partition-test:backup:INFO: PHASE END: Cleanup
partition-test:backup:INFO: END OF BACKUP
/usr/bin/mysql-zrm started successfully
*使用ZRM记者查看备份的状态。
$ /usr/bin/mysql-zrm-reporter --where backup-set=partition-test --show backup-status-info
REPORT TYPE : backup-status-info
backup_set backup_date backup_level backup_status backup_type comment
-----------------------------------------------------------------------------------------------------------------------------
partition-test Tue 25 Nov 2008 06:11:19 0 Backup succeeded regular ----
PM PST
* Delete a few rows from the MovieID
database to test the recovery from the backup image.
mysql> delete from MovieID where MovieTitle regexp 'Sherlock Holmes*';
Query OK, 28 rows affected (0.15 sec)
*运行报告以识别要还原的备份映像,并从备份映像进行还原。
$ /usr/bin/mysql-zrm-reporter --where backup-set=partition-test --show restore-info
REPORT TYPE : restore-info backup_set backup_date backup_level backup_directory backup_status comment ----------------------------------------------------------------------------------------------------------------------------------------------------- partition-test Tue 25 Nov 2008 06:11:19 0 /var/lib/mysql-zrm/partition-test/200811 Backup succeeded ---- PM PST 25181119
$ /usr/bin/mysql-zrm-restore --user=root --password=boot12 --source-directory=/var/lib/mysql-zrm/partition-test/20081125181119/
恢复:信息:ZRM for MySQL社区版 - 版本2.1
BackupSet1:restore:警告:二进制日志记录已关闭。
BackupSet1:restore:INFO:从逻辑备份恢复数据库:myisamnetflix
BackupSet1:restore:INFO:在163秒内恢复完成。
*检查MovieID
表中已删除的行是否已被还原。
mysql> select count(*) from MovieID where MovieTitle regexp 'Sherlock Holmes*';
28 rows in set (0.23 sec)
上面的例子说明了ZRM可以在MySQL 5.1中备份和恢复分区表。 有关如何快速配置ZRM 2.1的白皮书,请访问http://www.zmanda.com/quick-mysql-backup.html 。