Mysql之備份及還原相關實驗

本節索引

實驗一：冷備份數據庫并還原

實驗二：結合LVM邏輯卷實現幾乎熱備

實驗三：數據庫數據文件損壞后，利用mysqldump還原至最新狀態

實驗四：誤刪除表后，利用mysqldump還原至最新狀態

實驗五：Xtrabackup完全備份及還原

實驗六：Xtrabackup完全，增量備份及還原

實驗七：Xtrabackup單表導出和導入

 

實驗準備：

CentOS虛擬機兩臺，一臺作為數據庫服務器，一臺作為中間存放備份的服務器

數據庫服務器：

操作系統：CentOS7.4 ? ? ? ? ? ? ?IP：192.168.30.10 ? ? ? 提示符顏色：藍色

中間服務器：

操作系統：CentOS7.5 ? ? ? ? ? ? IP：192.168.30.17 ? ? ? ? 提示符顏色：黃色

兩臺主機安裝MariaDB 5.5.56，并且開啟二進制日志功能

 

實驗一：冷備份數據庫并還原

備份：

數據庫初始狀態為：

1停止mysql服務

systemctl stop mariadb.service

2打包并壓縮備份文件

（注：生產環境應將備份文件與數據庫服務器分開存放）

tar Jcvf /data/all.tar.xz /var/lib/mysql/

3模擬破壞數據庫

rm -rf /var/lib/mysql/*

 

還原：

tar xvf /data/all.tar.xz

mv? var/lib/mysql/* /var/lib/mysql/

確認文件已生成

ls /var/lib/mysql/

重新啟動服務：

systemctl start mariadb.service

確認數據庫已恢復

 

實驗二：結合LVM邏輯卷實現幾乎熱備

備份：

1.創建邏輯卷

新建一個磁盤分區/dev/sda6

pvcreate /dev/sda6

vgcreate vg0 /dev/sda6

lvcreate -L 4G -n lv_mysql vg0

lvcreate -L 4G -n lv_binlog vg0

mkfs.xfs lv_mysql lv_binlog

mkdir /data/{mysql,binlog}

mount /dev/vg0/lv_mysql /data/mysql/

mount /dev/vg0/lv_binlog /data/binlog/

2修改目錄權限

chown -R mysql.mysql /data/mysql/

chown -R mysql.mysql /data/binlog/

3修改配置文件

vim /etc/my.cnf

[mysqld]

datadir=/data/mysql

log_bin=/data/binlog/mysql-bin

4重啟mysql服務

systemctl restart mariadb

5添加讀鎖，防止有用戶繼續寫入數據

mysql> flush tables with read lock;

刷新日志

mysql> flush logs;

查看當前二進制日志

mysql> show master logs;

記錄最新的二進制日志為：mariadb-bin.000004

6創建快照

lvcreate -L 1G -n lv_mysql_snap -s -p r /dev/vg0/lv_mysql

7解鎖數據庫

Mysql> unlock tables；

8掛載邏輯卷快照

mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap? /mnt

9將邏輯卷文件備份至backup目錄

mkdir /backup

cp -a /mnt/* /backup/

10刪除快照,否則影響用戶訪問數據庫速度

umount /mnt

lvremove /dev/vg0/lv_mysql_snap

備份過程結束

 

Mysql >? create databases db2

Mysql >? create databases db3

 

模擬數據庫損壞:

rm –rf ?/data/mysql/*

 

還原：

停止mysql服務

systemctl stop mariadb

拷貝備份文件至數據庫目錄

cp -av /backup/*? /data/mysql/

開啟mysql服務

systemctl? start mariadb

此時數據庫已還原至快照時的狀態

二進制繼續修復db1，db2

mysql> flush tables with read lock;

mysqlbinlog –start-position=245 mysql-bin.000004 > /backup/bin.sql

mysqlbinlog mysql-bin.000005 >> /backup/bin.sql

確?；謴瓦^程中，沒有用戶可以讀取或寫入

vim /etc/my.cnf

[mysqld]

skip_networking

或iptables –A

重啟mysql服務使生效

systemctl ?restart mariadb

導入二進制文件

mysql < /backup/bin.sql

此時數據庫已還原至最新狀態

最后，恢復用戶訪問

vim /etc/my.cnf

[mysqld]

skip_networking????????????? 去掉

或清除防火墻

systemctl restart mariadb

 

 

實驗三：數據庫數據文件損壞后，利用mysqldump還原至最新狀態

初始數據庫為：

利用mysqldump生成備份文件

mysqldump -A -F –single-transaction –master-data=2>/backup/fullbak_`date +%F`.sql

ls /backup -l

mysql> create database db2

mysql> create database db3

此時數據庫為：

最新二進制日志為：mariadb-bin.000006

模擬日常將備份傳至中間主機：192.168.30.17

接下來為了方便演示，我們之間在中間主機進行數據庫恢復：

rm? -rf? /var/lib/mysql

 

還原：

利用mysqlbinlog命令可查看二進制日志，看到創建db2，db3發生在position245之后

mysqlbinlog mariadb-bin.000006

首先還是停止mysql服務

systemctl stop mariadb.service

禁止其他用戶訪問或修改數據庫

vim /etc/my.cnf

[msyqld]

skip_networking

生成二進制備份文件

mysqlbinlog –start-position=245? mysql-bin.000012 > bin.sql

重新開啟mysql服務

systemctl start mariadb.service

修復至mysqldump全備份階段

mysql < fullbak_2018-06-14.sql

繼續二進制修復db2，db3

mysql < bin.sql

此時，數據庫修復完成，最后別忘了修改配置文件

vim /etc/my.cnf

[msyqld]

skip_networking?? 去掉此行

重啟mysql服務

systemctl restart mysql

 

 

 

實驗四：誤刪除表后，利用mysqldump還原至最新狀態

前期準備:開啟二進制日志功能

實驗場景：模擬周日進行了全備份，周一上午10表被誤刪除，10點之前與之后均發生了其他操作，嘗試還

原數據庫中誤刪除的表并且操作不發生丟失

數據庫初始狀態

模擬周日進行完全備份：

模擬周一上午10點數據發生了修改(students表原總行數為25)

Mysql >? insert students values(26,’mysql’,’30’,’M’,2,3);

模擬周一上午10點某張表被刪除

Mysql >? drop table students;

模擬周一上午10點至10點10分間又進行了其他操作

insert teachers values(5,’mht’,’46’,’m’)；

 

周一上午10點10分，發現students表丟失

 

還原：

Mysql > ?flush tables with read lock;

查看當前二進制日志為：mariadb-bin.000003

Mysql > ?show master logs;

刷新二進制日志

Mysql > ?flush logs

查看全備份的position，確定全備份到目前之間的所有日志文件及position

less /backup/fullbak

mysqlbinlog –start-position=245 /var/lib/mysql/mariadb-bin.000003 > /backup/bin.sql

mysqlbinlog /var/lib/mysql/mariadb-bin.000004 >> /backup/bin.sql

找到想要撤銷的操作，刪除

vim /backup/bin.sql

停止mysql服務

systemctl stop mariadb.service

禁止其他用戶訪問或修改數據庫

vim /etc/my.cnf

[msyqld]

skip_networking

刪除庫下數據(不用緊張^^)

rm -rf /var/lib/mysql/*

重新啟動mysql服務

systemctl start mariadb.service

導入周日的全備份

mysql < /backup/fullbak_2018-06-14.sql

繼續導入修改過的二進制備份

mysql < /backup/bin.sql

我們看到students表還原成功，而且不論是表刪除之前還是之后發生的操作也都恢復回來了

此時，數據庫修復完成，最后別忘了修改配置文件

vim /etc/my.cnf

[msyqld]

skip_networking?? 去掉此行

重啟mysql服務

systemctl restart mysql

 

 

實驗五：Xtrabackup完全備份及還原

安裝xtrabackup包(epel)

yum install percona*

數據庫初始為：

利用innobackupex命令完全備份

Innobackupex ?[ –user=root –password=!@#$% ] ?/backup/

模擬日常將備份傳至中間主機：192.168.30.17

scp -r /backup/ 192.168.30.17:/

 

還原：

接下來為了方便演示，我們之間在中間主機進行數據庫恢復：

停止mysql服務

systemctl stop mariadb.service

清空數據庫

rm –rf /var/lib/mysql/*

整合完全備份

innobackupex –apply-log /backup/2018-06-14_17-08-53/

自動復制到數據庫目錄，但所屬主為root，需要變更

innobackupex –copy-back /backup/2018-06-14_17-08-53/

chown -R mysql.mysql /var/lib/mysql/

重新開啟mysql服務

systemctl start mariadb.server

數據庫恢復成功

 

 

實驗六：Xtrabackup完全，增量備份及還原

初始數據庫為：

mkdir /backup/

mkdir /backup/{inc1,inc2}

Innobackupex ?[ –user=root –password=!@#$% ] ?/backup/

模擬第一次修改數據庫

mysql> insert students values(26,’test1′,18,’M’,1,2)；

第一次增量備份

innobackupex? –incremental /backup/inc1? –incremental-basedir=

/backup/2018-06-14_17-50-43

模擬第二次修改數據庫

mysql> insert students values(27,’test1′,28,’M’,1,2)；

第二次增量備份

Innobackupex? –incremental/backup/inc2? –incremental-basedir=

/backup/inc1/2018-06-14_17-55-52

此時backup目錄結構

復制到遠程主機做備份：

scp -r /backup/ 192.68.30.17:/

 

還原：

接下來為了方便演示，我們之間在中間主機進行數據庫恢復：

停止mysql服務

systemctl stop mariadb.service

清空數據庫

rm –rf /var/lib/mysql/*

整合完全備份：

innobackupex –apply-log –redo-only /backup/2018-06-14_17-50-43/

整合第一次增量備份：

innobackupex –apply-log –redo-only /backup/2018-06-14_17-50-43/ –incremental-dir /backup/inc1/2018-06-14_17-55-52

整合第二次增量備份：

innobackupex –apply-log –redo-only /backup/2018-06-14_17-50-43/ –incremental-dir /backup/inc2/2018-06-14_17-59-00

自動復制到數據庫目錄，但所屬主為root，需要變更

innobackupex –copy-back /backup/2018-06-14_17-50-43/

更改屬主為mysql

chown -R mysql.mysql /var/lib/mysql/*

重新開啟mysql服務

systemctl start mariadb.service

此時，我們看到第一次修改，第二次修改的數據也已還原

 

 

 

實驗七：Xtrabackup單表導出和導入

前期準備：必須設置獨立表空間

vim /etc/my.cnf

[mysqld]

innodb_file_per_table

初始表信息為:

1單表備份

innobackupex –include=”hellodb.students” /backup/

2備份表結構

mysql -e ‘show create table hellodb.students’ > students.sql

刪除其中的多余部分，留下下圖所示內容

vim students.sql

3模擬破壞表

mysql -e ‘drop table hellodb.students’

還原：

整合備份

innobackupex –apply-log –export /backup/2018-06-14_19-03-21

導入表結構

mysql hellodb < students.sql

此時表結構已生成，但數據為空

刪除表空間

mysql -e ‘alter table hellodb.students discard tablespace’

復制相關文件到hellodb目錄

cd /backup/2018-06-14_19-37-02/hellodb/

cp students.cfg students.exp students.ibd /data/mysql/hellodb/

修改hellodb目錄下文件權限

chown mysql.mysql /data/mysql/hellodb/*

mysql>alter table students import tablespace;

此時數據已恢復完成