0

Mysql is not writing events to binlog, I have tried change the binlog configurations, but can't solve.

Conf:

server_id = 2
log-bin = master
binlog_format = row
replicate-do-db = our_db
log-slave-updates = true

'%log%' config: Seems that log_bin_basename does not exists in 5.5 and log-bin is used to configure the name and automatically turn on the log_bin, but I am still not receiving events in binlog, it just creates the file and write ' master.000919 | 4 | Format_desc | 2 | 107 | Server ver: 5.5.50-0+deb7u2-log, Binlog ver: 4' expire_logs_days = 10 max_binlog_size = 200M binlog_do_db = our_db

I also verified that the events are being write to general log

Version: mysql Ver 14.14 Distrib 5.5.50, for debian-linux-gnu (x86_64) using readline 6.2

The events that I need to be write in binlog are common inserts.

'%log%' config:

Variable_name                            Value                           
back_log                                 50                              
binlog_cache_size                        32768                           
binlog_direct_non_transactional_updates  OFF                             
binlog_format                            ROW                             
binlog_stmt_cache_size                   32768                           
expire_logs_days                         10                              
general_log                              OFF                             
general_log_file                         /opt/mysql/SRV01STM085.log      
innodb_flush_log_at_trx_commit           1                               
innodb_locks_unsafe_for_binlog           OFF                             
innodb_log_buffer_size                   8388608                         
innodb_log_file_size                     5242880                         
innodb_log_files_in_group                2                               
innodb_log_group_home_dir                ./                              
innodb_mirrored_log_groups               1                               
log                                      OFF                             
log_bin                                  ON                              
log_bin_trust_function_creators          OFF                             
log_error                                                                
log_output                               FILE                            
log_queries_not_using_indexes            OFF                             
log_slave_updates                        ON                              
log_slow_queries                         OFF                             
log_warnings                             1                               
max_binlog_cache_size                    18446744073709547520            
max_binlog_size                          209715200                       
max_binlog_stmt_cache_size               18446744073709547520            
max_relay_log_size                       0                               
relay_log                                                                
relay_log_index                                                          
relay_log_info_file                      relay-log.info                  
relay_log_purge                          ON                              
relay_log_recovery                       OFF                             
relay_log_space_limit                    0                               
slow_query_log                           OFF                             
slow_query_log_file                      /opt/mysql/SRV01STM085-slow.log 
sql_log_bin                              ON                              
sql_log_off                              OFF                             
sync_binlog                              0                               
sync_relay_log                           0                               
sync_relay_log_info                      0
Improve this question

1 Answer 1

Reset to default
0

From the 5.5 manual:

Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the base name. This is a known issue; see Bug #19614 for more information.

Newer versions have made changes in this area, but there continues to be a difference between how the VARIABLE log_bin (in my.cnf) and the commandline --log-bin (when invoking mysqld).

From https://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html :

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.

Improve this answer
9
  • change log-bin to log_bin did not work.
    – Gabriel Weich
    May 22, 2019 at 17:55
  • @GabrielNoth - Different syntax for different places where you set it. In my.cnf, you need log_bin=ON and log_bin_basename=master.
    – Rick James
    May 22, 2019 at 19:24
  • Seems that log_bin_basename does not exists in 5.5 and log-bin is used to configure the name and automatically turn on the log_bin.
    – Gabriel Weich
    May 23, 2019 at 13:05
  • But I am still not receiving events in binlog, it just creates the file and save the event ' master.000919 | 4 | Format_desc | 2 | 107 | Server ver: 5.5.50-0+deb7u2-log, Binlog ver: 4'
    – Gabriel Weich
    May 23, 2019 at 13:12
  • I verified that a query executed by a normal client is write to binlog, but one insert by a trigger isn't. After trying almost every possible solution, I made a trigger that writes the inserts to another table and a Python that delete all registers in that table every second, the delete write in binlog and then I can capture it. I have to turn on log_bin_trust_function_creators also.
    – Gabriel Weich
    May 24, 2019 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.