Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to manage binary logs in MYSQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

MYSQL binary log management script

The principle of the script is to generate new binary logs by flush every hour, back up the binary logs to NFS, and compress them:

#! / bin/bash

# Purpose: manage binary logs, refresh binary logs every hour, and copy the logs to the nfs server to facilitate future recovery and problem analysis!

# Author:carl_zhang

# Date:

Www.2cto.com

# some variables some parameters

# define information about the database

USERNAME=root

PASSWORD=zhang@123

HOSTIP=localhost

MYSQL=/usr/local/mysql/bin/mysql

# set the path to the binary log

BIN_LOG_DIR=/data/dbdata

# set the path of binary log backup

BIN_LOG_BACKUP_DIR=/data/binlog_backup

# define a constant to skip the last log file

COUNT=0

# define a log file

LOGFILE=/data/binlog_backup/binlog_backup.log

# find the name prefix of the binary log

BINLOG_PREFIX= `grep "log-bin=" / etc/my.cnf | awk-Fairchild'{print $2}'`

# compare the list of binary log files with the list of backed-up binaries

If [!-f "$BIN_LOG_BACKUP_DIR/backup.file"]; then

Touch $BIN_LOG_BACKUP_DIR/backup.file

Fi

# execute flush logs before running file comparison

$MYSQL-u$USERNAME-p$PASSWORD-h$HOSTIP-e "flush logs"

FILE_LIST= `comm-23 $BIN_LOG_DIR/$BINLOG_PREFIX.index $BIN_LOG_BACKUP_DIR/ backup.file`

# count the number of log files

FILE_COUNT= `comm-23 $BIN_LOG_DIR/$BINLOG_PREFIX.index $BIN_LOG_BACKUP_DIR/backup.file | wc-l`

# Program text

# start backing up binary log files

For file in $FILE_LIST

Do

BINLOG_NAME= `basename $file`

Let COUNT=$COUNT+1

If [$COUNT-eq $FILE_COUNT]; then

# skip the latest binary log file

Echo "skip the lastest binlog file" > > $LOGFILE

Else

Cp $BIN_LOG_DIR/$BINLOG_NAME $BIN_LOG_BACKUP_DIR/

If [$?-eq 0]; then

Echo "`date-d" today "+% YMY% mMMI% dMUE% HMUE% MMUE% S` $BINLOG_NAME backup success" > > $LOGFILE

Else

Echo "`date-d" today "+% YMY% MMY% dMMI% HMUE% MMMUE% S` $BINLOG_NAME backup faild, Please check it out" > > $LOGFILE www.2cto.com

Exit 5

Fi

# compress and store binary logs

Gzip $BIN_LOG_BACKUP_DIR/$BINLOG_NAME

If [$?-eq 0]; then

Echo "`date-d" today "+% YMY% mMMI% dMUE% HMUE% MMUE% S` $BINLOG_NAME gzip success" > > $LOGFILE

Else

Echo "`date-d" today "+% YMY% MMY% dMMI% HMUE% MMMUE% S` $BINLOG_NAME gzip faild, Please check it out" > > $LOGFILE

Exit 5

Fi

Echo. / $BINLOG_NAME > > $BIN_LOG_BACKUP_DIR/backup.file

Fi

Done

This is how to manage binary logs in the MYSQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.