Updating 4 to 5

Hi all,

I have serveral servers running mysql 4, and need to update to mysql 5.

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).

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Updating 4 to 5

On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel 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@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Updating 4 to 5

----- Original Message -----
From: "Rob Wultsch"
To: "Grant Peel"
Cc:
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 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@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gpeel@thenetnow.com
>
>
>

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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Updating 4 to 5

On Sat, Jul 3, 2010 at 4:00 AM, Grant Peel wrote:
> ----- Original Message ----- From: "Rob Wultsch"
> To: "Grant Peel"
> Cc:
> 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 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 table=
s,
>>> (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@gmail.com
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dgpeel@thenet=
now.com
>>
>>
>>
>
> 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:
>
> =A0 Run a complete backup of all filesystems, (/,/var,/home,/user) to an =
NFS
> server,
>
> =A0 Build the new server,
>
> =A0 Restore all filesystems to the new disk (that is built with mysql 5),
>
> =A0 Run the mysql upgrade script,
>
> =A0 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 .



--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg