Large Binlogs Generated When Reverting Features
DISCLAIMER : Not a Drupal Expert, Just a MySQL DBA

There are really two classes of answers for this question


STANDALONE DB SERVER
MASTER/SLAVE REPLICATION


STANDALONE DB SERVER

If you know you are going to perform a feature-revert, the simplest thing to do is this

StandAlone Method #1 : Disable Binary Logs


STEP 01) Run SET GLOBAL SQL_LOG_BIN = 0;
STEP 02) Run your feature-revert
STEP 03) Run SET GLOBAL SQL_LOG_BIN = 1;


This will disable binary logging during the feature revert and reenable afterwards

StandAlone Method #2 : Erase All Binary Logs Except the Last One

You could crontab a job that runs this:

PURGE BINARY LOGS BEFORE NOW();


StandAlone Method #3 : Erase All Binary Logs

You could crontab a job that runs this:

RESET MASTER;


MASTER/SLAVE REPLICATION

In standard MySQL Master/Slave Replication, a Master Has Binary Logging Active and the Slave Does Not. You cannot just disable binary logging like you would for a StandAlone DB Server because you need to replicate the DB Changes of the feature-revert.

Here is what to do stem the rise of binary logs and relay logs

Replication Master (Manage Binary Logs)

At the completion of a feature-revert, which binary logs are safe to remove so that MySQL Replication can still work and remain intact?

Here is what you do:


STEP 01) Run SHOW SLAVE STATUS\G On the Slave.


For example:

mysql&gt; show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.64.97.249
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000658
        Read_Master_Log_Pos: 799061333
             Relay_Log_File: relay-bin.003861
              Relay_Log_Pos: 47059
      Relay_Master_Log_File: mysql-bin.000658
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 799061333
            Relay_Log_Space: 47059
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)



STEP 02) Get binary log from Relay_Master_Log_File

Field #10 of SHOW SLAVE STATUS\G
That would be mysql-bin.000658

STEP 03) On the Master, Run PURGE BINARY LOGS TO 'mysql-bin.000658';


Replication Slave (Manage Relay Logs)

To force a Slave to Throttle the amount of Relay Logs to Just 2GB, add this to the Slave's my.cnf

[mysqld]
relay_log_space_limit=2G


and restart mysql on the Slave. When a Relay Log is fully processed it is automatically deleted and makes room for the next Relay Log.

EPILOGUE

You should make sure you have this set in /etc/my.cnf on the StandAlone DB Server or Master

[mysqld]
expire_logs_days = 3;


and restart mysql. This will make sure all binary logs older that 3 days are automatically deleted.

If you are curious and want to know what runs in a feature-revert You could


Look through source code of the feature-revert module
take the binary logs and dump the contents in plain text SQL


Run the following:

mysqlbinlog mysql-bin.000138 mysql-bin.000139 mysql-bin.000140 mysql-bin.000141 &gt; Revert.sql
less Revert.sql


UPDATE 2012-08-09 12:40 EDT

If you are concerned with point-in-time recovery, you must decide how many days worth of binary logs you are willing to keep.

Here is a scenario: Say you want to be able to revert back up to 7 days ago. You would have to setup the following:

STEP 01) Have the Slave maintain its set of Binary Logs with an expiration of 7 days

Add this to /etc/my.cnf on the Slave

[mysqld]
log-bin=mysql-bin
expire_logs_days=7


STEP 02) service mysql restart

STEP 03) Perform a midnight Backups on the Slave as follows


STOP SLAVE;
FLUSH LOGS;
mysqldump on the Slave
START SLAVE;


STEP 04) Delete backups older than 14 days

Once you have Steps 3 and 4 automated, you must let mysql rotate the binary logs on the Slave. You should not manually delete the binary logs on the Slave. Keep in mind that point-in-time recovery is not automatic. It is rigorous manual process.

Would you like a description of a point-in-time recovery ??? Here we go...

Let's say you have the following


It's August 9, 2012 12:11 PM
run mysqldumps on the Slave every night at midnight
each backup is named by date Backup_99999999.sql in /backups
and you have the following list of binary logs on the Slave


_

-rw-rw---- 1 mysql mysql 1073742269 Aug  2 11:22 mysql-bin.001789
-rw-rw---- 1 mysql mysql 1073762807 Aug  2 16:22 mysql-bin.001790
-rw-rw---- 1 mysql mysql 1073742048 Aug  2 21:28 mysql-bin.001791
-rw-rw---- 1 mysql mysql 1073741969 Aug  3 06:34 mysql-bin.001792
-rw-rw---- 1 mysql mysql 1073741954 Aug  3 11:20 mysql-bin.001793
-rw-rw---- 1 mysql mysql 1073742182 Aug  3 16:19 mysql-bin.001794
-rw-rw---- 1 mysql mysql 1073742228 Aug  3 21:37 mysql-bin.001795
-rw-rw---- 1 mysql mysql 1073753003 Aug  4 06:32 mysql-bin.001796
-rw-rw---- 1 mysql mysql 1073742100 Aug  4 13:18 mysql-bin.001797
-rw-rw---- 1 mysql mysql 1073741891 Aug  4 18:56 mysql-bin.001798
-rw-rw---- 1 mysql mysql 1073742424 Aug  5 06:12 mysql-bin.001799
-rw-rw---- 1 mysql mysql 1073742017 Aug  5 18:35 mysql-bin.001800
-rw-rw---- 1 mysql mysql 1073741952 Aug  6 05:27 mysql-bin.001801
-rw-rw---- 1 mysql mysql 1073757571 Aug  6 13:00 mysql-bin.001802
-rw-rw---- 1 mysql mysql 1073742172 Aug  6 17:35 mysql-bin.001803
-rw-rw---- 1 mysql mysql 1073742067 Aug  7 00:35 mysql-bin.001804
-rw-rw---- 1 mysql mysql 1073777487 Aug  7 10:45 mysql-bin.001805
-rw-rw---- 1 mysql mysql 1073791986 Aug  7 15:51 mysql-bin.001806
-rw-rw---- 1 mysql mysql 1073742035 Aug  7 20:37 mysql-bin.001807
-rw-rw---- 1 mysql mysql 1073741978 Aug  8 05:28 mysql-bin.001808
-rw-rw---- 1 mysql mysql 1073742217 Aug  8 10:30 mysql-bin.001809
-rw-rw---- 1 mysql mysql 1073742422 Aug  8 14:41 mysql-bin.001810
-rw-rw---- 1 mysql mysql 1073743447 Aug  8 18:47 mysql-bin.001811
-rw-rw---- 1 mysql mysql 1073742241 Aug  9 00:36 mysql-bin.001812
-rw-rw---- 1 mysql mysql 1073741985 Aug  9 08:45 mysql-bin.001813
-rw-rw---- 1 mysql mysql  743967477 Aug  9 12:10 mysql-bin.001814


You want to reload the Master going back to August 5, 2012 02:30 PM

Here are you steps 

STEP 01) Take all binary logs that encompass August 5, 2012 from 12:00AM to 2:30PM. This would be binlogs mysql-bin.001799 and mysql-bin.001800. These are used because


PRINCIPLE : the first write of a binlog is also the last write of the previous binlog

the last write to mysql-bin.001798 is Aug 4 18:56 (6:56PM) 2012-08-04 18:56:00
the first write to mysql-bin.001799 is Aug 4 18:56 (6:56PM) 2012-08-04 18:56:00
the last write to mysql-bin.001799 is Aug 5 06:12 (6:12AM) 2012-08-05 06:12:00
the first write to mysql-bin.001800 is Aug 5 06:12 (6:12AM) 2012-08-05 06:12:00
the last write to mysql-bin.001800 is Aug 5 18:35 (6:35PM) 2012-08-05 18:35:00



The range 2012-08-04 18:56:00 to 2012-08-05 18:35:00 is the narrowest range that surround 2012-08-05 00:00:00 to 2012-08-05 14:30:00. This is why binlogs mysql-bin.001799 and mysql-bin.001800 are the correct choices

STEP 02) Generate all the SQL that ran August 5, 2012 from 12:00AM to 2:30PM

mysqlbinlog --start-datetime="2012-08-05 00:00:00" --stop-datetime="2012-08-05 14:30:00" mysql-bin.001799 mysql-bin.001800 &gt; ForwardChanges.sql


STEP 03) Make a copy of the August 5, 2012 midnight backup to PITR.sql

cp /backups/Backup_20120805.sql PITR.sql


STEP 04) Append ForwardChanges.sql to PITR.sql

cat ForwardChanges.sql &gt;&gt; PITR.sql


STEP 05) Load PITR.sql into the Master

mysql -h IPAddressOfMaster -u ... -p... &lt; PITR.sql


When the script is done, the database will be in the state it was as of August 5, 2012 2:30PM

Pretty easy, huh ???