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

Today, I will talk to you about how to back up mysql binlog logs in real time, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

Before backing up the binlog server (192.168.1.248)

Grant REPLICATION SLAVE on *. * 'repl'@'192.168.1.250' identified by' repl'

The following operations are all in the backup binlog server operation (192.168.1.250)

Mkdir / backup/binlog

Cat backup_binlog.sh

#! / bin/sh

# specify the location of mysqlbinlog

BACKUP_BIN=/usr/local/mysql/bin/mysqlbinlog

# specify the location where the remote binlog is backed up locally

LOCAL_BACKUP_DIR=/backup/binlog/

# specify the log when backing up binlog

BACKUP_LOG=/backup/binlog/backup.log

# specify the ip of which binlog to back up

REMOTE_HOST=192.168.1.248

REMOTE_PORT=3306

REMOTE_USER=repl

REMOTE_PASS=repl

FIRST_BINLOG=mysql-bin.000001

# time to wait before reconnecting after failure

SLEEP_SECONDS=10

# # create local_backup_dir if necessary

Mkdir-p ${LOCAL_BACKUP_DIR}

Cd ${LOCAL_BACKUP_DIR}

# # run while cycle, wait for a specified time after disconnection, and then reconnect

While:

Do

If [`ls-A "${LOCAL_BACKUP_DIR}" | wc-l`-eq 0]; then

LAST_FILE=$ {FIRST_BINLOG}

Else

LAST_FILE= `ls-l ${LOCAL_BACKUP_DIR} | grep-v backup.log | tail-n 1 | awk'{print $9}'`

Fi

# ${BACKUP_BIN}-- raw-- read-from-remote-server-- stop-never-- host=$ {REMOTE_HOST}-- port=$ {REMOTE_PORT}-user=$ {REMOTE_USER}-- password=$ {REMOTE_PASS} ${LAST_FILE}

/ usr/local/mysql/bin/mysqlbinlog-- raw-- read-from-remote-server-- stop-never-- host=$ {REMOTE_HOST}-- port=$ {REMOTE_PORT}-user=$ {REMOTE_USER}-- password=$ {REMOTE_PASS} ${LAST_FILE}

Echo "`date +"% Y/%m/%d% H:%M:%S "`mysqlbinlog stops. Return code: $?" | tee-a ${BACKUP_LOG}

Echo "${SLEEP_SECONDS} seconds later connect again and continue backup" | tee-a ${BACKUP_LOG}

Sleep ${SLEEP_SECONDS}

Done

Run the backup script in the background

Nohup sh backup_binlog.sh &

After reading the above, do you have any further understanding of how to back up mysql binlog logs in real time? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.