Ⅰ露乏、bonlog server介紹
對于binlog的備份车海，之前文章里說的是有從機(jī)笛园，一般不備份，那現(xiàn)在人家就是 要備份嘛侍芝，怎么辦嘛研铆，
寫個腳本每天夜里去把前一天產(chǎn)生的binlog拷貝出來可以不？
行啊州叠，沒問題棵红，你可以的，但你這個一下子整會不會有點(diǎn)累嘛
從5.6版本開始咧栗，我們有更好的辦法了逆甜，用mysqlbinlog可以將遠(yuǎn)端的binlog時時地拉取到本地來
這可不是簡單的拷貝哦虱肄，是通過Replication API去時時拉取產(chǎn)生的event，相當(dāng)于做了一個主從交煞。
Ⅱ咏窿、給我弄，操作起來
MySQL server
準(zhǔn)備工作：創(chuàng)建rpl賬號素征，授予replication權(quán)限
看下當(dāng)前二進(jìn)制文件是which one集嵌？
(root@localhost) [(none)]> show master status;
+------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+---------------------------------------------+
| bin.000022 | 194 | | | d565cde8-0573-11e8-89b2-525400a4dac1:1-1747 |
+------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)
binlog server
[root@VM_42_63_centos backup]# pwd
/data/backup
在備份目錄下弄
[root@VM_42_63_centos backup]# mysqlbinlog --read-from-remote-server --raw --host=123.207.244.133 --port=3306 --user=rpl --password=123 --stop-never bin.000022 &
[1] 17292
[root@VM_42_63_centos backup]# mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
看下binlog拉過來了沒？no problem!!!
[root@VM_42_63_centos backup]# ll
total 4
-rw-r----- 1 root root 194 Mar 21 16:38 bin.000022
MySQL server 刷一刷
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]> flush binary logs;
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [(none)]> show master status;
+------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+---------------------------------------------+
| bin.000026 | 194 | | | d565cde8-0573-11e8-89b2-525400a4dac1:1-1747 |
+------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)
binlog server
[root@VM_42_63_centos backup]# ll
total 20
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000022
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000023
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000024
-rw-r----- 1 root root 235 Mar 21 16:38 bin.000025
-rw-r----- 1 root root 194 Mar 21 16:38 bin.000026
okay ! 沒毛病稚茅，都同步過來了纸淮、
Ⅲ、重要參數(shù)參數(shù)
--read-from-remote-server：從遠(yuǎn)端拉binlog亚享，不加會在本地找
--raw：本地以二進(jìn)制方式保存binlog咽块，不指定則以文本保存
--stop-never：一直同步不斷
bin.000022：從這個binlog文件開始拉
- 指定為raw，數(shù)據(jù)不會時時落盤欺税，而是先搞到內(nèi)存里侈沪，然后每4k刷盤一次，一旦連接斷開晚凿，內(nèi)存中數(shù)據(jù)馬上都刷到磁盤上
- 不指定raw亭罪，則需要用--result-file參數(shù)指定數(shù)據(jù)寫入某個文本，不可指定為目錄歼秽，此時數(shù)據(jù)會時時刷盤
Ⅳ应役、問題與解決
如果mysqlbinlog斷了怎么辦，并不會像主從一樣去嘗試重連
這里我抄襲一個陳老師腳本(陳老師博客請百度搜索ivictor)
思路：將mysqlbinlog寫在一個死循環(huán)里燥筷，斷了就發(fā)起重新執(zhí)行
#!/bin/sh
BACKUP_BIN=/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/backup/binlog/
BACKUP_LOG=/backup/binlog/backuplog
REMOTE_HOST=192.168.244.145
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}
## 運(yùn)行while循環(huán)箩祥，連接斷開后等待指定時間，重新連接
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停止肆氓，返回代碼：$?" | tee -a ${BACKUP_LOG}
echo "${SLEEP_SECONDS}秒后再次連接并繼續(xù)備份" | tee -a ${BACKUP_LOG}
sleep ${SLEEP_SECONDS}
done
