From: martín arrieta
Date: August 3 2012 1:31pm
Subject: Re: Changing the bin log format on existing servers
List-Archive: http://lists.mysql.com/replication/2378
Message-Id: <CAEXwVJJ=5txNoUKVGVk1jqOfwJEzRDYuiyys4iFEbJzA4_u1WQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

No, it will not brake the data.

The difference between row and statement is that in row format, the
server will store the update of each row affected, for example "update
table set val1=3D"test" where id in (1, 2, 3);" in binlog will store 3
updates for each row and in statement mode will store the update just
like you executed on the master.. the result will be the same. The
problem with statement based replication is with non deterministic
values, for example random values, selects with limit and not sort,
etc..

Also, remember that mysql support "mixed" format, wich in this case
the server will decide what is the best format for each binlog insert,
so yes... it is safe to change from one to another.. just remember
that you will have to check your isolation level if you want to use
statement format (only repeatable read is supported with statement
format).

Bye!


On 3 August 2012 09:40, G=F6tz Reinicke - IT Koordinator
<goetz.reinicke@stripped> wrote:
> Hi & thank you for your suggestion; but do you know, if the change is
> save regarding sync etc.
>
> Or could this change brake some data?
>
> Do you ever did such change by your own and can share your experiences?
>
>         Best regards . G=F6tz
>
> Am 03.08.12 13:28, schrieb mart=EDn arrieta:
>> Binlog format is a dynamic variable, you can change this variable at
>> runtime. You can also change the binlog format for a specific session
>> in case that you need it.
>>
>>>From the doc:
>>
>> The logging format also can be switched at runtime. To specify the
>> format globally for all clients, set the global value of the
>> binlog_format system variable:
>>
>> mysql> SET GLOBAL binlog_format =3D 'ROW';
>>
>> My recommendation is make the change at runtime AND make the change in
>> the my.cnf file for the next reboot.
>>
>> Martin Arrieta.
>>
>>
>> On 3 August 2012 05:42, G=F6tz Reinicke <goetz.reinicke@stripped>=
 wrote:
>>> Hi,
>>>
>>> we run a master/slave mysql 5.1.61 Red Hat EL 6.3 setup - two servers.
>>>
>>> Both use the default binlog format.
>>>
>>> Today I'm asked to change the bin log format to 'ROW' because a special
>>> Application needs that format. (Confluence Wiki ...)
>>>
>>> My Question:
>>>
>>> Is it possible to shutdown servers, 'just' change the option (my.cfg) o=
n
>>> both, the master and the slave, start servers without expecting known
>>> problems?
>>>
>>> We do have a few databases currently installed (for Websit, Webmail,
>>> projektmanagement software) which work fine with all the curren (and
>>> default) mysql server settings.
>>>
>>> Thanks for any suggestions or hints! Regards . G=F6tz
>
>
> --
> G=F6tz Reinicke
> IT-Koordinator
>
> Tel. +49 7141 969 82 420
> Fax  +49 7141 969 55 420
> E-Mail goetz.reinicke@stripped
>
> Filmakademie Baden-W=FCrttemberg GmbH
> Akademiehof 10
> 71638 Ludwigsburg
> www.filmakademie.de
>
> Eintragung Amtsgericht Stuttgart HRB 205016
>
> Vorsitzender des Aufsichtsrats:
> J=FCrgen Walter MdL
> Staatssekret=E4r im Ministerium f=FCr Wissenschaft,
> Forschung und Kunst Baden-W=FCrttemberg
>
> Gesch=E4ftsf=FChrer:
> Prof. Thomas Schadt
>