Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


share|improve this question
up vote 12 down vote accepted

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




and restart your server.

share|improve this answer
straightforward and concise. +1 !!! – RolandoMySQLDBA May 12 '11 at 16:16
@Rolando Thank you sir! – Derek Downey May 12 '11 at 16:54
> [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
@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

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';


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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