ZRM 2.1:备份MySQL分区表

ZRM 2.1:备份MySQL分区表 MySQL 5.1通常可供生产使用。其中的关键特点之一 MySQL 5.1是partit ...

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