Shulou(Shulou.com)06/01 Report--

MySQL DBA occasionally encounters because there is not enough space, and before you cannot delete data, you may think of temporarily solving the space problem by deleting binlog. I also encountered this situation on the weekend, because I was not next to the computer, find the leader and use my mobile phone to type the command to the boss to delete the binlog. Think about writing a binlog deletion tool. If you encounter such problems in the future, it is appropriate to use the scripting tool directly.

Delbinlog.sh

#! / bin/bash

# # author yangyi@youzan.com

# date 20160327

# delnums is how many are deleted at a time, and deleted in batches.

# rest_num is the number of binlog to be retained, which can be adjusted according to the actual situation.

If [$#-lt 4]; then

Echo "Usage: $0-p PORT-r res_nums"

Exit 1

Fi

While getopts ": PRV r:" arg

Do

Case $arg in

p)

# echo "packs arg:$OPTARG"

PORT=$OPTARG

r)

# echo "dudes arg:$OPTARG"

Res_nums=$OPTARG

?) # what is arg when there is an unrecognized option?

Echo "unkonw argument"

Echo "Usage: $0-p PORT-r res_nums"

Exit 1

Esac

Done

If [- S / srv/my_$PORT/mysqld.sock]; then

SOCKET= "/ srv/my_$PORT/mysqld.sock"

Elif [- S / srv/my$PORT/run/mysql.sock]; then

SOCKET= "/ srv/my$PORT/run/mysql.sock"

Fi

MYSQL= "mysql-uroot-S ${SOCKET}"

Del_nums=3

Logs_num= `$ MYSQL-e "show master logs;" | wc-l `

Echo "there are $logs_num binary logs."

If [[$res_nums-gt $logs_num]]

Then

Exit 0

Fi

While [[$res_nums-lt $logs_num]; do

Del_to_binlog= `$ MYSQL-- skip-column-names-e "show master logs;" | head-n $del_nums | awk 'END {print $1}' `

$MYSQL-e "purge master logs to'$del_to_binlog';"

Echo "purge master logs to $del_to_binlog..."

Logs_num= `$ MYSQL-- skip-column-names-e "show master logs;" | wc-l `

Sleep 2

Done

Logs_num= `$ MYSQL-e "show master logs;" | wc-l `

Echo "there are $logs_num binary logs."

If necessary, you can modify it appropriately according to the actual situation of your own production environment.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.