21

How do I show the binlog_format on a MySQL server?

And if I dont like it how do I set it to XX permanently?

Where XX is STATEMENT, ROW or MIXED.

Nifle
  • 1,472
  • 7
  • 17
  • 30

2 Answers2

32

To see the current binlog_format value:

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

To change it:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

Source: http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

Matt Healy
  • 1,342
  • 2
  • 12
  • 17
17

Matt Healy answered the question on how to show/set the format from the mysql client (on a running server) with SET GLOBAL binlog_format = [STATEMENT|ROW|MIXED]

To set the value permanently, and assuming you have access to the my.cnf, add:

[mysqld]
...

binlog_format=XX

...

and then restart your server.

Community
  • 1
Derek Downey
  • 23,440
  • 11
  • 78
  • 104
  • straightforward and concise. +1 !!! – RolandoMySQLDBA May 12 '11 at 16:16
  • 2

    [mysqld] .....

    binlog-format=XX ..... That needs to be binlog_format=XXX Underscore!

     –  Feb 01 '12 at 18:32
  • @Jeff When mysqld reads /etc/my.cnf, it totally understands both dashes (-) and underscores (_). In the mysql client, an underscore is mandatory. +1 anyway, and welcome to the DBA StackExchange !!! – RolandoMySQLDBA Feb 01 '12 at 18:52
  • 1
    @jeff thanks! It's strange that they flip flop between '-' and '_', but the docs definitely say the option file parameter is binlog-format=format – Derek Downey Feb 01 '12 at 19:47
  • I think binlog-format (with dash) is used from the command line, while binlog_format (with underscore) is the system variable name. – Dylan Hogg Jun 28 '19 at 03:19