Mysql not writing to binlog

MySQL

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

Best Answer

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.

SUGGESTION #1

If web.files has no FULLTEXT indexes, you should convert web.files to InnoDB with

ALTER TABLE web.files ENGINE=InnoDB;

I suggested this because InnoDB will allow UPDATEs, DELETEs, and INSERTs to harmoniously coexist with SELECTs via MVCC and Transaction Isolation.

If you are not the Project Manager/Lead Developer, please consult with your Project Manager/Lead Developer before doing this.

SUGGESTION #2

SELECT * FROM web.files order by file_name_eng desc

From the EXPLAIN plan, I can see that the query

Query must do a full table scan because there is no WHERE clause
There is no index on file_name_eng column, so there is no assistance in order the table (a filesort would happen, but happen fast)

Therefore, you should run

ALTER TABLE web.files ADD INDEX (file_name_eng);

Rhetorial Questions

Do you need SELECT * ?
Could you list only the columns you really need ?

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.