MySQL, check & enable binary logs
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
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.