MariaDB備份與恢復(fù)
[toc]
本文基于馬哥的linux運(yùn)維課程視頻, 結(jié)合自己在實(shí)驗(yàn)的過程中歸納整理所著, 在此致謝馬哥!
基礎(chǔ)概念
注意點(diǎn):
- (1) 做還原測(cè)試, 用于測(cè)試備份的可用性
- (2) 還原演練
備份類型: 完全備份, 部分備份
熱備份, 溫備份, 冷備份:
- 熱備: 讀寫操作均可執(zhí)行
- 溫備: 可讀不可寫
- 冷備: 讀寫均不可
innodb支持熱備, MyISAM支持溫備, 不能熱備
物理備份, 邏輯備份:
- 物理備份: 直接復(fù)制數(shù)據(jù)文件進(jìn)行備份 (占用更多空間但速度快, 不需要專用工具)
- 邏輯備份: 從數(shù)據(jù)庫中"導(dǎo)出"數(shù)據(jù)另存而進(jìn)行的備份 (需要特定的客戶端工具, 會(huì)丟失數(shù)據(jù)精度且可能更占用空間, 但與存儲(chǔ)引擎無關(guān))
備份時(shí)需要考慮的因素:
持鎖多久
備份過程的時(shí)長
備份時(shí)是否會(huì)增加線上負(fù)載
恢復(fù)過程的時(shí)長
備份內(nèi)容 (注意不要將備份內(nèi)容和原始數(shù)據(jù)放在同一塊硬盤中, 最好不同主機(jī)):
- 數(shù)據(jù)
- 二進(jìn)制日志 (注意一定要記錄正在使用的二進(jìn)制日志的文件名及事件位置!), InnoDB的事務(wù)日志
- 服務(wù)器的配置文件
- 代碼 (存儲(chǔ)過程, 存儲(chǔ)函數(shù), 觸發(fā)器, 事件調(diào)度器)
設(shè)計(jì)備份方案:
- 物理備份還是邏輯備份?
- 冷備還是熱備?
- 完全備份還是增量備份? 一般使用"完全 + 增量"的方式進(jìn)行
備份工具:
-
mysqldump: 邏輯備份工具, 適用于所有存儲(chǔ)引擎, 支持溫備但不支持熱備, 支持完全備份和部分備份, 對(duì)InnoDB存儲(chǔ)引擎支持熱備 -
cp, tar等復(fù)制歸檔工具: 物理備份工具, 適用于所有存儲(chǔ)引擎, 但只能進(jìn)行冷備, 支持完全備份和部分備份 -
lvm的快照: 幾乎熱備 (請(qǐng)求施加一個(gè)全局讀鎖, 待持鎖成功后立即拍快照, 然后釋放鎖, 一般請(qǐng)求迅速時(shí)1s - 5s即可), 需要借助文件系統(tǒng)管理工具進(jìn)行備份 -
mysqlhotcopy: 幾乎冷備, 僅適用于MyISAM存儲(chǔ)引擎
備份工具的選擇:
-
mysqldump + 復(fù)制binlog
- 先通過mysqldump做完全備份, 再復(fù)制binlog中指定時(shí)間范圍的event (增量備份)
- 時(shí)間慢但可遠(yuǎn)程備份
-
lvm2快照 + 復(fù)制binlog
- 先使用cp或tar等做物理備份 (完全備份), 再復(fù)制binlog中指定時(shí)間范圍的event (增量備份)
-
xtrabackup
- 開源, 由Percona提供的支持對(duì)InnoDB做熱備(物理備份)的工具
- 支持完全備份和增量備份
備份和恢復(fù)
1. 邏輯備份工具: mysqldump, mydumper, phpMyAdmin
- 原理: 將Schema和和數(shù)據(jù)存儲(chǔ)在一起, 導(dǎo)出一個(gè)巨大的SQL語句, 是一個(gè)巨大的備份文件, 這個(gè)文件比較小的時(shí)候可直接使用cat等命令查看
- mysqldump: 客戶端命令, 通過mysql協(xié)議了解至mysqld服務(wù)器
mysqldump [options] [db_name [tbl_name ...]]
shell> mysqldump [options] db_name [tbl_name ...] # 不會(huì)自動(dòng)創(chuàng)建database語句
shell> mysqldump [options] --databases db_name ... # 會(huì)自動(dòng)創(chuàng)建database語句
shell> mysqldump [options] --all-databases # 會(huì)自動(dòng)創(chuàng)建database語句
mysqldump -uroot --databases hellodb mydb > /tmp/alldb_bak.sql
# 特別注意!
# MyISAM: 支持溫備, 因此需要先鎖定備份庫, 然后啟動(dòng)備份操作
# 鎖定方法:
--lock-all-tables, -x # 備份前鎖定所有數(shù)據(jù)庫中所有表, 用于備份整個(gè)數(shù)據(jù)庫, 使用此選項(xiàng)一定要慎重!
--lock-tables, -l # 備份前鎖定要備份的那個(gè)庫的所有表, 用于備份單個(gè)數(shù)據(jù)庫
# 對(duì)InnoDB一樣生效, 但只能實(shí)現(xiàn)溫備
# InnoDB: 支持熱備
--single-transaction # 啟動(dòng)一個(gè)巨大的事務(wù)
# 其它選項(xiàng):
-E, --events # 備份與指定數(shù)據(jù)庫相關(guān)的所有事件調(diào)度器
--routines, -R # 備份與指定數(shù)據(jù)庫相關(guān)的所有存儲(chǔ)過程或存儲(chǔ)函數(shù)
--triggers # 備份與指定數(shù)據(jù)庫相關(guān)的所有觸發(fā)器, 默認(rèn)啟用
--master-data[=value] # 值可設(shè)定為0/1/2等, 記錄正在使用的二進(jìn)制日志的文件名及事件位置, 當(dāng)值設(shè)為2時(shí), 會(huì)保存一個(gè)已被注釋的CHANGE MASTER TO語句, 此語句記錄了開始被備份時(shí)事件處于哪個(gè)二進(jìn)制文件的哪個(gè)position, 此position之前的內(nèi)容會(huì)被備份
# 0: 不啟用
# 1: 記錄為CHANGE MASTER TO語句, 此語句不被注釋
# 2: 記錄為CHANGE MASTER TO語句, 此語句被注釋
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;
--flush-logs # 鎖定表完成后, 執(zhí)行FLUSH LOGS命令, 對(duì)二進(jìn)制日志進(jìn)行日志滾動(dòng)操作
1.1 mysql備份與結(jié)合二進(jìn)制日志做時(shí)間點(diǎn)還原
注意還原時(shí)會(huì)產(chǎn)生大量的二進(jìn)制日志, 而這些日志是我們不需要的, 此時(shí)最好關(guān)閉二進(jìn)制日志的記錄功能: SET sql_log_bin=OFF或SET sql_log_bin=0, 等待還原完成后再開啟此功能: SET sql_log_bin=ON
需要的文件:
- 全量備份后的all.sql文件
- 所有的二進(jìn)制日志文件
shell> mysqldump -uroot --master-data=2 --all-databases --lock-all-tables > all.sql # 再次強(qiáng)調(diào), 使用--lock-all-tables選項(xiàng)一定要慎重!
shell > mysql -uroot -p # 先對(duì)數(shù)據(jù)庫中某些表進(jìn)行一些操作后, 假如此時(shí)服務(wù)器正好宕機(jī), 接下來就需要恢復(fù)數(shù)據(jù)
shell> less all.sql # 查看日志文件從什么位置開始備份, 假如MASTER_LOG_POS=245, 也就是說245之前的內(nèi)容已經(jīng)被備份, 而245之后的數(shù)據(jù)只有二進(jìn)制日志中有SQL記錄, 而需要恢復(fù)成宕機(jī)前但數(shù)據(jù)已經(jīng)過修改的樣子, 就需要從二進(jìn)制日志中導(dǎo)出備份后數(shù)據(jù)庫執(zhí)行的SQL語句
shell> mysqlbinlog --start-position=245 mysql-bin.000002 > incre.sql
shell> mysql -uroot -p < all.sql # 先還原備份時(shí)數(shù)據(jù)庫的數(shù)據(jù)
shell> mysql -uroot -p < incre.sql # 在還原備份后數(shù)據(jù)庫被修改的數(shù)據(jù)
生產(chǎn)環(huán)境中如果數(shù)據(jù)量不大, 可以使用mysqldump每周做一次全量備份, 每半天(或者每一天)通過復(fù)制二進(jìn)制文件做一次增量備份(先FLUSH LOGS, 然后將中間的二進(jìn)制日志文件復(fù)制出來), 之后如果數(shù)據(jù)庫宕機(jī), 恢復(fù)數(shù)據(jù)就可以先恢復(fù)全量數(shù)據(jù), 然后再依次恢復(fù)每半天(或者每一天)的增量數(shù)據(jù)就可以了.
# 實(shí)例:
# 0. 連入數(shù)據(jù)庫查看此時(shí)使用的二進(jìn)制日志文件
SHOW BINARY LOGS;
SHOW MASTER STATUS; # 假如此時(shí)正在使用的二進(jìn)制日志文件是mysql-bin.000005
# 1. 先做全量備份
mysqldump -uroot -predhat --master-data=2 --all-databases --lock-all-tables --flush-logs > all.sql
# 1.1 查看備份的起始position
less all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=245;
# 1.2 連入數(shù)據(jù)庫查看此時(shí)使用的二進(jìn)制日志文件
SHOW MASTER LOGS; # 假如此時(shí)正在使用的二進(jìn)制日志文件是mysql-bin.000006, 此文件恰好就是全量備份后數(shù)據(jù)庫使用的二進(jìn)制日志文件
# 2. 連入數(shù)據(jù)庫做一些操作, 如插入一條數(shù)據(jù), 然后再做增量備份
mysqldump -uroot -p
USE testdb;
INSERT INTO mytbl1 (name,gender) VALUES ('incre1','M');
FLUSH LOGS; # 特別注意一定要做滾動(dòng)日志的操作!
SHOW MASTER STATUS; # 假如日志滾動(dòng)后正在使用的二進(jìn)制日志文件由mysql-bin.000006變?yōu)閙ysql-bin.000007, 說明mysql-bin.000006記錄的就是第一次增量備份所需要使用的二進(jìn)制日志文件
\q # 退出數(shù)據(jù)庫并將mysql-bin.000006備份到其他磁盤
# 接下來還原數(shù)據(jù)
# 3. 還原全量數(shù)據(jù)
mysql -uroot -predhat < all.sql
# 4. 先將mysql-bin.000006中記錄下的SQL語句導(dǎo)出(注意篩選出start-position為245之后的SQL), 然后再還原第一次增量數(shù)據(jù)
mysqlbinlog --start-position=245 mysql-bin.000006 > incre1.sql
mysql -uroot -predhat < incre1.sql
2. 基于lvm2備份和還原
2.1 備份
# 注意修改/etc/my.cnf的配置文件, 將mysql的datadir目錄指向邏輯卷所在目錄
# (1) 請(qǐng)求鎖定所有表
mysql> FLUSH TABLES WITH READ LOCK; # 將內(nèi)存中所有表的數(shù)據(jù)同步到磁盤, 然后施加讀鎖
# 此時(shí)數(shù)據(jù)庫中有人正在執(zhí)行大事務(wù), 此事務(wù)無法被鎖定, 需要等待事務(wù)執(zhí)行完才能施加讀鎖
# (2) 記錄二進(jìn)制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER LOGS;
# 也可以不登錄數(shù)據(jù)庫, 直接要把結(jié)果記錄下來
mysql -e 'FLUSH LOGS'
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/RECORD.FILE # 加入此時(shí)正在使用的二進(jìn)制改為mysql-bin.000004
# (3) 邏輯卷創(chuàng)建快照
# 創(chuàng)建的快照大小根據(jù)實(shí)際的增量數(shù)據(jù)大小而定
lvcreate -L 1G --snap-shot -n lv1-snap -p r /dev/vg1/lv1
# 創(chuàng)建完快照后一定要掛載測(cè)試, 因?yàn)閯?chuàng)建的快照可能有問題! 文件系統(tǒng)為xfs的快照在掛載時(shí)可能有問題!
mkdir /mnt/snap
mount /dev/vg1/lv1-snap /mnt/snap/
umount /mnt/snap/
# (4) 拍完快照后記得釋放鎖, 否則線上業(yè)務(wù)會(huì)受到很大影響!
mysql> UNLOCK TABLES;
# (5) 對(duì)數(shù)據(jù)庫做一些操作
mysql> DROP DATABASE testdb;
# (6) 掛載快照并將快照中的數(shù)據(jù)復(fù)制到其他硬盤或其他主機(jī)中
mount -r /dev/vg1/lv1 /mnt/
scp /mnt/data 192.168.30.10:/bak_data/
# (7) 備份完成后刪除快照
lvremove /dev/vg1/lv1-snap
# (8) 制定好策略, 通過原卷備份二進(jìn)制日志
scp /var/log/mariadb/mysql-bin.* 192.168.30.10:/bak_data/
2.2 還原
# 1. 把數(shù)據(jù)文件和二進(jìn)制文件分別放在my.cnf中二進(jìn)制文件和數(shù)據(jù)文件指定的目錄下, 注意修改至mysql:mysql權(quán)限, 否則mariadb服務(wù)無法讀取文件中的內(nèi)容
chown -R mysql:mysql /mnt/mysql
chown -R mysql:mysql /var/log/maraidb/
# 2. 接下來還原
mysqlbinlog mysql-bin.000004 > incre.sql
mysql -uroot < incre.sql
3. xtrabackup
- 通過mysql協(xié)議鏈接至mysql數(shù)據(jù)庫, 因此必須online備份
- 僅對(duì)InnoDB存儲(chǔ)引擎支持熱備(完全備份和增量備份), 對(duì)MyISAM只支持溫備而且不支持增量備份
- 使用xtrabackup之前必須先在/etc/my.cnf的[mysqld]或[mysqld_safe]配置段開啟一個(gè)參數(shù):
innodb_file_per_table=ON, 如果此參數(shù)沒有開啟, 很多備份的高級(jí)功能將無法使用, 開啟后可在數(shù)據(jù)庫中查看:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | OFF | # 此項(xiàng)配置在服務(wù)啟動(dòng)前就應(yīng)該開啟!
+--------------------------+----------+
3.1 使用xtrabackup做完全備份與還原
# 1. 安裝, 支持windows和linux等平臺(tái), 下面以redhat7為例
yum -y install percona-xtrabackup
# 2. 完全備份
innobackup /PATH/TO/BACKUP_DIR
# 3. 整理備份數(shù)據(jù)
innobackup --apply-log /PATH/TO/BACKUP_DIR
# --apply-log的作用在于回滾未提交的事務(wù)以及同步已提交的事務(wù)至數(shù)據(jù)文件中, 從而保持?jǐn)?shù)據(jù)文件的一致性.
# 不過完全備份中有大事務(wù)未完成, 而增量備份中才完成這個(gè)大事務(wù)時(shí), --apply-log參數(shù)不能加!
# 3. 還原
# 注意: 還原時(shí)不能啟動(dòng)mysql服務(wù)!
innobackup --copy-back /PATH/TO/BACKUP_DIR
chown -R mysql:mysql /PATH/TO/MYSQL_DATADIR
cat xtrabackup_checkpoints
backup_type = full-prepared # 說明是做全量備份
from_lsn = 0
to_lsn = 1597945
last_lsn = 1597945
compact = 0
recover_binlog_info = 0
image-20191112152654110.png
image-20191112165542042.png
image-20191112165626031.png
3.2 使用xtrabackup做增量備份與還原
- 增量備份僅能用于InnoDB或XtraDB表, 對(duì)于MyISAM表而言, 增量備份其實(shí)是完全備份
- 增量備份要基于上一次的備份進(jìn)行
innobackup --incremental /backup --increment-basedir=BASEDIR
cat xtrabackup_checkpoints
backup_type = incremental # 說明是增量備份
from_lsn = 1597945 # 上一次備份結(jié)束的日志序列號(hào), 同時(shí)也是這一次開始增量備份的日志序列號(hào)
to_lsn = 1601588 # 本次結(jié)束的日志序列號(hào)
last_lsn = 1601588
compact = 0
recover_binlog_info = 0
# 1. 全量備份到/tmp/目錄下
innobackup /tmp/ # 假設(shè)生成的全量備份目錄名為/tmp/2019-11-12_21-56-49/
# 2. 基于全量備份進(jìn)行第一次增量備份
innobackup --incremental /tmp/ --increment-basedir=/tmp/2019-11-12_21-56-49/ # 假設(shè)生成的第一次增量備份目錄名為/tmp/2019-11-12_21-56-49/
# 增量備份的整理過程與全量備份的整理過程不同
# 3. 先基于全量備份做整理
innobackupex --apply-log --redo-only /tmp/2019-11-12_21-56-49/ --incremental-dir=/tmp/
# 4. 再基于第一次增量備份做整理
innobackupex --apply-log --redo-only /tmp/2019-11-12_21-56-49/ --incremental-dir=/tmp/2019-11-12_22-10-24/
# 可以看到整理后2019-11-12_21-56-49/xtrabackup_checkpoints文件中備份類型的值變?yōu)閘og-applied
cat 2019-11-12_21-56-49/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1601588
last_lsn = 1601588
compact = 0
recover_binlog_info = 0
# 5. 此時(shí)可以基于合并后的全量備份的進(jìn)行還原
innobackup --copy-back /tmp/2019-11-12_21-56-49/
image-20191112165730297.png
image-20191112165828431.png
3.3 使用xtrabackup導(dǎo)入或?qū)С鰡螐埍?/h4>
前提條件:
- 需要"導(dǎo)出"表在創(chuàng)建之前, mysql服務(wù)器就啟用了
innodb_file_per_table選項(xiàng)
- "導(dǎo)入"表的服務(wù)器同時(shí)啟用了
innodb_file_per_table和innodb_expand_import選項(xiàng)
前提條件:
- 需要"導(dǎo)出"表在創(chuàng)建之前, mysql服務(wù)器就啟用了
innodb_file_per_table選項(xiàng) - "導(dǎo)入"表的服務(wù)器同時(shí)啟用了
innodb_file_per_table和innodb_expand_import選項(xiàng)
MySQL 5.6 Innodb新特性之export/import 表文件
選項(xiàng)innodb_expand_import在某些新版的mysql中已改為innodb_import_table_from_xtrabackup, 關(guān)于innodb_expand_import以及導(dǎo)出單張表的percona官方詳細(xì)說明: Expand Table Import
導(dǎo)入單張表的percona官方詳細(xì)說明: Restoring Individual Tables
image-20191112165902354.png
# 1. 先做完全備份, 需要確保這次完全備份沒有apply-log過
innobackup /tmp/ # 假如生成的完全備份目錄名為/tmp/2019-11-13_00-11-49/
# 2. 在完全備份的基礎(chǔ)上apply-log并export出每個(gè)innodb表的數(shù)據(jù)文件
innobackup --apply-log --export /tmp/2019-11-13_00-11-49/
# 此時(shí)去查看完全備份的每個(gè)數(shù)據(jù)庫目錄下, 會(huì)發(fā)現(xiàn)多了以.exp和.cfg結(jié)尾的文件
ll /tmp/2019-11-13_00-11-49/hellodb/
total 132
-rw-r-----. 1 root root 65 Nov 13 00:11 db.opt
-rw-r--r--. 1 root root 396 Nov 13 00:29 test.cfg
-rw-r--r--. 1 root root 16384 Nov 13 00:29 test.exp
-rw-r-----. 1 root root 8586 Nov 13 00:11 test.frm
-rw-r-----. 1 root root 98304 Nov 13 00:11 test.ibd
# 3. 將.exp和.ibd結(jié)尾的文件拷貝到需要恢復(fù)的數(shù)據(jù)庫服務(wù)器上, 創(chuàng)建與原表結(jié)構(gòu)相同的新表
mysql> CREATE TABLE hellodb.test (...) ENGINE=InnoDB;
# 4. 將此表的表空間刪除
mysql> ALTER TABLE hellodb.test DISCARD TABLESPACE;
# 5. 將對(duì)應(yīng)表的.ibd和.exp文件復(fù)制到新服務(wù)器的數(shù)據(jù)目錄(注意修改權(quán)限), 然后導(dǎo)入
mysql> ALTER TABLE hellodb.test IMPORT TABLESPACE;
導(dǎo)入時(shí)報(bào)錯(cuò): "ERROR 1030 (HY000): Got error -1 from storage engine", 在xtrabackup的man幫助里有關(guān)于這個(gè)問題及解決方法的詳細(xì)說明:
Importing the Table
On the destination server running Percona Server with XtraDB and
innodb_import_table_from_xtrabackup option enabled, or MySQL 5.6, create
a table with the same structure, and then perform the following steps:· Execute ALTER TABLE test.export_test DISCARD TABLESPACE;
· If you see the following message, then you must enable inn‐
odb_file_per_table and create the table again: ERROR 1030 (HY000):
Got error -1 from storage engine· Copy the exported files to the test/ subdirectory of the destination
server's data directory· Execute ALTER TABLE test.export_test IMPORT TABLESPACE;
The table should now be imported, and you should be able to SELECT from
it and see the imported data.
從man幫助中可以看出, 需要開啟innodb_file_per_table選項(xiàng)并重新創(chuàng)建表, 然后復(fù)制導(dǎo)出的文件到指定的數(shù)據(jù)文件目錄中, 重新導(dǎo)入表空間, 實(shí)測(cè)確實(shí)有效!
