主頁 > 知識(shí)庫 > MySQL系列之十二備份與恢復(fù)

MySQL系列之十二備份與恢復(fù)

系列教程

MySQL系列之開篇 MySQL關(guān)系型數(shù)據(jù)庫基礎(chǔ)概念
MySQL系列之一 MariaDB-server安裝
MySQL系列之二多實(shí)例配置
MySQL系列之三基礎(chǔ)篇
MySQL系列之四 SQL語法
MySQL系列之五視圖、存儲(chǔ)函數(shù)、存儲(chǔ)過程、觸發(fā)器
MySQL系列之六用戶與授權(quán)
MySQL系列之七 MySQL存儲(chǔ)引擎
MySQL系列之八 MySQL服務(wù)器變量
MySQL系列之九 mysql查詢緩存及索引
MySQL系列之十 MySQL事務(wù)隔離實(shí)現(xiàn)并發(fā)控制
MySQL系列之十一日志記錄
MySQL系列之十二備份與恢復(fù)
MySQL系列之十三 MySQL的復(fù)制
MySQL系列之十四 MySQL的高可用實(shí)現(xiàn)
MySQL系列之十五 MySQL常用配置和性能壓力測試

一、備份策略贅述

1、備份的類型

類型1：

熱備份：讀寫不受影響（MyISAM不支持熱備，InnoDB支持熱備）
溫備份：僅可以執(zhí)行讀操作
冷備份：離線備份，讀寫操作均中止

類型2：

物理備份：復(fù)制數(shù)據(jù)文件進(jìn)行備份，占用較多的空間，速度快
邏輯備份：將數(shù)據(jù)導(dǎo)出至文本文件中，占用空間少，速度慢，可能丟失精度

類型3：

完全備份：備份全部數(shù)據(jù)
增量備份：僅備份上次完全備份或增量備份以后變化的數(shù)據(jù)，備份較快，還原復(fù)雜
差異備份：僅備份上次完全備份以來變化的數(shù)據(jù)，備份較慢，還原簡單

2、備份需要考慮的因素

溫備的持鎖多久，在鎖狀態(tài)的情況下無法寫入數(shù)據(jù)
備份產(chǎn)生的負(fù)載，要調(diào)空閑的時(shí)間備份
備份過程的時(shí)長，數(shù)據(jù)量大的時(shí)候時(shí)間會(huì)很長，要選擇合適的方案
恢復(fù)過程的時(shí)長，備份數(shù)據(jù)需要即時(shí)測試

3、備份的目標(biāo)

數(shù)據(jù)庫數(shù)據(jù)，每個(gè)表空間單獨(dú)存放
二進(jìn)制日志，需要和數(shù)據(jù)分開存儲(chǔ)
InnoDB的事務(wù)日志
存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器或事件調(diào)度器等
服務(wù)器的配置文件：/etc/my.cnf

4、備份工具

mysqldump工具：邏輯備份工具，適用所有存儲(chǔ)引擎溫備；支持完全或部分備份；對(duì)InnoDB存儲(chǔ)引擎支持熱備；Schema（數(shù)據(jù)庫的定義）和數(shù)據(jù)存儲(chǔ)在一起。

用法：
           shell> mysqldump [options] db_name [tbl_name ...]
           shell> mysqldump [options] --databases db_name ...
           shell> mysqldump [options] --all-databases

選項(xiàng)：
	-A：備份所有庫
	-B db_name1,[db_name2,...]：備份指定庫
	-E：備份相關(guān)的所有event scheduler
	-R：備份所有存儲(chǔ)過程和存儲(chǔ)函數(shù)
	--triggers：備份表相關(guān)觸發(fā)器，默認(rèn)啟用,用--skip-triggers，不備份觸發(fā)器
	--master-data={1|2}：
		 1：所備份的數(shù)據(jù)之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定默認(rèn)為1
		 2：記錄為注釋的CHANGE MASTER TO語句，注意：此選項(xiàng)會(huì)自動(dòng)關(guān)閉--lock-tables功能，自動(dòng)打開--lock-all-tables功能（除非開啟--single-transaction）
	-F：備份前滾動(dòng)日志，鎖定表完成后，執(zhí)行flush logs命令,生成新的二進(jìn)制日志文件，配合-A時(shí)，會(huì)導(dǎo)致刷新多次數(shù)據(jù)庫，在同一時(shí)刻執(zhí)行轉(zhuǎn)儲(chǔ)和日志刷新，則應(yīng)同時(shí)使用--flush-logs和-x，--master-data或-single-transaction,此時(shí)只刷新一次；建議：和-x，--master-data或 --single-transaction一起使用
	--compact 去掉注釋，適合調(diào)試，生產(chǎn)不使用
	-d：只備份表結(jié)構(gòu)
	-t：只備份數(shù)據(jù)，不備份create table
	-n：不備份create database，可被-A或-B覆蓋
	--flush-privileges：備份前刷新授權(quán)表，備份mysql庫或相關(guān)時(shí)需要使用
	-f：忽略SQL錯(cuò)誤，繼續(xù)執(zhí)行
	--hex-blob：使用十六進(jìn)制符號(hào)轉(zhuǎn)儲(chǔ)二進(jìn)制列（例如，“abc”變?yōu)?x616263），受影響的數(shù)據(jù)類型包括BINARY， VARBINARY，BLOB，BIT
	-q：不緩存查詢，直接輸出，加快備份速度

MyISAM備份選項(xiàng)：支持溫備；不支持熱備，所以必須先鎖定要備份的庫，而后啟動(dòng)備份操作

-x,--lock-all-tables：加全局讀鎖，鎖定所有庫的所有表，同時(shí)加--single-transaction或--lock-tables選項(xiàng)會(huì)關(guān)閉此選項(xiàng)功能，注意：數(shù)據(jù)量大時(shí)，可能會(huì)導(dǎo)致長時(shí)間無法并發(fā)訪問數(shù)據(jù)庫

-l,--lock-tables：對(duì)于需要備份的每個(gè)數(shù)據(jù)庫，在啟動(dòng)備份之前分別鎖定其所有表，默認(rèn)為on,--skip-lock-tables選項(xiàng)可禁用,對(duì)備份MyISAM的多個(gè)庫,可能會(huì)造成數(shù)據(jù)不一致

InnoDB備份選項(xiàng)：支持熱備，可用溫備但不建議用

--single-transaction：此選項(xiàng)Innodb中推薦使用，不適用MyISAM，此選項(xiàng)會(huì)開始備份前，先執(zhí)行START TRANSACTION指令開啟事務(wù)此選項(xiàng)通過在單個(gè)事務(wù)中轉(zhuǎn)儲(chǔ)所有表來創(chuàng)建一致的快照。僅適用于存儲(chǔ)在支持多版本控制的存儲(chǔ)引擎中的表（目前只有InnoDB可以）; 轉(zhuǎn)儲(chǔ)不保證與其他存儲(chǔ)引擎保持一致。

在進(jìn)行單事務(wù)轉(zhuǎn)儲(chǔ)時(shí)，要確保有效的轉(zhuǎn)儲(chǔ)文件（正確的表內(nèi)容和二進(jìn)制日志位置），需要保證沒有其他連接使用以下語句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE

此選項(xiàng)和 --lock-tables（此選項(xiàng)隱含提交掛起的事務(wù)）選項(xiàng)是相互排斥備份大型表時(shí)，建議將--single-transaction選項(xiàng)和--quick結(jié)合一起使用

InnoDB建議備份策略：
	mysqldump –uroot –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM建議備份策略：
	mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

xtrabackup工具：由Percona提供支持對(duì)InnoDB做熱備(物理備份)的工具，支持完全備份、增量備份

由Percona公司提供的mysql數(shù)據(jù)庫備份工具，開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫進(jìn)行熱備的工具；

xtrabackup 是用來備份 InnoDB 表的，不能備份非 InnoDB 表；

innobackupex 腳本用來備份非 InnoDB 表，同時(shí)會(huì)調(diào)用 xtrabackup 命令來備份 InnoDB 表，還會(huì)和 MySQL Server 發(fā)送命令進(jìn)行交互，如加全局讀鎖（FTWRL）、獲取位點(diǎn)（SHOW SLAVE STATUS）等。即innobackupex是在xtrabackup 之上做了一層封裝實(shí)現(xiàn)的；

雖然目前一般不用 MyISAM 表，只是 MySQL 庫下的系統(tǒng)表是 MyISAM 的，因此備份基本都通過 innobackupex 命令進(jìn)行；

xtrabackup版本升級(jí)到2.4后，相比之前的2.1有了比較大的變化：innobackupex 功能全部集成到 xtrabackup 里面，只有一個(gè) binary程序，另外為了兼容考慮，innobackupex作為 xtrabackup 的軟鏈接，即xtrabackup現(xiàn)在支持非Innodb表備份，并且Innobackupex在下一版本中移除，建議通過xtrabackup替換innobackupex。

使用innobakupex備份時(shí)，其會(huì)調(diào)用xtrabackup備份所有的InnoDB表，復(fù)制所有關(guān)于表結(jié)構(gòu)定義的相關(guān)文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關(guān)文件，同時(shí)還會(huì)備份觸發(fā)器和數(shù)據(jù)庫配置信息相關(guān)的文件。這些文件會(huì)被保存至一個(gè)以時(shí)間命名的目錄中,在備份時(shí)，innobackupex還會(huì)在備份目錄中創(chuàng)建如下文件：

1）xtrabackup_checkpoints：備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號(hào))范圍信息,每個(gè)InnoDB頁(通常為16k大小)都會(huì)包含一個(gè)日志序列號(hào)，即LSN。LSN是整個(gè)數(shù)據(jù)庫系統(tǒng)的系統(tǒng)版本號(hào)，每個(gè)頁面相關(guān)的LSN能夠表明此頁面最近是如何發(fā)生改變的；
2）xtrabackup_binlog_info：MySQL服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二進(jìn)制日志事件的位置；
3）xtrabackup_info：innobackupex工具執(zhí)行時(shí)的相關(guān)信息；
4）backup-my.cnf：備份命令用到的配置選項(xiàng)信息；
5）xtrabackup_logfile：備份生成的日志文件。

用法：
	innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

選項(xiàng)：
    --user：該選項(xiàng)表示備份賬號(hào)
    --password：該選項(xiàng)表示備份的密碼
    --host：該選項(xiàng)表示備份數(shù)據(jù)庫的地址
    --databases：該選項(xiàng)接受的參數(shù)為數(shù)據(jù)名，如果要指定多個(gè)數(shù)據(jù)庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時(shí)，在指定某數(shù)據(jù)庫時(shí)，也可以只指定其中的某張表。如："mydatabase.mytable"。該選項(xiàng)對(duì)innodb引擎表無效，還是會(huì)備份所有innodb表
    --defaults-file：該選項(xiàng)指定從哪個(gè)文件讀取MySQL配置，必須放在命令行第一個(gè)選項(xiàng)位置
    --incremental：該選項(xiàng)表示創(chuàng)建一個(gè)增量備份，需要指定--incremental-basedir
    --incremental-basedir：該選項(xiàng)指定為前一次全備份或增量備份的目錄，與--incremental同時(shí)使用
    --incremental-dir：該選項(xiàng)表示還原時(shí)增量備份的目錄
    --include=name：指定表名，格式：databasename.tablename
    --apply-log：一般情況下,在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會(huì)包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件仍處理不一致狀態(tài)。此選項(xiàng)作用是通過回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件使數(shù)據(jù)文件處于一致性狀態(tài)
	--use-memory：該選項(xiàng)表示和--apply-log選項(xiàng)一起使用，prepare 備份的時(shí)候，xtrabackup做crash recovery分配的內(nèi)存大小，單位字節(jié)。也可(1MB,1M,1G,1GB)，推薦1G
	--export：表示開啟可導(dǎo)出單獨(dú)的表之后再導(dǎo)入其他Mysql中
	--redo-only：此選項(xiàng)在prepare base full backup，往其中merge增量備份時(shí)候使用
	--copy-back：做數(shù)據(jù)恢復(fù)時(shí)將備份數(shù)據(jù)文件拷貝到MySQL服務(wù)器的datadir
	--move-back：這個(gè)選項(xiàng)與--copy-back相似，唯一的區(qū)別是它不拷貝文件，而是移動(dòng)文件到目的地。這個(gè)選項(xiàng)移除backup文件，用時(shí)候必須小心。使用場景：沒有足夠的磁盤空間同事保留數(shù)據(jù)文件和Backup副本

注意：

1）datadir目錄必須為空。除非指定innobackupex --force-non-empty-directorires選項(xiàng)指定，否則--copy-backup選項(xiàng)不會(huì)覆蓋；

2）在restore之前,必須shutdown MySQL實(shí)例，不能將一個(gè)運(yùn)行中的實(shí)例restore到datadir目錄中；

3）由于文件屬性會(huì)被保留，大部分情況下需要在啟動(dòng)實(shí)例之前將文件的屬主改為mysql，chown -R mysql:mysql /data/mysqldb

mysqlbackup工具：熱備份，MySQL Enterprise Edition組件
mysqlhotcopy工具：幾乎冷備，僅適用于MyISAM存儲(chǔ)引擎
基于lvm快照備份：幾乎熱備，需要在拍快照前鎖表
tar + cp 等歸檔復(fù)制工具備份：完全冷備

二、備份方案

1、cp + tar == 物理冷備

將數(shù)據(jù)目錄打包壓縮備份，需要停服務(wù)，不推薦

1）備份：

~]# mkdir /backup
~]# systemctl stop mariadb #停止服務(wù)
~]# tar Jcf /backup/mariadb_all.tar.xz /var/lib/mysql/ #打包壓縮
backup]# systemctl start mariadb

2）還原：

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf  #將損壞的庫刪除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz  #解壓打包的數(shù)據(jù)庫文件
backup]# cp -av var/lib/mysql/ /var/lib/ #還原
backup]# systemctl start mariadb #啟動(dòng)服務(wù)，恢復(fù)成功

2、lvm快照 + binlog == 幾乎物理熱備 + 增量備份

1）備份：需要將數(shù)據(jù)庫目錄存放到lvm邏輯卷上

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf  #將損壞的庫刪除
~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz  #解壓打包的數(shù)據(jù)庫文件
backup]# cp -av var/lib/mysql/ /var/lib/ #還原
backup]# systemctl start mariadb #啟動(dòng)服務(wù)，恢復(fù)成功

準(zhǔn)備lvm環(huán)境：
~]# pvcreate /dev/sda5
~]# vgcreate vg0 /dev/sda5
~]# lvcreate -n lv_data -L 10G vg0
~]# lvcreate -n lv_binlog -L 10G vg0
~]# mkfs.xfs /dev/vg0/lv_data
~]# mkfs.xfs /dev/vg0/lv_binlog
~]# mkdir -pv /data/{mysqldb,binlog}  #創(chuàng)建數(shù)據(jù)目錄和二進(jìn)制日志存放目錄
~]# chown -R mysql:mysql /data/
~]# vim /etc/fstab
	UUID=4e3d726a-d420-4c1e-812b-da315012ba86 /data/mysqldb xfs defaults 0 0
	UUID=6dd98866-769f-4369-8738-291fbcc94ca1 /data/binlog xfs defaults 0 0

配置數(shù)據(jù)庫，模擬生成大量數(shù)據(jù)：
~]# yum install mariadb-server -y
~]# vim /etc/my.cnf
    [mysqld]
    datadir = /data/mysqldb  #指定數(shù)據(jù)庫存放路徑
    log_bin = /data/binlog/mariadb-bin  #開啟二進(jìn)制日志記錄，并且存放到指定路徑
    innodb_file_per_table = ON  #開啟每個(gè)表單獨(dú)的表空間
~]# systemctl start mariadb
~]# mysql  #連接數(shù)據(jù)庫，這里省略了用戶名和密碼，以下都是如此
MariaDB [(none)]> CREATE DATABASE school;  #創(chuàng)建一個(gè)測試的庫
MariaDB [(none)]> use school
MariaDB [school]> CREATE TABLE testtb (id int auto_increment primary key,name char(30),age int default 20);  #創(chuàng)建一張數(shù)據(jù)表
MariaDB [school]> DELIMITER //  #修改語句結(jié)束符為“//”
MariaDB [school]> CREATE PROCEDURE pro_testtb()  #寫一個(gè)存儲(chǔ)過程，目的是生成十萬條記錄測試用
    -> BEGIN
    -> declare i int;
    -> set i = 1;
    -> while i  100000
    -> do INSERT INTO testtb(name,age) VALUES (CONCAT('testuser',i),i);
    -> SET i = i + 1;
    -> END while;
    -> END//
MariaDB [school]> DELIMITER ;  #記得將語句結(jié)束符再改回來
MariaDB [school]> CALL pro_testtb;  #調(diào)用存儲(chǔ)過程來
MariaDB [school]> SELECT COUNT(*) FROM testtb;  #查看一下表中有十萬條記錄
+----------+
| COUNT(*) |
+----------+
|    99999 |
+----------+

開始備份：
MariaDB [school]> FLUSH TABLES WITH READ LOCK;  #備份前切記鎖表，防止用戶繼續(xù)寫入
MariaDB [school]> FLUSH LOGS;  #滾動(dòng)一下二進(jìn)制日志
MariaDB [school]> SHOW MASTER LOGS;  #查看二進(jìn)制日志的位置
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     30334 |
| mariadb-bin.000002 |   1038814 |
| mariadb-bin.000003 |  29178309 |
| mariadb-bin.000004 |       528 |
| mariadb-bin.000005 |       245 |  #將此出記錄下來，我們后邊需要用到
+--------------------+-----------+
~]# lvcreate -L 5G -n lv_mysql_snap -s -p r /dev/vg0/lv_data  #需要再開一個(gè)終端創(chuàng)建快照，不要退出mysql終端
MariaDB [school]> UNLOCK TABLES;  #創(chuàng)建快照后第一時(shí)間解鎖，小心用戶投訴
~]# mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/  #將快照掛載到/mnt
~]# cp -av /mnt/ /backup  #拷貝數(shù)據(jù)到備份目錄
~]# umount /mnt/
~]# lvremove /dev/vg0/lv_mysql_snap  #拷貝完成后即時(shí)刪除快照，影響服務(wù)器性能，到此完全備份完成~

再加點(diǎn)數(shù)據(jù)：
MariaDB [school]> CALL pro_testtb;  #讓我們模擬再來插入十萬條數(shù)據(jù)
MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
|   199998 |  #現(xiàn)在是二十萬條記錄數(shù)據(jù)了
+----------+

2）還原：

模擬數(shù)據(jù)庫損壞：
~]# rm -rf /data/mysqldb/*  #服務(wù)器崩潰，不多BB，直接清空庫
~]# systemctl stop mariadb  #停服務(wù)

開始還原：
~]# cp -av /backup/* /data/mysqldb/  #將備份的文件cp到對(duì)應(yīng)的庫目錄下
在/etc/my.cnf的[mysqld]下加上skip_networking，禁止用戶使用數(shù)據(jù)庫，防止恢復(fù)過程中的數(shù)據(jù)寫入
~]# systemctl start mariadb  #啟動(dòng)服務(wù)
~]# ls -1 /data/binlog/  #查看二進(jìn)制日記的文件個(gè)數(shù)
    mariadb-bin.000001
    mariadb-bin.000002
    mariadb-bin.000003
    mariadb-bin.000004
    mariadb-bin.000005
    mariadb-bin.000006
    mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000005 > binlog.sql  #到出完全備份時(shí)間點(diǎn)以后的數(shù)據(jù)
~]# mysqlbinlog /data/binlog/mariadb-bin.000006 >> binlog.sql  #將之后的所有數(shù)據(jù)都追加到同一sql文件中
~]# mysql  binlog.sql  #利用二進(jìn)制日志從我們之前完全備份的點(diǎn)開始增量還原
~]# mysql -e 'SELECT COUNT(*) FROM school.testtb'  #查看一下，二十萬條記錄都在，nice
+----------+
| COUNT(*) |
+----------+
|   199998 |
+----------+
到/etc/my.cnf的[mysqld]下刪除skip_networking，重啟服務(wù)，到此還原完成~

3、mysqldump + InnoDB + binlog= 完全邏輯熱備 + 增量備份

1）備份：這里我就不再生成數(shù)據(jù)了，就接著上邊的環(huán)境做了

~]# mysqldump -A -F -E -R --single-transaction --master-data=2 --flush-privileges  > /backup/full-`date +%F-%T`.sql  #全庫完全備份

2）模擬故障：

MariaDB [(none)]> CREATE DATABASE db1;  #創(chuàng)建一個(gè)庫
MariaDB [(none)]> CREATE DATABASE db2;  #再創(chuàng)建一個(gè)庫
MariaDB [school]> use school;
MariaDB [school]> DROP TABLE testtb;  #誤操作，將我們二十萬條記錄的表刪掉了
MariaDB [school]> CREATE TABLE students (id INT(4) AUTO_INCREMENT PRIMARY KEY,name CHAR(30),age TINYINT);  #后續(xù)又有用戶創(chuàng)建了其他的表
MariaDB [school]> INSERT INTO students(name,age) VALUES ('user1',20);  #并且還加入了數(shù)據(jù)

3）還原：

此時(shí)，我們發(fā)現(xiàn)了有一個(gè)表不見了，需要緊急恢復(fù)，開始吧
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;  #鎖表
MariaDB [(none)]> FLUSH LOGS;  #刷新滾動(dòng)一次二進(jìn)制日志文件
MariaDB [(none)]> SHOW MASTER LOGS;  #查看當(dāng)前的日志狀態(tài)
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |     30334 |
| mariadb-bin.000002 |   1038814 |
| mariadb-bin.000003 |  29178309 |
| mariadb-bin.000004 |       528 |
| mariadb-bin.000005 |  29177760 |
| mariadb-bin.000006 |  29177786 |
| mariadb-bin.000007 |       953 |
| mariadb-bin.000008 |       245 |
+--------------------+-----------+
~]# systemctl stop mariadb  #停止服務(wù)，準(zhǔn)備修復(fù)
~]# head -30 /backup/full-2018-06-14-05\:33\:47.sql |grep "CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000007', MASTER_LOG_POS=245;  #找到完全備份的日志點(diǎn)，在mariadb-bin.000007的245
~]# ls -1 /data/binlog/
mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000007 > /backup/binlog.sql #將完全備份之后的二進(jìn)制日志導(dǎo)出來
~]# mysqlbinlog /data/binlog/mariadb-bin.000008 >> /backup/binlog.sql
~]# vim /backup/binlog.sql  #修改導(dǎo)出的sql文件，把誤操作的SQL語句刪除
刪除"DROP TABLE `testtb` /* generated by server */"這行

導(dǎo)入備份：
~]# rm -rf /data/mysqldb/*  #先清空故障庫
~]# vim /etc/my.cnf  #編輯配置文件
	在[mysqld]加入skip_networking，防止用戶寫入數(shù)據(jù)
~]# systemctl start mariadb  #啟動(dòng)服務(wù)
~]# mysql  /backup/full-2018-06-14-05\:33\:47.sql  #導(dǎo)入完全備份
~]# mysql  /backup/binlog.sql  #導(dǎo)入增量備份
MariaDB [(none)]> show databases;  #查看一下我們的數(shù)據(jù)是否成功恢復(fù)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |  #已恢復(fù)
| db2                |  #已恢復(fù)
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
MariaDB [(none)]> SELECT COUNT(*) FROM school.testtb;
+----------+
| COUNT(*) |
+----------+
|   199999 | #已恢復(fù)
+----------+
MariaDB [(none)]> SELECT * FROM school.students;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |   20 | #已恢復(fù)
+----+-------+------+
到現(xiàn)在為止，已經(jīng)完成恢復(fù)，把配置文件中的skip_networking刪除，重啟服務(wù)，大功告成~

4、Xtrabackup + InnoDB == 完全熱備 + 增量備份

1）完全備份

~]# innobackupex --user=root /backup/  #這里省略了密碼

2）增刪數(shù)據(jù)

MariaDB [school]> CALL pro_testtb;  #增加一些數(shù)據(jù)
MariaDB [school]> SELECT COUNT(*) FROM testtb;  #現(xiàn)在有三十萬條記錄了
+----------+
| COUNT(*) |
+----------+
|   299998 |
+----------+
MariaDB [school]> INSERT INTO students VALUES (2,'user2',21);
MariaDB [school]> UPDATE students SET age=19 WHERE id=1;
MariaDB [school]> SELECT * FROM students;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |   19 |
|  2 | user2 |   21 |
+----+-------+------+

3）增量備份

~]# mkdir /backup/inc{1,2}  #創(chuàng)建增量備份的目錄
~]# innobackupex --incremental /backup/inc1/ --incremental-basedir=/backup/2018-06-14_10-44-57/  #指定在完全備份的基礎(chǔ)上增量備份

4）增刪數(shù)據(jù)

MariaDB [(none)]> CREATE DATABASE db3; 
MariaDB [(none)]> DROP TABLE school.students;  #誤操作刪除了表
MariaDB [(none)]> use school
MariaDB [school]> CALL pro_testtb;  #后續(xù)又有數(shù)據(jù)產(chǎn)生
MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
|   399997 |
+----------+
MariaDB [school]> SELECT * FROM students;  #到此出發(fā)現(xiàn)students表不見了，怎么辦？
ERROR 1146 (42S02): Table 'school.students' doesn't exist

5）故障出現(xiàn)

~]# rm -rf /data/mysqldb/*  #還原前清空數(shù)據(jù)目錄
MariaDB [(none)]> show databases;  #此時(shí)數(shù)據(jù)庫已經(jīng)沒了
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

6）緊急還原

恢復(fù)完全備份和增量備份：
~]# systemctl stop mariadb  #停止服務(wù)
~]# innobackupex --apply-log --redo-only /backup/2018-06-14_10-44-57/  #整理完全備份的數(shù)據(jù)，因?yàn)樾枰Ａ魶]有做完的事務(wù)日志所以一定要記得加"--redo-only"選項(xiàng)
~]# innobackupex --apply-log --redo-only /backup/2018-06-14_10-44-57/ --incremental-dir=/backup/inc1/2018-06-14_10-52-05/  #在完全備份的基礎(chǔ)上將增量備份導(dǎo)入到一塊，這里是最新的增量備份，"--redo-only"選項(xiàng)可以不加，加上也可以，為了誤操作我就都加了
~]# ls /data/mysqldb/  #確認(rèn)一下數(shù)據(jù)庫目錄是否為空
~]# innobackupex --copy-back /backup/2018-06-14_10-44-57/  #導(dǎo)入備份數(shù)據(jù)
~]# chown -R mysql:mysql /data/mysqldb/  #記得修改數(shù)據(jù)的所屬組和所屬者
~]# vim my.cnf 加入skip_networking，防止此時(shí)用戶操作數(shù)據(jù)
~]# systemctl start mariadb  #啟動(dòng)服務(wù)，此時(shí)已經(jīng)恢復(fù)到了最新的備份時(shí)的狀態(tài)了

依靠二進(jìn)制日志，恢復(fù)最新增量備份到now的數(shù)據(jù)：
~]# cat /backup/2018-06-14_10-44-57/xtrabackup_binlog_info  #查看一下備份時(shí)的二進(jìn)制日志記錄點(diǎn)
	mariadb-bin.000011      35740416
~]# ls -1 /data/binlog/  #看看我們的二進(jìn)制日志文件記錄到哪里了
mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.000009
mariadb-bin.000010
mariadb-bin.000011
mariadb-bin.000012
mariadb-bin.000013
mariadb-bin.index
~]# mysqlbinlog --start-position=35740416 /data/binlog/mariadb-bin.000011 > /backup/binlog.sql  #將最新增量備份之后的二進(jìn)制日志記錄的數(shù)據(jù)導(dǎo)出來
~]# mysqlbinlog /data/binlog/mariadb-bin.000012 >> /backup/binlog.sql
~]# mysqlbinlog /data/binlog/mariadb-bin.000013 >> /backup/binlog.sql
編輯 /backup/binlog.sql 文件，將 "DROP TABLE `school`.`students` /* generated by server */" 刪除，撤銷誤刪除操作
MariaDB [(none)]> SET sql_log_bin=0;  #先臨時(shí)關(guān)閉二進(jìn)制日記記錄功能
MariaDB [(none)]> source /backup/binlog.sql  #導(dǎo)入增量備份之后的最新數(shù)據(jù)
查看確認(rèn)一下數(shù)據(jù)有沒有恢復(fù)完整，把my.cnf中的skip_networking刪除，重啟服務(wù)
到此已經(jīng)恢復(fù)到了最新的狀態(tài)~

5、使用Xtrabackup實(shí)現(xiàn)單表備份

1）備份單表

~]# innobackupex --include="testdb.testlog" /backup  #備份表數(shù)據(jù)
~]# mysql -e 'SHOW CREATE TABLE testdb.testlog' > /backup/desc_testdb_testlog.sql  #備份表空間
~]# mysql -e 'DROP TABLE testdb.testlog'  #模擬故障，刪除testlog表

2）還原單表

~]# innobackupex --apply-log --export /backup/2018-06-14_17-47-02/  #整理表數(shù)據(jù)
~]# vim /backup/desc_testdb_testlog.sql  #編輯創(chuàng)建表空間的語句，刪除以下字段
    Table   Create Table
    testlog
~]# mysql testdb  /backup/desc_testdb_testlog.sql  #導(dǎo)入表空間
~]# mysql testdb -e 'DESC testlog'  #查看是否導(dǎo)入成功
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(30) | YES  |     | NULL    |                |
| age   | int(11)  | YES  |     | 20      |                |
+-------+----------+------+-----+---------+----------------+
~]# mysql -e 'ALTER TABLE testdb.testlog DISCARD TABLESPACE'  #清除表空間
~]# cd /backup/2018-06-14_17-47-02/testdb/
testdb]# cp testlog.cfg testlog.exp testlog.ibd /var/lib/mysql/testdb/  #將表數(shù)據(jù)復(fù)制到庫目錄
~]# chown -R mysql:mysql /var/lib/mysql/testdb/  #修改所屬者和所屬組
~]# mysql -e 'ALTER TABLE testdb.testlog IMPORT TABLESPACE'  #導(dǎo)入表空間

總結(jié)

本篇文章就到這里了，希望可以給你帶來一些幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容！

您可能感興趣的文章:

MySQL 邏輯備份與恢復(fù)測試的相關(guān)總結(jié)
詳解Mysql之mysqlbackup備份與恢復(fù)實(shí)踐
MySQL5.7 mysqldump備份與恢復(fù)的實(shí)現(xiàn)
淺析MySQL 備份與恢復(fù)
詳解mysql的備份與恢復(fù)

標(biāo)簽：蘭州衡水崇左仙桃銅川湘潭黃山湖南

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL系列之十二備份與恢復(fù)》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。