[mythtv-users] MySQL 5.1 to 5.5 upgrade on Gentoo

[Michael T. Dean]

On 08/20/2014 09:37 AM, Tom Dexter wrote:
> On Tue, Aug 19, 2014 at 8:01 PM, Karl Newman wrote:
>
>> I think mysql_upgrade mostly just adds some new system tables. It ran pretty
>> quick on my system (a few seconds), which implies to me it wasn't doing a
>> lot of data manipulation.
> Yea...what I was unclear on is...assuming I'm going to restore the
> database, if it's best to have all those system tables updated before
> restoring.  It seems like that'd be the case.

IMHO, it's much better to always have a fresh, made-for-this-version 
database than to bring old cruft from the old version over into your new 
version--especially when talking about a MySQL server that likely has 
very little data total in it (generally just MythTV and maybe another 
application or two's tiny data set).

I always delete then entire MySQL data directory and use 
mysql_install_db to create a brand new database (with the script that's 
designed to create the starting database for the MySQL version I just 
installed).  Then I just create a mythtv user and restore the MythTV 
database--which takes 5min or so (a pittance compared to the process of 
upgrading the database, and even less noticeable when compared to 
upgrading the OS).  And this way, there's never any need to use 
mysql_upgrade or worry about changes to MySQL that could impact your 
binary data file compatibility.  And, most importantly, it allows me to 
be a MythTV user without having to become a database administrator (who 
knows the ins and outs of the different MySQL versions and the proper 
process for upgrading from one to the next, since, of course, you should 
never skip versions when upgrading your MySQL database--i.e. you 
shouldn't upgrade from MySQL 5.0 from MySQL 5.5, you need to instead 
upgrade from MySQL 5.0 to 5.1, then from 5.1 to 5.5, see 
http://dev.mysql.com/doc/refman/5.5/en/upgrading.html).  This also has 
the benefit, with current versions of MythTV and their time zone table 
requirements, of forcing you to re-create the MySQL time zone tables so 
they get updated to the current version of the Olson (Eggert? :) 
database on your upgraded system.

I have never once had a problem with MySQL functionality working 
improperly due to changes to MySQL's mysql database schema/grant tables/...

Now if you're administering a multi-terabyte MySQL database at work, 
this is not the right approach to take.  But then again, if you're 
administering a multi-terabyte MySQL database at work, you probably 
/should/ be a database administrator who knows the ins and outs of MySQL...

Mike