MYSQL备份与还原、基于LVM的备份、mysqldump使用、使用完全备份恢复步骤参考、生产环境备份参数参考、xtrabackup介绍

备份与还原

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

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

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
二进制日志文件不应该与数据文件放在同一磁盘

冷、温、热备份
冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持

物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快

逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

备份时需要考虑的因素
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长
备份什么数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件

备份工具
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来快速备份数据库

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

mysqldump使用

mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份

语法：
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3…]
mysqldump [OPTIONS] –A [OPTIONS]

示例：

备份db数据库
mysqldump db > db.sql
*这条命令只备份数据库里的表数据，不会生成创建数据库的语句
*还原前需要自己创建数据库

加上-B可以生成数据库创建语句
mysqldump -B db > db.sql

备份多个数据库
mysqldump -B db1 db2 db3 > db.sql

备份所有数据库
mysqldump -A > db.sql
*这条命令会备份information_schema和performance_schema之外的所有数据库

备份db数据库中的test表
mysqldump db test > db.sql

mysqldump常见选项：
-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 一起使用实现，此时只刷新一次日志。

示例：

现有二进制日志文件
mysql-bin.000001
mysql-bin.000002
mysql-bin.index

mysqldump -F -A --single-transaction --master-data=2 > db.sql

执行后生成新文件，此时列表如下
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.index

同时生成的文件里会记录二进制日志位置，如果不加--single-transaction，就会每备份一个库刷新一次二进制日志

–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 不缓存查询，直接输出，加快备份速度

MyISAM备份选项：
支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作锁定方法如下：
-x,–lock-all-tables：加全局读锁，锁定所有库的所有表，同时加–single-transaction或–lock-tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库

-l,–lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,–skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

InnoDB备份选项：
支持热备，可用温备但不建议用
–single-transaction
此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将–single-transaction选项和–quick结合一起使用

完全备份恢复步骤参考

假设每天0点做完全备份，结果中午的时候mysql服务器硬盘突然GG，数据库丢失。
当天0点用以下命令进行完全备份
mysqldump -A > db.sql

mysql的二进制日志文件如下
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.index

下面开始还原步骤
1.先限制普通用户访问
2.查看完全备份中的二进制起始位置，在db.sql中找到以下句子

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-000002',MASTER_LOG_POS=361
可以看到起始位置是02的361

3.启动mysql服务，如果自启动的就不用管，先进入mysql记录二进制日志位置

一般mysql启动后会生成新的二进制文件，此时位置应该是新文件的245，日志文件列表应该如下
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.index
05是新生成的，只需要导出02的245开始到04结尾即可

4.使用命令导出二进制日志

mysqlbinlog --start-postion=361 mysql-bin.000002 > binlog.sql
mysqlbinlog mysql-bin.000003 >> binlog.sql
mysqlbinlog mysql-bin.000004 >> binlog.sql

5.在mysql中临时关闭二进制日志

set sql_log_bin=off;

6.导入完全备份再导入二进制日志记录

source /data/bak/db.sql
source /data/bak/binlog.sql

至此数据恢复完成，检查没问题即可正常使用，记得恢复sql_log_bin=on

生产环境备份参数参考

InnoDB建议备份策略

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACDIR/fullbak_$TIME.sql
*指定的字符集要匹配真实字符集

MyISAM建议备份策略

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACDIR/fullbak_$TIME.sql

分库备份脚本

使用循环分库备份
for db in `mysql -e 'show database'|grep -Ev '(information_schema|performance_schema|Database)$'`;do mysqldump -B $db --single-transaction --master-data=2 |gzip > /data/bak/$db.sql.gz;done

不使用循环分库备份
mysql -e 'show database'|grep -Ev '(information_schema|performance_schema|Database)$'|sed -rn 's#(.*)#mysqldump -B \1 --single-transaction --master-data=2 |gzip > /data/bak/\1\.sql\.gz#p'|bash

xtrabackup介绍

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

xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQLServer 没有交互

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

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/

用法：
innobackupex [option] BACKUP-ROOT-DIR
–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

–apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态

–use-memory：和–apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G

–export：表示开启可导出单独的表之后再导入其他Mysql中

–redo-only：此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并

备份生成的相关文件

使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
(1)xtrabackup_info：innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence 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：备份生成的日志文件

备份还原步骤参考

完全备份和恢复：

1.做完全备份到/bak
xtrabackup --backup --target-dir=/bak/

2.还原前把备份复制到临时目录做准备
cp /bak/* /bak2/

3.回滚未完成的事务
xtrabackup --prepare --target-dir=/bak2/

4.确保数据库目录是空的，且服务停止，然后复制到数据库目录
xtrabackup --copy-back --target-dir=/bak2/
这条命令会读取数据库配置中指定的数据库路径作为目标路径，这里只需要指定上面处理好的备份路径

5.还原文件属性
chown -R mysql:mysql /var/lib/mysql

6.启动服务
至此还原完毕。

增量备份和恢复：
先做完全备份：

xtrabackup --backup --target-dir=/bak/base

进行第1次增量备份

xtrabackup --backup --target-dir=/bak/z1 --incremental-basedir=/bak/base
目录指定完全备份就完全备份的增量

进行第2次增量备份

xtrabackup --backup --target-dir=/bak/z2 --incremental-basedir=/bak/z1
指定是哪个备份的增量

还原步骤：

1.同样复制一份bak2，清空数据库目录和停止服务，然后对bak2进行预处理

xtrabackup --prepare --apply-log-only --target-dir=/bak2/base
加上--apply-log-only可以让他不回滚不完整的事物

2.合并第1次增量备份到完全备份，

xtrabackup --prepare --apply-log-only --target-dir=/bak2/base --incremental-dir=/bak2/z1

3.合并第2次增量备份到完全备份，

xtrabackup --prepare --target-dir=/bak2/base --incremental-dir=/bak2/z2
这里因为是最后一次增量备份所以不加--apply-log-only
如果不是最后一次还是要加上

4.复制到数据库目录，同样要保证目录是空的且服务未启动

xtrabackup --copy-back --target-dir=/bak2/base

5.跟上面一样要还原属性再启动服务