一. 數據備份基礎知識
1. 備份：存儲的數據副本；
原始數據：持續改變；
恢復：把副本應用到線上系統；
僅能恢復至備份操作時刻的數據狀態；
2. 基于時間點的數據恢復:
即基于 binary logs的數據數據,可以根據指定時間點以后發生的事務來恢復數據
3. 為什么備份？
災難恢復：硬件故障（冗余）、軟件故障（bug）、自然災害、黑客攻擊、誤操作、…
測試；
4. 備份時應該注意事項：
能容忍最多丟失多少數據；
恢復數據需要在多長時間內完成；
需要恢復哪些數據；
5. 數據的恢復與備份都應該做恢復演練：
測試備份的可用性；
增強恢復操作效率；
…
6. 備份類型：
備份的數據的集范圍：
完全備份和部分備份
完全備份：整個數據集；
部分備份：數據集的一部分，比如部分表；
7. 完全備份、增量備份、差異備份：
完全備份
增量備份：僅備份自上一次完全備份或 增量備份以來變化的那部數據；
差異備份：僅備份自上一次完全備份以來變化的那部數據；
8. 物理備份、邏輯備份：
物理備份：復制數據文件進行備份；
邏輯備份：從數據庫導出數據另存在一個或多個文件中；
9. 根據數據服務是否在線：
熱備：讀寫操作均可進行的狀態下所做的備份；
溫備：可讀但不可寫狀態下進行的備份；
冷備：讀寫操作均不可進行的狀態下所做的備份；
10.備份需要考慮因素：
鎖定資源多長時間？
備份過程的時長？
備份時的服務器負載？
恢復過程的時長？
11. 備份策略：
完全+差異
完全+增量
備份手段：物理、邏輯
備份頻率:
基于數據變化量考慮；
基于可用的備份存儲空間考慮；
12. 備份什么？
數據
二進制日志、InnoDB的事務日志；
代碼（存儲過程、存儲函數、觸發器、事件調度器）
服務器的配置文件
13. 備份工具：
mysqldump：mysql服務自帶的備份工具；邏輯備份工具；
支持完全、部分備份；
InnoDB：支持熱備；
MyISAM：支持溫備；
cp/tar
lvm2：快照（請求一個全局鎖），之后立即釋放鎖，達到幾乎熱備的效果；物理備份；
注意：不能僅備份數據文件；要同時備份事務日志；
前提：要求數據文件和事務日志位于同一個邏輯卷；
xtrabackup：
由Percona提供，開源工具，支持對InnoDB做熱備，物理備份工具；
完全備份、部分備份；
完全備份、增量備份；
完全備份、差異備份；
mysqlhotcopy
基于select實現的冷備份工具
備份：SELECT cluase INTO OUTFILE 'FILENAME';
恢復：CREATE TABLE
導入：LOAD DATA
二. mysqldump：
工作機制:
由mysqldump 發起一個新會話,連接至mysql 服務器端,并執行SELECT * ,使用SELECT命令查詢并獲得
對應數據庫中的全部表數據,并重新建立一個相同配置的數據庫,生成INSERT命令, 將剛剛SELECT 獲得
的數據全部插入到新的數據庫中,以實現備份效果;
1.特性
支持邏輯備份、完全備份、部分備份；
mysqldump 無法備份索引,僅能備份數據,因此恢復時候,需要重建索引
2.其他二次封裝工具：
mydumper
phpMyAdmin
3.Usage:
mysqldump [OPTIONS] database_name [tables]
#備份指定數據或者表
注意: 此命令在還原前,不會先生成庫,因此需要自己先建立一個庫;
mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
#備份指定數據庫,可為多個
# –databases選項,會自動先創建庫,再進行備份
mysqldump [OPTIONS] –all-databases [OPTIONS]
#備份全部數據庫
-u user_name 指定登錄用戶
-h host_add 指定主機, 如localhost
-p passwd 密碼
注意: 輸出的備份文件,需要使用輸出重定向來實現
eg :
mysqldump -uroot -p -hlocalhost hellodb > hellodb.sql
4.基于MyISAM存儲引擎 的備份：
支持溫備，備份時要鎖定表；
-x, –lock-all-tables：鎖定所有庫的所有表，讀鎖,僅能讀,不能寫；
-l, –lock-tables：鎖定指定庫所有表；
5. 基于InnoDB存儲引擎 的備份：
支持溫備和熱備(基于事務利用隔離級別來實現熱備份)；
–single-transaction：創建一個事務，基于此快照執行熱備份；
注意: 需要確保對應庫下面的所有表,都是innodb 引擎
6. 其它選項：
-R, –routines：備份數據庫的 存儲過程和存儲函數；
#常為必須項
–triggers :備份數據的觸發器
#常為必須項
-E, –events :備份數據的事件調度器
–master-data[=#]
#定義指明備份時刻binlog 處于的日志文件以及具體位置
#注意: 需要先開啟binlog 功能
0: 不記錄,默認為不記錄
1：記錄為CHANGE MASTER TO語句，此語句不被注釋；
2：記錄為CHANGE MASTER TO語句，此語句被注釋；
示例:
[root@localhost mysql]# mysqldump -uroot -hlocalhost -p --master-data=2 hellodb > hellodb.sql.3[root@localhost mysql]vim hellodb.sql.3
-- MySQL dump 10.14 Distrib 5.5.44-MariaDB, for Linux (x86_64)---- Host: localhost Database: hellodb-- -------------------------------------------------------- Server version 5.5.44-MariaDB-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000001', MASTER_LOG_POS=245;
–flush-logs：鎖定表完成后，即進行日志刷新操作；
#可以是binlog 重新滾動, 以便恢復的時候,直接從binlog 第一個記錄開始備份,提高效率
備份注意事項:
特別注意備份時候的各表時間點必須同步,否則恢復時候會出錯, 因此備份多個數據庫時候,必須同時鎖定多個庫
備份一個數據庫時候,必須鎖定庫里面的全部表
7. 數據恢復詳解:
1) 示例:
mysqldump -uroot -p -hlocalhost hellodb > hellodb.sql
備份文件內容示例:
-- MySQL dump 10.14 Distrib 5.5.44-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: hellodb -- ------------------------------------------------------ -- Server version 5.5.44-MariaDB /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `classes` -- DROP TABLE IF EXISTS `classes`; #備份前,會先刪除同名的表,以避免沖突 /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `classes` ( ..........(略)
2) 基于binlog 的數據恢復示例:
操作: 新插入任意內容到hellodb庫中的courses 表,
刪除hellodb庫中的courses 表其中couseid=3的項目
利用binlog 恢復到刪除couseid=3前的數據
恢復步驟:
1. 準備好已經備份的數據文件hellodb.sql.3
2. 修改binlog 文件,將最后的操作事件剔除,
即使用mysqlbinlog–stop-position=### binlog_file > new_binlog_file
生成新的可以是用的binlog 文件
3. 進入mysql , 停用binlog: SET sql_log_bin=OFF;
4. 導入原始數據: \. /path to hellodb.sql.3
5. 導入binlog: \. /path to new_binlog_file
6. 啟用binlog: SET sql_log_bin=ON;
——–> 完成
1. 準備數據
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> INSERT INTO courses (course) VALUES ('aaa'),('bbbb');Query OK,
2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 8 | aaa |
| 9 | bbbb |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> DELETE FROM courses WHERE courseID=1;Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 8 | aaa |
| 9 | bbbb |
+----------+----------------+
8 rows in set (0.00 sec)
2. 修改binlog 并生成新的日志文件
[root@localhost mysql]# mysqlbinlog master-log.000001
......
# at 8614#161120 9:52:11 server id 1 end_log_pos 8716 Queryhread_id=8 exec_time=0error_code=0SET TIMESTAMP=1479606731/*!*/;DELETE FROM courses WHERE courseID=1/*!*/;
......
[root@localhost mysql]# mysqlbinlog --stop-position=8614 master-log.000001 > tmp.sql
MariaDB [(none)]>SET sql_log_bin=OFF;
MariaDB [(none)]> \. /root/hellodb.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)
.......
MariaDB [hellodb]> \. /var/lib/mysql/tmp.sqlQuery OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec).......
MariaDB [hellodb]> SELECT * FROM courses;+----------+----------------+| CourseID | Course |+----------+----------------+| 1 | Hamo Gong || 2 | Kuihua Baodian || 3 | Jinshe Jianfa || 4 | Taiji Quan || 5 | Daiyu Zanghua || 6 | Weituo Zhang || 7 | Dagou Bangfa || 8 | aaa || 9 | bbbb |+----------+----------------+9 rows in set (0.00 sec)
MariaDB [(none)]>SET sql_log_bin=ON;
作業：備份腳本
三. 基于lvm2的備份：
#無法基于腳本實現,使用不多,了解即可
前提：數據目錄位于邏輯卷，包含了數據文件和事務日志；
(1) 請求鎖定所有表；
mysql> FLUSH TABLES WITH READ LOCK;
(2) 記錄二進制文件事件位置；
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS;' >> /PATH/TO/SOME_POS_FILE
(3) 創建快照卷
lvcreate -L # -s -p r – SNAM-NAME /dev/VG-NAME/LV-NAME
(4) 釋放鎖
mysql> UNLOCK TABLES
(5) 掛載快照卷，并執行備份，備份完成后刪除快照卷；
(6) 周期性備份二進制日志；
percona：
InnoDB –> XtraDB (mariadb)
Innobackup –> Xtrabackup
四. Xtrabackup：
工作機制: 需要基于用戶名和密碼,連接到mysql 服務端,直接復制底層的數據塊,并保存到本地指定位置,以實現備份
備份文件自動以時間戳命名,并支持tar 歸檔或者壓縮存放
特別提醒:
需要手動備份/etc/myl.cnf 文件, 特定情景下,還需要備份 crontab, sudo 等文件
注意：未盡的內容，請參考官方文檔；
1.特性:
MyISAM：溫備，不支持增量備份；
InnoDB：熱備，增量；
物理備份，速率快、可靠；備份完成后自動校驗備份結果集是否可用；還原速度快；
2. Usage:
[innobackupex [–defaults-file=#] –backup | innobackupex [–defaults-file=#] –prepare] [OPTIONS]
#The innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program.
innobackupex 詳細使用,直接查詢man innobackupex即可,有詳細的說明;
3. 完全備份：
innobackupex –user=user_name –password=### /path to backup dir/
注意: 執行命令以后,必須確保在最后有提示 "completed OK " 才是最終備份成功!
4. 完全備份+binlog 還原：
完全備份：innobackupex –user –password= –host= /PATH/TO/BACKUP_DIR
恢復準備：innobackupex –apply-log /PATH/TO/BACKUP_DIR
#應用日志
數據恢復：innobackupex –copy-back
#數據拷貝(恢復)
注意：–copy-back需要在mysqld主機本地進行，mysqld服務不能啟動；
innodb_log_file_size可能要重新設定；
5. 完全備份+增量備份+binlog 還原
數據備份策略： 完全備份+增量備份 (或 完全備份+差異備份 )
增量備份: ( –incremental-basedir 指定的為第一次完全備份或者上一次增量備份)
innnobackup –user=## –password= –incremental /path to backup_dir
–incremental-basedir=/path to last_backup_dir
–incremental: 用于指明此次備份為增量備份
–incremental-basedir=/path to last_backup_dir
#用于指明基于哪個備份文件作增量, 該文件可以為完全備份,
#也可以為上一次增量備份產出的備份文件
備注:
差異備份:–incremental-basedir指定的為上一次完全備份
數據恢復準備：
恢復機制:
先將最后一次完全備份的數據恢復進去(–redo-only ,只執行,不提交),
再將后續若干次增量備份的數據依次恢復到利用完全備份所恢復數據中
直到最后一個增量備份文件導入,才執行提交(即不適用–redo-only)
此后才使用binlog 回放恢復后續數據
innobackupex –apply-log –-redo-only BASEDIR(完全備份數據)
innobackupex –apply-log –redo-only BASEDIR(完全備份數據) —incremental-dir=INCREMENTAL-DIR(距完全備份最近的
一次增量備份數據)
………(重復合并后續的增量備份文件)
innobackupex –apply-log BASEDIR(已經合并的完全備份數據) —incremental-dir=INCREMENTAL-DIR(最后一次增量備
份的數據)
(此時取消–redo-only選項)
注意:由于部分數據,可能存在已經執行,但是并未commit提交的情況,為了防止恢復數據時候重復提交,因此
在恢復時候,需要使用–redo-only ,只執行,不提交,直到將最后一個增量備份恢復時,才提交,即不使用
–redo-only 選項
最后恢復工作：
innobackupex –copy-back BASEDIR(已經合并完后續全部增量備份的完全備份數據文件)
6.備份單庫：
xtrabackup 支持單庫備份,使用 –databases database_name 即可
# 此功能極少用到,了解即可
7. 安裝與程序環境
1) 安裝:需要單獨安裝percona-xtrabackup包
建議 使用yum 來安裝RPM包,以解決依賴關系
[root@localhost ~]# yum install ./percona-xtrabackup-2.3.2-1.el7.x86_64.rpm
2) 程序環境:
[root@localhost ~]# rpm -ql percona-xtrabackup/usr/bin/innobackupex #二次封裝的xtrabackup /usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/ xbstream /usr/bin/xtrabackup/usr/share/doc/percona-xtrabackup-2.3.2 /usr/share/doc/percona-xtrabackup-2.3.2/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz
備份文件說明:
eg :
innobackupex –user=root –password= /mydate
注意: xtrabckup_logfile 為二進制日志文件, 不可以直接CAT 查看, 容易損壞文件
[root@localhost 2016-11-20_12-31-34]# vim xtrabackup_checkpoints
backup_type = full-backuped
#此處可以看到備份類型from_lsn = 0 #起始日志序列號to_lsn = 1840910 last_lsn = 1840910 #結束日志序列號compact = 0 #是否壓縮存放recover_binlog_info = 0#定義是否恢復二進制日志信息
~
[root@localhost 2016-11-20_12-31-34]# cat backup-my.cnf #mysql 配置信息備份 # This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] innodb_checksum_algorithm=innodb innodb_log_checksum_algorithm=innodb innodb_data_file_path=ibdata1:10M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=5242880 innodb_fast_checksum=false innodb_page_size=16384 innodb_log_block_size=512 innodb_undo_directory=. innodb_undo_tablespaces=0
————————————————————————————————————————-
8. 數據恢復詳解:
1) 完全備份+binlog 下的數據恢復步驟:
1.數據恢復時候,mysql服務需要停止,并且mysql工作目錄,需要清空(一般為/var/lib/mysql/)
2.做恢復數據的主機,也需要安裝percona-xtrabackup
3.應用日志:
同步日志文件中的數據(已經提交執行commit, 未提交的則rockball)
innnobackupex –apply-log /path to backup_dir
需要completed OK! 才最終完成)
4.數據還原:
將數據從備份目錄復制回實際數據庫目錄
innobackupex –copy-back /path to backup_dir
5.權限還原:
將已經恢復的數據,修改屬主屬組為mysql
chown -R mysql/*
6. 啟動服務—>完成
2) 完全備份+增量備份+binlog 數據恢復:
情景: 已經做過一次完全備份,并做了一次增量備份,但在做下一次增量備份之前,
mysql-server 掛了,此時只能基于完全備份+增量備份的數據文件,利用
binlog 日志來回放事件,恢復數據
步驟:
1.數據恢復時候,mysql服務需要停止
2.需要的話,恢復已經備份的my.cnf,sudo ,crontab 等文件
3.查詢最后一次增量備份在binlog 中的起始位置:
即查看最后一次增量備份數據中的xtrabckup_binlog_info
[root@localhost 2016-11-20_15-33-31]# cat xtrabackup_binlog_info master-log.000001 24686
4. 將掛掉的mysql服務器上的binlog 日志在最后一次增量備份起始位置以后的信息,都導出
保存為代用的日志文件
mysqlbinlog –start-position=24686 master-log.000001 > /mydate/tmp.sql
5. 將完全備份數據,增量備份數據,binlog 都拷貝到需要恢復的主機上
6. 在需要恢復的主機上,首先利用完全備份的數據恢復數據庫
innobackupex –apply-log –redo-only /path to full_backup_file
7. 繼續利用距完全備份最近的增量備份文件來恢復數據,即將后續的增量備份合并到完全備份中
innobackupex –apply-log –redo-only /path to full_backup_file –incremental-dir=/path to incre_backup_dir
#合并以后,查看完全備份文件中的xtrabackup_checkpoints ,其中的lsn(日志序列號)會
變為合并進來的增量備份的lsn
8. 若還存在后續的增量備份,則重復第7步,繼續將后續的增量備份合并到完全備份文件中
innobackupex –apply-log –redo-only /path to full_backup_file –incremental-dir=/path to incre_backup_dir#
9. 合并最后一個增量備份文件,將–redo-only 取消(即執行提交)
innobackupex –apply-log /path to full_backup_file –incremental-dir=/path
to the_last_incre_backup_dir#
10.執行最后恢復操作
innobackupex –copy-back BASEDIR(已經合并完后續全部增量備份的完全備份數據文件)
11.權限還原:
將已經恢復的數據,修改屬主屬組為mysql
chown -R mysql/*
12.啟動mysql ,并進去mysql ,暫停二進制日志記錄 sql_log_bin
SET sql_log_bin=OFF;
13. 導入準備好的二進制日志文件,回放后續事件:
mysql -uroot -p -hlocalhost < /mydate/tmp.sql
14. 檢查數據恢復情況,若成功還原,則啟用二進制日志記錄
SET sql_log_bin=ON;
————->完成;
原創文章，作者：ldt195175108，如若轉載，請注明出處：http://www.www58058.com/60153
