天天看點
傳回

【幹貨】MySQL 資料庫定時備份總結

作者：幻好

前言

在操作資料過程中，可能會導緻資料錯誤，甚至資料庫奔潰，而有效的定時備份能很好地保護資料庫。本篇文章主要講述了幾種方法進行 MySQL 定時備份資料庫。

【幹貨】MySQL 資料庫定時備份總結

資料庫備份的分類

資料庫備份方式分很多種，從影響資料庫的角度劃分：

  • 熱備份: 讀寫不受影響
  • 溫備份: 僅可以執行讀操作
  • 冷備份: 離線備份, 讀寫操作均中止

從備份方式劃分：

  • 實體備份：值對資料庫作業系統的實體檔案（如資料檔案、日志檔案等）的備份。
  • 邏輯備份：指對資料庫邏輯元件 （如表等資料庫對象）的備份。

從備份政策劃分：

  • 完全備份：每次對資料庫進行完整備份。可以備份整個資料庫，包含使用者表、系統表、索引、視圖和存儲過程中所有資料庫對象。但它需要花費更多的時間和空間，是以，做一次完全備份的周期要長些。
  • 差異備份：備份那些自從上次完全備份之後被修改過的檔案，值備份資料庫的部分内容，比完全備份小，是以存儲和恢複速度快。
  • 增量備份：隻有那些在上次完全備份或者增量備份後修改的檔案才會被備份。
【幹貨】MySQL 資料庫定時備份總結

MySQL資料備份

mysqldump指令備份資料

在MySQL中提供了指令行導出資料庫資料以及檔案的一種友善的工具mysqldump ,我們可以通過指令行直接實作資料庫内容的導出dump,首先我們簡單了解一下mysqldump指令用法:

#MySQLdump常用
mysqldump -u root -p --databases 資料庫1 資料庫2 > xxx.sql

mysqldump常用操作示例

備份全部資料庫的資料和結構

mysqldump -uroot -proot -A > /data/mysqlDump/mydb.sql
  • 備份全部資料庫的結構（加 -d 參數） 
mysqldump -uroot -proot -A -d > /data/mysqlDump/mydb.sql
  • 備份全部資料庫的資料(加 -t 參數) 
mysqldump -uroot -proot -A -t > /data/mysqlDump/mydb.sql

備份單個資料庫的資料和結構(,資料庫名mydb)

mysqldump -uroot-proot mydb > /data/mysqlDump/mydb.sql
  • 備份單個資料庫的結構 
mysqldump -uroot -proot mydb -d > /data/mysqlDump/mydb.sql
  • 備份單個資料庫的資料 
mysqldump -uroot -proot mydb -t > /data/mysqlDump/mydb.sql
  • 備份多個表的資料和結構（資料，結構的單獨備份方法與上同） 
mysqldump -uroot -proot mydb t1 t2 > /data/mysqlDump/mydb.sql
  • 一次備份多個資料庫 
mysqldump -uroot -proot --databases db1 db2 > /data/mysqlDump/mydb.sql

MySQL 還原備份資料

有兩種方式還原，第一種是在 MySQL 指令行中，第二種是使用 SHELL 行完成還原

  • 在系統指令行中，輸入如下實作還原： 
mysql -uroot -proot < /data/mysqlDump/mydb.sql
  • 在登入進入mysql系統中,通過source指令找到對應系統中的檔案進行還原： 
mysql> source /data/mysqlDump/mydb.sql

編寫腳本維護備份的資料庫檔案

在 Linux中，通常使用BASH 腳本對需要執行的内容進行編寫，加上定時執行指令crontab 實作日志自動化生成。

以下代碼功能就是針對mysql進行備份，配合crontab，實作備份的内容為近一個月（31天）内的每天的mysql資料庫記錄。

編寫BASH維護固定數量備份檔案

在Linux中，使用vi或者vim編寫腳本内容并命名為：mysql_dump_script.sh

#!/bin/bash

#儲存備份個數，備份31天資料
number=31
#備份儲存路徑
backup_dir=/root/mysqlbackup
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#備份工具
tool=mysqldump
#使用者名
username=root
#mima
password=TankB214
#将要備份的資料庫
database_name=edoctor

#如果檔案夾不存在則建立
if [ ! -d $backup_dir ];
then   
    mkdir -p $backup_dir;
fi

#簡單寫法 mysqldump -u root -proot users > /root/mysqlbackup/users-$filename.sql
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#寫建立備份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的備份
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判斷現在的備份數量是否大于$number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #删除最早生成的備份，隻保留number數量的備份
  rm $delfile
  #寫删除檔案日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi

如上代碼主要含義如下：

1.首先設定各項參數，例如number最多需要備份的數目，備份路徑，使用者名等。

2.執行mysqldump指令儲存備份檔案，并将操作列印至同目錄下的log.txt中标記記錄檔。

3.定義需要删除的檔案：通過ls指令擷取第九列，即檔案名列，再通過實作定義操作時間最晚的那個需要删除的檔案。

4.定義備份數量：通過ls指令加上

統計以sql結尾的檔案的行數。

5.如果檔案超出限制大小，就删除最早建立的sql檔案

使用crontab定期執行備份腳本

在 Linux 中，周期執行的任務一般由cron這個守護程序來處理[ps -ef|grep cron]。cron讀取一個或多個配置檔案，這些配置檔案中包含了指令行及其調用時間。cron的配置檔案稱為“crontab”，是“cron table”的簡寫。

cron服務

cron是一個 Liunx 下 的定時執行工具，可以在無需人工幹預的情況下運作作業。

service crond start //啟動服務 service crond stop //關閉服務 service crond restart //重新開機服務 service crond reload //重新載入配置 service crond status //檢視服務狀态

crontab文法

crontab指令用于安裝、删除或者列出用于驅動cron背景程序的表格。使用者把需要執行的指令序列放到crontab檔案中以獲得執行。每個使用者都可以有自己的crontab檔案。/var/spool/cron下的crontab檔案不可以直接建立或者直接修改。該crontab檔案是通過crontab指令建立的。

在crontab檔案中如何輸入需要執行的指令和時間。該檔案中每行都包括六個域，其中前五個域是指定指令被執行的時間，最後一個域是要被執行的指令。每個域之間使用空格或者制表符分隔。

格式如下：minute hour day-of-month month-of-year day-of-week commands 合法值 00-59 00-23 01-31 01-12 0-6 (0 is sunday)

除了數字還有幾個個特殊的符号就是"_"、"/"和"-"、","，_代表所有的取值範圍内的數字，"/"代表每的意思,"/5"表示每5個機關，"-"代表從某個數字到某個數字,","分開幾個離散的數字。

-l 在标準輸出上顯示目前的crontab。-r 删除目前的crontab檔案。-e 使用VISUAL或者EDITOR環境變量所指的編輯器編輯目前的crontab檔案。當結束編輯離開時，編輯後的檔案将自動安裝。

建立cron腳本

第一步：寫cron腳本檔案,命名為mysqlRollBack.cron。15,30,45,59 * * * * echo "xgmtest....." >> xgmtest.txt 表示，每隔15分鐘，執行列印一次指令 第二步：添加定時任務。執行指令 “crontab crontest.cron”。搞定 第三步："crontab -l" 檢視定時任務是否成功或者檢測/var/spool/cron下是否生成對應cron腳本

注意：這操作是直接替換該使用者下的crontab，而不是新增

定期執行編寫的定時任務腳本（記得先給shell腳本執行權限）

0 2 * * * /root/mysql_backup_script.sh

随後使用crontab指令定期指令編寫的定時腳本

crontab mysqlRollback.cron

再通過指令檢查定時任務是否已建立。

crontab 的使用示例：

  • 每天早上6點 
0 6 * * * echo "Good morning." >> /tmp/test.txt //注意單純echo，從螢幕上看不到任何輸出，因為cron把任何輸出都email到root的信箱了。
  • 每兩個小時 
0 */2 * * * echo "Have a break now." >> /tmp/test.txt
  • 晚上11點到早上8點之間每兩個小時和早上八點 
0 23-7/2，8 * * * echo "Have a good dream" >> /tmp/test.txt
  • 每個月的4号和每個禮拜的禮拜一到禮拜三的早上11點 
0 11 4 * 1-3 command line
  • 1 月 1 日早上 4 點 
0 4 1 1 * command line SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root //如果出現錯誤，或者有資料輸出，資料作為郵件發給這個帳号 HOME=/
  • 每小時執行/etc/cron.hourly内的腳本 
01 * * * * root run-parts /etc/cron.hourly
  • 每天執行/etc/cron.daily内的腳本 
02 4 * * * root run-parts /etc/cron.daily
  • 每星期執行/etc/cron.weekly内的腳本 
22 4 * * 0 root run-parts /etc/cron.weekly
  • 每月去執行/etc/cron.monthly内的腳本 
42 4 1 * * root run-parts /etc/cron.monthly
  • 每天的下午4點、5點、6點的5 min、15 min、25 min、35 min、45 min、55 min時執行指令。 
5，15，25，35，45，55 16，17，18 * * * command
  • 每周一，三，五的下午3：00系統進入維護狀态，重新啟動系統。 
00 15 * * 1，3，5 shutdown -r +5
  • 每小時的10分，40分執行使用者目錄下的innd/bbslin這個指令： 
10，40 * * * * innd/bbslink
  • 每小時的1分執行使用者目錄下的bin/account這個指令：
mysql 資料庫 sql
上一篇: 一起看看MySQL中的隐藏列
下一篇: 全網最詳細之pt-osc 處理MySQL外鍵表流程分析

繼續閱讀