Database Backup and Restore

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 mythconverg_backup.pl mythconverg_restore.pl

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

By saving the scripts to a directory in the user's PATH, you can execute them without specifying a full path. If you do not save them to a directory in the PATH, you should execute the scripts from the current directory as:

./mythconverg_backup.pl --help

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 from a shell (not in MySQL), 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:

mythconverg_backup.pl

(assuming the script is in the user's PATH). If you would like to see what's happening, run the command with the --verbose argument, instead:

mythconverg_backup.pl --verbose

Note that many of the command-line options described below may be added to the backup resource file, as described in the --help output.

Automating Database Backups

Once you have verified your backup configuration by successfully creating a backup with the mythconverg_backup.pl script, you may want to automate your database backups by configuring a cron job. To minimize the chances of affecting recordings and to attempt to prevent data-integrity issues, you should choose a time to run the backup script when your system is least likely to be recording any shows.

Alternatively, you may wish to run the backup script from a system start/shutdown script (i.e an init script). When doing so, call the script before starting mythbackend or after stopping mythbackend. Note, however, that checking whether to perform the backup is the responsibility of the init script, not the mythconverg_backup.pl script. Therefore, in a system with multiple frontends/backends, the init script should ensure the backup is created only on the master backend.

Database Backup

The mythconverg_backup.pl 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

mythconverg_backup.pl --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).

mythconverg_backup.pl --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.

mythconverg_backup.pl --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.)

mythconverg_backup.pl --backup_xmltvids

Database Restore

The mythconverg_restore.pl script will restore a backup of the MythTV database (as created by the mythconverg_backup.pl script). When restoring, you're simply restoring the database schema and data, so the database itself must be created first. The proper way to create the database is to run the mc.sql script (distributed in the database directory of the source distribution). On most MySQL installations, running this script requires database root privilages--i.e. you must run it as the database user root, for example:

mysql -uroot -p < database/mc.sql

If you do not create the database before running the restore script and you did not specify the --create_database argument to the script, the script will exit with the error, "ERROR: The database does not exist."

If a backup filename is not specified, the most-recent backup file in the backup directory will be automatically selected for restore. The script determines which backup is most recent based on the date/time information in the backup filename, not the database schema version or the filesystem timestamp. The script will automatically determine whether the file is compressed and uncompress the file, as needed.

Full restore of the most-recent backup file

The script determines which is the most-recent backup file based on the date/time information in the backup filename and database schema version information is ignored. To ensure you're restoring the desired backup file, you may wish to do a Full restore of a specific backup file

mythconverg_restore.pl

Full restore of a specific backup file

Change the filename, as required:

mythconverg_restore.pl --filename mythconverg-1214-20080626150513.sql.gz

Note that when specifying a backup filename, the directory component of the path should not be specified. If the file does not exist in the DBBackupDirectory specified in the backup resource file (~/.mythtv/backuprc), specify the directory location with the --directory argument.

mythconverg_restore.pl --directory /home/mythtv --filename mythconverg-1214-20080626150513.sql.gz

Partial restore of a backup

A partial restore restores only the data about existing recordings and recording rules. It is primarily useful when installing MythTV on new hardware where it's easier to start with the default configuration and change settings for the current hardware rather than undo settings for the old hardware and change settings for the new hardware. After restoring the database, you will need to configure MythTV and all plugins you wish to use.

mythconverg_restore.pl --partial_restore

If you would like to do a partial/new-hardware restore and have upgraded MythTV, you must first do a full restore, then start and exit mythtv-setup (to upgrade the database), then create a backup, then drop the database, then follow the instructions for doing a partial restore with the new (upgraded) backup file.

Partial restore of a backup, including plugin data

A partial restore, as described above, restores only the data about existing recordings and recording rules. By adding the --with_plugin_data argument, the same recording/rule data will be restore, as well as all data from plugins. Note that after the restore, you will still need to configure both MythTV and any plugins you plan to use.

mythconverg_restore.pl --partial_restore --with_plugin_data

Change the hostname of a MythTV frontend or backend

Note that changing the hostname is performed on an existing database and does not restore a database. Therefore, to change a hostname, ensure that the database exists (restore an old database, as above, if necessary) and execute the following command, replacing "XXXX" and "YYYY" with appropriate values for the old and new hostnames, respectively:

mythconverg_restore.pl --change_hostname --old_hostname="XXXX" --new_hostname="YYYY"

Restore xmltvids after a channel scan

To restore xmltvids using a backup created with the mythconverg_backup.pl --backup_xmltvids, execute the following:

mythconverg_restore.pl --restore_xmltvids

To specify which backup file to use:

mythconverg_restore.pl --restore_xmltvids --filename mythtv_xmltvid_backup-20080626211808.sql