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

[Michael T. Dean]

On 02/06/2012 02:17 PM, Dale Pontius wrote:
> 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?
>> You should do a backup (
>> http://www.mythtv.org/wiki/Database_Backup_and_Restore ), because that
>> way, you have a backup.
...
>> 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.
> 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.

FWIW, I often do 10 or more 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Replacing_an_existing_database 
a day when I'm working on my development system.  It's actually quite 
easy.  Assuming you already have a mythconverg database (whether you 
created it with mc.sql or your distro did it for you when you installed 
a package or even if you have a fully-populated schema), you simply run 
the command:

mythconverg_restore.pl --drop_database --create_database \
                        --filename mythconverg-1214-20080626150513.sql.gz

If you don't already have a database, you will need to create one (by 
using database/mc.sql script) before doing a restore--at which point, 
you may as well then just use the above script--because you now have a 
database that it can drop before creating a new one.  In other words, 
the --drop_database --create_database arguments never cause any 
problems--and often prevent a lot of problems.

And, the backup/restore approach would allow you to simply upgrade to 
MySQL 5.5 (directly from MySQL 5.0), then restore the database backup, 
then be up and running.  No need to go to 5.1 as an interim step.

Mike