11.MySQL备份详解

0.备份介绍
1.备份类型
2.冷、温、热备份
3.备份工具介绍
4.备份工具详解一：mysqldump
5.备份工具详解二：xtrabackup
6.分库备份的指令

0.备份介绍

0.1为什么要备份

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

0.2备份注意要点

能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据

0.3还原要点

做还原测试，用于测试备份的可用性
还原演练

1.备份类型

1.1完全备份，部分备份

完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表

1.2完全备份、增量备份、差异备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂    
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

NOTE：二进制日志文件不应该与数据文件放在同一磁盘

2.冷、温、热备份

2.1介绍

冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行

MyISAM：温备，不支持热备 InnoDB：都支持

2.2物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

2.3备份时需要考虑的因素

温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长

2.4备份什么

数据
二进制日志、 InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件

3.备份工具介绍

3.1介绍

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup： 从MariaDB 10.1.26开始集成，基于PerconaXtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition组件
mysqlhotcopy： PERL 语言实现， 几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、 FLUSH TABLES和cp或scp来快速备份数据库

逻辑备份工具： mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份
    mysqldump [OPTIONS] database [tables]
    mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
    mysqldump [OPTIONS] –A [OPTIONS]
mysqldump参考：
    https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

3.2基于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) 制定好策略，通过原卷备份二进制日志

4.备份工具详解一：mysqldump

4.1mysqldump常见选项

-A， --all-databases 备份所有数据库，含create database
-B , --databases db_name… 指定备份的数据库，包括create database语句
-E, --events：备份相关的所有event scheduler
-R, --routines：备份所有存储过程和自定义函数
--triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 指定字符集
--master-data[=#]： 此选项须启用二进制日志
    1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
    2：记录为注释的CHANGE MASTER TO语句
        此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（ 除非开启--single-transaction）
-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。 
    建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x， --master-data 一起使用实现，此时只刷新一次日志
--compact 去掉注释，适合调试，生产不使用
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,--no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关时需要使用
-f, --force 忽略SQL错误，继续执行
--hex-blob 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY， BLOB， BIT的数据类型的列时使用，避免乱码
-q, --quick 不缓存查询，直接输出，加快备份速度

5.备份工具详解二：xtrabackup

5.1xtrabackup简要介绍

Percona
    官网： www.percona.com
    percona-server
    InnoDB --> XtraDB
Xtrabackup
    percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
    手册： https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
特点：
    备份还原过程快速、可靠
    备份过程不会打断正在执行的事务
    能够基于压缩等功能节约磁盘空间和流量
    自动实现备份检验
    开源，免费
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 之上做了一层封装实现的

5.2xtrabackup备份过程

xtrabackup备份过程.jpg

5.3xtrabackup的用法

5.3.1安装

xtrabackup版本升级到2.4后， 相比之前的2.1有了比较大的变化：
    innobackupex 功能全部集成到 xtrabackup 里面，只有一个 binary程序， 另外为了兼容考虑， innobackupex作为 xtrabackup 的软链接，
    即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在一版本中移除，建议通过xtrabackup替换innobackupex
xtrabackup安装：
    yum install percona-xtrabackup 在EPEL源中
    最新版本下载安装：
    https://www.percona.com/downloads/XtraBackup/LATEST/

5.3.2备份

备份: innobackupex [option] BACKUP-ROOT-DIR

选项说明： https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
--user：该选项表示备份账号
--password：该选项表示备份的密码
--host：该选项表示备份数据库的地址
--databases：该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；如： "xtra_test dba_test"，同时，在指定某数据库时，
            也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file：该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental-basedir：该选项指定为前一次全备份或增量备份的目录， 与--incremental同时使用
--incremental-dir：该选项表示还原时增量备份的目录
--include=name：指定表名，格式： databasename.tablename

Prepare： innobackupex --apply-log [option] BACKUP-DIR
选项说明：
--apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。
              因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory：和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等， 推荐1G
--export：表示开启可导出单独的表之后再导入其他Mysql中
--redo-only：此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并

5.3.3还原

还原： innobackupex --copy-back [选项] BACKUP-DIR

innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。
            使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本

还原注意事项：
1.datadir 目录必须为空。除非指定innobackupex --force-non-empty�directorires选项指定，否则--copy-back选项不会覆盖
2.在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户chown -R mysql:mysql /data/mysql以上需要在用户调用innobackupex之前完成 --force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败

5.3.4备份生成的相关文件

使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、 MERGE、 CSV和
ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时， innobackupex
还会在备份目录中创建如下文件：
(1)xtrabackup_info： innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(logsequence number日志序列号)， BINLOG的位置
(2)xtrabackup_checkpoints：备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会
  包含一个日志序列号LSN。 LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
(3)xtrabackup_binlog_info： MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
(4)backup-my.cnf：备份命令用到的配置选项信息
(5)xtrabackup_logfile：备份生成的日志文件

5.4新版xtrabackup完全备份及还原

5.4.1准备工作

1 在原主机做完全备份到/backups
    xtrabackup --backup --target-dir=/backup/
    scp -r /backup/* 目标主机:/backup
2 在目标主机上
    1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
          xtrabackup --prepare --target-dir=/backup/
    2）复制到数据库目录
        注意：数据库目录必须为空， MySQL服务不能启动
        xtrabackup --copy-back --target-dir=/backup/
    3）还原属性
        chown -R mysql:mysql /var/lib/mysql
    4）启动服务
        systemctl start mariadb

5.4.2备份过程

示例：新版xtrabackup完全，增量备份及还原

1 备份过程
    1）完全备份： xtrabackup --backup --target-dir=/backup/base
    2）第一次修改数据
    3）第一次增量备份
        xtrabackup --backup --target-dir=/backup/inc1 --incremental�basedir=/backup/base
    4）第二次修改数据
    5）第二次增量
        xtrabackup --backup --target-dir=/backup/inc2 --incremental�basedir=/backup/inc1
    6） scp -r /backup/* 目标主机:/backup/
    7） 备份过程生成三个备份目录
        /backup/{base， inc1， inc2}

5.4.2还原过程

    1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
        xtrabackup --prepare --apply-log-only --target-dir=/backup/base
    2）合并第1次增量备份到完全备份，
        xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
    3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
        xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
    4）复制到数据库目录，注意数据库目录必须为空， MySQL服务不能启动
        xtrabackup --copy-back --target-dir=/backup/base
    5）还原属性： chown -R mysql:mysql /var/lib/mysql
    6）启动服务： systemctl start mariadb

6.分库备份的指令

方案1：
for db in `mysql -e 'show databases'|grep -vE '^(information_schema|performance_schema|Database)$'`;
do 
mysqldump -B $db --single-transaction --master-data=2 > /data/$db.sql;
done

方案2：
for db in `mysql -e 'show databases'|grep -vE '^(information_schema|performance_schema|Database)$'`;
do 
mysqldump -B $db --single-transaction --master-data=2 |gzip > /data/$db.sql.gz;
done

方案3：
mysql -e 'show databases'|grep -vE '^(information_schema|performance_schema|Database)$'|sed -rn 's@(.*)@mysqldump -B \1 --single-transaction --master-data=2 |gzip > /data/\1\.sql\.gz@p'|bash
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,651评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,468评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,931评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,218评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,234评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,198评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,084评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,926评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,341评论 1赞 311
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,563评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,731评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,430评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,036评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,676评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,829评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,743评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,629评论 2赞 354