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.

2 Answers 2

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.

5
  • straightforward and concise. +1 !!! Commented May 12, 2011 at 16:16
  • 2
    > [mysqld] ..... > > binlog-format=XX > > ..... That needs to be binlog_format=XXX Underscore!
    – user6353
    Commented Feb 1, 2012 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 !!! Commented Feb 1, 2012 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 Commented Feb 1, 2012 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
    Commented Jun 28, 2019 at 3:19
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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