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

[Michael T. Dean]

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