17

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.

14

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.

  • straightforward and concise. +1 !!! – RolandoMySQLDBA May 12 '11 at 16:16
  • 2
    > [mysqld] ..... > > binlog-format=XX > > ..... That needs to be binlog_format=XXX Underscore! – user6353 Feb 1 '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 1 '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 1 '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 at 3:19
25

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.