Ryan Ryan - 11 months ago 49
MySQL Question

How can I purge up to the completed slave binary log, minus 50?

I'm using bash to purge binary logs on Master, only if the Slave has completed them. Instead I'd like to leave a bit more logs on the server. Can you help me turn

(subtracting 50 from

Here's my script:

# Fetch the current `Relay_Master_Log_File` from Slave
relay_master_log_file=$(ssh user@slave "mysql -hlocalhost -uroot -e 'SHOW SLAVE STATUS\G' | grep Relay_Master_Log_File | cut -f2- -d':' | sed -e 's/^[ \t]*//'")

# Purge binary logs on Master up to the current `Relay_Master_Log_File`
purge_cmd="PURGE BINARY LOGS TO '$relay_master_log_file';"
ssh user@master <<EOF
mysql -e "$purge_cmd"

But I'd like to keep 50 (or
) binary logs on Master instead.

Answer Source

Assuming you have mysql-bin.000345 in a variable, you could perform these steps:

  1. Strip the beginning part with the trailing zeros, leaving only 345
  2. Use Bash arithmetics $((...)) to subtract n from 345
  3. Use printf to format the result padded with zeros

For example:

num=$(shopt -s extglob; echo ${filename##mysql-bin.+(0)})
newname=mysql-bin.$(printf '%06d' $((num - n)))