List:Replication« Previous MessageNext Message »
From:martín arrieta Date:August 3 2012 1:31pm
Subject:Re: Changing the bin log format on existing servers
View as plain text  
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="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ötz 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ötz
>
> Am 03.08.12 13:28, schrieb martín 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 = '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ötz 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) on
>>> 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ötz
>
>
> --
> Götz Reinicke
> IT-Koordinator
>
> Tel. +49 7141 969 82 420
> Fax  +49 7141 969 55 420
> E-Mail goetz.reinicke@stripped
>
> Filmakademie Baden-Württemberg GmbH
> Akademiehof 10
> 71638 Ludwigsburg
> www.filmakademie.de
>
> Eintragung Amtsgericht Stuttgart HRB 205016
>
> Vorsitzender des Aufsichtsrats:
> Jürgen Walter MdL
> Staatssekretär im Ministerium für Wissenschaft,
> Forschung und Kunst Baden-Württemberg
>
> Geschäftsführer:
> Prof. Thomas Schadt
>
Thread
Changing the bin log format on existing serversGötz Reinicke3 Aug
  • Re: Changing the bin log format on existing serversmartín arrieta3 Aug
    • Re: Changing the bin log format on existing serversGötz Reinicke - IT Koordinator3 Aug
      • Re: Changing the bin log format on existing serversmartín arrieta3 Aug