Upgrading major MySQL versions

[Walter Heck]

Hey guys,

I have a question for you guys:
I am wanting to upgrade a client that has MySQL 5.0 running on MMM
2.0.9 to MySQL 5.1. I think MMM should be able to help greatly with
this. The question is: how to do this so as not to break replication
(and ultimately, service ;) )?
This cluster only has two masters, so no slaves to worry about.
Here's what I thought:

1. Set cluster to passive, to make sure no failover occurs
2. Upgrade passive master to 5.1
3. set cluster active, switchover, set passive again
4. Upgrade other master
5. set cluster active again

The question is: when I execute step 3, will that automatically start
replication? I don't worry about replication from 5.0 -> 5.1, but I do
worry about replication from 5.1 -> 5.0 and don't wnat that to start
automatically.

Has any of you done this before? Any experiences you'd like to share?

kidn regards,

Walter

[Istvan Podor]

Hey Walter,

I did this before..

What you wrote down is a kind reasonable. So you have nothing to worry
about. But, one very important point.

As you know, replication is "forward-compatible" (i think this is how
we should call it) what means, a newer version of mysql can replicate
from an older but not (sure) vica-versa.

so lets say you upgrade db1 to 5.1, start replication from db2 which
is still5.0 and when you move the writer role to db1, you can't move
backward from that point without risking.
Don't forget this :)

And another note. I keep meeting with issues when 5.0 was upgraded to
5.1 and the mysql servers are keep restarting (debian/ubuntu mostly)
after select queries! Which is pretty weird, but what I want to point
to is that to test the upgrade! I recommend to set up a slave with the
same configuration like your masters, add to mmm. upgrade to 5.1, send
some reader traffic and see if it works well.

Regards,
Istvan

[Walter Heck]

Hey Istvan,

so the circular replication will not be started automatically once i
put the cluster back to active in step 3? Because if that replication
starts, it will start both ways and thus do backwards replication,
which i want to make sure doesn't happen.

Walter

[Pascal Hofmann]

Hi Walter,

> so the circular replication will not be started automatically once i
> put the cluster back to active in step 3? Because if that replication
> starts, it will start both ways and thus do backwards replication,
> which i want to make sure doesn't happen.

MMM does not touch replication settings of the two masters and won't
start replication.

Cheers
Pascal

[Walter Heck]

Both MMM 2 and 1? That is good to hear :)

thanks for the insight!

Walter

[Pascal Hofmann]

> Both MMM 2 and 1? That is good to hear :)

afaik: yes.

[Pierre]

Hi Walter,

I did this two months ago with MMM 1.2.x. I did not have problem with
MMM, but I had with replication. I used my 5.0 my.cnf file for Mysql
5.1.

With InnoDB, if you are using the "READ_COMMITED" isolation level, the
statement based replication no longer works. This means that when the
active master will run with Mysql 5.1, you may get errors like
"Transaction level 'READ-COMMITTED' in InnoDB is not safe or binlog
mode 'STATEMENT'".

In this case, you need to update your my.cnf file to use row based
binary logging.

Regards,
Pierre

[Walter Heck]

Hi Pierre,

thanks for the tip. We upgraded safely and without 1 second of downtime, yay!

Walter

[Tomas]

Hi Walter and other experts,

I have some questions of upgrade mysql also.

As see from mysql manual, it suggest to

1. stop slave on passive server
2.SET sql_log_bin = 0 on slave or passive server first,
3 upgrade passive server,

4. stop slave on active server
5. .SET sql_log_bin = 0 on active server
6. upgrade active server.

then start slave on both server ?

Am I right? because that will not let mysql_upgrade run both server
twice ?

Another question is very simple, which upgrade step you think is best?

a. mysqldump -> reimport to a fresh new mysql 5.1

or b. just upgrade mysql binary to 5.1 and run mysql_upgrade ?

I think step a is not good for MMM architecture.

--And is that risky to upgrade to 5.1 ? I do not want my production
crash during sql queries after upgrade to 5.1

Thanks all

Yours Tomas

then

[Tomas]

yes, i left something, after upgrade , set sql_log_bin=1 :)

[Istvan Podor]

Hi Tomas,

Actually set sql_log_bin is a session variable so it's won't affect
anything else except your session :)

Istvan

[Arjen Lentz]

Hi Tomas

On 06/11/2009, at 2:43 AM, Tomas wrote:
> I have some questions of upgrade mysql also.
>
> As see from mysql manual, it suggest to
>
> 1. stop slave on passive server
> 2.SET sql_log_bin = 0 on slave or passive server first,
> 3 upgrade passive server,
>
> 4. stop slave on active server
> 5. .SET sql_log_bin = 0 on active server
> 6. upgrade active server.
>
> then start slave on both server ?
> Am I right?

The manual is wrong, since sql_log_bin is only a session variable, it
cannot be used to disable binlogging globally.

> because that will not let mysql_upgrade run both server twice ?

That's pretty much harmless, but there's another why you can avoid
having the other server execute this stuff.
You shouldn't have 5.0 replicate from 5.1, so essentially the old
server may not replicate from the new.
Before you upgrade the passive master, you stop replication - on both
sides.
During the upgrade, you move away the binary and relay logs on the
master you're upgrading. You have to leave master.info (so it
maintains its position to the old master - otherwise it'll be missing
data later!)
When it's upgraded, you check the binlog position with show master
status, and you set the active master to begin its replication from
that position, before restarting it.

> Another question is very simple, which upgrade step you think is best?
>
> a. mysqldump -> reimport to a fresh new mysql 5.1

Well before an upgrade, having both a logical (mysqldump) and physical
backup (cp or xtrabackup) is good.
Yes you could do a dump/reload, if you want. On the passive master.
Absolutely.
Generally, upgrades in MySQL are ok without dump/reload, particularly
since the introduction of mysql_upgrade.

> or b. just upgrade mysql binary to 5.1 and run mysql_upgrade ?

That's what we did, with the extra replication repositioning trickery.

> I think step a is not good for MMM architecture.

You can do either, and because of MMM the users won't have an outage.

> --And is that risky to upgrade to 5.1 ? I do not want my production
> crash during sql queries after upgrade to 5.1

Any major operation has some risk, hence the additional backups
(you'll want to have them locally so you can quickly restore them, or
have another machine able to become 2nd master). What would you do if
one of your master servers were to go up in smoke? That's exactly the
same contingency plan you need to have in place when doing this kind
of upgrade.

Cheers,
Arjen.

> On Nov 5, 3:01 am, Walter Heck <walterh...@gmail.com> wrote:
>> Hi Pierre,
>>
>> thanks for the tip. We upgraded safely and without 1 second of
>> downtime, yay!
>>
>> Walter
>>
>> On Wed, Nov 4, 2009 at 20:42, Pierre <ppo...@reservit.com> wrote:
>>
>>> Hi Walter,
>>
>>> I did this two months ago with MMM 1.2.x. I did not have problem
>>> with
>>> MMM, but I had with replication. I used my 5.0 my.cnf file for Mysql
>>> 5.1.
>>
>>> With InnoDB, if you are using the "READ_COMMITED" isolation level,
>>> the
>>> statement based replication no longer works. This means that when
>>> the
>>> active master will run with Mysql 5.1, you may get errors like
>>> "Transaction level 'READ-COMMITTED' in InnoDB is not safe or binlog
>>> mode 'STATEMENT'".
>>
>>> In this case, you need to update your my.cnf file to use row based
>>> binary logging.
>>
>>> Regards,
>>> Pierre
> >

--
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Exceptional Services for MySQL at a fixed budget.

Follow our blog at http://openquery.com/blog/
OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org