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

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ù)庫(kù)全量備份。

其中：

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

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

執(zhí)行shell命令：

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

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

mysql> source /home/backup/bakdup.sql

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

1、查看log_bin是否開(kāi)啟

show variables like '%log_bin%';

如下命令所示，則為未開(kāi)啟：

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#在使用之前，請(qǐng)?zhí)崆皠?chuàng)建以下各個(gè)目錄backupDir=/data/backup/daily#增量備份時(shí)復(fù)制mysql-bin.00000*的目標(biāo)目錄，提前手動(dòng)創(chuàng)建這個(gè)目錄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#這個(gè)是用于產(chǎn)生新的mysql-bin.00000*文件# wc -l 統(tǒng)計(jì)行數(shù)# awk 簡(jiǎn)單來(lái)說(shuō)awk就是把文件逐行的讀入，以空格為默認(rèn)分隔符將每行切片，切開(kāi)的部分再進(jìn)行各種分析處理。Counter=wc -l $BinFile |awk '{print $1}'NextNum=0#這個(gè)for循環(huán)用于比對(duì)$Counter,$NextNum這兩個(gè)值來(lái)確定文件是不是存在或最新的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用于檢測(cè)目標(biāo)文件是否存在，存在就寫(xiě)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

新建測(cè)試表：

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');

增量備份：

#進(jìn)行增量備份,生成新的備份日志文件，這樣舊的日志文件就不會(huì)輸入新的日志，方便排查問(wèn)題。[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、解碼查看生成的日志文件內(nèi)容

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

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

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

基于位置恢復(fù)：

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

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

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

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

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

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

基于時(shí)間恢復(fù)：

從日志開(kāi)頭截止到某個(gè)時(shí)間點(diǎn)的恢復(fù)：

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

從某個(gè)時(shí)間點(diǎn)到日志結(jié)尾的恢復(fù)：

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

從某個(gè)時(shí)間點(diǎn)到某個(gè)時(shí)間點(diǎn)的恢復(fù)：

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

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

1、全量備份

優(yōu)點(diǎn)：備份與恢復(fù)操作簡(jiǎn)單方便。

缺點(diǎn)：

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

2、增量備份

優(yōu)點(diǎn)：沒(méi)有重復(fù)數(shù)據(jù)，備份量不大，時(shí)間短。

缺點(diǎn)：

恢復(fù)過(guò)程需要逐個(gè)應(yīng)用備份文件，增加了恢復(fù)時(shí)間；當(dāng)恢復(fù)到之前的某個(gè)備份時(shí)，需要先恢復(fù)最后的全量備份，然后再逐個(gè)恢復(fù)每個(gè)增量備份，可能更加繁瑣和耗時(shí)。

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

全量備份：全量備份是對(duì)整個(gè)數(shù)據(jù)庫(kù)的備份、數(shù)據(jù)庫(kù)結(jié)構(gòu)和文件結(jié)構(gòu)的備份。全量備份保存的是備份完成時(shí)刻的數(shù)據(jù)庫(kù)。全量備份是增量備份的基礎(chǔ)。增量備份：備份自上一次備份之后增加或變化的文件或者內(nèi)容。特點(diǎn)是沒(méi)有重復(fù)數(shù)據(jù)，備份量不大，時(shí)間短，不過(guò)恢復(fù)麻煩。