How to back up mysql binlog logs in real time
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.
