Database Backup and Restore

From MythTV Official Wiki
Jump to: navigation, search

Script-based Database Backup and Restore

MythTV distributes scripts for performing MythTV 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.

The scripts were written to work with any version of MythTV. The most-up-to-date version of the scripts are available for download from the source repository (backup and restore). If you cannot find the scripts installed on your system or if you just want the most-current version, download copies from the previous links. 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.

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:

./ --help

Quick Start

Both scripts require several pieces of information, much of which can be determined by looking at the MythTV configuration file, ~/.mythtv/config.xml. 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

If you do not specify the directory as above, the backups will be stored to a directory defined in the DB Backups storage group, falling through to the Default storage group.

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

(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: --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.

A full database restore may take up to an hour. 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 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 script. Therefore, in a system with multiple frontends/backends, the init script should ensure the backup is created only on the master backend.

If you run mythwelcome on a dedicated frontend/backend then the script can be used to perform a daily database backup at final closedown of the day when the system is otherwise idle.

Database Backup

Before Running the Script

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

These defaults should work well for all users. To use the defaults, create a backup resource file that specifies a backup directory, and backup the database using:

See the below examples for information on changing some of the defaults.


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

The Role of When Changing MythTV Versions

When changing versions, (e.g. from 34.0 to 34), the database schema is likely to change. Upgrading the database to the current schema version is done automatically.

To help users who may need to revert to the previous version (and schema), MythTV will attempt to perform an automatic backup of the database. This is done solely as a last resort for those users who didn't properly prepare for the upgrade. You should never rely on the automatic backup. Always manually perform a database backup (using and copy the resulting backup to a safe location before upgrading MythTV versions.

The script (or mysql_dump if isn't available) is used to create the automatic backup. The backup is stored in the directory with the most available space defined under the DB Backups Storage Group. If DB Backups isn't found, then the Default Storage Group is used. If no accessible directories are found there, the backup will be stored in /tmp.

Savy users will verify that this backup process is working before changing MythTV versions. To prove that the backup will work, it must be tested using the same environment as the user doing the upgrade. That means that if mythbackend requests the upgrade, the user is most likely mythtv. If the user chooses to run mythtv-setup before starting the backend, the user will be themself ($USER.)

If the user selected above has a .mythtv/backuprc file, with an entry similar to:

DBBackupDirectory /var/lib/db_backups

and that matches the DB Backups or Default Storage Group, then manually running will fairly test the backup that will be done before the upgrade starts (the .mythtv/backuprc DBBackupDirectory directory overrides the Storage Group entry.)

Obviously, if a manual run of puts the backup in a directory other than the one done by the upgrade process, the test is of no value.

Why bother? Discussions on the mythtv-users' mailing list confirm that sometimes the automatic database backup can't be found. Likely causes are write permission problems, or, if /tmp is used and the machine is re-booted before the user realizes the backup is needed, its no longer available. Remember, though, that the automatic backup is only a last resort. You should never need the backup created automatically for you because you will always have a backup you created before upgrading MythTV.

Important.png Note: Store a copy of your backup in a safe place. A thumb drive is nice, as is a NAS.

Checking your schema version

Occasionally you may need to check what your schema version is. This can be done in a number of ways

  • (easiest) Look at the database setup page of the Web App (v34 and later)
  • check in the database:
 select data from settings where value = 'DBSchemaVer';

Even more occasionally, you may want to check if the schema version you are on is the correct version. The required schema version for a particular mythtv release is defined in the mythtv code. Make sure you look at the git branch that matches the version you are running. The most likely locations to find it are

  • mythtv/bindings/perl/
  • mythtv/libs/libmythtv/dbcheck.cpp

Failing that, git grep may help you find it.

Database Restore

Before Running the Script

The script will restore a backup of the MythTV database (as created by the script). When restoring, you're simply restoring the database schema and data, so the database itself must be created first, but must remain empty (no tables or data) so do not run mythtv-setup or mythbackend before restoring the database. Also, the MySQL Time Zone Tables must be installed on the MySQL server prior to using MythTV. If they have not yet been installed on the server (it's a new MySQL server install) or you did anything to remove them, ensure you install them first.

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.


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

Full restore of a specific backup file

Change the directory and filename, as required: --directory /home/mythtv --filename mythconverg-1214-20080626150513.sql.gz

If you have specified the directory in the backuprc file, you can simply use: --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.

Replacing an existing database

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. You can do this by adding the --drop_database --create_database arguments to the command-line. --drop_database --create_database --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 as a last-resort when some of the data in the database, or the database schema itself, is corrupt (or, more precisely, a 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.

When doing a partial restore, you will need to completely re-configure MythTV (including all MythTV hosts--all frontend and backend systems) and all plugins you wish to use after restoring the database. Even if you uninstall some plugins you had used in the previous version of MythTV, the storage space used by the old plugin data will be negligible compared to the overall database size, and there will be no performance impact from leaving the old plugin data intact. And since MythTV and MythTV plugins automatically clean up the data in the database, even a full restore of a years-old database will be just as clean as a new database into which you've done a partial restore.

Therefore, a partial restore is significantly more difficult and time-consuming than a full restore, leaves significantly more opportunities for problems, and provides no benefits to the user or the system. Note, also, that if data in the tables that store information about recordings and recording rules is corrupt, it will also be corrupt after restore--so a partial restore is not a magic fix for broken data.

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. If you must do a partial restore when upgrading MythTV, there are several extra steps to perform--just one more great reason to do a full restore.

Before performing a partial restore, the database must exist and must have (mostly-empty) tables. First, drop the existing database. You can do this from the command line with the command:

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

then create a new database (and note that doing so may change MySQL passwords/permissions, so may cause authentication problems). Then, start mythtv-setup to create the database schema. Finally, exit mythtv-setup, and then restore the database: --partial_restore
Partial restore when upgrading MythTV

Again, a partial restore is primarily useful as a last-resort when some of the data in the database is corrupt. In general, do not do a partial restore--instead do a full restore. See above for reasons why the partial restore is not necessary and is not recommended.

If you are trying to do a partial restore at the same time as an upgrade, you must ensure that the database backup containing your data is for the same database schema version as the running database schema version. You can not use a partial restore to circumvent the database upgrade process. This makes a partial restore much more complicated than a full restore, so again, you should probably just do a full restore.

To do a partial restore when upgrading your MythTV version, first (before the MythTV upgrade) do a complete backup of your old-schema-version database, then drop the database and create a new, empty database, then start (the old-version) mythtv-setup to create a new, empty (old-version) schema, then exit mythtv-setup. Then do a partial restore of the old-schema-version database backup to the new, empty database schema (that you just created). 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, then configure MythTV backends, frontend, and plugins.

If you've already upgraded MythTV and did not create a backup of an "empty" old-schema-version database, you will need to do a full restore of your old-version backup. Then, start mythtv-setup to upgrade the schema. If this worked, there is no reason to do a partial restore. If you still feel you must, you may then do a complete backup of your new-schema-version database and then do a partial restore (from the top--you're just now ready to start the partial restore process).

Change the hostname of a MythTV frontend or backend

Before changing the hostname of a MythTV frontend or backend, make sure you create a good database backup, so that you can get back to a known state if things go wrong.

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 will fail because of duplicate entries--and will corrupt the database in the process. Therefore, you will need to restore the database backup you made before attempting the host name change (specifically, one you made before you ran any MythTV programs on the new host name), and then perform the name change.

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: --change_hostname --old_hostname="XXXX" --new_hostname="YYYY"

Restore xmltvids after a channel scan

To restore xmltvids using a backup created with the --backup_xmltvids, execute the following: --restore_xmltvids

To specify which backup file to use: --restore_xmltvids --filename mythtv_xmltvid_backup-20080626211808.sql