MySQL數據庫備份與恢復-No.2 技术頭條網

MySQL數據庫備份與恢復-No.2

2019-07-03 14:26:19 cyylog

MySQL數據備份

databases　Binlog my.conf

所有備份數據都應放在非數據庫本地，而且建議有多份副本。

測試環境中做日常恢復演練，恢復較備份更為重要。

備份： 能夠防止由於機械故障以及人為誤操作帶來的數據丟失，例如將數據庫文件保存在了其它地方。

冗餘： 數據有多份冗餘，但不等備份，只能防止機械故障還來的數據丟失，例如主備模式、數據庫集群

備份過程中必須考慮因素：

1. 數據的一致性

2. 服務的可用性

邏輯備份：備份的是建表、建庫、插入等操作所執行SQL語句（DDL DML DCL），適用於中小型數據庫，效率相對較低。

mysqldump 
mydumper

物理備份：直接複製數據庫文件，適用於大型數據庫環境，不受存儲引擎的限制，但不能恢復到不同的MySQL版本。

tar,cp 
xtrabackup 
inbackup 
lvm snapshot

備份又有以下三種：

完全備份

增量備份

差異備份

tar數據備份（物理）

備份期間：服務不可用

備份的過程：【完全物理備份】

1. 停止數據庫

2. tar備份數據

3. 啟動數據庫

[root@cyy ~]# systemctl stop mysqld

[root@cyy ~]# mkdir /backup

[root@cyy ~]# tar -cf /backup/`date +%F`-mysql-all.tar /var/lib/mysql

注：備份文件應該複製其它服務器或存儲上

還原的過程：

1. 停止數據庫

2. 清理環境

3. 導入備份數據

4. 啟動數據庫

5. binlog恢復

[root@cyy ~]# systemctl stop mysqld

[root@cyy ~]# rm -rf /var/lib/mysql/*

[root@cyy ~]# tar -xf /backup/2019-06-07-mysql-all.tar -C /

[root@cyy ~]# systemctl start mysqld

lvm快照（物理）

Lvm快照實現物理備份 + Binlog

數據一致，服務可用

注：MySQL數據lv 和將要創建的snapshot 必須在同一VG，因此VG必須要有一定的剩於空間

優點：

幾乎是熱備 (創建快照前把表上鎖，創建完後立即釋放)

支持所有存儲引擎

備份速度快

無需使用昂貴的商業軟件(它是操作系統級別的)

缺點：

可能需要跨部門協調(使用操作系統級別的命令，DBA一般沒權限)

無法預計服務停止時間

數據如果分佈在多個捲上比較麻煩(針對存儲級別而言)

操作流程：

1、flush table with read locak; 
2、create snapshot 
3、show master status;　show slave status; [可選] 
4、unlock tables; 
5、Copy files from the snapshot 
6、Unmount the snapshot. 
7、Remove snapshot

正常安裝MySQL：

1. 安裝系統 
2. 準備LVM，例如 /dev/vg_tianyun/lv-mysql，mount /var/lib/mysql 
3. 安裝MySQL，默認datadir=/var/lib/mysql =====================================================
MySQL運行一段時間，數據並沒有存儲LVM：將現在的數據遷移到LVM 

1. 準備lvm及文件系統  

[root@cyy ~]# lvcreate -n lv-mysql -L 2G datavg 
[root@cyy ~]# mkfs.xfs /dev/datavg/lv-mysql

2. 將數據遷移到LVM 
[root@cyy ~]# systemctl stop mysqld 
[root@tianyun ~]# mount /dev/datavg/lv-mysql /mnt/ //臨時掛載點 

[root@tianyuncyy ~]# cp -a /var/lib/mysql/* /mnt //將MySQL原數據鏡像到臨時掛載點 
[root@cyy ~]# umount /mnt/ 

[root@cyy ~]# vim /etc/fstab //加入fstab開機掛載 /dev/datavg/lv-mysql /var/lib/mysql xfs defaults 0 0 

[root@cyy ~]# mount -a 
[root@cyy ~]# chown -R mysql.mysql /var/lib/mysql
[root@cyy ~]# systemctl start mysqld =====================================================

LVM快照備份流程：

1. 加全局讀鎖 
mysql> flush tables with read lock;

2. 創建快照 
[root@cyy ~]# lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql [root@cyy ~]# mysql -p'(cyyCyy..123)' -e 'show master status' > /backup/`date +% F`_position.txt

3. 釋放鎖 mysql> unlock tables;
1-3必須同一會話中完成 
[root@cyy ~]# echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/ datavg/lv-mysql; UNLOCK TABLES;" | mysql -p'(cyyCyy..123)' 
[root@cyy ~]# echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/ datavg/lv-mysql; " | mysql -p'(cyyCyy..123)'

4. 從快照中備份 
[root@cyy ~]# mount -o ro /dev/datavg/lv-mysql-snap /mnt/ //xfs -o ro,nouuid 
[root@cyy ~]# cd /mnt/ 
[root@cyy mnt]# tar -cf /backup/`date +%F`-mysql-all.tar ./*

5. 移除快照 
[root@cyy ~]# cd; umount /mnt/ 
[root@cyy ~]# lvremove -f /dev/vg_cyy/lv-mysql-snap

LVM快照恢復流程：

1. 停止數據庫 
2. 清理環境 
3. 導入數據 
4. 修改權限 
5. 啟動數據庫 
6. binlog恢復 
[root@cyy ~]# tar -xf /backup/2019-06-07-mysql-all.tar -C /var/lib/mysql/ 
[root@cyy ~]# systemctl start mysqld

Tar解壓的注意事項：

tar -tf 2019-06-07-mysql-all.tar |less

腳本 + Cron

#!/bin/bash/env bash 

#LVM backmysql... 

back_dir=/backup/`date +%F` 
[ -d $back_dir ] || mkdir -p $back_dir 

echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql; \\ 
UNLOCK TABLES;" | mysql -p'(Cyy..123)'

mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/ 

rsync -a /mnt/ $back_dir 

if [ $? -eq 0 ];then 
 
umount /mnt/ 
lvremove -f /dev/datavg/lv-mysql-snap 
fi