2

We're using features to manage content types and field groups, and every code update, when we perform a features-revert to push out the latest CTs and field group changes, we get a huge amount of data written to our binlogs -- a gig about every 10 mins:

-rw-rw----. 1 mysql mysql 1073759569 Aug  1 02:31 mysql-bin.000138
-rw-rw----. 1 mysql mysql 1073766800 Aug  1 02:42 mysql-bin.000139
-rw-rw----. 1 mysql mysql 1074139941 Aug  1 02:50 mysql-bin.000140
-rw-rw----. 1 mysql mysql 1073954804 Aug  1 02:59 mysql-bin.000141

which quickly locks up our MySQL servers.

What might be causing the large amount of data to be written? What can we be doing on the MySQL side and the Drupal side to keep this in check?

Improve this question

1 Answer 1

Reset to default
2

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> 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 > 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 > 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 >> PITR.sql

STEP 05) Load PITR.sql into the Master

mysql -h IPAddressOfMaster -u ... -p... < 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 ???

Improve this answer
1
  • Thanks @RolandoMySQLDBA. What about point-in-time recovery, if bin logs are cleared from the slaves? Would that be based on binlogs from the master?
    – KM.
    Commented Aug 9, 2012 at 15:40

Your Answer

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

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