Not the answer you need?
Register and ask your own question!

Disable binary logs while purging the records

krishnakrishna ContributorInactive User Role Beginner
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

Comments

  • mmikemmike Entrant Current User Role Beginner
    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
  • krishnakrishna Contributor Inactive User Role Beginner
    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
  • unixroninunixronin Contributor Current User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.