概述
备份类型
完全备份、部分备份、增量备份、差异备份
- 完全备份:整个数据集
- 部分备份:只备份数据子集,如部分库或表
- 增量备份:仅备份最近一次完全备份或者增量备份(如果有增量备份)以来变化的数据,备份较快,还原复杂
- 差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
注意:二进制日志文件不应该与数据文件放在同一磁盘
冷备份、温备份、热备份
- 冷备:读写操作均不可进行,数据库停止服务
- 温备:读操作可执行,但写操作不可执行
- 热备:读写操作均可执行
1 2 3
| MyISAM:支持温备,不支持热备
InnoDB:都支持
|
物理备份、逻辑备份
- 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
- 逻辑备份:从数据库中“到处”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度
增量备份 图示:
![增量备份]()
差异备份 图示:
![差异备份]()
推荐:增量备份 + 热备 + 物理备份
备份什么
- 数据
- 二进制日志、InnoDB 的事务日志
- 用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
- 服务器的配置文件
备份注意要点
- 能容忍最多丢失多少数据
- 备份产生的负载
- 备份过程的时长
- 温备的持锁多久
- 恢复数据需要在多长时间内完成
- 需要备份和恢复哪些数据
还原要点
- 做还原测试,用于测试备份的可用性
- 还原演练,写成规范的技术文档
备份工具
- cp, ta r 等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备份
- LVM 的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
- mysqldump:逻辑备份工具,适用所有存储引擎,对 MyISAM 存储引擎进行温备;支持完全或部分备份;对 InnoDB 存储引擎支持热备,结合 binlog 的增量备份
- xtrabackup:由 Percona 提供支持对 InnoDB 做热备(物理备份)的工具,支持完全备份、增量备份
- MariaDB Backup: 从 MariaDB 10.1.26 开始集成,基于 Percona XtraBackup 2.3.8 实现
- mysqlbackup:热备份, MySQL Enterprise Edition 组件
- mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于 MyISAM 存储引擎,使用 LOCK TABLES、
- FLUSH TABLES 和 cp 或 scp 来快速备份数据库
基于 LVM 的快照备份
1 2 3 4 5 6 7 8 9 10 11 12 13
| mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS; mysql> SHOW MASTER STATUS; mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
lvcreate -L
mysql> UNLOCK TABLES;
|
冷备份和还原
冷备份和还原需要停止数据库服务
mysqldump
mysqldump 是逻辑备份工具,是 MySQL 的客户端工具,通过 mysql 协议连接至 mysql 服务器进行备份
1 2 3 4 5 6
| mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
mysqldump [OPTIONS] -A [OPTIONS]
|
常见选项:
-A:备份所有数据库,包含 create database 语句
- -A 包括 mysql 数据库,但不包括 information_schema 和 performance_schema
-B db_name…:备份指定的数据库,包含 create database 语句
–flush-privileges:-A 包含了 mysql 数据库,其中包含了权限的数据库,需要加上–flush-privileges,将来恢复的时候权限才会生效
-n:不备份 create database 语句,会被-A 或-B 覆盖
-t:只备份数据,不备份表结构,即不备份 create table 语句
-d:只备份表结构,不备份数据,即只备份 create table 语句
-E:备份相关的所有 event scheduler
-R:备份所有存储过程和自定义函数
–triggers:备份表相关触发器,默认启用,用 –skip-triggers 不备份触发器
–default-character-set=utf8mb4:指定字符集
–single-transaction:置事务的隔离级别为可重复读,即 REPEATABLE READ,这样能保证在一个事务中所有相同的查询读取到同样的数据,也就大概保证了在 dump 期间,如果其他 innodb 引擎的线程修改了表的数据并提交,对该 dump 线程的数据并无影响,在这期间不会锁表
–master-data=1|2
此选项会自动关闭–lock-tables 功能,自动打开-x | –lock-all-tables 功能(除非开启–single-transaction)
- 1:默认值,所备份的数据之前加一条记录为 CHANGE MASTER TO 语句,适合于主从复制多机使用
- 2:所备份的数据之前加一条记录为 CHANGE MASTER TO 语句,但是被注释,适合于单机使用
-F:在 dump 之前会执行--flush-logs
刷新日志。使用-A 或-B 时一次性 dump 多个库,每个库都会刷新一次。建议搭配–master-data 或–lock-all-tables,就只会刷新一次
–compact:去掉注释,节约备份占用的空间,适合调试,生产不使用
-f:忽略 SQL 错误,继续执行
–hex-blob:使用十六进制符号转储二进制列,当有包括 BINARY, VARBINARY,BLOB,BIT 的数据类型的列时使用,避免乱码
-q:不缓存查询,直接输出,加快备份速度
mysqldump 备份的最佳姿势
Innodb
1 2 3 4 5 6 7 8 9 10 11 12
| mysqldump -uroot -p \ -A \ -F \ -E \ -R \ --triggers \ --single-transaction \ --master-data=1 \ --flush-privileges \ --default-character-set=utf8mb4 \ --hex-blob \ > ${BACKUP}/fullbak_${BACKUP_TIME}.sql
|
MyISAM
1 2 3 4 5 6 7 8 9 10 11 12
| mysqldump -uroot -p \ -A \ -F \ -E \ -R \ -x \ --master-data=1 \ --flush-privileges \ --triggers \ --default-character-set=utf8mb4 \ --hex-blob \ > ${BACKUP}/fullbak_${BACKUP_TIME}.sql
|
实战案例
- mysqldump 结合二进制日志文件实现增量备份
数据库接手的时候,早期的二进制日志文件已经没有了,此时可以先用 msyqldump 做全量备份,然后再定期备份二进制日志文件
每天都全量备份,误删数据表,该如何恢复?
1 2 3 4 5 6 7 8 9 10 11 12 13
|
[root@centos8 ~] MariaDB [hellodb]> set sql_log_bin=0; MariaDB [hellodb]> source /backup/allbackup_2019-11-27_10:20:08.sql; MariaDB [hellodb]> source /backup/inc.sql MariaDB [hellodb]> set sql_log_bin=1;
|
xtrabackup
xtrabackup 是一个开源的 mysql 数据库备份工具,支持 InnoD 和 XtraDB 引擎
mysqldump 基于逻辑备份,备份和还原比较慢,增量备份(配合二进制日志)需要自己写脚本
xtrabackup 基于物理备份,备份和还原很快,自动化增量备份
mysqldump 的优点是相较于 xtrabackup,备份文件更小,节省磁盘空间
当数据量比较大的时候,推荐使用 xtrabackup 备份
xtrabackup 特点:
- 备份还原过程快速、可靠
- 备份过程不会打断正在执行的事务
- 能够基于压缩等功能节约磁盘空间和流量
- 自动实现备份检验
- 开源,免费
xtrabackup 备份过程:
![]()
安装:
xtrabackup2.4 支持 mysql5.7,xtrabackup8.0 支持 mysql8.0,这里以 2.4 版本为例,2.4 版本和 8.0 差不多,和旧版本有不少出入
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
| [root@c71 src] [root@c72 src]$rpm -qi percona-xtrabackup-24-2.4.20-1.el7 Name : percona-xtrabackup-24 Version : 2.4.20 Release : 1.el7 Architecture: x86_64 Install Date: Fri 23 Oct 2020 09:40:03 AM CST Group : Applications/Databases Size : 33240760 License : GPLv2 Signature : RSA/SHA256, Tue 21 Apr 2020 04:45:08 AM CST, Key ID 9334a25f8507efa5 Source RPM : percona-xtrabackup-24-2.4.20-1.el7.src.rpm Build Date : Tue 21 Apr 2020 04:44:27 AM CST Build Host : minimal-centos-7-x64-623.ci.percona.com Relocations : (not relocatable) URL : http://www.percona.com/software/percona-xtrabackup Summary : XtraBackup online backup for MySQL / InnoDB Description : Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines [root@c72 src]$rpm -ql percona-xtrabackup-24-2.4.20-1.el7 /usr/bin/innobackupex /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/lib64/xtrabackup/plugin/keyring_file.so /usr/lib64/xtrabackup/plugin/keyring_vault.so /usr/share/doc/percona-xtrabackup-24-2.4.20 /usr/share/doc/percona-xtrabackup-24-2.4.20/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
|
用法:
xtrabackup 工具备份和还原,需要三步实现:
- 备份,对数据库做完全或者增量备份
- 预准备:还原前,先对备份的数据,整理至一个临时目录
- 还原:将整理好的数据,复制回数据库目录中
1 2 3 4 5 6
| xtrabackup [--defaults-file=/etc/my.cnf] --backup [OPTIONS]
xtrabackup [--defaults-file=/etc/my.cnf] --prepare [OPTIONS]
xtrabackup [--defaults-file=/etc/my.cnf] --copy-back [OPTIONS]
|
OPTIONS:
- –defaults-file:该选项指定从哪个文件读取 MySQL 配置,必须放在命令行第一个选项位置
- -u:user 该选项表示备份账号
- -p:password 该选项表示备份的密码
- -h:host 该选项表示备份数据库的地址
- –backup:备份
- –databases:该选项接受的参数为数据库名,如果要指定多个数据库,彼此间需要以空格隔开;
如:”xtra_test dba_test”,同时,在指定某数据库时,也可以只指定其中的某张表。
如:”mydatabase.mytable”。该选项对 innodb 引擎表无效,还是会备份所有 innodb 表
- –incremental-basedir:该选项指定为前一次全备份或增量备份的目录,与–incremental 同时使用
- –prepare:预准备
- –apply-log-only:预准备的过程中,默认回滚未完成的事务,–apply-log 选项能阻止回滚
- –copy-back:还原,拷贝整理好的备份数据到 datadir
- –incremental-dir:该选项表示还原时增量备份的目录
还原注意事项:
- 保证 datadir 目录为空
- 还原期间关闭 mysql 服务
- 重新启动之前,需要
chown -R mysql:mysql datadir
范例 1:完全备份及还原
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| [root@4710419222 backup]$mkdir /data/backup [root@4710419222 backup]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/base
[root@c71 data]$scp -r root@47.104.192.22:/data/backup ./
[root@c71 data]$systemctl stop mysql.service
[root@c71 data]$rm -rf /data/mysql/*
[root@c71 data]$xtrabackup --prepare --target-dir=/data/backup/base
[root@c71 data]$xtrabackup --copy-back --target-dir=/data/backup/base [root@c71 data]$chown -R mysql:mysql /data/mysql/
[root@c71 data]$systemctl start mysqld.service
|
范例 2:完全、增量备份及还原
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| [root@c71 ~]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/base
[root@c71 base]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
[root@c71 base]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1 [root@c71 base]$ll /data/backup/ total 12 drwxr-x--- 10 root root 4096 Oct 23 10:56 base drwxr-x--- 10 root root 4096 Oct 23 10:54 inc1 drwxr-x--- 10 root root 4096 Oct 23 10:58 inc2
[root@c71 base]$xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base [root@c71 base]$xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1 [root@c71 base]$xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2
[root@c71 base]$systemctl stop mysqld.service [root@c71 base]$rm -rf /data/mysql/* [root@c71 base]$ll /data/mysql/ total 0 [root@c71 base]$xtrabackup --copy-back --target-dir=/data/backup/base [root@c71 base]$chown -R mysql:mysql /data/mysql/ [root@c71 base]$systemctl start mysqld.service
|