Mysql資料備份還原(企業實用級)

一:全量備份

使用mysqldump 每天對資料進行定點全量備份,并且删除7天前的資料

實作方法,伺服器crontab 定時執行備份shell;

#!/bin/bash  
baseDir="/home/backup/";  
cd "$baseDir";  
echo "開始備份資料庫";  
echo `mysqldump -h 127.0.0.1 -uroot -p123456 --default-character-set=utf8 -x  test> test_dump_$(date +%Y%m%d_%H%M%S).sql`;
echo "備份資料庫test完成";
echo `mysqldump -h 127.0.0.1 -uroot -p123456 --default-character-set=utf8 -x  test2> test2$(date +%Y%m%d_%H%M%S).sql`;
echo "備份資料test2完成";    
oldDate=`date --date='8 day ago' +%Y%m%d`;  
#删除目前日期-8的備份  
echo `rm -rf test_dump_$oldDate*`;
echo `rm -rf test2_dump_$oldDate*`;
echo "删除$oldDate的備份成功"  
           

上面shell 隻要每天定時執行,即可全量備份對應的資料庫資料,并且删除指定天數的備份資料,完成全量備份(這裡最好用root 賬号,其他賬号也可以就是可能有些權限不夠,根據錯誤資訊對應給就可以)

crontab 添加下面的代碼,每天淩晨2點執行sql_back.sh

1 2 * * * sh /home/backup/sql_back.sh
           

二:日志備份還原

全量備份隻能備份前天資料,并且一般是在淩晨完成,那對當天資料則需要用日志備份;

1:日志備份設定

找到mysql的配置檔案my.conf,修改或在添加下面的配置

binlog_format=mixed //日志模式,必須調成mixed,row 模式用mysqlbinlog 還原的sql是無法直接執行恢複的!!
datadir = /www/server/data  //資料存儲檔案,包括bin-log
log-bin=mysql-bin  //開啟log-bin 字首名稱叫mysql-bin 目錄位置在上面配置
expire_logs_days=7  //設定biglog保留時間為7天
max_binlog_size  = 100M    //超過max_binlog_size或超過6小時會切換到下一序号檔案
           

注意:binlog_format 一定要調成mixed 才能還原sql

2:還原日志

在誤操作後第一時間應該停止資料庫通路,并記錄目前時間點,并執行下面sql使日志檔案分割儲存

flush logs
           

通過mysqlbinlog工具進行日志還原

查找mysqlbinlog 位置,然後cd 到對應位置

find / -name mysqlbinlog
           

根據時間點在日志檔案夾把需要還原的日志備份出來,然後通過下面指令逐個恢複日志sql

/www/server/mysql/bin/mysqlbinlog -v --base64-output=decode-rows --skip-gtids=true mysql-bin.000013 --database=test_db --start-datetime=‘2016-07-20 00:00:01’ --stop-datetime=‘2016-07-24 23:59:59’ > binglog000013.sql
           

最後先通過整體備份的sql還原資料庫,然後執行日志檔案還原的sql,這裡對應的時間點應該注意,把對應錯誤操作要去除