Modify

Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#4760 closed patch (fixed)

Use a script for the database backup

Reported by: sphery <mtdean@…> Owned by: ijr
Priority: minor Milestone: unknown
Component: mythtv Version: head
Severity: medium Keywords:
Cc: kormoc Ticket locked: no

Description

The attached patch, mythtv-database_backup_using_script.patch , modifies the new automatic database backup functionality to invoke a script (potentially a distro-specific script) to create the database backup. It's primary purpose is to allow packagers to customize the database backup procedure and to remove the code in myth calling the externally-provided binaries, mysqldump and gzip. It also has the additional advantage of allowing users to perform database backups whenever desired (i.e. in startup/shutdown scripts, in a cron job, ...) in such a way that the automatically-created database backups are no different from the manually-created backups.

The script path can be specified with the "undocumented" setting "DatabaseBackupScript?" (default value gContext->GetShareDir?() + "database_mythconverg_backup.sh"). A matching script (gContext->GetShareDir?() + "database_mythconverg_restore.sh") can be used by the user to restore the database from a backup created by database_mythconverg_backup.sh. If either script is customized by the packager, the other script should be tested to verify it works with the other. This way, any distribution-specific backup/restore parameters/techniques can be hidden within the scripts, yet the MythTV HOWTO can provide generic guidance on backing up and restoring the database.

To prevent the user from having to configure the database within MythTV and within the database backup script, a temporary database information file is created with name=value pairs specifying dbHostName, dbPort, dbUserName, dbPassword, dbName, dbSchemaVer, dbBackupDirectory, and dbBackupFilename. The information file is written with only read by owner filesystem permissions and is deleted immediately after execution of the backup script.

The database information file provides the dbBackupDirectory to ensure that the script can place the backup in the appropriate directory, i.e. as specified in the DB Backups storage group. The dbBackupFilename "suggested" filename is also provided for use by the script. Since it is impossible to force the script to use these values, Myth will check for the existence of a file in the dbBackupDirectory whose name begins with dbBackupFilename, perhaps with additional extensions--such as ".gz". If any files matching the pattern are found, the filename will be returned to be used for the GUI messages; if the script uses some other directory or filename, the returned filename will be "", thus preventing GUI messages specifying the name of the backup file. To ensure this works, the script must provide a proper exit code.

The backup script is executed with a single command-line argument providing the name of the temporary database information file. The restore script will provide "--help" output to help the user determine the appropriate command-line arguments to pass the script.

Example scripts will be provided in the new programs/scripts directory (an initial backup script is in the separate patch, mythtv-database_backup_scripts.patch ) and will be installed to the sharedir. Packagers who customize the scripts are encouraged to create restore scripts using similar command-line arguments to those specified in the programs/scripts/database_mythconverg_restore.sh script to make usage of scripts easier. If you would like the scripts placed in a different location within the source distribution or at install, please let me know.

Currently, only the database_mythconverg_backup.sh script is provided. I began it as a shell script thinking it would be relatively quick and simple, but the script grew ever more complex as I tried to make a relatively-secure script that could also be used for "manual" backups. The script has already grown large enough that it may make sense to rewrite it in Perl. Please let me know whether a sh-based or Perl-based script would be preferable.

I plan to add code to allow rotation of backups and to allow the user to specify default values/configuration in a separate configuration file (especially useful for manual execution). The database_mythconverg_restore.sh script will provide full getopts support as well as attempt to parse configuration files to determine database configuration information. However, not knowing whether the devs would prefer a sh-based approach or a complete rewrite to Perl, I decided to upload what I had for review rather than write a bunch more code only to have to reimplement it later in another language. Once the appropriate script language is decided, I can finish both scripts rather quickly (though I won't have a chance to do much work on them until next weekend--around Mar 1).

Most all of the ideas in here came from Daniel K., Rob S., and Stuart M. on IRC. I'll take responsibility for all the errors, oversights, and bad ideas, though.

Attachments (7)

mythtv-database_backup_using_script.patch (8.8 KB) - added by sphery <mtdean@…> 10 years ago.
mythtv-database_backup_scripts.patch (16.5 KB) - added by sphery <mtdean@…> 10 years ago.
mythtv-4760-database_backup_using_script.patch (6.7 KB) - added by sphery <mtdean@…> 10 years ago.
Updated patch
mythtv-4760-database_backup_scripts.patch (78.0 KB) - added by sphery <mtdean@…> 10 years ago.
Updated patch with more-functional Perl scripts
mythtv-4760-database_backup_scripts-20080503.patch (78.0 KB) - added by sphery <mtdean@…> 10 years ago.
Updated scripts. Replaces mythtv-4760-database_backup_scripts.patch. Restore script was missing one config-file-parsing change required on some configurations.
mythtv-4760-database_backup_using_script-20080627.patch (6.7 KB) - added by sphery <mtdean@…> 10 years ago.
Updated patch (for changes to trunk)
mythtv-4760-database_backup_scripts-20080627.patch (107.7 KB) - added by sphery <mtdean@…> 10 years ago.
Update patch (adds new features, see comment, below)

Download all attachments as: .zip

Change History (13)

Changed 10 years ago by sphery <mtdean@…>

Changed 10 years ago by sphery <mtdean@…>

comment:1 Changed 10 years ago by nigel

While using a script is tidier in many ways, I think it should be optional. e.g. The current code works well for Mac OS X clients, but requiring an installed script to circumvent an accidental upgrade on a random machine is not good.
I would recommend adding !DBUtil::DoBackup?(QString, QString), and having DBUtil::BackupDB() decide which one to call.

Changed 10 years ago by sphery <mtdean@…>

Updated patch

Changed 10 years ago by sphery <mtdean@…>

Updated patch with more-functional Perl scripts

comment:2 Changed 10 years ago by sphery <mtdean@…>

mythtv-4760-database_backup_using_script.patch is an updated patch, tested with post-Qt4 trunk, that takes into account Nigel's suggestion of keeping the "built-in" DBUtil::DoBackup?(QString) as well as adding a new (preferred) DBUtil::DoBackup?(QString, QString) that calls a script as described above. (Though I now realize that my original description specified variables names of the form dbXxx--such as dbName--when really it has always been of the form DBXxx--such as DBName.)

mythtv-4760-database_backup_scripts.patch provides both a backup (database_mythconverg_backup.pl) and restore (database_mythconverg_restore.pl) script, written in Perl. The Perl scripts are much more understandable than the shell scripts and have more functionality. The scripts will work with any version of MythTV and only require the modules Getopt::Long and File::Temp; however, it is strongly recommended that the DBI and DBD::mysql modules also be installed. If IO::Compress::Gzip or IO::Uncompress::Gunzip (for backup and restore, respectively) are installed, compression/uncompression will be "internally" without using the external gzip executable. Neither DBI/DBD::mysql nor IO::Compress/IO::Uncompress are required (the script will check for their presence and use them only if available).

Once a script-based backup capability goes into Myth, I will write up and submit a patch for the HOWTO documentation.

When testing the script-based backup, you may use the "hidden" DB setting BackupDBScriptArgs with the value '--verbose' to see what the script is doing.

Script Highlights:

The backup script can be used automatically by mythbackend or "manually" by users. It will automatically determine as much information as possible (generally only requiring the user specify a DBBackupDirectory) and supports a backup resource file to make usage easier. After creating the backup, it will compress it (by default using the gzip algorithm, but allows the user to specify a compression program so those who would rather trade a lot of CPU resources for a little disk space can use bzip2). It will also rotate backup files (by default keeping 5 backups and removing the oldest). See the --help output for (way too much) information (and a quick start).

The restore script can be called to do a full restore (i.e. as at http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.5 ) or a partial/new-hardware restore (as at http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.7 ). Like the backup script, it will automatically determine as much information as possible (again, generally only requiring the user specify a DBBackupDirectory) and supports a backup resource file to make usage easier. If no backup filename is specified, it will attempt to find the newest backup file (based on filename). It will automatically uncompress backups. It also allows the user to specify the location of the mc.sql file and will attempt to create the initial database, if the database does not already exist. Again, see the --help output for full details (and a quick start).

Changed 10 years ago by sphery <mtdean@…>

Updated scripts. Replaces mythtv-4760-database_backup_scripts.patch. Restore script was missing one config-file-parsing change required on some configurations.

comment:3 Changed 10 years ago by kormoc

  • Cc kormoc added

Changed 10 years ago by sphery <mtdean@…>

Updated patch (for changes to trunk)

Changed 10 years ago by sphery <mtdean@…>

Update patch (adds new features, see comment, below)

comment:4 Changed 10 years ago by sphery <mtdean@…>

Updated patches attached. mythtv-4760-database_backup_using_script-20080627.patch was updated for changes to trunk. mythtv-4760-database_backup_scripts-20080627.patch adds new features.

database_mythconverg_backup.pl:

database_mythconverg_restore.pl:

Patches are ready to apply (no new features are planned). I recommend the committer not worry about testing all script features (as it takes forever). I've done a /lot/ of testing on it (and have had help from several users) and will be very responsive to questions/comments/bug reports I see on the list or here.

comment:5 Changed 10 years ago by danielk

  • Resolution set to fixed
  • Status changed from new to closed

(In [17882]) Fixes #4760. Adds DB backup script capability to MythTV using patch from sphery.

comment:6 Changed 10 years ago by danielk

(In [17883]) Refs #4760. Accidentally committed debugging code in [17882], reverting.

Add Comment

Modify Ticket

Action
as closed The owner will remain ijr.
The resolution will be deleted. Next status will be 'new'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.