6

I am running MySQL 5.1.47 on CentOS. How do I check if binary logging for InnoDB is active? How do I enable binary logging if they are not enabled? Just run?
mysqld --log-bin

Adrien Hingert
  • 309
  • 2
  • 3
  • 9

1 Answers1

10

Check if binary logging is active:

mysql> show variables like '%bin%';
+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| binlog_cache_size               | 32768                |
| binlog_format                   | STATEMENT            |
| innodb_locks_unsafe_for_binlog  | OFF                  |
| log_bin                         | ON                   |
| log_bin_trust_function_creators | OFF                  |
| log_bin_trust_routine_creators  | OFF                  |
| max_binlog_cache_size           | 18446744073709547520 |
| max_binlog_size                 | 419430400            |
| sql_log_bin                     | ON                   |
| sync_binlog                     | 0                    |
+---------------------------------+----------------------+
10 rows in set (0.00 sec)

The key part here is log_bin ON but the other results can be interesting / important too.

You should also see a bunch of files in your datadir named hostname-bin.00001. You can use mysqlbinlog <filename> to see the queries in the binary logs.

To turn it on, mysql> SET GLOBAL log_bin = ON;

You will also want to edit your my.cnf so that log_bin = ON the next time you restart MySQL.

Ladadadada
  • 26,337
  • 7
  • 59
  • 90
  • 3
    It is also a good practice to set log-bin = /path/to/bin-log, (my-cnf), and use another disk partition, this way, if you forget to clean up the binary logs, you mysqld will continue running without major problems. – CarlosH Mar 07 '12 at 03:32