1

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:
2

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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