MySQL數(shù)據(jù)庫備份

前段時間公司給我發(fā)了一個需求耐亏,讓我寫一個數(shù)據(jù)庫定時備份的腳本转晰,我剛來到這家公司不久,給我發(fā)這個任務(wù)應(yīng)該是摸摸我的底桐磁,看看我shell腳本寫的怎么樣悔耘,當(dāng)時也沒多想讓我寫我就寫唄??????

腳本需求:
把數(shù)據(jù)庫中的數(shù)據(jù)備份出來,每周進行一次全量備份我擂,每日進行一次增量備份衬以,全量備份的數(shù)據(jù)保留一個月,增量備份的數(shù)據(jù)保留一周

挺常見的一個需求校摩,網(wǎng)上應(yīng)該一搜一大把看峻,本這有現(xiàn)成干嘛要自己寫的運維核心指導(dǎo)思路(其實就是想偷懶、摸魚衙吩、劃水)互妓,我打開宏顏獲水家的搜索引擎度娘,結(jié)果老辣的度娘來了一句:走給年輕的運維上一課??????坤塞,我就讓度娘給結(jié)結(jié)實實的教訓(xùn)了一頓车猬,數(shù)據(jù)庫備份的概念一堆,腳本就那幾個還用不上尺锚,完全無語。

先科普幾個概念(寫文檔那有不科普概念湊字?jǐn)?shù)的你說是不是這個理兒??)惜浅,這幾個概念了解個大概就行瘫辩。

說點大實話:
想看腳本直接到最后省時省心也不用看我瞎逼逼。
概念也不是我總結(jié)的是我復(fù)制我老師的坛悉,畢竟有運維核心指導(dǎo)思想嘛有現(xiàn)成干嘛要自己寫??伐厌。
ps:
度娘:“瞎說什么大實話,我度娘是最好的搜索引擎裸影,就像我家的云盤下載速度一樣優(yōu)秀挣轨。”
我:“????????”


1. 數(shù)據(jù)庫備份的重要性

在生產(chǎn)環(huán)境中轩猩,數(shù)據(jù)庫的安全性是至關(guān)重要的卷扮,任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴(yán)重的后果荡澎。數(shù)據(jù)庫的備份的重要性主要體現(xiàn)在:

  • 提高系統(tǒng)的高可用性和災(zāi)難可恢復(fù)性,在數(shù)據(jù)庫系統(tǒng)崩潰時晤锹,沒有數(shù)據(jù)庫備份就沒法找到數(shù)據(jù)摩幔。

  • 使用數(shù)據(jù)庫備份還原數(shù)據(jù)庫時、數(shù)據(jù)庫崩潰時提供數(shù)據(jù)恢復(fù)最小代價的最優(yōu)方案鞭铆,如果讓用戶重新添加數(shù)據(jù)或衡,代價太大。

  • 沒有數(shù)據(jù)庫就沒有一切车遂,數(shù)據(jù)庫的備份是一種災(zāi)害的強力手段封断。
    使用數(shù)據(jù)庫的過程中,有多種原因造成數(shù)據(jù)丟失:程序錯誤舶担、認為錯誤坡疼、計算機失誤、磁盤失敗柄沮、災(zāi)難(如火災(zāi)回梧、地震)和盜竊。

2. 數(shù)據(jù)庫備份的分類

數(shù)據(jù)庫備份方式分很多種

從影響數(shù)據(jù)庫的角度劃分:

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

從備份方式劃分:

  • 物理備份:值對數(shù)據(jù)庫操作系統(tǒng)的物理文件(如數(shù)據(jù)文件祖搓、日志文件等)的備份狱意。
  • 邏輯備份:指對數(shù)據(jù)庫邏輯組件 (如表等數(shù)據(jù)庫對象)的備份。

從備份策略劃分:

  • 完全備份:每次對數(shù)據(jù)庫進行完整備份拯欧∠甓冢可以備份整個數(shù)據(jù)庫,包含用戶表镐作、系統(tǒng)表藏姐、索引、視圖和存儲過程中所有數(shù)據(jù)庫對象该贾。但它需要花費更多的時間和空間羔杨,所以,做一次完全備份的周期要長些杨蛋。

  • 差異備份:備份那些自從上次完全備份之后被修改過的文件兜材,值備份數(shù)據(jù)庫的部分內(nèi)容,比完全備份小逞力,因此存儲和恢復(fù)速度快曙寡。

  • 增量備份:只有那些在上次完全備份或者增量備份后修改的文件才會被備份。

3.完全備份寇荧、增量備份举庶、差異備份區(qū)別

各備份差別

4. 邏輯備份的優(yōu)缺點

  • 優(yōu)點:

    • 邏輯備份的結(jié)果,我們可以使用文本處理工具來處理
    • 對于MyIASM 引擎和 InnoDB 引擎各自的速度有差異
    • 邏輯備份有很強的兼容性,而物理備份對版本要求比較高
  • 缺點:

    • 邏輯備份會丟失浮點數(shù)的精度
    • 邏輯備份生成的文件比源文件內(nèi)容更大
    • 邏輯備份會被數(shù)據(jù)庫產(chǎn)生壓力,物理備份不會

概念說差不多了上正題吧


邏輯備份mysqldump

執(zhí)行邏輯備份時必須打開MySQL的二進制日志
MySQL配置文件路徑

/etc/my.cnf

修改MySQL配置文件

vim my.cnf 
log-bin = /usr/local/mysql/data/mybinlog
# log-bin = 這只是一個二進制文件的存儲路徑
:wq 
service mysql restart

完整my.cnf配置文件(這只是我原來測試的文件,只是讓大家看個大概樣子和修改內(nèi)容揩抡,讀者的mysql配置文件可能和我不同)

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
log-bin=mysql-bin
#這行用于開啟mysql二進制文件
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
  • MySQL程序內(nèi)
表級別備份 
mysqldump [OPTIONS] database [tables] 
庫級別備份 
mysqldump [OPTIONS] --databases DB1 [DB2 DB3...] 
全庫級別備份 
mysqldump [OPTIONS] --all-databases [OPTIONS]
  • MySQL程序內(nèi)
表級別備份 
mysqldump -u用戶名 -p密碼 指定哪個數(shù)據(jù)庫 指定數(shù)據(jù)中的哪個表 > 備份文件存儲路徑
例:mysqldump -uroot -p123 db_itcast it_student > /tmp/mysqlbak/it_student.sql
庫級別備份 
mysqldump -u用戶名 -p密碼 --databases 數(shù)據(jù)庫名稱1 [數(shù)據(jù)庫名稱2] > 備份文件存儲路徑
例:mysqldump -uroot -p123 --databases db_itcast > /tmp/mysqlbak/db_itcast.sql
全庫級別備份
mysqldump  -u用戶名 -p密碼 --all-databases [options] > 備份文件存儲路徑
例:mysqldump -uroot -p123 --all-databases > /tmp/mysqlbak/all.sql
  • 常用參數(shù)
--flush-logs, -F   開始備份前刷新日志 
--flush-privileges 備份包含mysql數(shù)據(jù)庫時刷新授權(quán)表 
--lock-all-tables, -x MyISAM一致性户侥,服務(wù)可用性(針對所有庫所有表) 
--lock-tables, -l 備份前鎖表(針對要備份的庫) 
--single-transaction 適用InnoDB引擎镀琉,保證一致性,服務(wù)可用性 
--master-data=2 表示將二進制日志位置和文件名寫入到備份文件并在dump文件中注釋掉這一行添祸; 
--master-data=1 表示將二進制日志位置和文件名寫入到備份文件,在dump文件中不注釋這一行滚粟; --master-data參數(shù)其他說明: 
1)恢復(fù)時會執(zhí)行,默認是1 
2)需要RELOAD privilege并必須打開二進制文件 
3)這個選項會自動打開--lock-all-tables刃泌,關(guān)閉--lock-tables 
--single-transaction :保持?jǐn)?shù)據(jù)的一致性與完整性 
--master-data :開啟使用二進制文件凡壤,默認關(guān)閉的 

腳本需求分析

需求:

  1. 每周進行一次全量備份
  2. 每日進行一次增量備份
  3. 全量備份的數(shù)據(jù)保留一個月
  4. 增量備份的數(shù)據(jù)保留一周
  5. 備份文件格式為:.sql
  6. 文件命名格式為:lk_20190711_092557.sql
  • 通過對整體需求的分析確定要使用邏輯備份mysqldump
  • 第1第2兩條需求提出了定時備份的概念,那就需要設(shè)計計劃任務(wù)來滿足定時的需求
  • 第1第2兩條需求提出了兩個時間節(jié)點分別是周和天耙替,需要滿足這兩個時間節(jié)點
  • 第3第4兩條需求提出了全量和增量的保存時間不同亚侠,需要定時刪除
  • 第5第6兩條需求對文件命名格式提出了要求需要通過date命令添加時間戳

腳本思路

  1. 第一個難點是判斷現(xiàn)在的時間是星期幾同時確定進行備份的方式
    執(zhí)行date命令后發(fā)現(xiàn)第一個字段代表星期,這個問題就很好解決了俗扇。
[root@vlnx12801 etc]# date
Sun Aug 11 23:33:36 CST 2019

通過awk截取第一個字段就可以獲取到星期的參數(shù)

[root@vlnx12801 etc]# date | awk '{print $1}'
Sun

進一步拓展把這一部分變成腳本硝烂,就可以判斷星期進而確定備份方式了

#!/bin/sh
if [ "Sun" == date | awk '{print $1}' ]
    then 
       echo '今天是$WEEK'
else
       echo '今天是$WEEK'
fi

這樣第一個難題判斷時間就解決了

  1. 通過前面對邏輯備份mysqldump命令的解釋基本已經(jīng)了解mysql的備份過程了結(jié)合前面的星期判斷就可以基本滿足需求一二了,轉(zhuǎn)變?yōu)槟_本
#!/bin/sh
if [ "Sun" == date | awk '{print $1}' ]
    then 
       mysqldump -uroot -p123456 --all-databases > /home/mysql/backup/mysql_buckup.spl
else
       mysqldump --single-transaction --flush-logs --master-data=2 -uroot -p123456 > /home/mysql/backup/mysql_buckup1.spl
fi
  1. 解決規(guī)范化命名的問題铜幽,需要用到date命令給文件添加時間戳滞谢,同時進行排序滿足命名格式要求
#!/bin/sh
if [ "Sun" == date | awk '{print $1}' ]
    then 
       mysqldump -uroot -p123456 --all-databases > /home/mysql/backup/ql/`date +lk_%Y%m%d_%H%M%S`.spl
else
       mysqldump --single-transaction --flush-logs --master-data=2 -uroot -p123456 > /home/mysql/backup/zl/`date +lk_%Y%m%d_%H%M%S`.spl
fi
  1. 定時刪除文件
find /home/mysql/backup/ql/ -mtime +30 -name "*.sql" -exec rm -Rf {} \;
find /home/mysql/backup/zl/ -mtime +7 -name "*.sql" -exec rm -Rf {} \;
轉(zhuǎn)換為腳本后基本滿足需求,但是非常簡陋
#!/bin/sh
if [ "Sun" == date | awk '{print $1}' ]
    then 
       mysqldump -uroot -p123456 --all-databases > /home/mysql/backup/ql/`date +lk_%Y%m%d_%H%M%S`.spl
else
       mysqldump --single-transaction --flush-logs --master-data=2 -uroot -p123456 > /home/mysql/backup/zl/`date +lk_%Y%m%d_%H%M%S`.spl
fi
find /home/mysql/backup/ql/ -mtime +30 -name "*.sql" -exec rm -Rf {} \;
find /home/mysql/backup/zl/ -mtime +7 -name "*.sql" -exec rm -Rf {} \;
  1. 進一步對腳本進行優(yōu)化提升腳本的可閱讀性將腳本中大量重復(fù)出現(xiàn)的參數(shù)進行提取定義為全局變量
  • 可以提取的內(nèi)容:
  • 數(shù)據(jù)庫的用戶名密碼
  • 命名規(guī)范的時間戳
  • 星期判斷
  • 存儲路徑
提取整理后的變量
#全局變量
DB_USER="root"
DB_PASSWORD="123456"
#數(shù)據(jù)庫用戶名密碼

DB_BACKUPDIR=/home/mysql/backup
#數(shù)據(jù)庫備份存儲主路徑
zl=$DB_BACKUPDIR/zl
#數(shù)據(jù)庫增量備份存儲路徑除抛,存儲路徑為DB_DACKUPDIR變量定義的路徑下
ql=$DB_BACKUPDIR/ql
#數(shù)據(jù)庫全量備份存儲路徑狮杨,存儲路徑為DB_DACKUPDIR變量定義的路徑下

DATE=`date +lk_%Y%m%d_%H%M%S`
#命名時間戳
WEEK=`date | awk '{print $1}'`
#星期變量

完整的成品就直接看最后吧,我都說到這份上了應(yīng)該沒啥問題也應(yīng)該看明白了到忽,望理解橄教,借老羅一句話李姐萬歲??

  1. 添加計劃任務(wù)
0 3 * * * /root/mysql_backup,sh

腳本

#!/bin/sh
#########################################################
#此腳本用來完成數(shù)據(jù)庫備份                               
#備份規(guī)則:                                              
#   每天進行一次增量備份  備份數(shù)據(jù)時間為 凌晨3:00  
#   每周進行一次全量備份  備份數(shù)據(jù)時間為 凌晨3:00  
#   增量備份保留時間為:7天                                
#   全量備份保留時間為:30天                           
#備份文件格式為:.sql                                   
#文件命名格式為:lk_20190711_092557.sql                 
#請手動添加計劃任務(wù):0 3 * * * /root/mysql_backup,sh  
#########################################################
#全局變量
DB_USER="root"
DB_PASSWORD="123456"
#數(shù)據(jù)庫用戶名密碼

DB_BACKUPDIR=/home/mysql/backup
#數(shù)據(jù)庫備份存儲主路徑
zl=$DB_BACKUPDIR/zl
#數(shù)據(jù)庫增量備份存儲路徑,存儲路徑為DB_DACKUPDIR變量定義的路徑下
ql=$DB_BACKUPDIR/ql
#數(shù)據(jù)庫全量備份存儲路徑喘漏,存儲路徑為DB_DACKUPDIR變量定義的路徑下

DATE=`date +lk_%Y%m%d_%H%M%S`
#命名時間戳
WEEK=`date | awk '{print $1}'`
#星期變量


#創(chuàng)建目錄
mkdir -p $DB_BACKUPDIR $ql $zl

#備份代碼塊
if [ "Sun" == $WEEK ]
    then 
        mysqldump -u$DB_USER -p$DB_PASSWORD --all-databases >$ql/$DATE.sql
        echo "今天是$WEEK护蝶,執(zhí)行全量備份"
        echo "存儲路徑為$ql"
        FILE_PATH=$ql/$DATE.sql
        if [ -f "$FILE_PATH" ]
            then
                echo "文件$FILE_PATH存在"
        else
                echo "文件$FILE_PATH不存在"
        fi
else
        mysqldump --single-transaction --flush-logs --master-data=2 -u$DB_USER -p$DB_PASSWORD > $zl/$DATE.sql   
        echo "今天是$WEEK執(zhí)行增量備份"
        FILE_PATH=$zl/$DATE.sql
        if [ -f "$FILE_PATH" ]
            then
                echo "文件$FILE_PATH存在"
        else
                echo "文件$FILE_PATH不存在"
        fi

fi

#保留文件代碼塊
find $ql -mtime +30 -name "*.sql" -exec rm -Rf {} \;
find $zl -mtime +7 -name "*.sql" -exec rm -Rf {} \;

碼了一晚上字??????,這個腳本也應(yīng)該看明白了翩迈,數(shù)據(jù)庫備份思路也應(yīng)該理解了吧持灰。有什么不明白的歡迎交流,如果我在文中有什么不恰當(dāng)或不正確的內(nèi)容歡迎大佬指出负饲,我會積極改正搅方,都看到這里了,如果覺得我文章對你有幫助的話就賞我包辣條錢唄,讓我解解饞绽族。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市衩藤,隨后出現(xiàn)的幾起案子吧慢,更是在濱河造成了極大的恐慌,老刑警劉巖赏表,帶你破解...
    沈念sama閱讀 211,561評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件检诗,死亡現(xiàn)場離奇詭異匈仗,居然都是意外死亡,警方通過查閱死者的電腦和手機逢慌,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,218評論 3 385
  • 文/潘曉璐 我一進店門悠轩,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人攻泼,你說我怎么就攤上這事火架。” “怎么了忙菠?”我有些...
    開封第一講書人閱讀 157,162評論 0 348
  • 文/不壞的土叔 我叫張陵何鸡,是天一觀的道長。 經(jīng)常有香客問我牛欢,道長骡男,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,470評論 1 283
  • 正文 為了忘掉前任傍睹,我火速辦了婚禮隔盛,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘拾稳。我一直安慰自己吮炕,他們只是感情好,可當(dāng)我...
    茶點故事閱讀 65,550評論 6 385
  • 文/花漫 我一把揭開白布熊赖。 她就那樣靜靜地躺著来屠,像睡著了一般。 火紅的嫁衣襯著肌膚如雪震鹉。 梳的紋絲不亂的頭發(fā)上俱笛,一...
    開封第一講書人閱讀 49,806評論 1 290
  • 那天,我揣著相機與錄音传趾,去河邊找鬼迎膜。 笑死,一個胖子當(dāng)著我的面吹牛浆兰,可吹牛的內(nèi)容都是我干的磕仅。 我是一名探鬼主播,決...
    沈念sama閱讀 38,951評論 3 407
  • 文/蒼蘭香墨 我猛地睜開眼簸呈,長吁一口氣:“原來是場噩夢啊……” “哼榕订!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起蜕便,我...
    開封第一講書人閱讀 37,712評論 0 266
  • 序言:老撾萬榮一對情侶失蹤劫恒,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體两嘴,經(jīng)...
    沈念sama閱讀 44,166評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡丛楚,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,510評論 2 327
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了憔辫。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片趣些。...
    茶點故事閱讀 38,643評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖贰您,靈堂內(nèi)的尸體忽然破棺而出坏平,到底是詐尸還是另有隱情,我是刑警寧澤枉圃,帶...
    沈念sama閱讀 34,306評論 4 330
  • 正文 年R本政府宣布功茴,位于F島的核電站,受9級特大地震影響孽亲,放射性物質(zhì)發(fā)生泄漏坎穿。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 39,930評論 3 313
  • 文/蒙蒙 一返劲、第九天 我趴在偏房一處隱蔽的房頂上張望玲昧。 院中可真熱鬧,春花似錦篮绿、人聲如沸孵延。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,745評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽尘应。三九已至,卻和暖如春吼虎,著一層夾襖步出監(jiān)牢的瞬間犬钢,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,983評論 1 266
  • 我被黑心中介騙來泰國打工思灰, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留玷犹,地道東北人。 一個月前我還...
    沈念sama閱讀 46,351評論 2 360
  • 正文 我出身青樓洒疚,卻偏偏與公主長得像歹颓,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子油湖,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,509評論 2 348

推薦閱讀更多精彩內(nèi)容