一、MySQL數(shù)據(jù)庫全量備份與恢復步驟

1、創(chuàng)建專用備份文件夾

mkdir -p /data/backup

2、執(zhí)行全量備份命令

/usr/bin/mysqldump -uroot -padmin --lock-all-tables --flush-logs test > /home/backup.sql

如上一段代碼所示，其功能是將 test 數(shù)據(jù)庫全量備份。

其中：

MySQL 用戶名為：root密碼為：admin備份的文件路徑為：/home（當然這個路徑也是可以按照個人意愿修改的）備份的文件名為：backup.sql參數(shù) flush-logs：使用一個新的日志文件來記錄接下來的日志參數(shù)參數(shù) lock-all-tables：鎖定所有數(shù)據(jù)庫

3、執(zhí)行全量恢復命令

執(zhí)行shell命令：

mysql -h localhost -uroot -padmin < bakdup.sql

或者登錄數(shù)據(jù)庫后執(zhí)行：

mysql> source /home/backup/bakdup.sql

二、MySQL數(shù)據(jù)庫增量備份與恢復步驟

1、查看log_bin是否開啟

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    |+---------------------------------+-------+

2、修改/etc/my.cnf

修改/etc/my.cnf，在[mysqld]下添加log-bin=mysql-bin，再次查看：

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                             |+---------------------------------+--------------------------------+

3、增量備份

增量備份腳本：

#!/bin/bash#在使用之前，請?zhí)崆皠?chuàng)建以下各個目錄backupDir=/data/backup/daily#增量備份時復制mysql-bin.00000*的目標目錄，提前手動創(chuàng)建這個目錄mysqlDir=/var/lib/mysql#mysql的數(shù)據(jù)目錄logFile=/data/backup/daily_bak.logBinFile=/var/lib/mysql/mysql-bin.index#mysql的index文件路徑，放在數(shù)據(jù)目錄下的mysqladmin -uroot -p123456 flush-logs#這個是用于產(chǎn)生新的mysql-bin.00000*文件# wc -l 統(tǒng)計行數(shù)# awk 簡單來說awk就是把文件逐行的讀入，以空格為默認分隔符將每行切片，切開的部分再進行各種分析處理。Counter=wc -l $BinFile |awk '{print $1}'NextNum=0#這個for循環(huán)用于比對$Counter,$NextNum這兩個值來確定文件是不是存在或最新的for file in cat $BinFiledo   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    fidoneecho date +"%Y年%m月%d日 %H:%M:%S" $Next Bakup succ! >> $logFile

新建測試表：

CREATE TABLE ly_ycasp_qxpt0.test  (     id int NOT NULL AUTO_INCREMENT,     name varchar(255) NULL,     PRIMARY KEY (id));

插入三條數(shù)據(jù)：

insert into test (id, name) values(1, 'test1');
insert into test (id, name) values(2, 'test2');
insert into test (id, name) values(3, 'test3');
insert into test (id, name) values(4, 'test4');

誤刪id=3和id=4這兩條數(shù)據(jù)：

delete from test where id = 3;
delete from test where id = 4;

繼續(xù)插入id=5和id=6條數(shù)據(jù)：

insert into test (id, name) values(5, 'test5');insert into test (id, name) values(6, 'test6');

增量備份：

#進行增量備份,生成新的備份日志文件，這樣舊的日志文件就不會輸入新的日志，方便排查問題。[root@localhost data]# mysqladmin -uroot -padmin flush-logs

查看增量備份文件：

-rw-r-----. 1 mysql mysql      4361 Jan  5 09:50 mysql-bin.000001-rw-r-----. 1 mysql mysql       156 Jan  5 09:50 mysql-bin.000002

4、解碼查看生成的日志文件內容

mysqlbinlog  --no-defaults --base64-output=decode-rows -v mysql-bin.000001  > /opt/bk01.txt

5、根據(jù)日志文件恢復數(shù)據(jù)

在測試庫先還原全量數(shù)據(jù)，然后再根據(jù)這里的增量備份，使數(shù)據(jù)還原。

基于位置恢復：

恢復數(shù)據(jù)到誤操作前一次的指定位置：

mysqlbinlog --s較好-position='操作id'  二進制日志 | mysql -u 用戶名 -p 密碼

[root@localhost mysql]# mysqlbinlog --s較好-position='3323' mysql-bin.000001 | mysql -uroot -padmin

恢復數(shù)據(jù)到誤操作后一次的指定位置：

mysqlbinlog --start-position='操作id' 二進制日志 | mysql -u 用戶名 -p 密碼

[root@localhost mysql]# mysqlbinlog --start-position='4078' mysql-bin.000002 | mysql -uroot -padmin

基于時間恢復：

從日志開頭截止到某個時間點的恢復：

mysqlbinlog [--no-defaults] --s較好-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u用戶名 -p密碼

從某個時間點到日志結尾的恢復：

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u用戶名 -p密碼

從某個時間點到某個時間點的恢復：

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小時:分鐘:秒' --s較好-
datetime='年-月-日 小時:分鐘:秒' 二進制日志 | mysql -u用戶名 -p密碼

三、MySQL數(shù)據(jù)庫全量備份與增量備份優(yōu)缺點

1、全量備份

優(yōu)點：備份與恢復操作簡單方便。

缺點：

數(shù)據(jù)存在大量的重復；占用大量的備份空間；備份與恢復時間長。

2、增量備份

優(yōu)點：沒有重復數(shù)據(jù)，備份量不大，時間短。

缺點：

恢復過程需要逐個應用備份文件，增加了恢復時間；當恢復到之前的某個備份時，需要先恢復最后的全量備份，然后再逐個恢復每個增量備份，可能更加繁瑣和耗時。

延伸閱讀1：MySQL數(shù)據(jù)庫全量備份與增量備份簡介

全量備份：全量備份是對整個數(shù)據(jù)庫的備份、數(shù)據(jù)庫結構和文件結構的備份。全量備份保存的是備份完成時刻的數(shù)據(jù)庫。全量備份是增量備份的基礎。增量備份：備份自上一次備份之后增加或變化的文件或者內容。特點是沒有重復數(shù)據(jù)，備份量不大，時間短，不過恢復麻煩。