Difference between revisions of "Database Backup and Restore"

Revision as of 22:12, 9 December 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.

Verifying a Successful Restore

In the event that you do not specify the --verbose option, the only output from a successful run of the script will be:

Successfully restored backup.

If errors occur, error messages will be displayed.

To see more details (for both successful and failed restores), specify the --verbose command-line option.

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

Introduction

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

Examples

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

Introduction

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 you are replacing an existing database with a known-good backup by using a full restore--for example, because your database has been corrupted or a database schema upgrade failed or you're replacing a package-provided database with your "old" database after re-installing your distro--you will need to drop the existing database first. You can do this from the command line with the command:

mysql -uroot -p -e 'DROP DATABASE IF EXISTS mythconverg;'

If you do not drop the existing database before performing a full restore, the script will exit with the error, "ERROR: Unable to do a full restore. The database contains data." Once you have dropped the database, make sure you run the above command to execute the mc.sql script to create a new database, and then proceed with the restore.

When restoring a backup, the script will attempt to choose the most-recent backup file in the backup directory if no backup filename is specified on the restore command line. 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.

Examples

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 directory and filename, as required:

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

If you have specified the directory in the backuprc file, you can simply use:

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

Note that when specifying a backup filename, the directory component of the path should be specified separately. 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, as above.

Partial restore of a backup

A partial restore restores only the data about existing recordings and recording rules. It is primarily useful as a last-resort when some of the data in the database is corrupt (or, 2nd-to-last resort, as starting over from scratch would be a true last resort). In general, do not do a partial restore--instead do a full restore. Note that if data in the tables that store information about recordings and recording rules is corrupt, it will also be corrupt after restore. Furthermore, when doing a partial restore, you will need to completely re-configure MythTV and all plugins you wish to use after restoring the database.

Note, however, that a partial restore will only work if the backup schema version is identical to the schema version used by the currently-installed mythbackend. Therefore, if you are trying to do a partial restore at the same time as an upgrade, you must first (before the upgrade) do a complete backup of your old-schema-version database, then drop the database and create a new, empty database, then start mythtv-setup to create a new, empty schema, then exit mythtv-setup. Then do a partial restore of the old-schema-version database backup with data (that you just created) to the new, empty schema. Then, do a complete backup of the new, "partial", old-schema-version database. Once you've upgraded, rather than doing a partial restore of the old-schema-version database backup with full data, you'll do a complete restore of the partial, old-schema-version database backup (the one most-recently created). Also, if the upgrade involves changing hostname, ensure you change the hostname before starting any MythTV applications. Finally, perform the MythTV upgrade, and then start mythtv-setup to upgrade the "partial" database, the configure MythTV backends, frontend, and plugins.

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. Again, in general, do not do a partial restore--instead do a full restore.

If you must do a partial restore, adding the --with_plugin_data argument instructs the script to restore the same recording/rule data as well as all data from plugins, such as MythVideo, MythMusic, MythWeather, etc. Note that after the restore, you will still need to re-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