Disable binary logs while purging the records

[krishna]

Hi Team,

We have a parameter “–binlog-ignore-db” to disable logging for a particular database. Here, if the parameter is not existing in my.cnf file we have to add in the configuration file and restart mysql to have the changes affective. Below are the variables that are currently enabled as per the DB:

±----------------------------------------±------+
| Variable_name | Value |
±----------------------------------------±------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | ROW |
| binlog_stmt_cache_size | 32768 |
±----------------------------------------±------+

Is there a way where I can add the parameter dynamically as we will not be able to restart the mysql instance as there are other DB’s running which cannot be down for a second. Please suggest…!

Best Regards,
Krishna

[mmike]

Hi, Krishna.

Unfortinately, there is no way to do so as binlog-ignore-db is a command-line/config-file option. You can see if those are set while mysql is running with ‘show master status’.

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#option_mysqld_binlog-ignore-db

[krishna]

Hi Mike,

Thanks for your reply.

Could you please have this incorporated in the next release of MySQL as it can be a good option to ignore binary logging when we are performing huge operations on particular Database.

Thanks,
Krishna

[unixronin]

Krishna, Allow me to introduce you to an already-existing MySQL feature: SET SESSION SQL_LOG_BIN = 0; This will allow you to selectively have all commands in that session omitted from the binlog. The setting remains in effect for the duration of the setting. However, keep in mind that if you do this, then your master and your slave will be massively out of sync after your “huge operation” is done, unless you manually apply the same set changes on each of your slave(s) as well.