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

This article is to share with you about what binlog backup is like, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Binlog server

Explanation: sometimes backup will choose full + binlog, so how do you back up your binlog?

After mysql 5.6, you can use mysqlbinlog to back up the logs of remote machines to a local directory to achieve incremental or log-safe incremental backups.

Environment:

Mysql-5.7.16

Centos 6.7

172.16.11.187:binlog server (database server that collects binlog)

172.16.11.188: the machine where the database of binlog needs to be backed up

Using mysqlbinlog to transfer remote binlog files to binlogserver

187:mysqlbinlog-R-- raw-- host=172.16.11.188-- user='root'-- password='123456'-P 3306-- stop-never mysql-bin.000005 (the path where the command is executed is / root) a

188:mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 177 | |

| | mysql-bin.000002 | 426 |

| | mysql-bin.000003 | 9688778 | |

| | mysql-bin.000004 | 177 | |

| | mysql-bin.000005 | 201 | |

| | mysql-bin.000006 | 154 | |

+-+ +

6 rows in set (0.00 sec)

Mysql > flush logs

Query OK, 0 rows affected (0.01 sec)

Mysql > flush logs

Query OK, 0 rows affected (0.00 sec)

Mysql > flush logs

Query OK, 0 rows affected (0.00 sec)

Mysql > flush logs

Query OK, 0 rows affected (0.00 sec)

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 177 | |

| | mysql-bin.000002 | 426 |

| | mysql-bin.000003 | 9688778 | |

| | mysql-bin.000004 | 177 | |

| | mysql-bin.000005 | 201 | |

| | mysql-bin.000006 | 201 | |

| | mysql-bin.000007 | 201 | |

| | mysql-bin.000008 | 201 | |

| | mysql-bin.000009 | 201 | |

| | mysql-bin.000010 | 154 | |

+-+ +

10 rows in set (0.00 sec)

Check on binlog server to see if the new binlog added by flush log on 188has come:

[root@ces1 ~] # ls-lrth

Total 9.4M

-rw-r- 1 root root 201 Aug 14 15:25 mysql-bin.000005

-rw-r- 1 root root 201 Aug 14 15:31 mysql-bin.000006

-rw-r- 1 root root 201 Aug 14 15:31 mysql-bin.000007

-rw-r- 1 root root 201 Aug 14 15:31 mysql-bin.000008

-rw-r- 1 root root 123 Aug 14 15:31 mysql-bin.000010

-rw-r- 1 root root 201 Aug 14 15:31 mysql-bin.000009

-rw-r- 1 root root 201 Aug 14 15:33 mysql-bin.000015

-rw-r- 1 root root 201 Aug 14 15:33 mysql-bin.000016

-rw-r- 1 root root 123 Aug 14 15:33 mysql-bin.000018

-rw-r- 1 root root 201 Aug 14 15:33 mysql-bin.000017

Found out binlog was already here.

Expansion: general master-slave replication, if the master-slave connection stops, it will automatically try to connect, and binlogserver will not automatically try to connect if it stops.

Use scripts to solve this problem:

Binlog_remote.sh

#! / bin/sh

BACKUP_BIN=/tmp/mysqlbinlog

LOCAL_BACKUP_DIR=/data/db/binlog_backup

BACKUP_LOG=/data/db/binlog_backup/backuplog

REMOTE_HOST=172.16.11.188

REMOTE_PORT=3306

REMOTE_USER=root

REMOTE_PASS=123456

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 backuplog | 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}

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

The above is what binlog backup is like, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.