當(dāng)前位置：首頁(yè) > 编程资源 > 综合教程 >内容正文

综合教程

MySQL-14-备份恢复

發(fā)布時(shí)間：2023/10/11 综合教程 248 老码农

生活随笔收集整理的這篇文章主要介紹了 MySQL-14-备份恢复小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

備份類(lèi)型

1 熱備

在業(yè)務(wù)正常時(shí),對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份數(shù)據(jù),并且能夠一致性恢復(fù)（只能是innodb）

對(duì)業(yè)務(wù)影響非常小

2 溫備

鎖表備份,只能查詢(xún)不能修改（myisam）

影響到寫(xiě)入操作

3 冷備

關(guān)閉數(shù)據(jù)庫(kù)業(yè)務(wù),數(shù)據(jù)庫(kù)沒(méi)有任何變更的情況下,進(jìn)行備份數(shù)據(jù).

業(yè)務(wù)停止

常用備份工具

1 邏輯備份工具

基于SQL語(yǔ)句進(jìn)行備份

mysqldump       *****

mysqlbinlog     *****

2 物理備份工具

基于磁盤(pán)數(shù)據(jù)文件備份

xtrabackup(XBK)：percona 第三方   *****

MySQL Enterprise Backup（MEB）

邏輯備份和物理備份的比較

1 mysqldump (MDP)

優(yōu)點(diǎn)：

1.不需要下載安裝

2.備份出來(lái)的是SQL，文本格式，可讀性高，便于備份處理

3.壓縮比較高，節(jié)省備份的磁盤(pán)空間

缺點(diǎn)：

依賴(lài)于數(shù)據(jù)庫(kù)引擎，需要從磁盤(pán)把數(shù)據(jù)讀出，然后轉(zhuǎn)換成SQL進(jìn)行轉(zhuǎn)儲(chǔ)，比較耗費(fèi)資源，數(shù)據(jù)量大的話(huà)效率較低

建議：

100G以?xún)?nèi)的數(shù)據(jù)量級(jí)，可以使用mysqldump

超過(guò)1TB以上，我們也可選擇的是mysqldump，配合分布式的系統(tǒng)

1EB = 1024PB = 1000000TB

2 xtrabackup(XBK)

優(yōu)點(diǎn)：

1.類(lèi)似于直接cp數(shù)據(jù)文件，不需要管邏輯結(jié)構(gòu)，相對(duì)來(lái)說(shuō)性能較高

缺點(diǎn)：

可讀性差

壓縮比低，需要更多磁盤(pán)空間

建議：

數(shù)據(jù)庫(kù)容量 大于100G，小于1TB時(shí)，使用xbk備份方式

備份策略

備份方式：

全備:全庫(kù)備份，備份所有數(shù)據(jù)

增量:備份變化的數(shù)據(jù)

邏輯備份=mysqldump+mysqlbinlog

物理備份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog

備份周期:

根據(jù)數(shù)據(jù)量設(shè)計(jì)備份周期

比如：周日全備，周1-周6增量

mysqldump備份工具

mysqldump (邏輯備份的客戶(hù)端工具)

客戶(hù)端通用參數(shù)

-u  -p   -S   -h  -P

本地備份:

mysqldump -uroot -p  -S /tmp/mysql.sock

遠(yuǎn)程備份:

mysqldump -uroot -p  -h 10.0.0.51 -P3306

備份專(zhuān)用基本參數(shù)

-A 全備參數(shù)

[root@db01 ~]# mkdir -p /data/backup

[root@db01 ~]# mysqldump -uroot -p -A >/data/backup/full.sql

# 補(bǔ)充:建議不用加--set-gtid-purged=OFF

# 1.常規(guī)備份是要加 --set-gtid-purged=OFF,解決備份時(shí)的警告

# [root@db01 ~]# mysqldump -uroot -p123 -A  --set-gtid-purged=OFF  >/backup/full.sql

# 2.構(gòu)建主從時(shí),做的備份,不需要加這個(gè)參數(shù)

# [root@db01 ~]# mysqldump -uroot -p123 -A >/backup/full.sql

-B db1 db2 db3 備份多個(gè)單庫(kù)

說(shuō)明：生產(chǎn)中需要備份，生產(chǎn)相關(guān)的庫(kù)和MySQL庫(kù)

[root@db01 ~]# mysqldump -uroot -p  -B mysql gtid >/data/backup/full.sql

備份單個(gè)或多個(gè)表

world數(shù)據(jù)庫(kù)下的city,country表

[root@db01 ~]# mysqldump -uroot -p world city country >/backup/bak1.sql

以上備份恢復(fù)時(shí):必須庫(kù)事先存在,并且ues才能source恢復(fù)

高級(jí)參數(shù)應(yīng)用*****

##特殊參數(shù)使用1（必須要加）

-R            備份存儲(chǔ)過(guò)程及函數(shù)

--triggers  	備份觸發(fā)器

-E            備份事件

[root@db01 backup]# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql

##特殊參數(shù)使用2

-F 在備份開(kāi)始時(shí),刷新一個(gè)新binlog日志

[root@db01 backup]# mysqldump -uroot -p  -A  -R --triggers -F >/bak/full.sql

##特殊參數(shù)使用3

--master-data=2 ***** 這個(gè)參數(shù)很重要，必須要加

以注釋的形式,保存?zhèn)浞蓍_(kāi)始時(shí)間點(diǎn)的binlog的狀態(tài)信息

[root@db01 ~]# mysqldump -uroot -p  -A  -R --triggers --master-data=2 >/back/world.sql

[root@db01 ~]# grep 'CHANGE' /backup/world.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功能：

（1）在備份時(shí)，會(huì)自動(dòng)記錄，二進(jìn)制日志文件名和位置號(hào)

     0  默認(rèn)值

     1  以change master to命令形式，可以用作主從復(fù)制

     2  以注釋的形式記錄，備份時(shí)刻的文件名+postion號(hào)

（2）自動(dòng)鎖表

（3）如果配合 --single-transaction，只對(duì)非InnoDB表進(jìn)行鎖表備份，InnoDB表進(jìn)行熱備，實(shí)際上是實(shí)現(xiàn)快照備份

--single-transaction  這個(gè)參數(shù)配合上個(gè)參數(shù)一起使用

innodb存儲(chǔ)引擎開(kāi)啟熱備(快照備份)功能

master-data可以自動(dòng)加鎖

（1）在不加--single-transaction，啟動(dòng)所有表的溫備份，所有表都鎖定

（1）加上--single-transaction，對(duì)innodb進(jìn)行快照備份，對(duì)非innodb表可以實(shí)現(xiàn)自動(dòng)鎖表功能

備份必加參數(shù)

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

##特殊參數(shù)使用4

--set-gtid-purged=auto   默認(rèn)就行，一般不用加這個(gè)參數(shù)，了解一下

參數(shù): auto, on, off

使用場(chǎng)景:

1. --set-gtid-purged=OFF,可以使用在日常備份參數(shù)中.

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

2. auto, on:在構(gòu)建主從復(fù)制環(huán)境時(shí)需要的參數(shù)配置

mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

備份小練習(xí)

實(shí)現(xiàn)所有表的單獨(dú)備份

提示：

information_schema.tables

mysqldump -uroot -p123 world city >/backup/world_city.sql

####

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 --single-transaction --set-gtid-purged=0  -R -E --triggers>/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema');

壓縮備份并添加時(shí)間戳

[root@db01 backup]# mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

[root@db01 backup]# mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz

mysqldump備份的恢復(fù)方式（在生產(chǎn)中恢復(fù)要謹(jǐn)慎，恢復(fù)會(huì)刪除重復(fù)的表）

set sql_log_bin=0;

source /backup/full_2018-06-28.sql

set sql_log_bin=1;

注意：

1、mysqldump在備份和恢復(fù)時(shí)都需要mysql實(shí)例啟動(dòng)為前提。

2、一般數(shù)據(jù)量級(jí)100G以?xún)?nèi)，大約15-45分鐘可以恢復(fù)，數(shù)據(jù)量級(jí)很大很大的時(shí)候（PB、EB）

3、mysqldump是覆蓋形式恢復(fù)的方法。

一般我們認(rèn)為，在同數(shù)據(jù)量級(jí)，物理備份要比邏輯備份速度快.

邏輯備份的優(yōu)勢(shì):

1、可讀性強(qiáng)

2、壓縮比很高

備份時(shí)優(yōu)化參數(shù)

(1) max_allowed_packet   最大的數(shù)據(jù)包大小

mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

(2) 增加key_buffer_size  (臨時(shí)表有關(guān))

(3) 分庫(kù)分表并發(fā)備份

(4) 架構(gòu)分離,分別備份      (架構(gòu)拆分,分布式備份)

從mysqldump 全備中獲取庫(kù)和表的備份

1、獲得表結(jié)構(gòu)

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、獲得INSERT INTO語(yǔ)句，用于數(shù)據(jù)的恢復(fù)

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.獲取單庫(kù)的備份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

企業(yè)故障恢復(fù)案例1（可以多參考案例2）

1 背景環(huán)境

正在運(yùn)行的網(wǎng)站系統(tǒng)，mysql-5.7.20數(shù)據(jù)庫(kù)，數(shù)據(jù)量50G，日業(yè)務(wù)增量1-5M

2 備份策略

每天23:00點(diǎn)，計(jì)劃任務(wù)調(diào)用mysqldump執(zhí)行全備腳本

3 故障時(shí)間點(diǎn)

年底故障演練:模擬周三上午10點(diǎn)誤刪除數(shù)據(jù)庫(kù)，并進(jìn)行恢復(fù)

4 思路

  4.1、停業(yè)務(wù)，掛維護(hù)頁(yè)，避免數(shù)據(jù)的二次傷害

  4.2、找一個(gè)臨時(shí)庫(kù)，恢復(fù)周二23：00全備

  4.3、截取周二23：00 --- 周三10點(diǎn)誤刪除之間的binlog，恢復(fù)到臨時(shí)庫(kù)

  4.4、測(cè)試可用性和完整性

  4.5、

    4.5.1 方法一：直接使用臨時(shí)庫(kù)頂替原生產(chǎn)庫(kù)，前端應(yīng)用割接到新庫(kù)

    4.5.2 方法二：將誤刪除的表導(dǎo)出，導(dǎo)入到原生產(chǎn)庫(kù)

  4.6、開(kāi)啟業(yè)務(wù)

  處理結(jié)果：經(jīng)過(guò)20分鐘的處理，最終業(yè)務(wù)恢復(fù)正常

5 故障模擬演練

5.1 準(zhǔn)備數(shù)據(jù)

create database backup;

use backup

create table t1 (id int);

insert into t1 values(1),(2),(3);

commit;

5.2 周二 23：00全備

mysqldump -uroot -p123 -A  -R  --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

5.3 模擬周二 23：00到周三 10點(diǎn)之間數(shù)據(jù)變化

use backup

insert into t1 values(11),(22),(33);

commit;

create table t2 (id int);

insert into t2 values(11),(22),(33);

5.4 模擬故障,刪除表(只是模擬，不代表生產(chǎn)操作)

drop database backup;

6 恢復(fù)過(guò)程

6.1 準(zhǔn)備臨時(shí)數(shù)據(jù)庫(kù)（多實(shí)例3307）

systemctl start mysqld3307

6.2 準(zhǔn)備備份

（1）準(zhǔn)備全備

    cd /backup

    gunzip full_2018-10-17.sql.gz

（2）截取二進(jìn)制日志

     -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=793;

     #mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:6-7' /data/binlog/mysql-bin.000036 >/backup/bin.sql

6.3 恢復(fù)備份到臨時(shí)庫(kù)

mysql -S /data/3307/mysql.sock

set sql_log_bin=0;

source /backup/full_2018-10-17.sql

source /backup/bin.sql

6.4 將故障表導(dǎo)出并恢復(fù)到生產(chǎn)

mysqldump   -S /data/3307/mysql.sock backup t1 >/backup/t1.sql

#mysql -uroot -p123

>set sql_log_bin=0

>use backup

>source /backup/t1.sql;

企業(yè)故障恢復(fù)案例2

數(shù)據(jù)準(zhǔn)備

利用mysqldump進(jìn)行全備+binlog

創(chuàng)建數(shù)據(jù)庫(kù)

mysql> create database backup charset utf8mb4;

創(chuàng)建一張表

mysql> use backup;

mysql> create table t1 (id char(255));

寫(xiě)一個(gè)shell腳本模擬業(yè)務(wù)一直在往表中插入數(shù)據(jù)

#!/bin/bashIP="10.0.0.51"USER="root"PWD="root123"DBNAME="backup"while true; do    sleep 1    insert_sql="insert into t1 values(`date +%s`)"    mysql -h${IP} -u${USER} -p${PWD} ${DBNAME} -e "${insert_sql};commit"    echo ${insert_sql}done

執(zhí)行shell腳本，開(kāi)始往數(shù)據(jù)庫(kù)插入數(shù)據(jù)

模擬在某個(gè)時(shí)間點(diǎn)對(duì)數(shù)據(jù)庫(kù)進(jìn)行了全備

[root@db01 backup]# mysqldump -uroot -proot123 -A -R -E -F --triggers --master-data=2 --single-transaction >./full.sql參數(shù)解釋?zhuān)?A						    對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例全備，里面有多少庫(kù)備份多少庫(kù)-R						    備份存儲(chǔ)過(guò)程和函數(shù)-E						    備份事件--triggers			  備份觸發(fā)器--master-data=2		記錄備份時(shí)刻的binlog信息，自動(dòng)鎖表。不加--single-transaction ,溫備份。加了--single-transaction對(duì)于InnoDB表不鎖表備份(快照備份)--single-transaction  對(duì)于InnoDB的表,進(jìn)行一致性快照備份,不鎖表.-F						    在備份開(kāi)始時(shí),刷新一個(gè)新binlog日志

模擬故障

全備完成以后，等幾分鐘，這時(shí)候又插入了新的數(shù)據(jù)。模擬數(shù)據(jù)庫(kù)出現(xiàn)故障，這里直接drop t1表mysql> drop table t1;

恢復(fù)

此時(shí)看到一直插入數(shù)據(jù)的腳本報(bào)錯(cuò)了，代表業(yè)務(wù)出錯(cuò)了

這里的數(shù)值是1576220628。待會(huì)恢復(fù)數(shù)據(jù)后最后一條數(shù)據(jù)要是這個(gè)1576220628，另外由于是快照備份，不鎖表。所以應(yīng)該是每秒插入1條數(shù)據(jù)

找到最近的全備，這里是在 /backup/full.sql

[root@db01 backup]# ll

total 51088

-rw-r--r-- 1 root root 52313239 Dec 13 15:02 full.sql

[root@db01 backup]# pwd

/backup

把全備恢復(fù)到測(cè)試庫(kù)中。注意不要直接恢復(fù)到生產(chǎn)庫(kù)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.04 sec)

# 恢復(fù)全備

mysql> set sql_log_bin=0;			# 臨時(shí)關(guān)閉二進(jìn)制日志記錄，避免恢復(fù)的時(shí)候，記錄了大量的二進(jìn)制日志

mysql> source /backup/full.sql;

mysql> set sql_log_bin=1;

查看全備full.sql中正在使用的binlog文件是哪個(gè)，以及POS開(kāi)始號(hào)（這里直接用POS號(hào)恢復(fù)了，沒(méi)用gtid）

-- CHANGE MASTER TO MASTER_LOG_FILE

# POS開(kāi)始號(hào)

[root@db01 backup]# head -30 full.sql |grep "CHANGE MASTER TO MASTER_LOG_FILE"

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=194;

# POS結(jié)束號(hào) 直接查看DROP前10行

[root@db01 backup]# mysqlbinlog /data/mysql/binlog/mysql-bin.000014 |grep -B10 "DROP"

# at 29266

#191213 15:03:48 server id 6  end_log_pos 29297 CRC32 0x4d4926e4 	Xid = 6272

COMMIT/*!*/;

# at 29297

#191213 15:03:48 server id 6  end_log_pos 29362 CRC32 0x25b456a0 	GTID	last_committed=109	sequence_number=110	rbr_only=no

SET @@SESSION.GTID_NEXT= 'a4b648cb-11b8-11ea-8636-000c2990cef0:1196'/*!*/;

# at 29362

#191213 15:03:48 server id 6  end_log_pos 29481 CRC32 0x910720d6 	Query	thread_id=20	exec_time=1	error_code=0

use `backup`/*!*/;

SET TIMESTAMP=1576220628/*!*/;

DROP TABLE `t1` /* generated by server */

截取二進(jìn)制日志。如果二進(jìn)制文件過(guò)大也可以直接用grep過(guò)濾

[root@db01 backup]# mysqlbinlog --skip-gtids --start-position=194 --stop-position=29297 /data/mysql/binlog/mysql-bin.000014 >./binlog.sql

恢復(fù)二進(jìn)制日志的數(shù)據(jù)

mysql> use backup;

mysql> set sql_log_bin=0;

mysql> source /backup/binlog.sql;

mysql> set sql_log_bin=1;

驗(yàn)證數(shù)據(jù) 這里最后一條數(shù)據(jù)是1576220628。剛好和業(yè)務(wù)中斷之前的對(duì)上，數(shù)據(jù)完美恢復(fù)

mysql> select * from t1 order by id desc limit 2;

+------------+

| id         |

+------------+

| 1576220628 |

| 1576220627 |

+------------+

10 rows in set (0.00 sec)

導(dǎo)出該表

[root@db01 backup]# mysqldump -S /data/3308/mysql.sock  -R -E -F --triggers --master-data=2 --single-transaction backup t1 >./t1.sql

在生產(chǎn)中恢復(fù)該表

mysql> use backup;

mysql> source /backup/t1.sql;

mysql> select * from t1 order by id desc limit 5;

xtrabackup備份工具(XBK、Xbackup)

安裝

1 安裝依賴(lài)包：

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

2 下載軟件并安裝

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

常用備份命令

xtrabackup

innobackupex    ******

備份方式（物理備份）

（1）對(duì)于非Innodb表（比如 myisam）是，鎖表cp數(shù)據(jù)文件，屬于一種溫備份。

（2）對(duì)于Innodb的表（支持事務(wù)的），不鎖表，拷貝數(shù)據(jù)頁(yè)，最終以數(shù)據(jù)文件的方式保存下來(lái)，把一部分redo和undo一并備走，屬于熱備方式。

題：xbk 在innodb表備份恢復(fù)的流程

  0、xbk備份執(zhí)行的瞬間,立即觸發(fā)Checkpoint,已提交的數(shù)據(jù)臟頁(yè),從內(nèi)存刷寫(xiě)到磁盤(pán),并記錄此時(shí)的LSN號(hào)

  1、備份時(shí)，拷貝磁盤(pán)數(shù)據(jù)頁(yè)，并且記錄備份過(guò)程中產(chǎn)生的redo和undo一起拷貝走,也就是checkpoint LSN之后的日志

  2、在恢復(fù)之前，模擬Innodb“自動(dòng)故障恢復(fù)”的過(guò)程，將redo（前滾）與undo（回滾）進(jìn)行應(yīng)用

  3、恢復(fù)過(guò)程是cp 備份到原來(lái)數(shù)據(jù)目錄下

innobackupex使用

全備

[root@db01 backup]# innobackupex --user=root --password=123  /data/backup

自主定制備份路徑名

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full

備份集中多出來(lái)的文件：

-rw-r----- 1 root root       24 Jun 29 09:59 xtrabackup_binlog_info

-rw-r----- 1 root root      119 Jun 29 09:59 xtrabackup_checkpoints

-rw-r----- 1 root root      489 Jun 29 09:59 xtrabackup_info

-rw-r----- 1 root root     2560 Jun 29 09:59 xtrabackup_logfile

xtrabackup_binlog_info ：（備份時(shí)刻的binlog位置）

[root@db01 full]# cat xtrabackup_binlog_info

mysql-bin.000003    536749

79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7

記錄的是備份時(shí)刻，binlog的文件名字和當(dāng)時(shí)的結(jié)束的position，可以用來(lái)作為截取binlog時(shí)的起點(diǎn)。

xtrabackup_checkpoints ：

backup_type = full-backuped

from_lsn = 0            上次所到達(dá)的LSN號(hào)(對(duì)于全備就是從0開(kāi)始,對(duì)于增量有別的顯示方法)

to_lsn = 160683027      備份開(kāi)始時(shí)間(ckpt)點(diǎn)數(shù)據(jù)頁(yè)的LSN

last_lsn = 160683036    備份結(jié)束后，redo日志最終的LSN。注意最終的last_lsn號(hào)要比to_lsn大9

compact = 0

recover_binlog_info = 0

（1）備份時(shí)刻，立即將已經(jīng)commit過(guò)的，內(nèi)存中的數(shù)據(jù)頁(yè)刷新到磁盤(pán)(CKPT).開(kāi)始備份數(shù)據(jù)，數(shù)據(jù)文件的LSN會(huì)停留在to_lsn位置。

（2）備份時(shí)刻有可能會(huì)有其他的數(shù)據(jù)寫(xiě)入，已備走的數(shù)據(jù)文件就不會(huì)再發(fā)生變化了。

（3）在備份過(guò)程中，備份軟件會(huì)一直監(jiān)控著redo的undo，如果一旦有變化會(huì)將日志也一并備走，并記錄LSN到last_lsn。

從to_lsn  ----》last_lsn 就是，備份過(guò)程中產(chǎn)生的數(shù)據(jù)變化

全備的恢復(fù)

準(zhǔn)備備份（Prepared）

將redo進(jìn)行重做，已提交的寫(xiě)到數(shù)據(jù)文件，未提交的使用undo回滾掉。模擬了CSR的過(guò)程

[root@db01 ~]# innobackupex --apply-log  /backup/full

恢復(fù)備份

前提：

1、被恢復(fù)的目錄是空

2、被恢復(fù)的數(shù)據(jù)庫(kù)的實(shí)例是關(guān)閉

systemctl stop mysqld

創(chuàng)建新目錄

[root@db01 backup]# mkdir /data/mysql1

數(shù)據(jù)授權(quán)

chown -R mysql.mysql /data/mysql1

恢復(fù)備份

[root@db01 full]# cp -a /backup/full/* /data/mysql1/

啟動(dòng)數(shù)據(jù)庫(kù)

vim /etc/my.cnf

datadir=/data/mysql1

[root@db01 mysql1]# chown -R mysql.mysql /data/mysql1

systemctl start mysqld

innobackupex 增量備份(incremental)

（1）增量備份的方式，是基于上一次備份進(jìn)行增量。

（2）增量備份無(wú)法單獨(dú)恢復(fù)。必須基于全備進(jìn)行恢復(fù)。

（3）所有增量必須要按順序合并到全備中。

增量備份命令

（1）刪掉原來(lái)備份

略.

（2）全備（周日）

[root@db01 backup]# innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log

（3）模擬周一數(shù)據(jù)變化

db01 [(none)]>create database cs charset utf8;

db01 [(none)]>use cs

db01 [cs]>create table t1 (id int);

db01 [cs]>insert into t1 values(1),(2),(3);

db01 [cs]>commit;

（4）第一次增量備份（周一）

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full  /backup/inc1 &>/tmp/inc1.log

（5）模擬周二數(shù)據(jù)

db01 [cs]>create table t2 (id int);

db01 [cs]>insert into t2 values(1),(2),(3);

db01 [cs]>commit;

（6）周二增量

 [root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1  /backup/inc2  &>/tmp/inc2.log

（7）模擬周三數(shù)據(jù)變化

db01 [cs]>create table t3 (id int);

db01 [cs]>insert into t3 values(1),(2),(3);

db01 [cs]>commit;

db01 [cs]>drop database cs;

恢復(fù)到周三誤drop之前的數(shù)據(jù)狀態(tài)

恢復(fù)思路：

1.  掛出維護(hù)頁(yè)，停止當(dāng)天的自動(dòng)備份腳本

2.  檢查備份：周日f(shuō)ull+周一inc1+周二inc2，周三的完整二進(jìn)制日志

3. 進(jìn)行備份整理（細(xì)節(jié)），截取關(guān)鍵的二進(jìn)制日志（從備份——誤刪除之前）

4. 測(cè)試庫(kù)進(jìn)行備份恢復(fù)及日志恢復(fù)

5. 應(yīng)用進(jìn)行測(cè)試無(wú)誤，開(kāi)啟業(yè)務(wù)

6. 此次工作的總結(jié)

恢復(fù)過(guò)程

1. 檢查備份

1afe8136-601d-11e9-9022-000c2928f5dd:7-9

2. 備份整理（apply-log）+合并備份（full+inc1+inc2）

(1) 全備的整理

注意這里是要 --redo-only 參數(shù)的。需要模擬innodb引擎數(shù)據(jù)自動(dòng)恢復(fù)

[root@db01 one]# innobackupex --apply-log --redo-only /data/backup/full

(2) 合并inc1到full中

[root@db01 one]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full

(3) 合并inc2到full中

[root@db01 one]# innobackupex --apply-log  --incremental-dir=/data/backup/inc2 /data/backup/full

(4) 最后一次整理全備 這里不需要 --redo-only 參數(shù)

[root@db01 backup]#  innobackupex --apply-log  /data/backup/full

3. 截取周二 23:00 到drop 之前的 binlog

[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql

4. 進(jìn)行恢復(fù)

[root@db01 backup]# mkdir /data/mysql/data2 -p

[root@db01 full]# cp -a * /data/mysql/data2

[root@db01 backup]# chown -R mysql.  /data/*

[root@db01 backup]# systemctl stop mysqld

vim /etc/my.cnf

datadir=/data/mysql/data2

systemctl start mysqld

Master [(none)]>set sql_log_bin=0;

Master [(none)]>source /data/backup/binlog.sql

Master [(none)]>set sql_log_bin=1;

備份集中單獨(dú)恢復(fù)表

思考:在之前的項(xiàng)目案例中,如果誤刪除的表只有10M,而備份有500G,該如何快速恢復(fù)誤刪除表?

拷貝表相關(guān)的文件，然后CP到生產(chǎn)環(huán)境

提示：

drop table city;

create table city like city_bak;

alter table city discard tablespace;

cp /backup/full/world/city.ibd  /application/mysql/data/world/

chown -R mysql.mysql  /application/mysql/data/world/city.ibd

alter table city import  tablespace;

mysql升級(jí)遷移

遷移(5.6.44  --->  5.7.26)

1 搭建5.6的測(cè)試環(huán)境

(1)創(chuàng)建必須的目錄

[root@oldboyedu ~]# mkdir /data/mysql/data  -p

[root@oldboyedu ~]# mkdir /application/ -p

[root@oldboyedu ~]# mkdir /data/binlog -p

上傳軟件至 /application 下并解壓

(2) 建用戶(hù),改權(quán)限

[root@oldboyedu ~]# useradd mysql

[root@oldboyedu ~]# chown -R mysql. /data/application/

(3) 修改環(huán)境變量

vim /etc/profile

export PATH=/application/mysql/bin:$PATH

source /etc/profile

(4)數(shù)據(jù)初始化

[root@oldboyedu ~]# yum remove mariadb-libs

[root@oldboyedu ~]# yum install -y libaio-devel

[root@oldboyedu ~]# \rm -rf /data/mysql/data/*

[root@oldboyedu ~]# /application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data

(5) 準(zhǔn)備配置文件和啟動(dòng)腳本

cat > /etc/my.cnf <<EOF

[mysqld]

basedir=/application/mysql

datadir=/data/mysql/data

server_id=99

user=mysql

log_bin=/data/binlog/mysql-bin

binlog_format=row

socket=/tmp/mysql.sock

[mysql]

socket=/tmp/mysql.sock

EOF

[root@oldboyedu data]# cp  /application/mysql/support-files/mysql.server /etc/init.d/mysqld

(6) 啟動(dòng)數(shù)據(jù)庫(kù)

[root@oldboyedu data]# /etc/init.d/mysqld start

[root@oldboyedu data]# mysqladmin -uroot -p password 123

2 遷移5.6 數(shù)據(jù)到 5.7  (擴(kuò)展)

(1) 5.6 數(shù)據(jù)庫(kù)備份

[root@oldboyedu ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql

[root@oldboyedu ~]# scp /tmp/full.sql 10.0.0.51:/data/3308

(2)準(zhǔn)備5.7數(shù)據(jù)庫(kù)

[root@db01 /]# systemctl start mysqld3308

[root@db01 /]# mysql -S /data/3308/mysql.sock

mysql> source /data/3308/full.sql

[root@db01 /]# mysql_upgrade  -uroot -p123 -S /data/3308/mysql.sock

(3) binlog的持續(xù)追加

(4) 停業(yè)務(wù),恢復(fù)剩余的binlog

(5) 業(yè)務(wù)割接

總結(jié)

以上是生活随笔為你收集整理的MySQL-14-备份恢复的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： ModuleNotFoundError:
下一篇： Small Multiple