MYSQL - Purge binary logs

1. SHOW SLAVE STATUS on slave server

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.120.251.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000047
          Read_Master_Log_Pos: 925196802
               Relay_Log_File: relay-bin.000140
                Relay_Log_Pos: 925196965
        Relay_Master_Log_File: mysql-bin.000047
             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: 925196802
              Relay_Log_Space: 925197179
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 55f6585c-bd69-11e2-90a5-ac162d8b1c44
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)


2. SHOW BINARY LOGS on master server

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |        540 |
| mysql-bin.000002 |        143 |
| mysql-bin.000003 |        143 |
| mysql-bin.000004 |        143 |
| mysql-bin.000005 |        143 |
| mysql-bin.000006 |        143 |
| mysql-bin.000007 |       4810 |
| mysql-bin.000008 |        224 |
| mysql-bin.000009 |    2090270 |
| mysql-bin.000010 | 1073795562 |
| mysql-bin.000011 | 1073787709 |
| mysql-bin.000012 | 1073755369 |
| mysql-bin.000013 | 1073760500 |
| mysql-bin.000014 |   13629930 |
| mysql-bin.000015 |      20507 |
| mysql-bin.000016 | 1073756709 |
| mysql-bin.000017 | 1073786502 |
| mysql-bin.000018 |  656410524 |
| mysql-bin.000019 |   11416561 |
| mysql-bin.000020 |   38092807 |
| mysql-bin.000021 | 1073764283 |
| mysql-bin.000022 | 1073763265 |
| mysql-bin.000023 | 1073758670 |
| mysql-bin.000024 | 1073761848 |
| mysql-bin.000025 | 1073767027 |
| mysql-bin.000026 | 1073807701 |
| mysql-bin.000027 | 1073815447 |
| mysql-bin.000028 | 1073746876 |
| mysql-bin.000029 | 1073752803 |
| mysql-bin.000030 | 1073810003 |
| mysql-bin.000031 | 1073742110 |
| mysql-bin.000032 | 1073807390 |
| mysql-bin.000033 | 1073759944 |
| mysql-bin.000034 | 1073742378 |
| mysql-bin.000035 | 1073764107 |
| mysql-bin.000036 | 1073775793 |
| mysql-bin.000037 |  996879580 |
| mysql-bin.000038 |    3833572 |
| mysql-bin.000039 |     240904 |
| mysql-bin.000040 |     610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 |  925713238 |
+------------------+------------+
47 rows in set (0.01 sec)

3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list. (mysql-bin.000046)

4. Copy all binary logs that will be deleted

[root@mysql]# cp mysql-bin.00000* /db/backup_logs/
[root@mysql]# cp mysql-bin.00001* /db/backup_logs/
[root@mysql]# cp mysql-bin.00002* /db/backup_logs/

[root@mysql]# cp mysql-bin.00003* /db/backup_logs/

5. Purge all log files except target log file

mysql> purge binary logs to 'mysql-bin.000039';

Query OK, 0 rows affected (1.00 sec)

6. List remaning binary logs on master

mysql> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000039 |     240904 |
| mysql-bin.000040 |     610464 |
| mysql-bin.000041 | 1073800478 |
| mysql-bin.000042 | 1073791022 |
| mysql-bin.000043 | 1073796481 |
| mysql-bin.000044 | 1073756133 |
| mysql-bin.000045 | 1073764282 |
| mysql-bin.000046 | 1073796542 |
| mysql-bin.000047 |  935732985 |
+------------------+------------+
9 rows in set (0.02 sec)