[mythtv-users] Long-postponed MySQL upgrades - pitfalls?

[Dale Pontius]

On 02/05/2012 11:39 PM, Michael T. Dean wrote:
> On 02/05/2012 07:57 PM, Dale Pontius wrote:
>> Are there any pitfalls to upgrading MySQL from 5.0 to 5.1 or 5.5?
>> Can this be achieved with a pair of binary upgrades? (5.0->5.1, then 5.1
>> ->   5.5)  Or will I just have to drop and reload?
>>
>> Back about the time of the MythTV upgrade from 0.21 to 0.22, Gentoo also
>> moved MySQL from 5.0 to 5.1.  I masked both until I was good and ready
>> to spend some time on it.  First I upgraded MythTV from 0.21 to 0.22.
>> That was an adventure in itself, because I had a "partially corrupted"
>> database, and took a bit of work to get through.
>>
>> After that, I kind of forgot about MySQL.
>>
>> Recently Gentoo announced that both 5.0 and 5.1 versions were being
>> masked for various problems, and I should be running 5.5.  I guess I
>> shouldn't ignore this any more.
>>
>> Is it possible to make these upgrades without dropping and reloading the
>> database?  In other words, can it upgrade the binary database?  If it
>> can, I presume that I should upgrade first from 5.0 to 5.1, then from
>> 5.1 to 5.5.  If it can't upgrade the binary database, is there any point
>> in pausing at 5.1?  If I have to drop/reload, shouldn't I just go
>> directly from 5.0 to 5.5?
> You should do a backup (
> http://www.mythtv.org/wiki/Database_Backup_and_Restore ), because that
> way, you have a backup.
>
> After you do a backup, you should upgrade MySQL, then you simply need to
> follow MySQL's instructions for upgrading the database (including
> upgrading binary data files, rebuilding and/or repairing tables and
> indices, ...), and handle any issues that may occur during the process.
> Oh, and note that MySQL docs provide information on upgrading from MySQL
> 5.0 to 5.1, or 5.1 to 5.5, but not 5.0 to 5.5, so you may need to do
> this in a 2-step process.  See chapter 2 of the appropriate MySQL
> version's documentation at http://dev.mysql.com/doc/ .
>
> Or, you can just restore the backup onto the upgraded version of MySQL,
> overwriting the existing database (
> http://www.mythtv.org/wiki/Database_Backup_and_Restore#Replacing_an_existing_database
> ).
>
> Put another way, SQL-based backups are portable across versions of
> MySQL.  Binary data files are not.
>
> Mike
>
I figured/hoped it was going to be a one-version-at-a-time upgrade.  I 
probably shouldn't fear a drop/restore, but I do, and figure that's for 
in case the binary upgrade fails.

Thanks,
Dale