閱讀本文大約需要 9 分鐘
概要
-
引言
-
增量備份
-
恢復增量備份
-
定時備份
引言
在產品上線之後，我們的資料是相當重要的，容不得半點閃失，應該做好萬全的準備，搞不好哪一天被黑客入侵或者惡意刪除，那就 gg 了。所以要對我們的線上資料庫定時做全量備份與增量備份。例如：每天做一次增量備份，每週做一次全量備份。
GitHub 地址：點選閱讀原文進入
https://github.com/zonezoen/MySQL_backup
複製程式碼增量備份
首先在進行增量備份之前需要檢視一下配置檔案，檢視 log_bin 是否開啟，因為要做增量備份首先要開啟 log_bin 。首先，進入到 myslq 命令列，輸入如下命令：
show variables like '%log_bin%';
複製程式碼如下命令所示，則為未開啟
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
複製程式碼修改 MySQL 配置項到如下程式碼段：vim /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#binlog setting，開啟增量備份的關鍵
log-bin=/var/lib/mysql/mysql-bin
server-id=123454
複製程式碼修改之後，重啟 mysql 服務，輸入：
show variables like '%log_bin%';
複製程式碼狀態如下：
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
複製程式碼好了，做好了充足的準備，那我們就開始學習增量備份了。
檢視當前使用的 mysql_bin.000*** 日誌檔案，
show master status;
複製程式碼狀態如下：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 | 610 | | | |
+------------------+----------+--------------+------------------+-------------------+
複製程式碼當前正在記錄日誌的檔名為 mysql-bin.000015 。
當前資料庫中有如下資料：
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
+-------+------+----+
複製程式碼我們插入一條資料：
insert into `zone`.`users` ( `name`, `sex`, `id`) values ( 'zone3', '0', '4');
複製程式碼檢視效果：
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
| zone3 | 0 | 4 |
+-------+------+----+
複製程式碼我們執行如下命令，使用新的日誌檔案：
mysqladmin -uroot -123456 flush-logs
複製程式碼日誌檔案從 mysql-bin.000015 變為 mysql-bin.000016，而 mysql-bin.000015 則記錄著剛剛 insert 命令的日誌。上句程式碼的效果如下：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
複製程式碼那麼到現在為止，其實已經完成了增量備份了。
恢復增量備份
那麼現在將剛剛插入的資料刪除，效果如下：
delete from `zone`.`users` where `id`='4'
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
+-------+------+----+
複製程式碼那麼現在就是重點時間了，從 mysql-bin.000015 中恢復資料：
mysqlbinlog /var/lib/mysql/mysql-bin.000015 | mysql -uroot -p123456 zone;
複製程式碼上一句程式碼指定了，需要恢復的 mysql_bin 檔案，指定了使用者名稱：root 、密碼：123456 、資料庫名：zone。效果如下：
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
| zone3 | 0 | 4 |
+-------+------+----+
複製程式碼OK，整一個增量備份的操作流程都在這裡了，那麼我們如何將它寫成指令碼檔案呢，程式碼如下：
#!/bin/bash
#在使用之前，請提前建立以下各個目錄
backupDir=/usr/local/work/backup/daily
#增量備份時複製mysql-bin.00000*的目標目錄，提前手動建立這個目錄
mysqlDir=/var/lib/mysql
#mysql的資料目錄
logFile=/usr/local/work/backup/bak.log
BinFile=/var/lib/mysql/mysql-bin.index
#mysql的index檔案路徑，放在資料目錄下的
mysqladmin -uroot -p123456 flush-logs
#這個是用於產生新的mysql-bin.00000*檔案
# wc -l 統計行數
# awk 簡單來說awk就是把檔案逐行的讀入，以空格為預設分隔符將每行切片，切開的部分再進行各種分析處理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#這個for迴圈用於比對$Counter,$NextNum這兩個值來確定檔案是不是存在或最新的
for file in `cat $BinFile`
do
base=`basename $file`
echo $base
#basename用於擷取mysql-bin.00000*檔名，去掉./mysql-bin.000005前面的./
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $logFile
else
dest=$backupDir/$base
if(test -e $dest)
#test -e用於檢測目標檔案是否存在，存在就寫exist!到$logFile去
then
echo $base exist! >> $logFile
else
cp $mysqlDir/$base $backupDir
echo $base copying >> $logFile
fi
fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile
#NODE_ENV=$backUpFolder@$backUpFileName /root/node/v8.11.3/bin/node /usr/local/upload.js
複製程式碼定時備份
輸入如下命令，進入定時任務編輯介面：
crontab -e
複製程式碼新增如下命令，其意思為：每分鐘執行一次備份指令碼，crontab 的具體規則就另外寫文了，與本文主題不太相關。
* * * * * sh /usr/your/path/mysqlbackup.sh
複製程式碼關於 crontab 的介紹，在上一篇推文中就有了，詳情請看上一篇推文
關注微信公眾號，回覆【mysql資源】，獲取 mysql 效能優化高階視訊教程
