[mythtv-users] Migrate recording schedules and recorded programs to 0.21

[Michael T. Dean]

On 05/17/2008 06:10 PM, Brian Phillips wrote:
> Michael T. Dean wrote:
>   
>> I'm soliciting testers and feedback.  They've been well tested (and
>> have been tested a /lot/), I just need to get a lot of people to test
>> them on a lot of differently-configured systems.  
>>
>> http://misc.thirdcontact.com/MythTV/database_mythconverg_backup.pl
>> http://misc.thirdcontact.com/MythTV/database_mythconverg_restore.pl
>>
>> To backup your DB:
>>
>> # echo "DBBackupDirectory=/home/mythtv" > ~/.mythtv/backuprc #
>> ./database_mythconverg_backup.pl --verbose 
>>
>> Once you get that working, you can run the script (i.e. in cron or
>> whatever) without the --verbose switch.
>>
>> To restore the DB (assuming you've already specified your
>> DBBackupDirectory, as above) using a full restore with the most
>> current backup in the DBBackupDirectory:  
>>
>> # ./database_mythconverg_restore.pl --verbose
>>
>> or, to specify a backup file:
>>
>> # ./database_mythconverg_restore.pl --verbose \
>>       --filename "mythconverg_1214_20080515221324.sql.gz"
>>
>> To do a partial restore (using the most current backup):
>>
>> # ./database_mythconverg_restore.pl --verbose --partial_restore
>>
>> Again, --verbose is only required if you like to see what it's doing,
>> and feel free to specify the backup filename. 
>>
>> See, also, the --help output for each script (for far more info than
>> you probably want). 
> Your link worked like a charm.

Thanks a lot for testing.

>   The only manual editing I had to do in the
> mythtv_restore.sql file before I loaded it back into the database was change
> the hostname (as my backend changed hostnames with the switch from mythdora
> to mythbuntu).
>   

Yeah.  I plan to modify it to allow a "23.15" (Changing your hostname:  
http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.15 ), eventually.  
Perhaps this week while traveling.  (Still wrestling with how to ensure 
it will work properly--even if someone chooses a 
stu^H^H^Hless-than-ideal hostname, like mythtv. ;)

> The perl scripts made the job very slick and I think that you've got a
> winner there.  I could tell they've been extensively tested as I found them
> to be quite thorough and complete.
>
> One tibit of feedback.  After I dropped the database according to 23.7, I
> tried to use database_mythconverg_restore.pl.  It died with an error that
> the database didn't exist.  I assume this is to guard against possibly
> creating a database with a name that has been fatfingered or something.
> Since the restore script pulls the database name from the exact same place
> the backup script gets its database name, I think that it would be a safe
> option to go ahead and just issue the "create database <dbname>;" if the
> database is not found, rather than dropping out with an error.  It wouldn't
> make sense to be backing up a database of <dbname> and not be comfortable
> restoring those files to the exact same <dbname>.
>   

In truth it's because "CREATE DATABASE <dbname>;" isn't enough.  In 
order to properly create and configure the database, you must run a few 
commands--those in mc.sql.  I had the option of either "hard-coding" 
mc.sql's commands into the restore script (and causing us to have to 
update them twice) or allowing the user to pass a location for mc.sql.  
I chose the latter.

The "--create_database" argument can be used to specify the location of 
the mc.sql file, and if provided, the script will /attempt/ to run the 
mc.sql script to create the database.  Note the word attempt.  On many 
(most?) systems, the MySQL mythtv user won't have authority to create 
the database, so for this to work, the user would either have to ensure 
that the mythtv user has appropriate permissions before running the 
restore script or specify a --username who has the appropriate 
authority--i.e. the MySQL root user--and put the password in the 
resource file or the database information file, since the script won't 
allow passing the password via the command line.

Therefore, I wanted to make sure the user had to either read a bit of 
the --help or just run mc.sql him/herself.  (BTW, thanks a lot for 
mentioning this as it helped me to notice that that section of the 
--help and the next--the last couple sections I added--aren't properly 
wrapped, so I need to fix the formatting.)

> After I manually issued the "create database mythconverg;" command and
> exited mysql, the restore script worked without incident.  Thanks!

Ideally you would have run the mc.sql.  Especially with MySQL 5 (which 
typically defaults to using UTF8 character set), it's important that the 
database be created to default to latin1 character set.  If MySQL were 
able to set the character set properly on the tables you imported from 
the backup (which it might), MythTV takes care of changing the database 
default when the database is upgraded, so your system would be fine.  If 
not, you're likely to have a database that won't make the upgrade to 
0.22 without a lot of manual work (and/or deleting a lot of info).

Thanks, again,
Mike