Difference between revisions of "Database Backup and Restore"

Revision as of 20:23, 14 January 2009

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 or to ensure your database schema is correct without losing old data. Note, however, that it will only work if the backup schema version is identical to the schema version used by the currently-installed mythbackend. After restoring the database, you will need to configure MythTV and all plugins you wish to use.

Before performing a partial restore, the database must exist and must have tables. After dropping the mythconverg database:

DROP DATABASE IF EXISTS mythconverg;

run the mc.sql script, as described above. Then, start mythtv-setup to create the database schema. Exit mythtv-setup, and then restore the database:

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, such as MythVideo, MythMusic, MythWeather, etc. Note that after the restore, you will still need to configure both MythTV and any plugins you plan to use.

In addition to the preparation mentioned above, you must start mythbackend (you may do this instead of starting mythtv-setup) and mythfrontend to ensure all the plugin tables are created and then exit mythfrontend and mythbackend before performing the restore:

mythconverg_restore.pl --partial_restore --with_plugin_data

Change the hostname of a MythTV frontend or backend

When changing the hostname of your system, you must perform the following actions before you start any MythTV programs. If you start a MythTV program before modifying the hostname in the database, the program will create new entries with the new hostname, meaning that the following procedure is likely to fail because of duplicate entries.

It is also critical to ensure you do not change the name of a host to the name of another already-existing host in the database--doing so will cause duplicate entries. Instead, if necessary, run the command below multiple times, using a temporary "placeholder" hostname to ensure the different hosts are not improperly merged.

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