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.
Add a comment
|
2 Answers
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.
answered May 12, 2011 at 15:03
-
-
2> [mysqld] ..... > > binlog-format=XX > > ..... That needs to be binlog_format=XXX Underscore!– user6353Feb 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 !!! 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=formatFeb 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. Jun 28, 2019 at 3:19
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
answered May 12, 2011 at 14:10
