SLAVE
If your Slaves are not Masters, then Slaves do not need binary logging at all. You can put a cap on the amount of relay log space accumulated by a Slave. In order to throttle relay logs at 4G, add relay_log_space_limit
to /etc/my/.cnf on every Slave
[mysqld]
relay_log_space_limit=4G
and restart mysql
If you cannot set this, at least you should have some kind of alerting that does SHOW SLAVE STATUS\G
and check the value of Relay_Log_Space
(total bytes consumed by relay logs).
MASTER
As for the Master, you could set expire_logs_days
to 1, but there is a severe warning I have for you...
If replication breaks, you have 1 day to fix it. Otherwise, a binary log on the Master may rotate away and you cannot run any CHANGE MASTER TO command to realign replication. I would leave expire_logs_days
at 3 on the Master.
SUGGESTION #1
If you have any overnight bulk processing to do, maybe should run the bulk processes on on the Master with SET SQL_LOG_BIN=0;
at the Start of the Session. This, of course, will not replicate to the Slave. You can perform the Same Bulk Load in Parallel to both Slaves.
SUGGESTION #2
Another thing you could do to manage the Master binary logs accumulation is this.
Run SHOW SLAVE STATUS\G
on both Slaves. Look at Relay_Master_Log_File
. That represents the binary log on the Master whose last command was executed on the Slave.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.92.250
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.009677
Read_Master_Log_Pos: 855227755
Relay_Log_File: relay-bin.000674
Relay_Log_Pos: 757296783
Relay_Master_Log_File: mysql-bin.009590
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: 757296646
Relay_Log_Space: 94274010765
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: 80561
1 row in set (0.00 sec)
In this example, Relay_Master_Log_File is mysql-bin.009590. All binary logs before this one can be removed from Master. You could run this on the Master:
PURGE BINARY LOGS TO 'mysql-bin.009590';
This will erase older logs and still leave replication in tact.
CAVEAT
Binary Logs are files that serially compiles (like a FIFO queue) all completed SQL transactions as either a SQL statement or a row change. A relay log is a file that collects binary log entries from a remote server (aka Master).
In MySQL Replication
- Master must have its binary logs enabled
- Slave compiles relay logs
- When all SQL in a relay log is processed, it is deleted
- On a Slave, when there is more that one relay log on a DB Server, it may indicate replication is falling behind because the IO thread is collecting SQL from a Master faster that the SQL thread can process the relay logs.
- Using relay_log_space_limit prevents replication from piling up and potentially filling up a disk. Relay logs rotate out based on rule #3
- It is possible for a DB Server to be both a Master and Slave. That's the only circumstance under which a Slave must have binary logs enabled. In that scenario, a DB Server will have both Binary Logs and Relay Logs.
If you failover to a Slave, and you want to Make it a Master
- service mysql stop
- Add
log-bin=mysql-bin
to /etc/my.cnf on the Slave
- service mysql start
You will have to setup replication of other Slaves to the newly promoted Master and make sure the data on the Slave match up with the newly promoted Master
UPDATE 2012-08-13 17:47 EDT
According to the MySQL Documentation on relay-log
option, you should define it. Here is why:
Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.
DISCLAIMER : Not a user of mha4mysql
.
SUGGESTION #1
Hang the Lag Slave off from the Standby Server. How?
- Step 01: Stop all Writes to the Master
- Step 02: Run
SHOW MASTER STATUS;
on the StandByMaster
- Step 03: Run mysqlbinlog against the binlog from
Step 02
- Step 04: Acquire timestamp from given position
- Step 05: Subtract 43200 sec (12 hrs) from the Timestamp acquired
- Step 06: Locate position just before timestamp acquired
- Step 07: Using StandByMaster Host IP, Log File and Position, run this on Lag Slave
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='...',MASTER_LOG_FILE='...',MASTER_LOG_POS=...;
START SLAVE;
All manual steps. I'm sorry.
SUGGESTION #2
Setup the Lag Slave under one of the Slaves PERMANENTLY. You'll never have worry about moving the Lag Slave between Masters again.
SUGGESTION #3
Setup the Lag Slave under a Blackhole Slave. You'll never have worry about moving the Lag Slave between Masters again.
I hope these suggestions helped or at least got you thinking.
Best Answer
It's ok to purge binlog on
M 01 ~ M 06
as long asS 01 ~ S 06
has relayed all the binlog you want to purge, but remember to purge slowly, for example each time purge 20 binlogs (depends on your single binlog file size and the server load)