Database Backup and Restore

From MythTV Official Wiki
Revision as of 01:19, 24 July 2008 by Sphery (talk | contribs) (Initial database backup information)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Script-based Database Backup and Restore

MythTV 0.22 adds a new scripted approach for performing database backups and restores. The scripts provided in the source distribution are full-featured, but may be replaced or modified by packagers to make the scripts more suitable for use with the distribution. The following text describes the use of the scripts distributed with the source version.

Although the scripts are only distributed with post-0.21 versions of MythTV, they were written to work with any version of MythTV (including 0.21-fixes and before). For those users running versions of MythTV that do not include the scripts, the scripts are available for download from the source repository (backup and restore). After downloading the scripts, save them to a location where you'll be able to find them and make them executable:

chmod a+x

Detailed help is available by executing either script with the --help argument.

Quick Start

Both scripts require several pieces of information, much of which can be determined by looking at the MythTV configuration files (~/.mythtv/config.xml or the MythTV mysql.txt file(s)). However, the backup directory must be specified. While the directory can be specified with the --directory command-line argument, since it's likely to be the same value for every run, it makes sense to add the backup directory to a backup resource file (~/.mythtv/backuprc). Do so by running the following command, replacing the directory, "/home/mythtv", with the desired backup directory path:

echo "DBBackupDirectory=/home/mythtv" > ~/.mythtv/backuprc

At this point, creating a backup should be as easy as running:

To see what's happening, run the command with the --verbose argument: --verbose

Database Backup

The script will create a database backup, compress the backup using the gzip compression algorithm, and rotate backups (keeping the 5 newest backups).

Backup database and rotate keeping the 10-most-recent backups --rotate 10

Backup database and compress with bzip2

Warning, compressing MySQL backup files with bzip2 is /significantly/ more resource-intensive than compressing with gzip (taking greater than 7 minutes, compared to 19 seconds, on my Athlon XP 2400+ with a 200MiB (before compression) backup file) but yields only about a 33% space savings (taking about 20MiB versus 30MiB after compression). Therefore, you should be very careful using bzip2 compression as doing so may interfere with your system's ability to record shows and only saves a small amount of space--the equivalent of less than 1 minute of recording of low-quality standard-definition MPEG-2). --compress bzip2

Backup database when mysqldump is not in the user's PATH

While it's possible to specify the location of the mysqldump executable, it's probably easier to add the directory containing mysqldump to the user's PATH or to place a link to the mysqldump executable in a directory that's in the user's PATH. --mysqldump /path/to/mysqldump

Backup xmltvid data (before doing a full channel scan)

Rather than creating a backup of the entire database, the backup script can be used to create a backup of xmltvid's. The backup contains a series of SQL UPDATE statements that can be executed to set the xmltvid for channels whose callsign is the same before and after the scan. Note, also, that the backup file will contain comments with additional channel information, which you can use to identify channels in case the callsign changes. (As a matter of fact, one commented section contains alternate-syntax UPDATE statements that set the xmltvid for channels whose channel number and name are the same before and after the scan.) --backup_xmltvids

Database Restore