MySQL的全量、增量備份與恢復
- MySQL備份的分類
- MySQL完全備份
- mysqldump備份庫
- 恢復資料庫
- MySQL備份思路
- 全量備份的shell腳本
- MySQL增量備份
- 開啟增量備份
- 增量恢復三種方式
- 一般恢復
- 基于位置的恢復
- 增量備份的shell腳本
- 制定企業備份策略的思路
MySQL備份的分類
從物理與邏輯的角度，備份可分為
-
物理備份：對資料庫作業系統的物理檔案（如資料檔案、日志檔案等）的備份
- 物理備份又可以分為脫機備份（冷備份）和聯機備份（熱備份）
- 冷備份：是在關閉資料庫的時候進行的
- 熱備份：資料庫處于運行狀態，這種備份方法依賴于資料庫的日志檔案
- 物理備份又可以分為脫機備份（冷備份）和聯機備份（熱備份）
-
邏輯備份：對資料庫邏輯組件（如表等資料庫物件）的備份
從資料庫的備份策略角度，備份可分為
- 完全備份：每次對資料進行完整的備份
- 差異備份：備份那些自從上次完全備份之后被修改過的檔案
- 增量備份：只有那些在上次完全備份或者增量備份后被修改的檔案才會被備份
MySQL完全備份
- 完全備份是對整個資料庫的備份、資料庫結構和檔案結構的備份
- 完全備份保存的是備份完成時刻的資料庫
- 完全備份是增量備份的基礎
完全備份的優點
- 備份與恢復操作簡單方便
完全備份的缺點
- 資料存在大量的重復
- 占用大量的備份空間
- 備份與恢復時間長
在生產環境中，完全備份和增量備份都會使用，需要制定合理高效的方案達到備份資料的目的，避免資料丟失造成的嚴重的后果
MySQL資料庫的完全備份可以采用用多種方式
- 直接打包資料庫檔案夾，如/usr/local/mysql/data
tar -zcvf data.tar.gz data
[root@maomao mysql]# ls
bin COPYING data data.tar.gz docs include lib man README share support-files
- 使用專用備份工具 mysqldump
MySQL自帶的備份工具，相當方便對MySQL進行備份
通過該命令工具可以將指定的庫、表或全部的庫匯出為SQL腳本，在需要恢復時可進行資料恢復
mysqldump備份庫
mysqldump命令對單個庫進行完全備份
語法：
mysqldump -u 用戶名 -p [密碼] [選項] [資料庫名] > /備份路徑/備份檔案名
mysqldump -uroot -p school > /home/school.sql
Enter password:
[root@maomao home]# ls
maomao school.sql
mysqldump命令對多個庫進行完全備份
語法：
mysqldump -u 用戶名 -p [密碼] [選項] --databases 庫名 1 [庫名2] … > /備份路徑/備份檔案名
mysqldump -uroot -p --databases school maomao > /home/school-maomao.sql
Enter password:
[root@maomao home]# ls
maomao school-maomao.sql
對所有庫進行完全備份
語法：
mysqldump -u 用戶名 -p [密碼] [選項] --all-databases > /備份路徑/備份檔案名
mysqldump -uroot -p --all-databases > /home/all-data.sql
[root@maomao home]# ls
all-data.sql maomao school-maomao.sql
對某個特定的表進行備份
語法：
mysqldump -u 用戶名 -p [密碼] [選項] 資料庫名 表名 > /備份路徑/備份檔案名
mysqldump -uroot -p school student >/home/school_student.sql
Enter password:
[root@maomao home]# ls
all-data.sql maomao school-maomao.sql school_student.sql
恢復資料庫
使用mysqldump命令匯出的SQL備份腳本，在進行資料恢復時可使用以下方法匯入
- source命令
- mysql命令
使用source恢復資料庫
- 登錄到MySQL資料庫
- 執行source 備份sql腳本的路徑
因為之前已經備份過了 模擬資料丟失 然后恢復
直接把庫刪了
mysql [school]>drop database school;
Query OK, 6 rows affected (0.01 sec)
進入sql腳本的目錄 登錄mysql之后進行恢復
mysql [school]>source school.sql
mysql [school]>show tables;
+------------------+
| Tables_in_school |
+------------------+
| category |
| grade |
| result |
| student |
| subject |
| t_student |
+------------------+
6 rows in set (0.00 sec)
使用mysql命令恢復庫資料
語法：
mysql -u 用戶名 -p [密碼] 庫名 < 庫備份腳本的路徑
先把maomao庫刪了
mysql [(none)]>drop database maomao;
Query OK, 1 row affected (0.01 sec)
然后創建新的maomao庫
create database maomao;
恢復
mysql -uroot -p maomao </home/maomao.sql
使用mysql命令恢復表資料
恢復表資料同樣可以使用source或者mysql命令
source恢復表的操作與恢復庫的操作相同
當備份檔案中只包含表的備份，而不包括創建庫的陳述句時，必須指定庫名，且目標庫必須存在
語法：
mysql -u 用戶名 -p [密碼] 庫名 < 表備份腳本的路徑
mysql -uroot -p school </home/school_student.sql
MySQL備份思路
- 定期實施備份，制定備份計劃或者策略，并嚴格遵守
- 除了進行完全備份，開啟MySQL服務器的日志功能是很重要的
- 完全備份加上日志，可以對MySQL進行最大化還原
- 使用統一的和易理解的備份檔案名稱
不要使用backup1、backup2等這樣沒有意義的名字
推薦使用庫名或者表名加上時間的命名規則
全量備份的shell腳本
首先先創建一個專門備份的用戶并且授權
create user backup identified by '123';
grant all on school.* to backup@'localhost' identified by "123";
#!/bin/bash
#auto backup mysql db
#before you back ,please grant to the user backup
#grant all on discuz.* to backup@'localhost' identified by "123456"
#flush privileges
BAK_DIR=/back
BAK_FILE=/back/full_`date +%Y%m%d`
MYSQLDB=school
MYSQLUSR=backup
MYSQLPWD=123
MYSQLCMD=/usr/bin/mysqldump
if [ $UID -ne 0 ];then
echo "You must to be use root"
exit 1
fi
if [ ! -d $BAK_DIR ];then
mkdir -p $BAK_DIR && echo -e "\033[32mcreate the backdir success\033[0m"
else
echo "THIS $BAK_DIR IS EXSITS..."
fi
mysqldump -u$MYSQLUSR -p$MYSQLPWD $MYSQLDB > $BAK_FILE.$MYSQLDB.sql
if [ $? -eq 0 ];then
echo -e "\033[32mTHE mysql backup $MYSQLDB successful!\033[0m"
else
echo -e "\033[32mTHE mysql backup $MYSQLDB faild!\033[0m"
fi
# 洗掉超過日期的備份
find /back -mtime +30 |xargs rm -rf
執行腳本
[root@maomao home]# bash mysql.sh
create the backdir success
mysqldump: [Warning] Using a password on the command line interface can be insecure.
THE mysql backup school successful!
備份成功
[root@maomao back]# ls
full_20210413.school.sql
MySQL增量備份
- 增量備份就是備份自上一次備份之后增加或變化的檔案或者內容
增量備份的特點
- 沒有重復資料，備份量不大，時間短
- 恢復麻煩：需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復
MySQL沒有提供直接的增量備份方法
可以通過MySQL提供的==二進制日志（binary logs）==間接實作增量備份
MySQL二進制日志對備份的意義
- 二進制日志保存了所有更新或者可能更新資料庫的操作
- 二進制日志在啟動MySQL服務器后開始記錄，并在檔案達到max_binlog_size所設定的大小或者接收到flush logs命令后重新創建新的日志檔案
- 只需定時執行flush logs方法重新創建新的日志，生成二進制檔案序列，并及時把這些舊的日志保存到安全的地方就完成了一個時間段的增量備份
開啟增量備份
實作增量備份之前需要開啟二進制日志功能，開啟而僅僅只日志功能有兩種方式：
mysql [(none)]>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
在沒開啟之前 log_bin的value是OFF
-
在MySQL組態檔的 [mysqld] 項中加入log-bin=filepath
[mysqld] log_bin=mysql-bin log_bin_index=mysql-bin.index 重啟mysql service mysqld restart 再次查看 mysql [(none)]>show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) -
使用mysqld --log-bin=filepath
/etc/init.d/mysqld restart --log-bin=mysql-bin
開啟二進制日志功能后 在mysql目錄的data里面會出現日志檔案
確定字符集使用的是utf-8的形式，避免出現亂碼問題
mysql [(none)]>show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
如果不是utf8 編輯my.cnf檔案
添加
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
然后使用mysqldump命令，對資料庫或者表進行全量備份
每天進行增量備份操作，使用mysqladmin的選項flush-logs生成新的二進制檔案
生成新的二進制檔案
mysqladmin -uroot -p123 flush-logs
添加新的資料
mysql [school]>insert into student (studentno,loginpwd,studentname,gender,gradeid,phone,address,borndate,email,identitycard)
values (1008,'123456','豬豬','男',1,'13800002009','廣東深圳','1999-1-30','text250@qq.com','123456199001011999');
Query OK, 1 row affected (0.00 sec)
insert into student (studentno,loginpwd,studentname,gender,gradeid,phone,address,borndate,email,identitycard)
values (1009,'123456','小宇','男',1,'13800002549','山西太原','1998-8-3','text387@qq.com','123456199001011387');
查看二進制日志
注意：如果遇到亂碼可以添加引數進行解碼
mysqlbinlog mysql-bin.000002
解碼
mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000002
解碼后：
把二進制檔案復制到保存備份的目錄，完成增量備份
cp mysql-bin.000002 /back
ls /back
full_20210413.school.sql mysql-bin.000002
增量恢復三種方式
- 一般恢復
- 基于位置恢復
就是將某個起始時間的二進制日志匯入資料庫中，從而跳過某個發生錯誤的時間點實作資料的恢復 - 基于時間點恢復
使用基于時間點的恢復，可能會出現在一個時間點里既同時存在正確的操作又存在錯誤的操作，所以我們需要一種更為精確的恢復方式
一般恢復
mysqlbinlog [--no-defaults] 增量備份檔案 | mysql -u 用戶名 -p
增量恢復之前需要全量恢復
mysql -uroot -p school </back/full_20210413.school.sql
mysqlbinlog /back/mysql-bin.000002 |mysql -uroot -p
恢復成功
基于位置的恢復
恢復資料到指定位置
mysqlbinlog --stop-position=’操作 id’ 二進制日志 |mysql -u 用戶名 -p 密碼
從指定的位置開始恢復資料
mysqlbinlog --start-position=’操作 id’ 二進制日志 |mysql -u 用戶名 -p 密碼
恢復資料到指定位置
使用delete洗掉插入的兩條資料
delete from student where identitycard=123456199001011999;
delete from student where identitycard=123456199001011387;
需要去查看一下備份日志里的位置點
找到位置點為499
mysqlbinlog --stop-position='499' /back/mysql-bin.000002 |mysql -uroot -p123
驗證：
只恢復了499位置點上面的sql
從指定的位置開始恢復資料
先洗掉資料
delete from student where identitycard=123456199001011999;
跳過第一條資料的499，只恢復后面那條資料
mysqlbinlog --start-position='574' /back/mysql-bin.000002 |mysql -uroot -p123
增量備份的shell腳本
#!/bin/bash
BAK_DIR=/back
MYSQLUSR=root
MYSQLPWD=123
# 重繪二進制日志
mysqladmin -u$MYSQLUSR -p$MYSQLPWD flush-logs
# 獲取最近一次的增量備份檔案
newlog=`ls -lt /usr/local/mysql/data |grep mysql-bin.0 |awk 'NR==2{print $9}'`
newlog="/usr/local/mysql/data/"$newlog
# 將備份日志保存到相應的目錄
cp $newlog $BAK_DIR
# 洗掉超過日期的備份
find /back -mtime +30 |xargs rm -rf
制定企業備份策略的思路
制定企業備份策略要根據企業資料庫的實際讀寫的頻繁性與資料的重要性進行
- 資料更新頻繁，則應該進行較為頻繁的備份，
- 資料較為重要，則在有適當更新時進行備份
- 在資料庫壓力小的時候進行全量備份，如一周一次，然后每天進行增量備份，并且實作主從復制架構
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/275755.html
標籤：其他
上一篇：hive sql系列（九）