ZRM 2.1: Backing Up MySQL Partitioned Tables

MySQL 5.1 is generally available for production use. One of the key features of MySQL 5.1 is partitioning. Table partitioning can help in improving performance and usability. Tables can be partitioned based on range (column values in a given range), list (column values matching a set of values), hash (user defined hash based on column values) or key (predefined hash function based on column values). Each partition can have different data directory.

Zmanda Recovery Manager for MySQL (ZRM) 2.1 release supports MySQL 5.1GA release. ZRM provides efficient backup and recovery of MySQL database. It can perform logical, raw as well as snapshot based backups. ZRM 2.1 supports storage snapshots (LVM and Solaris ZFS) as backup images and can be converted into regular backup image later.

This how to shows how to install and configure ZRM 2.1 to perform backup and recovery of MySQL partitioned tables.

This example uses MySQL 5.1.30 running on CentOS 4. The MySQL server has a myisamnetflix database that contains the MovieRatings partitioned table.

* Install MySQL 5.1.30 on the server. Download server and client images from the MySQL downloads site. The following rpms should be downloaded and installed:

MySQL-client-community-5.1.30-0.rhel4
MySQL-server-community-5.1.30-0.rhel4

* Follow the instructions to create default MySQL database and run MySQL server.

* Check to see if the MySQL partitioning is enabled

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

* Create the table with key partitioning on MovieID column and populate the table with data.

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;

* Download ZRM 2.1 rpm from the Zmanda downloads page.

* Create mysql-zrm.conf in the /etc/mysql-zrm/partition-test directory. partition-test is the backup set name.

host="localhost" 
databases="myisamnetflix" 
password="boot12" 
user="root" 
compress=1 
backup-mode=logical 

* Run a full backup of the backup set partition-set as mysql user. All ZRM operations should be performed as mysql user:

$ 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

* Use ZRM reporter to look at the status of the backups.

$ /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)

* Run a report to identify the backup image to be restored and restore from the backup image.

$ /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/

restore:INFO: ZRM for MySQL Community Edition - version 2.1
BackupSet1:restore:WARNING: Binary logging is off.
BackupSet1:restore:INFO: Restored database(s) from logical backup: myisamnetflix
BackupSet1:restore:INFO: Restore done in 163 seconds.

* Check to see if the deleted rows in the MovieID table have been restored.

mysql> select count(*) from MovieID where MovieTitle regexp 'Sherlock Holmes*';

28 rows in set (0.23 sec)

The above example shows ZRM can be used to backup and recover partitioned tables in MySQL 5.1. A whitepaper on how to quickly configure ZRM 2.1 is available at http://www.zmanda.com/quick-mysql-backup.html.