MySQL Replication – Does a Read-Only Slave Need Log-Bin and Relay-Bin Logs?

disk-spacelogsMySQLreplication

I'm running out of disk space on a newly initiated MySQL slave server. The bulk of disk space is in /var/log/mysql where I see both mysql-bin.000XXX and mysql-relay-bin.000XXX logs, each 1.1G.

Does a traditional read-only MySQL slave need both of these? mysql-bin.000XXX goes all the way back to 000001 which leads me to believe these aren't used.

Here's the relevant information from my my.cnf:

## Slave my.cnf
binlog_format                   = mixed
log_bin                         = /var/log/mysql/mysql-bin.log
sync_binlog                     = 1
pid_file                        = /var/run/mysqld/mysqld.pid
log_error                       = /var/log/mysql/error.log
general_log                     = 0
general_log_file                = /var/log/mysql/general.log
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 3

server_id                       = 2

relay-log                       = /var/log/mysql/mysql-relay-bin.log
replicate-wild-do-table         = my_table_prefix_%.%
log_slave_updates               = 1
read_only                       = 1

Here's a ls -lah of my log directory:

-rw-r-----  1 mysql mysql 1.1G Mar 29 19:48 mysql-bin.000001
-rw-r-----  1 mysql mysql 1.1G Mar 29 19:55 mysql-bin.000002
-rw-r-----  1 mysql mysql 1.1G Mar 29 20:03 mysql-bin.000003
...
-rw-r-----  1 mysql mysql 1.1G Mar 29 20:11 mysql-bin.000286
...
-rw-r-----  1 mysql mysql  254 Mar 31 12:27 mysql-relay-bin.000015
-rw-r-----  1 mysql mysql 1.1G Mar 31 12:27 mysql-relay-bin.000016
-rw-r-----  1 mysql mysql  326 Mar 31 12:27 mysql-relay-bin.000017
...
-rw-r-----  1 mysql mysql 1.1G Mar 31 12:27 mysql-relay-bin.000769

Slave is slowly catching up to master and purging mysql-relay-bin files appropriately. But it looks like mysql-bin files will stack up forever.

And slave status:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000291
          Read_Master_Log_Pos: 15703910
               Relay_Log_File: mysql-relay-bin.000016
                Relay_Log_Pos: 662979789
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: my_table_prefix_%.%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 662979592
              Relay_Log_Space: 271579588573
              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: 729594
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: 100
                  Master_UUID: 5e12-f712611e5-b2f1-f206553c91-3b124
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

Best Answer

You are right. Traditionally, replication slaves need binary logs enabled unless you plan to failover to it someday.

The only reasons for using binary logs on a slave would be

  • Setting you circular replication (Makes a Slave a Master at the same time)
  • You want to promote the Slave to a Master
  • Using it as a Distribution Master (All Binary Logs,No Data, See my old post Is it Possible to Recover Data from a BLACKHOLE table? that describes why one would use such a setup)

As for your actual situation, here are two suggestions (pick one)

SUGGESTION #1

First, login to mysql as root@localhost on that slave and run 

mysql> STOP SLAVE;
mysql> RESET MASTER;

Next, comment out this line from my.cnf

#log_bin                         = /var/log/mysql/mysql-bin.log
#log_slave_updates               = 1

Next, restart mysql

service mysql restart

Finally, manually delete those logs

cd /var/log/mysql
rm -f mysql-bin.*

SUGGESTION #2

If you do not want to restart mysql, do this

First, login to mysql as `root@localhost on that slave and run 

mysql> SET GLOBAL expire_logs_days = 1;
mysql> RESET MASTER;

Then, add this line to my.cnf

expire_logs_days = 1

That way, the slave would still pile up binary logs, but would auto delete binary logs older than 24 hours.

GIVE IT A TRY !!!

Related Question