Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 7 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

[mysqld]
.....

binlog-format=XX

.....

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
1  
> [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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

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.