Opened 12 years ago

Closed 12 years ago

Last modified 12 years ago

#4613 closed patch (fixed)

Backup database before schema upgrade

Reported by: sphery <mtdean@…> Owned by: cpinkham
Priority: critical Milestone: 0.21
Component: mythtv Version: head
Severity: high Keywords:
Cc: Ticket locked: no

Description

The attached patch, mythtv-database_backups_before_upgrade.patch, adds functionality to automatically backup the user's database before a database schema upgrade. Note that the current implementation will /only/ backup the DB if the schema needs to be upgraded. The fact that a backup is being created and its location is written at VB_IMPORTANT level, so it should show in the user's log.

Users may define the location for the backups with the specially-named "DB Backups" storage group. If there is no "DB Backups" storage group, the "Default" storage group will be used. If a user upgrades from a version of Myth that did not support StorageGroups?, the StorageGroup? code will fall back to using the old RecordFilePrefix? (the upgrade from 0.20 is untested, but the code in StorageGroup? seems to indicate it will work).

If the "DB Backups" or "Default" storage group is used and the user has specified multiple directories in the storage group, the backup will be written to the directory with the most free space. Users who always want the backup written to a specific directory (i.e. to use an external rotation or archiving script) should define a "DB Backups" storage group with a single directory. Note that the first backup when upgrading from a version of Myth before storage groups was added (i.e. 0.20) will have to be moved to this directory, then a "DB Backups" storage group can be defined for all future backups.

The backup filename will use the format <dbName>-<dbSchemaVer>-<time>.sql. The time format is the same as is used by the recording files. This should allow for easy sorting and rotation.

The patch also modifies the recently-added DBMS version check in UpgradeTVDatabaseSchema() (in dbcheck.cpp) to use the new DBUtil::CompareDBMSVersion() and DBUtil::GetDBMSVersion(). The new functions are significantly more robust as the MySQL server version string does not always fit the Major.Minor.Point format (i.e. it could be something like 5.0.44-standard or 5.0.44-enterprise-gpl, or may have even been changed to something unrecognizable by the person/packager who compiled the MySQL server--all of which will result in the unpatched version refusing to run). The DBUtil functionality simply parses the string and assumes the first digits found are the major version, the second digits are the minor version, and the third digits are the point version. It will also allow (appropriate) version comparisons if minor and/or point versions are not found. For those cases where even this won't work, there is an "undocumented" setting, "DBMSVersionOverride," users can manually add to their DB to allow Myth to continue to work (though this is not intended to allow using MySQL 4.x with Myth versions that require 5.x or above). For a user running a MySQL 5.0.44 version with an unrecognizable version string, the "DBMSVersionOverride" setting should get the data "5.0.44".

Though DBUtil::GetTables?() is not currently used, it will be used in an upcoming patch (to detect crashed tables and put warnings in the logs, as well as by the code that will eventually do automatic OPTIMIZE/ANALYZE on all tables and/or REPAIR crashed tables when the DB is sufficiently idle).

The backups currently rely on the MySQL client program, mysqldump. If it is not installed on the system from which the schema upgrade is being requested (i.e. the master backend), the backup will fail and a log message will be logged suggesting the user exit before the database upgrade and backup the database. Exiting will only be possible if the user is being prompted for the schema upgrade (i.e. not in non-interactive shells).

I've actually got a 90% complete patch for doing the backup without mysqldump (using queries against the DB). If there's any way the backup functionality is going into 0.21, I would recommend we use the mysqldump version (or at least only include the "native SQL" backup as a fallback if the mysqldump program is not available or fails). Since mysqldump has been tested by a lot of MySQL users and since the mythtv automatic backup will probably receive little actual testing (i.e. the backup won't actually be used to restore databases /that/ often), having a known working backup implementation would be a good thing. I think a potentially-buggy backup implementation is worse than having no backup implementation at all. If desired, I will complete the native SQL backup approach and add it as a fallback.

Unfortunately, this patch does not modify the schema version, so there is no visible effect after its application. An easy way to test is to move the "if (!dbutil.BackupDB())" clause up 3 lines (to just above "if (dbver == currentDatabaseVersion)").

Attachments (5)

mythtv-database_backups_before_upgrade.patch (16.7 KB) - added by sphery <mtdean@…> 12 years ago.
mythtv-database_backups_before_upgrade.2.patch (17.6 KB) - added by sphery <mtdean@…> 12 years ago.
Updated patch. Works after [15834] (SG move) and adds gzip compression (fast compression, but much smaller backup files)
mythtv-database_backups_on_master_backend_startup.patch (630 bytes) - added by sphery <mtdean@…> 12 years ago.
Though it could corrupt recordings if the master backend is restarted when a slave backend is recording, this makes it easy to test the DB backup functionality and might be useful for some users.
mythtv-database_backups_before_upgrade.3.patch (17.4 KB) - added by sphery <mtdean@…> 12 years ago.
Adds the --host argument to the mysqldump command.
mythtv-database_backups_before_upgrade.4.patch (17.4 KB) - added by sphery <mtdean@…> 12 years ago.
Fixes a typo (that had no adverse effects, but was wrong)

Download all attachments as: .zip

Change History (9)

Changed 12 years ago by sphery <mtdean@…>

Changed 12 years ago by sphery <mtdean@…>

Updated patch. Works after [15834] (SG move) and adds gzip compression (fast compression, but much smaller backup files)

comment:1 Changed 12 years ago by cpinkham

Owner: changed from Isaac Richards to cpinkham
Status: newassigned

Changed 12 years ago by sphery <mtdean@…>

Though it could corrupt recordings if the master backend is restarted when a slave backend is recording, this makes it easy to test the DB backup functionality and might be useful for some users.

comment:2 Changed 12 years ago by cpinkham

Milestone: unknown0.21
Priority: minorcritical
Severity: mediumhigh

Changed 12 years ago by sphery <mtdean@…>

Adds the --host argument to the mysqldump command.

Changed 12 years ago by sphery <mtdean@…>

Fixes a typo (that had no adverse effects, but was wrong)

comment:3 Changed 12 years ago by cpinkham

Resolution: fixed
Status: assignedclosed

(In [15901]) Add code to backup the database before any schema upgrade. By default this DB backup will be dumped into the Default Storage Group. If you assign dirs to the special 'DB Backups' Storage Group then these backups will be dumped into that directory. If gzip is present in /usr/bin or /bin, then it will be used to compress the dump file. mysqldump is required to be on the system because it is used to make the backup file.

For users upgrading from a pre-Storage Groups version of Myth, the backup file will be created in the directory specified in the old RecordFilePrefix? setting.

This also adds a DBUtil class with several helper functions to deal with things like DBMS version checking, backups, etc..

Closes #4613 using patch by Michael T. Dean.

comment:4 Changed 12 years ago by cpinkham

(In [15932]) Various changes related to the new Database Backup that runs before any schema upgrade.

  • Don't bother running the backup if the existing DB Schema version is empty (ie, when the DB is first created).

  • Use system() instead of myth_system() so that we can get the result of the mysqldump command.

  • Break out the compression into a separate call so that we can get the result of mysqldump instead of the result of the gzip.
  • If the user chooses to upgrade the schema, display a warning if the database was not successfully backed up.
  • If the DB was succesfully backed up, display the filename along with a message telling the user where they can locate the backup file if they experience issues.

References #4613.

WARNING: This bumps the MYTH_BINARY_VERSION version because of changes to

MythContext::PromptForSchemaUpgrade?(), so a recompile of mythtv and all plugins is necessary.

Note: See TracTickets for help on using tickets.