Step 1: Checking the Database Status
The first step is to check if the log-bin option is turned on or not and what binary log format is used. MySQL provides three types of bin-log format: ROW, MIXED and STATEMENT. The following query shows that the binary logging is turned off and the binary log format is STATEMENT.
mysql> select variable_value as "BINARY LOGGING STATUS (log-bin) :: " from information_schema.global_variables where variable_name='log_bin'; +-------------------------------------+ | BINARY LOGGING STATUS (log-bin) :: | +-------------------------------------+ | OFF | +-------------------------------------+ 1 row in set (0.00 sec) mysql> select variable_value as "BINARY LOG FORMAT (binlog_format) :: " from information_schema.global_variables where variable_name='binlog_format'; +---------------------------------------+ | BINARY LOG FORMAT (binlog_format) :: | +---------------------------------------+ | STATEMENT | +---------------------------------------+ 1 row in set (0.00 sec)
To update the setup, you need to update the MySQL configuration file (my.ini or my.cnf) by adding the following lines to the [mysqld] section (the Server Section).
log-bin=bin.log log-bin-index=bin-log.index max_binlog_size=100M binlog_format=row socket=mysql.sock
Step 3: Confirming the Changes
After restarting the MySQL server, you can check the binary logging status again. Make sure the log_bin is ON and the binary log format is the format supported by the replication tools. For example, Oracle GoldenGate 11.2 and 12c only supports ROW format.
mysql> select variable_value as "BINARY LOGGING STATUS (log_bin) :: " from information_schema.global_variables where variable_name='log_bin'; +-------------------------------------+ | BINARY LOGGING STATUS (log_bin) :: | +-------------------------------------+ | ON | +-------------------------------------+ 1 row in set (0.00 sec) mysql> select variable_value as "BINARY LOG FORMAT (binlog_format) :: " from information_schema.global_variables where variable_name='binlog_format'; +---------------------------------------+ | BINARY LOG FORMAT (binlog_format) :: | +---------------------------------------+ | ROW | +---------------------------------------+ 1 row in set (0.00 sec)
mysql> show binary logs; +------------+-----------+ | Log_name | File_size | +------------+-----------+ | bin.000001 | 144 | | bin.000002 | 239 | | bin.000003 | 319 | | bin.000004 | 107 | +------------+-----------+ 4 rows in set (0.00 sec)