[MYSQL] mysql备份方案

原创

大大刺猬

发布于 2024-12-16 18:30:23

4550

文章被收录于专栏：大大刺猬大大刺猬

导读

我们之前讲过在没有备份的时候怎么恢复数据,从ibd数据文件恢复, 从binlog日志恢复, 甚至从xfs文件系统恢复. 但这些都不能保证一定能恢复成功. 所以每次都会提到备份, 却发现我还没讲过mysql备份相关的文章. 啊! 这?

所以今天来讲讲mysql的备份(~~又水一篇~~)

备份分类

先来看看备份的分类, 分类方式又很多,

按照是否停机,可以分为冷备和热备,
按照备份结果,可以分为物理备份或者逻辑备份
按照备份对象/类型? 还能分全备和增备...

每种备份往往不只是属于一种, 比如mysqldump -A是全备,也是逻辑备份,还是热备. 所以我们不按照备份类型来讲, 而直接按照备份方案来讲.

备份目标

备份目标当然是mysql啦. 开个玩笑.

这里所说的备份目标(target)是指在哪备份, 对谁做备份. 比如mysql主从环境, 是对主库做备份还是从库做备份呢?

如果对主库做备份, 备份那么会影响业务(晚上业务也有跑批的), 业务也会影响备份(比如业务在跑DDL).
如果对从库做备份, 那么主从存在延迟的时候, 备份时间点就不是要恢复的时间点了.而且存在主从数据不一致的问题. 从库可能存在人为的写入,很多环境是没有设置super_read_obly的. 除了人为原因外, 还有数据库自身的原因,比如之前load data存在double为-0的情况. 还也可能是搭建的时候主从数据就已经不一致了.

所以,出于数据一致性的考虑, 建议主库备份; 出于性能的考虑,建议从库备份.

还有个target, 是指备份到哪? 本地? 还是nas? 还是备份完拷贝到其它地方(比如NBU拷走), 或者要上传到某些cos之类的对象存储上?

备份在本地的话, 本地空间的考虑就得评估下, 是否压缩(时间换空间)? 保留几天?

备份到其它地方的话, 就得考虑网络因素了.

这个不太好建议, 得根据实际情况来. 重要的是备份的有效性,光备份了,用的时候有问题就尴尬了. 所以定期做恢复演练也是非常重要的.

备份工具的选择

mysql作为一个免费且开源数据库, 拥有众多的备份工具. 这里就简单介绍几款比较流行的备份工具

mysqldump (推荐)

mysqldump是最简单的mysql备份工具, 也是最常用的. 可以备份全库,某几个库,某几张表, 还能加where条件. 非常的方便. 但是速度慢.

全库备份命令参考:

mysqldump -uroot -p123456 \
--all-databases \
--single-transaction \
--master-data=2 \
--events \
--triggers \
--routines \
--hex-blob \
--max-allowed-packet=1073741824 > all_db_20241216.sql

备份出来的sql语句, 且开头存在set sql_log_bin=off, 即导入数据的时候不会写binlog,有主从的环境需要注意下.(5.7.27之类的老版本没得这个问题.....),

--all-databases 不会备份sys库.

由于导出的只有一个sql文件, 所以导入的时候也存在性能问题,尤其是数据量大的时候需要注意下. 好在我们之前写过mysql并发导入脚本, 该脚本还能处理set sql_log_bin=off问题, 也处理了一些其它问题(比如disable_engine含有myisam的mysql5.7环境).

mydumper/myloader

由于mysqldump存在性能问题, 所以就有了第三方开源免费的 mysql逻辑备份工具--mydumper, 导出是mydumper,导入是同一套的myloader, 支持并发, 那速度就上去了. 但毕竟是第三方的工具, 很多环境不允许使用.... 而且导出多个库的时候写法比较麻烦....

参考

# 导出全库
mydumper -h 127.0.0.1 -P 3311 -u root -p 123456 -t 4 -o testmydumper  -L testmydumper.log

# 导出指定库
mydumper -h 127.0.0.1 -P 3311 -u root -p 123456 -t 4 --database db2 db1 -o testmydumper  -L testmydumper.log

# 导出多个库
mydumper -h 127.0.0.1 -P 3311 -u root -p 123456 -t 4 -x '^(db1\.|db2\.)' -o testmydumper  -L testmydumper.log

# 导出某张表
mydumper -h 127.0.0.1 -P 3311 -u root -p 123456 -t 4 --database db2 -x db2.t2 -o testmydumper  -L testmydumper.log

# 不覆盖导入
myloader -h 127.0.0.1 -u root -P 3311 -p 123456 -t 4 -d testmydumper

# 覆盖导入
myloader -h 127.0.0.1 -u root -P 3311 -p 123456 -t 4 -d testmydumper -o

trigger,routine之类的和mysqldump一样的,就不介绍了

xtrabackup (推荐)

xtrabackup貌似是mysql种唯一开源的物理备份工具了. 物理备份优点就是速度快, 嘎嘎快而且还能增倍(逻辑备份得通过binlog来实现增备). 如果要压缩的话, 还需要安装qpress, 属于稍微麻烦一丢丢的事. 既然属于第三方, 就存在有些客户不允许使用的情况.

xtrabackup分为2.x和8.x版本, 前者是针对mysql5.7环境, 后者针对mysql8.0环境. 我们之前还专门讲过xtrbackup的备份过程的. 感兴趣的可以往前面翻一翻.

2.4 官方文档https://docs.percona.com/percona-xtrabackup/2.4/using_xtrabackup/privileges.html

8.0 官方文档:https://docs.percona.com/percona-xtrabackup/8.0/index.html

xtrabackup是percona公司开源的mysql物理备份工具, 备份软件必须在mysql服务器上(备份的时候其实就是cd到data目录下面拷贝数据文件....) 2.2,2.1版本使用innobackupex备份myisam和innodb, (xtrabackup仅备份innodb)

2.3,2.4版本使用xtrabackup备份myisam和innodb (innobackupex只是个软连接,为了兼容性)

8.0版本备份的是Mysql8.0 2.x版本备份的是mysql 5.5,5.6,5.7版本

xtrabackup 全备

备份目录不存在的时候,会自动创建

xtrabackup --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf \
--host=192.168.101.21 \
--port=3308 \
--user=root \
--password=123456 \
--parallel=2 \
--target-dir=/data/backup/t20230303 \
--backup

xtrabackup 增备

第一次增量备份, 指定上次全备的路径(--incremental-basedir=/data/backup/t20230303)

    xtrabackup --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf \
    --host=192.168.101.21 \
    --port=3308 \
    --user=root \
    --password=123456 \
    --parallel=2 \
    --incremental-basedir=/data/backup/t20230303 \
    --target-dir=/data/backup/t20230304_inc \
    --backup

后面的增量备份指定上次增量备份的路径. (之前更之前的,比如第一次的也行,但增量会比较多.)

    xtrabackup --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf \
    --host=192.168.101.21 \
    --port=3308 \
    --user=root \
    --password=123456 \
    --parallel=2 \
    --incremental-basedir=/data/backup/t20230304_inc \
    --target-dir=/data/backup/t20230305_inc \
    --backup

xtrbackup 恢复(有增备的时候)

先恢复最近一次的全备, 然后恢复第一次增备,第二次.... (除了最后一次都要加 --apply-log-only)

恢复最后一次增备的时候, 就不要使用 --apply-log-only了

--apply-log-only : 仅应用日志,不回滚或提交日志里面的数据

#第一次完整备份的prepare
xtrabackup --prepare --target-dir=/data/backup/t20230303 --apply-log-only

#第一次增量备份的prepare
xtrabackup --prepare --target-dir=/data/backup/t20230303 --incremental-basedir=/data/backup/t20230304_inc  --apply-log-only

#第二次....

#最后一次增量备份的prepare (不要 --apply-log-only了)
xtrabackup --prepare --target-dir=/data/backup/t20230303 --incremental-basedir=/data/backup/t20230305_inc 


#mysql服务器删除/move掉 data_dir, redo_dir
mv /data/mysql_3308/mysqldata /data/mysql_3308/mysqldata_old
mv /data/mysql_3308/mysqllog/redolog /data/mysql_3308/mysqllog/redolog_old

#move或者copy.  空间不多的可以使用move, 空间多的使用copy
#xtrabackup --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf --move-back --target-dir=/data/backup/t20230303 --rsync
xtrabackup --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf --copy-back --target-dir=/data/backup/t20230303 --rsync

#修改data redo 目录的权限
chown mysql:mysql /data/mysql_3308/mysqldata /data/mysql_3308/mysqllog/redolog -R

#启动Mysqld验证
systemctl start mysqld_3308

xtrbackup 恢复(仅全备的时候)

#prepare
xtrabackup --prepare --target-dir=/data/backup/t20230303

#移除旧的data,redo
mv /data/mysql_3308/mysqdata /data/mysql_3308/mysqdata_old
mv /data/mysql_3308/mysqllog/redolog /data/mysql_3308/mysqllog/redolog_old

#恢复(move/copy)
xtrabackup --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf --copy-back --target-dir=/data/backup/t20230303 --rsync

#修改权限并启动验证
chown mysql:mysql /data/mysql_3308/mysqldata /data/mysql_3308/mysqllog/redolog -R
systemctl start mysqld_3308

innobackupex 的使用

innobackupex和xtrbackup使用有丢丢区别. (适用于2.3 2.4)

现在还有备份需求的,应该都不会使用到这个命令了... 所以我们简单过一下即可

# 全备
innobackupex --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf \
--host=192.168.101.21 \
--port=3308 \
--user=root \
--password=123456 \
--parallel=2  /data/backup/full_back

# 恢复
#全备prepare
innobackupex --apply-log /data/backup/full_back 

#mysql服务器删除/move掉 data_dir, redo_dir
mv /data/mysql_3308/mysqldata /data/mysql_3308/mysqldata_old
mv /data/mysql_3308/mysqllog/redolog /data/mysql_3308/mysqllog/redolog_old

#move或者copy.  空间不多的可以使用move, 空间多的使用copy
innobackupex --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf --rsync --copy-back /data/backup/full_back

#修改权限并启动验证
chown mysql:mysql /data/mysql_3308/mysqldata /data/mysql_3308/mysqllog/redolog -R
systemctl start mysqld_3308

mysqlbackup

企业版, 要money. 优点就是功能完善. 但使用mysql的客户大部分都是使用的社区版.

mysqlpump

也是逻辑备份的, 但不好用, 官方已经将它丢弃了.

mysqlpump is deprecated as of MySQL 8.0.34; expect it to be removed in a future version of MySQL. You can use such MySQL programs as mysqldump and MySQL Shell to perform logical backups, dump databases, and similar tasks instead.

myshell

myshell是innodb cluster种的一部分, 一种特殊的mysql客户端, 就是封装了一部分比较好用的功能. 比如备份.

支持实例,库,表的备份, 语法如下

util.dumpInstance(outputUrl[, options]) 
util.dumpSchemas(schemas, outputUrl[, options])
util.dumpTables(schema, tables, outputUrl[, options])

例子:

# 创建备份目录, 要求为空 
mkdir -p /data/backup/t20241216

# 使用myshell登录数据库
mysqlsh -uroot -p123456 -P3314

# 发起备份
util.dumpInstance("/data/backup/t20241216")

克隆插件

mysql克隆插件可以用来备份数据和搭建主从. 是8.0.17引入的, 是物理拷贝文件的方式. 所以就又可以用来备份.... 而且这是实例级的备份, 已经验证了恢复步骤. 就是每次备份成本优点高...

例子

-- 插件安装(主从都要)
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME = 'clone';

-- 主库创建克隆账号
create user clone_user@'%' identified by '123456';
-- 并授权
GRANT BACKUP_ADMIN ON *.* TO 'clone_user';
-- GRANT SELECT ON performance_schema.* TO clone_user;
-- GRANT EXECUTE ON *.* to clone_user;
flush privileges;

-- 从库设置白名单
SET GLOBAL clone_valid_donor_list = "127.0.0.1:3380"; 
-- 开始同步数据
CLONE INSTANCE FROM clone_user@127.0.0.1:3380 IDENTIFIED BY "123456";
-- 查看进度的
select STATE, CAST(BEGIN_TIME AS DATETIME) as "START TIME",
CASE WHEN END_TIME IS NULL THEN
LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
ELSE
LPAD(sys.format_time(POWER(10,12) * (UNIX_TIMESTAMP(END_TIME) - UNIX_TIMESTAMP(BEGIN_TIME))), 10, ' ')
END as DURATION
from performance_schema.clone_status;

select into outfile & load data

上面2个备份方式可能有点不常见, 所以我们再来看看更常见一点的备份方法. 比如直接select出数据. 缺点的话就是只能单张表的备份, 当然可以写脚本来实现批量备份.

例子:

select id,name into outfile '/tmp/t11.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from t11;

导入的话, 就使用load data, 当然也可以自己拼接为SQL语句然后导入

load data local infile '/tmp/t11.txt' ignore into table t11
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, name);

停机备份目录

以前没得这么多工具的时候, 备份就是直接关库, 然后把目录拷走, 这种方式很快, 毕竟物理的, 但缺点就是要停库, 属于冷备.

例子:

# 停
mysql -e 'shutdown'
# 备
cp -ra /data /backup/t20241216
# 启
mysqld

mysql workbench 之类的客户端连接工具

有些开发直接使用mysql workbench, navicate之类的图形化工具登录数据库, 然后查询数据并导出来作为备份, 这种方式很少, 通常是开发做的, 优点就是: 不用dba操心了.

应用自己实现

有些应用程序自己就带有备份功能, 但备份功能实现可能不那么靠谱, 之前也讲过案例, 就是每次查询2000W然后备份20W. 估计是以为limit就不会全部查询出来了, 殊不知Limit是server层实现的, 而数据查询是存储引擎层实现的. 导致引擎层把数据全部查询出来了.

flush table for export

还可以直接锁表, 然后拷走物理文件的方式实现备份. 不建议使用, 仅对于要求快速备份某张大表的时候可以试下.

-- 锁
flush table sbtest1 for export; -- 不要退出, 退出了就不锁表了. 就没得.cfg文件了

-- 考
cp sbtest1.cfg sbtest1.ibd /data/mysql_3318/mysqldata/db1/

-- 解
unlock tables;

备份方案1: xtrabackup 全备+增备

本方案使用xtrabacup的全备和增备功能来做.

没3天一个全备, 没全备的时候做基于上一次备份(全/增)的增备. binlog保存3天(冗余). 全备和增备保存9天. 这样就可以恢复到任一时刻了.

如果空间不够的话, 可以改为每7天一个全备, 每天一个增备. 建议增备期间的binlog都留着, 以防万一.

备份方案2: mysqldump 全备

对于无法使用第三方工具的情况, 就只能使用mysql自带的mysqldump命令来做了, 建议每天一个全备. binlog保留1周. 空间不足的可酌情保留.

备份方案3: 主库物理备份 + 从库逻辑备份

对于某些要求高的系统, 可以在主库做物理备份的同时, 从库再上个逻辑备份. 万一某种备份有问题, 也影响不大, 毕竟同时有问题的概率太低了. 但对于空间的使用较多. (从库本身就有备份的作用在)

补充和总结

上面只说了binlog保留多少天, 实际情况还可以备份Binlog, 可以直接拷贝走, 也可以使用mysqlbinlog命令来备份.

实际备份方案按照实际情况来, 自己搭配使用

不管使用哪种备份工具或者策略, 都要规范化. 最好是自动化. 很久以前写了个自动备份恢复的脚本,恢复的时候只需要选择一个时间即可,非常的方便.

定期做恢复演练是很必要的.

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据备份

mysql

备份

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据备份

mysql

备份

登录后参与评论

0 条评论

热度