16

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

Matthewh answered the question on how to show/set the format from the mysql client (on a running server).

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

[mysqld]
.....

binlog-format=XX

.....

and 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
23

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.