问题描述:

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 [email protected] "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 [email protected] <<EOF

mysql -e "$purge_cmd"

EOF

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

网友答案:

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)))
网友答案:

You can get it in your first command itself using awk and as a bonus skip use of grep, cut and sed.

# Fetch the current `Relay_Master_Log_File` from Slave
relay_master_log_file=$(ssh [email protected] "mysql -hlocalhost -uroot -e 'SHOW SLAVE STATUS\G' |
awk -F':[[:blank:]]*' '$1~/Relay_Master_Log_File/{split($2, a, /\./); printf "%s.%06d", a[1], a[2]-50}'
)

Based on comments below use:

ssh [email protected] <<-'EOF'
mysql -hlocalhost -uroot -e 'SHOW SLAVE STATUS\G' |
awk -F':[[:blank:]]*' '$1~/Relay_Master_Log_File/{split($2, a, /\./);
     printf "%s.%06d\n", a[1], a[2]-50}'
EOF
相关阅读:
Top