MySQL資料庫全備和增備、增量資料恢複案例以及定時清理 binlog 日誌

標籤：位置   tin   like   action   .gz   自動清理   ast   export   key   

一、mysql 全量備份以及增量備份1、全量備份命令：

• /application/mysql/bin/mysqldump -uroot -p123456 --lock-all-tables -A -B -F --master-data=2 --single-transaction --events|gzip > /opt/MysqlBackup/allbackup/allbackup.sql.gz*

如上一段代碼所示，其功能是將所有資料庫全量備份。其中 MySQL 使用者名稱為：root ，密碼為：123456。備份的檔案路徑為：/opt/Mysql_Backup/all_backup，當然這個路徑是按照個人意願修改的。備份的檔案壓縮包名為 all_backup.sql.gz參數 --lock-all-tables：鎖定所有資料庫;參數 -A：備份所有庫；參數 -B：指定多個庫，增加建庫語句和 use 語句；參數 -F：重新整理 binlog 日誌；參數 --master-data=0|1|2：        0: 不記錄        1：記錄為CHANGE MASTER語句        2：記錄為注釋的CHANGE MASTER語句；參數 --single-transaction：適合 innodb 交易資料庫備份；參數 --events：匯出事件；參數 gzip：備份壓縮檔。

2、全量備份指令碼：

• #!/bin/bash
  . /etc/init.d/functions
  user=root
  password="123456"
  BackupTools=/application/mysql/bin/mysqldump
  BackupDir=/opt/MysqlBackup
  AllBackup=$BackupDir/allbackup
  mkdir -p $AllBackup
  echo ‘==========‘$(date +"%Y-%m-%d %H:%M:%S")‘==========‘ “備份開始” >>$AllBackup/allbackup.log
  $BackupTools -u$user -p$password -A -B -F --master-data=2 --single-transaction --events|gzip >$AllBackup/allbackup$(date +%Y%m%d).sql.gz
  if [ $? -eq 0 ]
  then
  echo ‘==========‘$(date +"%Y-%m-%d %H:%M:%S")‘==========‘ "備份完成" >>$AllBackup/allbackup.log
  action "Mysql full backup is ok" /bin/true
  else
  action "Mysql full backup is not ok" /bin/false
  fi

3、恢複全量備份命令：

cd /opt/MysqlBackup/allbackup
gzip -d allbackup.sql.gz
mysql -uroot -p123456 < allbackup.sql
或者：

*mysql> source /opt/MysqlBackup/allbackup/allbackup.sql * 

4、增量備份

首先在進行增量備份之前需要查看一下設定檔，查看 logbin 是否開啟，因為要做增量備份首先要開啟 logbin 。首先，進入到 myslq 命令列，輸入如下命令：

mysql> show variables like ‘%logbin%‘;
+---------------------------------+-------+
| Variablename | Value |
+---------------------------------+-------+
| logbin | ON |
| logbintrustfunctioncreators | OFF |
| sqllogbin | ON |
+---------------------------------+-------+

如上已經開啟了binlog，如果沒有開啟，執行如下命令：

vim /etc/my.cnf
開啟：
[mysqld]
log-bin=mysql-bin

查看當前使用的 mysql_bin.000 記錄檔：

mysql> show master status;``
+------------------+----------+--------------+------------------+
| File | Position | BinlogDoDB | BinlogIgnoreDB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 533039 | | |
+------------------+----------+--------------+------------------+

當前正在記錄日誌的檔案名稱為 mysql-bin.000019。

增量備份指令碼

#!/bin/bash
export LANG=enUS.UTF-8
BackupDir=/opt/MysqlBackup/binlogbackup/
BinDir=/application/mysql/data/
LogFile=/opt/MysqlBackup/binlog.log
BinFile=/application/mysql/data/mysql-bin.index
mkdir -p $BackupDir
/application/mysql/bin/mysqladmin -uroot -p123456 flush-logs
Counter=wc -l $BinFile|awk ‘{print $1}‘
NextNum=0
for file in cat $BinFile
do
base=basename $file
NextNum=expr $NextNum + 1
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $LogFile
else
dest=$BackupDir/$base
if [ -e $dest ]
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BackupDir
echo $base copying >> $LogFile
fi
fi
done
echo date +"%Y年%m月%d日 %H:%M:%S" Backup succ!>> $LogFile

二、增量資料恢複案例1、情境概述

    a、MySQL資料庫每日零點自動全備    b、某天上午10點，小明莫名其妙地drop了一個資料庫    c、我們需要通過全備的資料檔案，以及增量的binlog檔案進行資料恢複

2、主要思想

    a、利用全備的sql檔案中記錄的CHANGE MASTER語句，binlog檔案及其位置點資訊，找出binlog檔案增量的部分    b、用mysqlbinlog命令將上述的binlog檔案匯出為sql檔案，並剔除其中的drop語句    c、通過全備檔案和增量binlog檔案的匯出sql檔案，就可以恢複到完整的資料

3、過程

4、操作過程

1）、類比資料

*CREATETABLEstudent(

idint(11)NOT NULLAUTOINCREMENT,

namechar(20)NOT NULL,

agetinyint(2)NOT NULLDEFAULT‘0‘,

PRIMARY KEY(id),

KEYindexname(name)

)ENGINE=InnoDBAUTOINCREMENT=8DEFAULTCHARSET=utf8

mysql>insertstudentvalues(1,‘zhangsan‘,20);

mysql>insertstudentvalues(2,‘lisi‘,21);

mysql>insertstudentvalues(3,‘wangwu‘,22);
*

2）、全備命令

/application/mysql/bin/mysqldump -uroot -p123456 --lock-all-tables -A -B -F --master-data=2 --single-transaction --events|gzip > /opt/MysqlBackup/allbackup/allbackup.sql.gz

3）、繼續插入資料

*mysql>insertstudentvalues(6,‘xiaoming‘,20);

mysql>insertstudentvalues(6,‘xiaohong‘,20);

此時誤操作，刪除了test資料庫

mysql>dropdatabasetest;
*
此時，全備之後到誤操作時刻之間，使用者寫入的資料在binlog中，需要恢複出來

4）、查看全備之後新增的binlog檔案

cd /opt/MysqlBackup/allbackup/
ls
allbackup20180831.sql.gz
gzip -d allbackup20180831.sql.gz
grep CHANGE allbackup20180831.sql
-- CHANGE MASTER TO MASTERLOGFILE=‘mysql-bin.000003‘, MASTERLOGPOS=107;

這是全備時刻的binlog檔案位置，即mysql-bin.000003的107行，因此在該檔案之前的binlog檔案中的資料都已經包含在這個全備的sql檔案中了

5）、移動binlog檔案，並讀取sql，剔除其中的drop語句

cp mysql-bin.000003 /tmp/
mysqlbinlog -d test mysql-bin.000003 > bin.log
用vim編輯檔案，剔除drop語句

在恢複全備資料之前必須將該binlog檔案移出，否則恢複過程中，會繼續寫入語句到binlog，最終導致增量恢複資料部分變得比較混亂

6）、恢複資料

mysql -uroot -p < all_backup_20180831.sql <全備恢複>

mysql -uroot -p -e "select * from test.student;"

+----+----------+-----+

|id|name |age|

+----+----------+-----+

| 1|zhangsan| 20|

| 2|lisi | 21|

| 3|wangwu | 22|

+----+----------+-----+

//此時恢複了全備時刻的資料

//然後使用003bin.sql檔案恢複全備時刻到刪除資料庫之間，新增的資料

mysql -uroot -p test< /tmp/bin.sql <增量 binlog 語句恢複>

</span># mysql -uroot -p -e "select * from test.student;"

+----+----------+-----+

|id|name |age|

+----+----------+-----+

| 1|zhangsan| 20|

| 2|lisi | 20|

| 3|wangwu | 20|

| 4|xiaoming| 20|

| 5|xiaohong| 20|

+----+----------+-----+

完成

5、小結

a、適合人為SQL語句造成的誤操作或者沒有主從複製等的熱備情況宕機時的修複b、恢複條件要全備和增量的所有資料c、恢複時建議對外停止更新，即禁止更新資料庫d、先恢複全量，然後把全備時刻點以後的增量日誌，按順序恢複成SQL檔案，然後把檔案中有問題的SQL語句刪除（也可通過時間和位置點），再恢複到資料庫

三、定時清理 binlog 日誌

最近磁碟增長的非常快，發現binlog日誌佔用很大的磁碟資源。我們採用手動清理，後面設定一下自動清理。

查看指定刪除日誌

mysql >show binary logs; 查看多少binlog日誌，佔用多少空間。mysql> PURGE MASTER LOGS TO ‘mysql-bin.002467‘; 刪除mysql-bin.002467以前所有binlog，這樣刪除可以保證*.index資訊與binlog檔案同步。

手動清理

    mysql>PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY); 手動刪除5天前的binlog日誌

自動化佈建清理

mysql> set global expire_logs_days = 5; 把binlog的到期時間設定為5天; mysql> flush logs; 刷一下log使上面的設定生效，否則不生效。

為保證在MYSQL重啟後仍然有效，在my.cnf中也加入此參數設定

expire_logs_days = 5

MySQL資料庫全備和增備、增量資料恢複案例以及定時清理 binlog 日誌