Difference between revisions of "Database Backup and Restore"

From MythTV Official Wiki
Jump to: navigation, search
m (Introduction: Break up the restore intro into specific sections/steps)
m (Fix 3rd typo)
(42 intermediate revisions by 9 users not shown)
Line 3: Line 3:
 
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.
 
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 ([http://svn.mythtv.org/trac/browser/trunk/mythtv/programs/scripts/database/mythconverg_backup.pl?format=txt backup] and [http://svn.mythtv.org/trac/browser/trunk/mythtv/programs/scripts/database/mythconverg_restore.pl?format=txt restore]).  After downloading the scripts, save them to a location where you'll be able to find them and make them executable:
+
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 or for users wanting updated versions of the scripts, the scripts are available for download from the source repository ([https://github.com/MythTV/mythtv/raw/master/mythtv/programs/scripts/database/mythconverg_backup.pl backup] and [https://github.com/MythTV/mythtv/raw/master/mythtv/programs/scripts/database/mythconverg_restore.pl restore]).  After downloading the scripts, save them to a location where you'll be able to find them and make them executable:
  
 
<pre>chmod a+x mythconverg_backup.pl mythconverg_restore.pl</pre>
 
<pre>chmod a+x mythconverg_backup.pl mythconverg_restore.pl</pre>
Line 14: Line 14:
  
 
== Quick Start ==
 
== Quick Start ==
 
+
Both scripts require several pieces of information, much of which can be determined by looking at the MythTV configuration file, <code>~/.mythtv/config.xml</code>.  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 (<code>~/.mythtv/backuprc</code>).  Do so by running the following command from a shell (not in MySQL), replacing the directory, <code>/home/mythtv</code>, with the desired backup directory path:
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:
+
  
 
<pre>echo "DBBackupDirectory=/home/mythtv" > ~/.mythtv/backuprc</pre>
 
<pre>echo "DBBackupDirectory=/home/mythtv" > ~/.mythtv/backuprc</pre>
 +
 +
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:
 
At this point, creating a backup should be as easy as running:
Line 28: Line 29:
  
 
Note that many of the command-line options described below may be added to the backup resource file, as described in the --help output.
 
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 ===
 
=== Verifying a Successful Restore ===
Line 35: Line 37:
 
<pre>Successfully restored backup.</pre>
 
<pre>Successfully restored backup.</pre>
  
If errors occur, error messages will be displayed.
+
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.
 
To see more details (for both successful and failed restores), specify the --verbose command-line option.
Line 48: Line 50:
 
== Database Backup ==
 
== Database Backup ==
  
=== Introduction ===
+
=== Before Running the mythconverg_backup.pl Script ===
  
 
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).
 
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).
Line 82: Line 84:
 
<pre>mythconverg_backup.pl --backup_xmltvids</pre>
 
<pre>mythconverg_backup.pl --backup_xmltvids</pre>
  
 +
=== The Role of mythconverg_backup.pl When Changing MythTV Versions ===
 +
When changing versions, (e.g. from {{CurrentRelease}}
 +
to {{CurrentDevelopment}}), the database schema is likely to change.
 +
This is done automatically.
  
== Database Restore ==
+
To help users who may need to revert to the previous version (and schema), an
 +
automatic backup of the database is done. ''mythconverg_backup.pl'' (or
 +
''mysql_dump'' if ''mythconverg_backup.pl'' isn't available) creates the
 +
backup.
  
=== Introduction ===
+
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 accessable directories,
 +
are found there, the backup will be stored in /tmp.
  
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.
+
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 upgradeThat
 +
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.)
  
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.
+
If the user selected above has a .mythtv/backuprc file, with an entry
 +
similar to:
 +
<pre>
 +
DBBackupDirectory /var/lib/db_backups
 +
</pre>
 +
and that matches the ''DB Backups'' or ''Default'' Storage Group, then
 +
manually running ''mythconverg_backup.pl'' will fairly test the backup
 +
that will be done before the upgrade starts (the .mythtv/backuprc
 +
DBBackupDirectory directory overrides the Storage Group entry.)
  
==== Creating the initial database ====
+
Obviously, if a manual run of ''mythconverg_backup.pl'' puts the backup
 +
in a directory other than the one done by the upgrade process, the test
 +
is of no value.
  
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:
+
Why bother? Discussions on the mythtv-users' mailing list confirm that
 +
sometimes the automatic database backup can't be foundLikely 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.
 +
{{Note box|Store a copy of your backup in a safe place. A thumb drive is nice, as is a NAS.}}
  
<pre>mysql -uroot -p < database/mc.sql</pre>
+
== Database Restore ==
  
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."
+
=== Before Running the mythconverg_restore.pl Script ===
  
==== Replacing an existing database ====
+
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 [[http://www.mythtv.org/docs/mythtv-HOWTO-6.html#ss6.2 must be created]] first.
  
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:
+
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.
 
+
<pre>mysql -uroot -p -e 'DROP DATABASE IF EXISTS mythconverg;'</pre>
+
 
+
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.
+
  
 
=== Examples ===
 
=== Examples ===
Line 128: Line 154:
  
 
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.
 
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 ====
 +
 +
{{Note box|
 +
This procedure requires an [[#Script-based_Database_Backup_and_Restore|up-to-date restore script]], newer than the one shipped with 0.23.
 +
}}
 +
 +
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 <code>--drop_database --create_database</code> arguments to the command-line.
 +
 +
<pre>mythconverg_restore.pl --drop_database --create_database --filename mythconverg-1214-20080626150513.sql.gz</pre>
  
 
==== Partial restore of a backup ====
 
==== 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 [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|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.
+
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 [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|full restore]].
  
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 [[Database_Backup_and_Restore#Quick_Start|complete backup]] of your old-schema-version database, then [[Database_Backup_and_Restore#Database_Restore|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 [[Database_Backup_and_Restore#Quick_Start|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 [[Database_Backup_and_Restore#Change_the_hostname_of_a_MythTV_frontend_or_backend|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.
+
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 databaseEven 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.
  
Before performing a partial restore, the database must exist and must have tablesAfter dropping the mythconverg database:
+
Therefore, a partial restore is significantly more difficult and time-consuming than a [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|full restore]], leaves significantly more opportunities for problems, and provides no benefits to the user or the systemNote, 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.
  
<pre>DROP DATABASE IF EXISTS mythconverg;</pre>
+
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 [[Database_Backup_and_Restore#Partial_restore_when_upgrading_MythTV|partial restore when upgrading MythTV]], there are several extra steps to perform--just one more great reason to do a [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|full restore]].
  
run the mc.sql script, as described [[Database_Backup_and_Restore#Database_Restore|above]]Then, start mythtv-setup to create the database schemaExit mythtv-setup, and then restore the database:
+
Before performing a partial restore, the database must exist and must have (mostly-empty) tablesFirst, drop the existing database.  You can do this from the command line with the command:
  
<pre>mythconverg_restore.pl --partial_restore</pre>
+
<pre>mysql -uroot -p -e 'DROP DATABASE IF EXISTS mythconverg;'</pre>
  
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.
+
then [http://www.mythtv.org/docs/mythtv-HOWTO-6.html#ss6.2 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 of a backup, including plugin data ====
+
<pre>mythconverg_restore.pl --partial_restore</pre>
  
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 [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|full restore]].
+
===== Partial restore when upgrading MythTV =====
  
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, etcNote that after the restore, you will still need to re-configure both MythTV and any plugins you plan to use.
+
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 [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|full restore]]See [[Database_Backup_and_Restore#Partial_restore_of_a_backup|above]] for reasons why the partial restore is not necessary and is not recommended.
  
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:
+
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 [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|full restore]], so again, you should probably just do a [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|full restore]].
  
<pre>mythconverg_restore.pl --partial_restore --with_plugin_data</pre>
+
To do a partial restore when upgrading your MythTV version, ''first'' (before the MythTV upgrade) do a [[Database_Backup_and_Restore#Quick_Start|complete backup]] of your old-schema-version database, then [[#Partial restore of a backup|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 [[Database_Backup_and_Restore#Quick_Start|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 [[Database_Backup_and_Restore#Change_the_hostname_of_a_MythTV_frontend_or_backend|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 [[Database_Backup_and_Restore#Full_restore_of_a_specific_backup_file|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 [[Database_Backup_and_Restore#Quick_Start|complete backup]] of your new-schema-version database and then do a [[Database_Backup_and_Restore#Partial_restore_of_a_backup|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 ====
 
==== Change the hostname of a MythTV frontend or backend ====
 +
 +
Before changing the hostname of a MythTV frontend or backend, make sure you [[Database_Backup_and_Restore#Quick_Start|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 is likely to fail because of duplicate entries.
 
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.

Revision as of 01:09, 24 October 2012

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 or for users wanting updated versions of 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 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:

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.

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

Before Running the mythconverg_backup.pl Script

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

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:

mythconverg_backup.pl

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

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

The Role of mythconverg_backup.pl When Changing MythTV Versions

When changing versions, (e.g. from 0.27.4 to 0.28), the database schema is likely to change. This is done automatically.

To help users who may need to revert to the previous version (and schema), an automatic backup of the database is done. mythconverg_backup.pl (or mysql_dump if mythconverg_backup.pl isn't available) creates the 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 accessable 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 mythconverg_backup.pl 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 mythconverg_backup.pl 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.

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

Database Restore

Before Running the mythconverg_restore.pl Script

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.

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.

Replacing an existing database

Important.png Note: This procedure requires an up-to-date restore script, newer than the one shipped with 0.23.

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.

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

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