[mythtv-users] Long-postponed MySQL upgrades - pitfalls?
Michael T. Dean
mtdean at thirdcontact.com
Mon Feb 6 19:36:58 UTC 2012
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
More information about the mythtv-users
mailing list