mysql表分区备份_ZRM 2.1：备份MySQL分区表
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-infoREPORT 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-infoREPORT 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 。