MySQL Logs - purge old entries

MySQL bin logs can take disk space - and if not off-loading to archive server - can hog up space quickly - esp on a VPS or cloud where paying for diskspace...

Update: created a script that does this now: purgebinarylogs

Note: the script uses .my.cnf file for login info - please make sure to set that up/test first..

Also can see here what drupal tables I ignore - note I am not running any ecommerce nor high volume make my living off drupal sites - else I'd be writing about something else.. :)

I have a SSH tunnel setup - TCP takes care of keeping the data (mysql binlogs) pretty well intact - something that mysql binlogs do not have - integrity checking. Also by using the SSH tunnels, can keep the firewall shut.

1. Check slave status: make sure things are running and caught up.
   

   *************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 127.0.0.1
                   Master_User: slave_user
                   Master_Port: 3307
                 Connect_Retry: 60
               Master_Log_File: bin.000031
           Read_Master_Log_Pos: 65660958
                Relay_Log_File: relay.000799
                 Relay_Log_Pos: 4698187
         Relay_Master_Log_File: bin.000031
              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: userwww.watch%,userwww.temp%,mysql.%,userdev.watch%,userdev.temp%,usertest.watch%, usertest.temp%,userwww2.watch%,userwww2.temp%
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 65660958
               Relay_Log_Space: 4698187
               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
   

   Slave IO, Slave SQL = Yes, Seconds_Behind_Master: 0 - we are good.

2. Check out the diskspace used:
   

   root@host /var/log/mysql #  ls -ltr
   total 2346716
   -rw-rw---- 1 mysql mysql 1073744645 Mar 13 14:29 bin.000025
   -rw-rw---- 1 mysql mysql  685764054 Mar 31 00:40 bin.000026
   -rw-rw---- 1 mysql mysql       2474 Mar 31 00:40 bin.000028
   -rw-rw---- 1 mysql mysql     648984 Mar 31 00:40 bin.000029
   -rw-rw---- 1 mysql mysql      14068 Mar 31 00:40 bin.000027
   -rw-rw---- 1 mysql mysql  541344128 Apr 12 12:04 bin.000030
   -rw-rw---- 1 mysql mysql        182 Apr 12 12:05 log-bin.index
   -rw-rw---- 1 mysql root    18611060 Apr 12 12:05 error.log
   -rw-rw---- 1 mysql mysql   80508261 Apr 13 23:47 bin.000031
   root@host /var/log/mysql #  du -ks .
   2346720	.
   

   2.3 Gigs - yeah that's a lot and got some older logs in there - up to a month old..

3. In the master, MySQL can see the logs also that mysql knows about:
   

   mysql> show binary logs;
   +------------+------------+
   | Log_name   | File_size  |
   +------------+------------+
   | bin.000025 | 1073744645 | 
   | bin.000026 |  685764054 | 
   | bin.000027 |      14068 | 
   | bin.000028 |       2474 | 
   | bin.000029 |     648984 | 
   | bin.000030 |  541344128 | 
   | bin.000031 |   81014400 | 
   +------------+------------+
   7 rows in set (0.00 sec)
   

4. Time to clean up some logs...
   

   mysql> purge binary logs to 'bin.000028'
       -> ;
   Query OK, 0 rows affected (6.45 sec)
   
   mysql> show binary logs;
   +------------+-----------+
   | Log_name   | File_size |
   +------------+-----------+
   | bin.000028 |      2474 | 
   | bin.000029 |    648984 | 
   | bin.000030 | 541344128 | 
   | bin.000031 |  81072551 | 
   +------------+-----------+
   4 rows in set (0.00 sec)
   
   mysql>
   

   and the file system

   root@host /var/log/mysql #  ls -ltr
   total 627276
   -rw-rw---- 1 mysql mysql      2474 Mar 31 00:40 bin.000028
   -rw-rw---- 1 mysql mysql    648984 Mar 31 00:40 bin.000029
   -rw-rw---- 1 mysql mysql 541344128 Apr 12 12:04 bin.000030
   -rw-rw---- 1 mysql root   18611060 Apr 12 12:05 error.log
   -rw-rw---- 1 mysql mysql  81056831 Apr 14 00:04 bin.000031
   -rw-rw---- 1 mysql mysql       104 Apr 14 00:04 log-bin.index
   

5. Note the "to" is up to, but not including...