Ryan Ryan - 3 months ago 12
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

mysql-bin.000345
into
mysql-bin.000295
(subtracting 50 from
345
)?

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"
EOF


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

Answer

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:

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