List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:July 3 2010 5:02pm
Subject:Re: Updating 4 to 5
View as plain text  
On Sat, Jul 3, 2010 at 4:00 AM, Grant Peel <gpeel@stripped> wrote:
> ----- Original Message ----- From: "Rob Wultsch" <wultsch@stripped>
> To: "Grant Peel" <gpeel@stripped>
> Cc: <mysql@stripped>
> Sent: Friday, July 02, 2010 3:57 AM
> Subject: Re: Updating 4 to 5
>
>
>> On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel <gpeel@stripped> wrote:
>>>
>>> Hi all,
>>>
>>> I have serveral servers running mysql 4, and need to update to mysql 5.
>>>
>> It would be good if mentioned what release of the various series you
>> were using or wanting to upgrade to.
>>
>>> I have version 5 setup on a new dev server and will be cloning that to
>>> the
>>> old servers, then restoring all the data from backups (mysql databases
>>> included).
>>
>> By restoring from backup for mysql a sql dump, or a filesystem backup?
>>
>>>
>>> Once I have restored the data from backups, I will neeed to run
>>> mysqlupgrade.
>>>
>>> My question is, will the mysqlupgrade script update all the mysql tables,
>>> (grant tables etc), as well as update all the users databases, or will
>>> there
>>> be other things that need to be done?
>>>
>>> -Grant
>>
>>
>> It depends. The way I generally do upgrades is the following:
>> 1. Identify the backup point for the current server. Do a 'mysqldump
>> --all-databases --complete-insert' from it using the mysqldump from
>> the version of mysql I will be using after the upgrade and record the
>> 'show slave status' while it is running.
>> 2. Import the backup on to the new server after removing any commands
>> that would perform ddl on the mysql schema.
>> 3. Setup replicaton and fail over to the new server at an opportune time.
>>
>> So, you should keep in mind a few things:
>> 1. Between version of MySQL the table format changes, and it is
>> generally worthing while to take advantage of the changes.
>> 2. mysqlupgrade runs REPAIR TABLE which acts differently in different
>> versions of MySQL See
>>
>> http://www.mysqlperformanceblog.com/2010/05/14/mysql_upgrade-and-innodb-tables/.
>>
>>
>> --
>> Rob Wultsch
>> wultsch@stripped
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>
> Rob,
>
> Thanks for taking the time to reply,
>
> The mysql databases would be restored from a filesystem backup.
>
> The whole server, mysql and all, are FreeBSD 6.x
>
> I usually:
>
>   Run a complete backup of all filesystems, (/,/var,/home,/user) to an NFS
> server,
>
>   Build the new server,
>
>   Restore all filesystems to the new disk (that is built with mysql 5),
>
>   Run the mysql upgrade script,
>
>   start the mysql servers, then fix any PHP issues (as php is upgraded from
> 4 to 5 as well).
>
> Comments please,
>
> -Grant

That may be ok, it may not. Your methodology should be fine if you are
only upgrading one release:
4.1 -> 5.0

But it may not be ok for other upgrades:
4.0 -> 5.0 (skipping 4.1)
4.0 -> 5.1 (skipping 4.1 and 5.0)
4.1 -> 5.1 (skipping 5.0)

At this point MySQL 5.0 is near EOL, so it would probably be wise to
upgrade to 5.1. It is unwise to skip upgrades so if you are going from
4.1 to 5.1, so you would need to do an intermediate upgrade to 5.0 .



-- 
Rob Wultsch
wultsch@stripped
Thread
Updating 4 to 5Grant Peel2 Jul
  • Re: Updating 4 to 5Rob Wultsch2 Jul
  • Re: Updating 4 to 5Grant Peel3 Jul
    • Re: Updating 4 to 5Rob Wultsch3 Jul