Quick Start Example - Backup/Recovery of partitioned tables
Jump to navigation
Jump to search
This example uses MySQL 5.1.30 running on CentOS 4. The MySQL server has a myisamnetflix database that contains the MovieRatings partitioned table. Logical backup method is used.
Installation
- 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.