首页 Mysql教程Mysql备份恢复

SQL语句

Mysql主从复制

运维派是国内成立最早的IT运维技术社区,欢迎关注公众号:yunweipai

5 备份和恢复

5.1 备份恢复概述

Mysql备份恢复插图

5.1.1 为什么要备份

灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

5.1.2 备份类型
  • 完全备份,部分备份 完全备份:整个数据集 部分备份:只备份数据子集,如部分库或表
  • 完全备份、增量备份、差异备份

增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂

Mysql备份恢复插图(1)

差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单

Mysql备份恢复插图(2)

注意:二进制日志文件不应该与数据文件放在同一磁盘

  • 冷、温、热备份 冷备:读、写操作均不可进行,数据库停止服务 温备:读操作可执行;但写操作不可执行 热备:读、写操作均可执行 MyISAM:温备,不支持热备 InnoDB:都支持
  • 物理和逻辑备份 物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快 逻辑备份:从数据库中“导出”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度
5.1.3 备份什么
  • 数据
  • 二进制日志、InnoDB的事务日志
  • 用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
  • 服务器的配置文件
5.1.4 备份注意要点
  • 能容忍最多丢失多少数据
  • 备份产生的负载
  • 备份过程的时长
  • 温备的持锁多久
  • 恢复数据需要在多长时间内完成
  • 需要备份和恢复哪些数据
5.1.5 还原要点
  • 做还原测试,用于测试备份的可用性
  • 还原演练,写成规范的技术文档
5.1.6 备份工具
  • cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
  • 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来快速备份数据库
5.1.6 基于LVM的快照备份

(1) 请求锁定所有表 mysql> FLUSH TABLES WITH READ LOCK; (2) 记录二进制日志文件及事件位置 mysql> FLUSH LOGS; mysql> SHOW MASTER STATUS; mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE (3) 创建快照 lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME (4) 释放锁 mysql> UNLOCK TABLES; (5) 挂载快照卷,执行数据备份 (6) 备份完成后,删除快照卷 (7) 制定好策略,通过原卷备份二进制日志

5.1.7 实战案例:数据库冷备份和还原

5.2 mysqldump备份工具

5.2.1 mysqldump 说明

逻辑备份工具:

mysqldump, mydumper, phpMyAdmin Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump:是MySQL的客户端命令,通过mysql协议连接至mysql服务器进行备份

命令格式:

mysqldump参考:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump 常见通用选项:

mysqldump的MyISAM存储引擎相关的备份选项:

MyISAM不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作

mysqldump的InnoDB存储引擎相关的备份选项:

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用

5.2.2 生产环境实战备份策略

InnoDB建议备份策略

MyISAM建议备份策略

5.2.3 mysqldump 备份还原实战案例
5.2.3.1 实战案例:特定数据库的备份脚本

5.2.3.2 实战案例:分库备份并压缩

5.2.3.3 实战案例:完全备份和还原

5.2.3.4 实战案例:利用二进制日志,还原数据库最新状态

5.2.3.5 实战案例:mysqldump 和二进制日志结合实现增量备份

[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 |gzip > /backup/all-date +%F.sql.gz

5.2.3.6 实战案例:恢复误删除的表

案例说明:每天2:30做完全备份,早上10:00误删除students,10:10才发现故障,现需要将数据库还原到10:10的状态,且恢复被删除的students表

5.3 xtrabackup备份工具

5.3.1 xtrabackup工具介绍

Percona 公司 官网:www.percona.com percona-server InnoDB --> XtraDB

Xtrabackup备份工具 percona提供的mysql数据库备份工具,惟一开源的能够对innodb和xtradb数据库进行热备的工具 手册:https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

xtrabackup 特点:

  • 备份还原过程快速、可靠

  • 备份过程不会打断正在执行的事务

  • 能够基于压缩等功能节约磁盘空间和流量

  • 自动实现备份检验

  • 开源,免费

    xtrabackup工具文件组成 Xtrabackup2.2 版之前包括4个可执行文件: innobackupex: Perl 脚本 xtrabackup: C/C++,编译的二进制程序 xbcrypt: 加解密 xbstream: 支持并发写的流文件格式

xtrabackup 是用来备份 InnoDB 表的,不能备份非 InnoDB 表,和 MySQL Server 没有交互

innobackupex 脚本用来备份非 InnoDB 表,同时会调用 xtrabackup 命令来备份 InnoDB 表,还会和 MySQL Server 发送命令进行交互,如加全局读锁(FTWRL)、获取位点(SHOW SLAVE STATUS)等。即innobackupex是在 xtrabackup 之上做了一层封装实现的

xtrabackup的新版变化 xtrabackup版本升级到2.4后,相比之前的2.1有了比较大的变化:innobackupex 功能全部集成到 xtrabackup 里面,只有一个 binary程序,另外为了兼容考虑,innobackupex作为 xtrabackup 的软链接,即xtrabackup现在支持非Innodb表备份,并且 Innobackupex 在下一版本中移除,建议通过xtrabackup替换innobackupex

xtrabackup备份过程

Mysql备份恢复插图(3)

备份生成的相关文件 使用innobackupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时,innobackupex还会在备份目录中创建如下文件:

  • xtrabackup_info:文本文件,innobackupex工具执行时的相关信息,包括版本,备份选项,备份时长,备份LSN(log sequence number日志序列号),BINLOG的位置
  • xtrabackup_checkpoints:文本文件,备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的
  • xtrabackup_binlog_info:文本文件,MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置,可利用实现基于binlog的恢复
  • backup-my.cnf:文本文件,备份命令用到的配置选项信息
  • xtrabackup_logfile:备份生成的二进制日志文件
5.3.2 xtrabackup安装

yum install percona-xtrabackup 在EPEL源中 最新版本下载安装: https://www.percona.com/downloads/XtraBackup/LATEST/

5.3.3 xtrabackup用法

xtrabackup工具备份和还原,需要三步实现

  1. 备份:对数据库做完全或增量备份

  2. 预准备: 还原前,先对备份的数据,整理至一个临时目录,

  3. 还原:将整理好的数据,复制回数据库目录中

xtrabackup 选项参考:

https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html

备份:

选项说明:

Prepare预准备:

选项说明:

还原:

选项说明:

还原注意事项:

  1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定,否则--copy-back选项不会覆盖

  2. 在restore之前,必须shutdown MySQL实例,不能将一个运行中的实例restore到datadir目录中

  3. 由于文件属性会被保留,大部分情况下需要在启动实例之前将文件的属主改为mysql,这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成

5.3.4 实战案例:利用xtrabackup实现完全备份及还原

注意:目前percona-xtrabackup-24-2.4.18-1.el8.x86_64.rpm不支持CentOS8上的mariadb-10.3版本

案例1:新版xtrabackup完全备份及还原

案例2:旧版xtrabackup完全备份及还原 1 在源主机备份

2 在目标主机预准备并还原

5.3.5 实战案例:利用xtrabackup完全,增量备份及还原

案例1:新版xtrabackup完全,增量备份及还原

案例2:旧版xtrabackup完全,增量备份及还原

1 在源主机备份

2 在目标主机还原

5.3.6 实战案例:xtrabackup单表导出和导入

本文链接:http://www.yunweipai.com/34246.html

SQL语句

Mysql主从复制

网友评论comments

发表评论

电子邮件地址不会被公开。 必填项已用*标注

暂无评论

Copyright © 2012-2020 YUNWEIPAI.COM - 运维派
扫二维码
扫二维码
返回顶部