概要
MySQL中备份数据的工具有
- mysqldump (MDP)
- percona Xtrabackup (XBK)
- MySQL Enterprise Backup (MEB)
- mysqlbinlog
其中MEB是MySQL官方的备份工具,是收费的。由于本身条件有限,没有使用过这款工具,故在这里就不做讲解。
备份方式
- 逻辑备份
全量备份:使用 mysqldump 可以实现数据的全量备份,但是无法实现增量备份
增量备份:依赖 binlog 可以实现增量备份
Tip:使用 binlog 进行增量备份的步骤:
1、mysql>flush logs; //滚动日志,产生一个新的 binlog
2、在Linux中将除了新产生的 binlog 之外的增量 binlog 文件使用"cp"命令拷贝备份
- 物理备份
全量备份:使用 XBK工具 可以实现数据的全量的物理备份
增量备份:使用 XBK工具 也可以实现数据的增量的物理备份
逻辑备份和物理备份的区别:
- 逻辑备份:逻辑备份是备份SQL语句,在恢复的时候执行备份中的SQL语句,从而实现数据库数据的重现
- 物理备份:物理备份就是备份数据文件了,比较形象点就是cp数据文件,但真正备份的时候自然不是cp这么简单
逻辑备份(mysqldump)
优点:
1、恢复简单
2、与存储引擎无关,因为备份的是SQL语句,所以消除了底层数据存储的差异
3、有助于避免数据损坏。因为若是磁盘驱动器有故障而要复制原始文件时,此时将得到一个损坏的备份
缺点:
1、必须有数据库服务器完成逻辑工作,需要更多的CPU周期
2、逻辑备份还原速度慢:因为需要MySQL加载和解释SQL语句、转化存储格式、重建引擎
物理备份(xtrabackup)
优点:
1、备份和恢复非常快(因为不需要执行任何的SQL语句,不要构建索引)
2、增量备份
3、流式备份
4、(解)压缩算法相比逻辑备份的zlib库更快
5、自身多线程支持
缺点:
1、不够灵活,无法单独备份指定表、指定库
2、无法同逻辑备份那样 grep 特定业务存在的字符串
3、空间损耗,且不支持逻辑备份恢复时的碎片整理功能
4、依赖于备份过程中生成的 redo log 大小,若过大就会极大的影响备份和恢复速度
5、逻辑备份在不同的MySQL版本中,兼容性好;而xtrabackup作为物理备份方案,对MySQL的版本依赖性较强,扩展性较差
备份类型
- 热备份:在业务运行时进行数据备份,对于业务影响小。(InnoDB存储引擎支持热备)
- 温备份:虽然也是在业务运行期间进行数据备份,但是备份时会长时间锁表。(MyISAM存储引擎支持温备)
- 冷备份:需要业务关闭才能备份
mysqldump逻辑备份
mysqldump备份属于热备份,但是并不是正真的热备份,只能是快照备份。快照备份也不会锁表,和真正的热备份的区别在于:快照备份只能备份快照之前的数据,在备份过程中产生的数据则无法备份,而真正的热备份可以把备份过程中产生的数据也进行备份。
比如我们想备份MySQL中某个数据库下的某些表:
[root@db01 backup]# mysqldump -uroot -p123 world city country > /backup/tab.sql
表示要备份world数据库中的city表和country表
TIP:若是想把数据库中的数据都按照表为单位进行备份,可通过 information_schema进行语句拼接,从而减少工作量。例如:
mysql>select concat("mysqldump -uroot -p123456"," ",table_schema," ",table_name," ",">/tmp/",table_name,".sql") from information_schema.tables where table_name='world' into outfile '/tmp/discard.sql';
使用"into outfile"可以将输出在屏幕上的查询结果输出到Linux系统的文件中,然后在Linux命令中执行"source /tmp/discard.sql"命令就可让里面拼接的语句开始执行。
若是把mysql数据库中表中的数据导入到linux文件中,一般文件后缀名为".csv",因为使用".csv"后缀名,将文件传送到windows时,就可以直接使用Excel打开(当然也可以使用".xlsx"后缀名),此方式多用于数据迁移。
TIP:在5.7版本中若是想从MySQL中直接把数据输出到linux文件中,需要在配置文件中配置"secure-file-priv"参数,此参数表示安全路径,只有安全路径,"into outfile"才能成功将MySQL中的数据导入linux中。此参数后面可跟指定的路径,也可以什么不跟,表示所有路径都是安全路径。
1、基础备份参数
-A:将整个MySQL数据库中数据进行备份
例:[root@db01 backup]# mysqldump -uroot -p123 -A >/backup/full.sql
-B:备份MySQL中指定的某些数据库中的所有数据
例:[root@db01 backup]# mysqldump -uroot -p123 -B world oldguo wordpress >/backup/db.sql
2、特殊备份参数
-R:备份存储过程和函数;MySQL中的存储过程和函数相当于Linux中的脚本,是工作人员自定义的函数
-E:备份事件;此事件相当于Linux中的crontab计划任务,是工作人员自定义的
--triggers:备份触发器
--single-transaction:对于InnoDB的表,进行一致性快照备份,不锁表
--master-data=2 此参数有两个功能:
- 记录备份时刻的 binlog 的position信息和文件名信息,用于数据恢复是,截取 binlog
- 自动锁表,操作完成后会自动解锁。(配合"--single-transaction"参数使用时,对于InnoDB存储引擎的表可以不锁表备份,是通过MVCC功能进行快照备份;不配合"--single-transaction"参数使用时就是全局锁表,进行温备份)
3、扩展参数
--set-gtid-purged参数:默认值为AUTO/ON,主要在构建主从时使用。 使用 mysqldump进行数据备份时,若是使用此参数的默认值,备份文件中就有一行GTID的信息,如:
SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';
此条语句指定了备份的数据所对应得GTID号得范围。构建主从时,由于可能主库已经存在数据,所以需要先备份,再恢复到从库,因为备份文件中有了这条语句,从库在同步主库的数据时,就会从GTID号为11的地方开始同步;若是备份文件中没有这条语句,从库就会从头开始同步主库的数据。从头开始同步数据就会和从库中已有的数据发生冲突,因为从库中已经有这部分数据了,最后就会致使主从构建失败。
设置 --set-gtid-purged=off 时,备份的文件中就不会有上面那一行的GTID信息。此种设置仅用于做普通的本机恢复备份。
--max_allowed_packet参数:此参数用于控制MySQL服务端和客户端之间一次传送过程中最大允许数据包的大小。(进行逻辑备份时,存储引擎层将磁盘中的数据提出给SQL层,SQL层将数据结构化成SQL语句,此过程也可称为MySQL服务器端和客户端的一次数据传输)
例:mysqldump -uroot -p123 -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
模拟演练
1、准备数据
mysql>create database backup;
mysql>use backup;
mysql>create table t1 (id int);
mysql>insert into t1 values(1),(2),(3);
mysql>commit;
2、模拟周二 23:00对数据库进行了全备
[root@db01 backup]#mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
TIP:使用mysqldump进行备份时,若是一台主机上有多个实例,可以使用"-S"参数来指定备份哪个实例中的数据
3、模拟周二 23:00 到周三 10 点之间的数据变化
mysql>use backup;
mysql>insert into t1 values(11),(22),(33);
mysql>commit;
mysql>create table t2 (id int);
mysql>insert into t2 values(11),(22),(33);
mysql>commit;
4、故障模拟
mysql>drop database backup; //backup数据库被删除了
5、在一台主机上准别另外一个MySQL实例作为临时数据库,进行数据恢复
[root@db01 ~]#systemctl start mysqld3307 //启动临时数据
[root@db01 backup]#gunzip full_2021-03-10.sql.gz //对周二23点做的全备文件进行解压 (gzip:压缩)
6、截取二进制日志
由于我们在全备时,使用了"--master-data=2"的参数,所以在全备文件full_2021-03-10.sql中会记录着这样一行信息:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753;
MASTER_LOG_POS=753代表着我们进行全备时,起始的position。所以我们只需将从此起始位置到全备文件最后的position之间所有的二进制日志截取就可以了。
[root@db01 backup]#mysqlbinlog --skip-gtids --start-position=753 --stop-position=1519 /data/binlog/mysql-bin.000002 >/backup/bin.sql //使用"--skip-gtids"参数,是因为我之前已经启用了GTID,不加此参数,直接截取事件号作为备份数据的话,数据恢复的时候会出错。
7、恢复数据到临时数据库
[root@db01 backup]#mysql -S /data/3307/mysql.sock
mysql>set sql_log_bin=0;
mysql>source /backup/full_2019-07-15.sql;
mysql>source /backup/bin.sql;
8、将故障表导出并恢复到生产
[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -B backup >/backup/bak.sql
[root@db01 ~]# mysql -uroot -p
mysql>set sql_log_bin=0;
mysql>source /backup/bak.sql;
mysql>set sql_log_bin=1;
物理备份
物理全备
XBK不是MySQL自带的备份工具,所以需要先进行安装才能使用。
1、下载percona-xtrabackup软件包
[root@db01 backup]#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
我这里将 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm 软件包下载到本地的backup目录下。
2、安装
[root@db01 backup]# yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
安装结束后,系统会提示 percona-xtrabackup 服务还需要一些依赖包,将这些依赖包也一并安装才能正常使用 percona-xtrabackup 服务。
[root@db01 backup]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
TIP:若是这些依赖包无法安装,或者因为 yum 仓库没有相应的依赖包,可以通过更新 yum 仓库来解决问题
[root@db01 ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
备份核心理念
- 针对非InnoDB存储引擎,进行锁表备份,然后copy所有的非InnoDB存储引擎的表文件
- 针对InnoDB存储引擎,立即触发CKPT,将内存中的脏页刷新到磁盘中,然后copy所有InnoDB存储引擎的表文件(ibdata1、ibd、frm),并且将备份过程中新的数据变化相关redo bufffer中的数据一起备份走,从而实现真正的热备份
- 在恢复数据时,xbk会调用InnoDB存储引擎的CSR过程,将数据和redo的LSN号追平,然后进行数据一致性恢复
模拟演练
1、进行物理全备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full
"--no-timestamp"参数:该选项表示不要创建一个时间戳目录来存储备份,指定到自己想要的备份文件夹
2、利用全备进行数据恢复
[root@db01 ~]# pkill mysqld
[root@db01 ~]# \rm -rf /data/mysql/data/* //模拟故障,数据库中的数据全部丢失了
[root@db01 ~]# innobackupex --apply-log /backup/full/ //进行数据恢复时,使用 --apply-log 参数来模拟MySQL中的CSR过程,即通过回滚未提交的事务及同步已经提交的事务至数据文件,使数据文件处于一致性状态
[root@db01 full]# cp -a /backup/full/* /data/mysql/data/ //通过上一步将数据文件和redo log中的LSN号追平后,就可以将备份文件拷贝到MySQL的数据目录下了
[root@db01 full]# chown -R mysql.mysql /data/mysql/data/* //修改拷贝过去的文件的属主、属组
[root@db01 full]# systemctl start mysqld //重启数据库
TIP:percona-xtrabackup 服务中我们主要使用 innobackupex 工具对MySQL数据库中的数据进行备份。
备份中产生的文件介绍
xtrabackup_binlog_info:记录备份时刻的二进制日志信息,可以作为 binlog 截取的起点。
xtrabackup_checkpoints:
from :备份中包含的LSN号的起点。全备时,值为0;增量备份时,值为上次备份结束的位置
to :ckpt 时的LSN。备份结束时的LSN,也是下次增量备份的起始位置
last_lsn :mysql 5.7版本会在"to"LSN号之后额外在增加9个LSN号留作redo维护使用,所以当"to"LSN号和"last"LSN号相差9时,就说明备份的过程中没有发生其它数据的变化
物理增量备份
增量备份需要依赖于全备。
1、模拟数据
mysql>create database full charset utf8mb4;
mysql>use full;
mysql>create table t1 (id int);
mysql>insert into t1 values(1),(2),(3);
mysql>commit;
2、进行周日的全备
[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp /backup/full
3、模拟周一数据的变化
mysql>create database inc1 charset utf8mb4;
mysql>use inc1;
mysql>create table t1 (id int);
mysql>insert into t1 values(1),(2),(3);
mysql>commit;
4、进行周一的增量备份
[root@db01 ~]#innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1
TIP:
--incremental 增量备份的开关
--incremental-basedir 指定基于哪个备份进行增量备份
/backup/inc1 增量备份的位置点
TIP:可以通过检查备份文件的LSN号,就会发现增量备份LSN的起始位置,就是上次备份LSN号的结束点:
[root@db01 backup]# cat /backup/full/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 217478672
last_lsn = 217478681
compact = 0
recover_binlog_info = 0
[root@db01 backup]# cat /backup/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 217478672
to_lsn = 217484653
last_lsn = 217484662
compact = 0
recover_binlog_info = 0
5、模拟周二的数据变化
mysql>create database inc2 charset utf8mb4;
mysql>use inc2;
mysql>create table t1 (id int);
mysql>insert into t1 values(1),(2),(3);
mysql>commit;
6、周二的增量备份
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2
7、周三的数据变化
mysql>create database inc3 charset utf8mb4;
mysql>use inc3;
mysql>create table t1 (id int);
mysql>insert into t1 values(1),(2),(3);
mysql>commit;
8、模拟周三上午10数据库崩溃
[root@db01 ~]# pkill mysqld
[root@db01 ~]# \rm -rf /data/mysql/data/*
数据恢复思路
- 停业务,挂维护页
- 查找可用备份并处理备份:full+inc1+inc2
- binlog:inc2 到故障时间点的binlog
- 恢复全备+增量+binlog
- 验证数据
- 起业务,撤维护页
一、数据恢复前准备(要使用绝对路径)
1、整理full
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/full
--redo-only:不加"--redo-only"时,使用"--apply-log"参数进行整理数据时,就会模拟CSR,进行redo、undo操作。若是使用了"--redo-only"参数,则说明进行数据整理时,只做redo操作,不做undo操作。
--redo-only参数用在整理基文件全备和所有增量合并时,不可以用在最后一次增量数据的整理。
原因: 当使用"--apply-log"进行数据整理时,就会模仿CSR过程,执行redo和undo操作。因为在热备的过程中,可能会存在未提交的事务(别的事务提交,把此事务一起刷新到磁盘),所以若是执行undo操作的话,LSN号就会发生改变。但是两个备份文件的拼接需要上一个备份文件的"last"LSN号和下一个"from"LSN号能够对接上,所以只能执行redo操作,不能执行undo操作。但是最后一个备份文件的拼接时,因为不需要再拼接备份文件,所以可以执行undo操作。所以就不需要"--redo-only"参数。
2、合并inc1到full,并整理备份
[root@db01 ~]#innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full
3、合并inc2到full,并整理备份
[root@db01 ~]#innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/ful
最后一次增量,无"--redo-only"
4、最后整理整体的数据
[root@db01 ~]# innobackupex --apply-log /backup/full
二、截取二进制
1、通过查看"xtrabackup_binlog_info"来获取要截取的二进制的起始位置
[root@db01 ~]#cat /backup/inc2/xtrabackup_binlog_info
mysql-bin.000031 1997 e16db3fd-a6e8-11e9-aee9-000c294a1b3b:1-9
因为我们是在周三发生数据崩溃,周二的时候我们有做增量备份,所以我们要知道周二的时候备份到了哪里。
从查看的结果中我们发现,周二增量备份时,已经备份到了 mysql-bin.000031文件,事件号为1997的位置,或者gtid号为9的位置了。
2、获取要截取的二进制日志位置为终点
[root@db01 ~]#mysqlbinlog /data/binlog/mysql-bin.000031 |grep 'SET'
...省略...
SET @@SESSION.GTID_NEXT= 'e16db3fd-a6e8-11e9-aee9-000c294a1b3b:12'/*!*/;
同过查看 mysql-bin.000031文件,我们可以发现,GTID号已经到了12了,所以只需要截取GTID号10~12的二进制日志数据即可。
TIP:在查看 xtrabackup_binlog_info 文件时,可能会出现多个GTID号,可通过查看mysql-bin.xxxxxx文件,来获取该选择使用哪个GTID号
3、截取二进制日志数据
[root@db01 ~]#mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031>/backup/binlog.sql
4、恢复数据
[root@db01 /]# cp -a /backup/full/* /data/mysql/data/
[root@db01 /]# chown -R mysql. /data/
[root@db01 /]# systemctl start mysqld[root@db01 /]#mysql -uroot -p111111
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql
mysql>set sql_log_bin=1;