mysql数据备份
数据备份
备份建议：
- 优先关闭数据的网络访问，避免备份时，有新数据写入，导致数据不一致等
备份类型：
注意： 二进制日志和数据文件必须分开放，不可在同一磁盘，不然两个同时损坏或丢失就GG了
完全备份，部分备份：
- 完全备份： 整个数据集，一般2-3份
- 部分备份： 只备份数据子集，如部分库或表
完全备份、增量备份、差异备份：
- 增量备份： 仅备份最近一次完全备份或增量备份后变化的数据，基于前面的基础，有变动的部分备份。速度较快，还原复杂
- 差异备份： 每一次都是，从当前备份时间到原始数据的所有变化数据。备份较慢，还原简单
冷、温、热备份：
- 冷备： 读、写操作均不可进行，数据库停止服务
- 温备： 读操作可执行；但写操作不可执行
- 热备： 读、写操作均可执行
MyISAM：温备，不支持热备（不支持事物，加只读锁实现）
InnoDB：都支持（事物隔离repeatable-read，幻读实现）
物理、逻辑备份：
- 物理备份： 停服务直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
- 逻辑备份： 用工具，从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
备份内容：
数据、二进制日志、innodb的事物日志
用户账号、权限设置、存储过程、函数、触发器、事件调度器
服务器配置文件
备份时的关注点：
- 能容忍最多丢失多少数据
- 备份产生的负载
- 备份过程的时长
- 温备的持锁多久
- 恢复数据需要在多长时间内完成
- 需要备份和恢复哪些数据
备份工具：
cp、tar等：
- 物理备份，使用于所有存储引擎。只支持冷备、完全、部分备份
lvm的快照：
- 先加全局读锁，快照完成后解锁，几乎热备，借助文件洗头工工具进行备份
mysqlduump：
- 逻辑备份工具，使用所有存储引擎，对myisam进行温备。支持完全备份。innodb支持热备，结合binlog的增量备份
xtrabackup：
- 由percona提供支持对innodb做热备的工具（物理），支持完全、增量
mariadb backup：
- mariadb 10版本集成，基于percona xtrabackup
mysqlbackup：
- 热备份，mysql企业版
基于lvm快照实现温备份：
1）锁定所有表
mysql
->flush tables with read lock;
2）记录二进制文件及事件的位置
mysql
->flush logs; #s生成新日志
show master status; #查看当前日志是哪个
mysql -e 'show master status' > 路径
3）创建快照
#快照大小建议根据当前库的数据大小创建
lvcreate -L 1G -s -p r -n data_snap /dev/数据所在卷
4）解锁
mysql
->unlock tables;
5）挂载快照，备份数据，将快照中的cp、tar到另外的地方去
6）备份完成后，删除快照
7）写计划任务，定期复制二进制日志
冷备份及还原的方法：
1）原始数据库，停服务，备份数据
systemctl stop mariadb
scp -a /var/lib/mysql 2.2.2.25:/backup/
scp -a /data/binlog 2.2.2.25:/backup/
scp -a /etc/my.cnf 2.2.2.25:/backup/
2）模拟原数据库损坏
rm -rf /var/lib/mysql
3）恢复原数据库，备份主机把数据还回去
rsync -a /backup/mysql 2.2.2.15:/var/lib/mysql
rsync -a /backup/binlog 2.2.2.15:/data/binlog
rsync -a /backup/my.cnf 2.2.2.15:/etc/my.cnf
systemctl start mariadb
mysqldump备份工具：
mysqldump [选项] 库 [表];
通用选项：
-B 库1 [库2,..] 导出指定库、多个库
-A 导出所有库
-E 备份所有的事件
-R 备份所有存储过程和自定义函数
--triggers 备份表相关的触发器，默认使用
--skip-triggers 不备份触发器
--default-character-set=utf8 指定字符集
--master-data=1 在备份完成的文件中记录当前二进制日志文件使用到哪个位置了，需要开启二进制日志才可用，必用选项
#master-data此选项的参数作用：
1 备份之前执行：change master to语句,默认为1，适合主从复制
2 备份时，加一条注释：change master to语句，适合单机使用
-F 备份前生成新日志文件，单独用时可能会多次刷新生成新文件，通过和--single-transaction或-x，--master-data 一起使用，此时只刷新一次二进制日志
--compact 去掉注释，适合调试，生产不使用
-d 只备份表结构,不备份数据
-t 只备份数据,不备份表结构,即create table
-n 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql表或相关时需要使用
-f 忽略SQL错误，继续执行
--hex-blob 二进制的数据用16进制存，包括BINARY， VARBIN/ARY，BLOB，BIT的数据类型时使用，避免乱码
-q 不查询缓存，直接输出，加快备份速度
--ignore-table=库.表 导出时忽略表，不支持通配符，只能一个一个写
--tables 表1 ... 导出指定表
适用myisam的选项：
-x 加全局读锁，同时加--single-transaction或--lock-tables选项会关闭此选项功能，要注意的是数据量大时，可能会导致长时间无法并发访问数据库
-l 系统自动加锁，哪个库正在备份加哪个，其他开放，默认为on,备份MyISAM的多个库时,可能会造成数据不一致
--skip-lock-tables 禁用系统自动加锁
#注：以上选项对InnoDB存储引擎一样生效，实现温备，但不推荐使用
例： myisam引擎温备全库
#全库备份
mysqldump –uroot -p –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > /backup/fullbak_$(date +%F).sql
适用innodb的选项：
支持事务和幻读，所以热备
--single-transaction 开始备份前，开启一个事务，基于事务幻读保障数据完整（所以只能是读已提交或者可重复读的事物隔离级别）
示例： innodb引擎热备全库
#-A时，备份了mysql库，它里面有系统所有存储过程、函数、触发器，所以可以省略：F、E、R、triggers，但单独库时这些必须加
mysqldump –uroot -p –A --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > /backup/fullbak_`date +%F`.sql
示例： 只备份指定表的表结构
mysqldump -uroot hj --single-transaction --default-character-set=utf8 -q -d --tables express_log terminal_log >> /tmp/xx.sql
基于mysqldump工具全库备份
1）备份原数据库
mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 > all.sql
2）模拟原数据库损坏
mysql -uroot -p123456
->drop table students;
3）恢复数据库
#先关闭数据的网络访问功能，避免其他人访问错误
#必须改配置文件
vim /etc/my.cnf
[mysqld]
skip-networking=1
systemctl restart mariadb
#恢复数据
mysql -uroot -h127.0.0.1
->show variables like "sql_log_bin"; 查看二进制日志状态
set sql_log_bin=0; 临时关闭二进制日志
\. all.sql
select * from students;
循环分库备份（除系统库外）
方法一：
for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`
do mysqldump -B $db | gzip > /data/$db.sql.gz
done
方法二：
mysql -uroot -e 'show databases' \
|grep -Ev '^(Database|information_schema|performance_schema)$' \
|sed -rn 's#(.*)#mysqldump -B \1 | gzip > /data/\1.sql.gz#p' \
|bash
方法三：
mysql -uroot -e 'show databases' \
|grep -Ev '^(Database|information_schema|performance_schema)$' \
|while read db
do mysqldump -B $db | gzip > /data/$db.sql.gz
done
方法四：
mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p' |bash
数据库备份脚本：
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=123456
[ -d "$DIR" ] || mkdir $DIR
for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ev "^Database|.*schema$"`
do
mysqldump -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
done
基于二进制日志还原到最新数据
需要有全库备份
注意：
- 有时候，虽然数据恢复了，但删表后的数据可能有逻辑错误，如：删的库存表，之后来了新订单，导致订单表更新了，但库存表没有更新
所以，为了避免这种错误，可以选择不要删表后面的操作，把二进制日志中的删表操作后的sql都不要
1）二进制日志独立存放
vim /etc/my.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin
2）完全备份
mysqldump -uroot -A -F --default-character-set=utf8 --single-transaction --master-data=2 | gzip > /backup/all.sql.gz
3）模拟数据修改
mysql -h127.0.0.1
->insert students (name,age,gender)value('mage',20,'M');
insert students (name,age,gender)value('wang',22,'M');
4）模拟原数据库损坏
mysql -uroot -p123456 -e 'drop table students'
5）还原完整数据
恢复时记得暂停日志记录
#关闭二进制日志，必须关闭，否则恢复时执行命令也被记录到新日志，不容易排查
mysql
->set sql_log_bin=0;
#恢复到备份时的数据
gzip -d all.sql.gz
mysql < all.sql
systemctl restart mariadb
6）用二进制日志还原最新数据
#查看备份时的日志记录位置
grep '^-- CHANGE MASTER TO' /data/all.sql
#查看现在的日志
mysql -e 'show master logs'
#只获取完全备份那个日志点后面的内容，因为后面的数据就是新增加的
#要注意的是，用grep把删除的操作去除，否则等于白做
mysqlbinlog /data/mariadb/mysql-bin.000002 --start-position=371|grep -v 'DROP TABLE' > /data/new.sql
#恢复数据
mysql
->set sql_log_bin=0;
\. /data/new.sql
set sql_log_bin=1;
增量备份：
需要基于完全备份
1）完全备份数据
mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/all-`date +%F`.sql
2）查看备份时的日志位置
grep '^-- CHANGE MASTER TO' /data/all.sql
3）增量备份数据
#完全备份后面的内容，都是增量备份的内容
mysqlbinlog --start-position=字节位置 /backup/备份时的二进制日志 > /backup/new-$(date +%F).sql
4）每次备份后，都要将二进制日志和备份文件拷到存放备份数据的主机，避免出现重大事故，要提桶跑路
xtrabackup备份工具：
percona公司的产品
该公司有自己对mysql二次开发的数据库： percona-server
且对innodb做了升级改进： xtradb 也是mariadb默认使用的innodb
2.4版本支持mysql 5.5、5.7、8.0和mariadb 5.5
8.0版本仅兼容MySQL 8.0.x、Percona Server for MySQL 8.0.x 及兼容版本、mariadb 10.3以上
mairadb 10中自带的热备工具mariadbbackup，是xtrabackup 8的分支，支持xtrabackup的选项，使用方式一致
xtrabackup工具文件组成
Xtrabackup2.2 版之前包括4个可执行文件：
- innobackupex: Perl 脚本
- xtrabackup: C/C++，编译的二进制程序
- xbcrypt: 加解密
- xbstream: 支持并发写的流文件格式
2.2版本以下说明:
- xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互
- innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即innobackupex是在 xtrabackup 之上做了一层封装实现的
2.2版本以上说明：
- xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化：innobackupex 功能全部集成到xtrabackup 里面，只有一个binary程序，另外为了兼容考虑，innobackupex作为 xtrabackup 的软链接，即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过xtrabackup替换innobackupex
xtrabackup备份原理：
- innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件
- xtrabackup在备份innoDB数据时，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）
- xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）
- innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态
- 当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态
- xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成
- innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES
- 最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出
备份生成的相关文件：
调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件
这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
xtrabackup_info:
- 文本文件，具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN、BINLOG的位置
LSN（log sequence number）日志序列号 。备份时对每个数据页（16K）都记录一个编号，且编号自己记录下来，后续再次备份时，根据编号判断，如果编号小于等于当时的编号，就不备份；反之，大于当时的编号，就备份，以此实现增量备份
xtrabackup_checkpoints：
- 文本文件，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。
LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
xtrabackup_binlog_info：
- 文本文件，当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可实现基于binlog的恢复
backup-my.cnf：
- 文本文件，备份命令用到的配置选项信息，使用mysql的服务选项
xtrabackup_logfile：
- 备份生成的二进制日志文件
innobackupex命令：
8.0以后innobackupex和xtrabackup命令进行了合并，下面的选项在xtrabackup中可用
xtrabackup工具备份和还原，需要三步实现:
- 备份： 对数据库做完全或增量备份
- 预准备： 还原前，先对备份的数据，整理至一个临时目录，
- 还原： 将整理好的数据，复制回数据库目录中
说明： 自动读取mysqld的datadir参数内容
官方选项说明： https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
备份相关选项：
innobackupex [option] 备份至目录
选项：
--user 该选项表示备份账号
--password 该选项表示备份的密码
--host 该选项表示备份数据库的地址
--databases 该选项接受的参数为数据库名，支持多个库，如：
"库1.表 库2.表" 该选项对innodb引擎无效（自动备份所有innodb的库）
--defaults-file 该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--incremental 该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental-basedir 该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
--incremental-dir 该选项表示还原时增量备份的目录
--include=name 只备份表，格式：databasename.tablename
--backup 开始数据备份
--target-dir=目录 备份后数据存放位置
例：
innobackupex --user=root --password=123 /backup/mysql
#输出成tar的数据流，管道后压缩
innobackupex --user= --password= --stream=tar ./ |bzip2
#指定备份后的存放路
innobackupex --backup --target-dir=/data/ 径
预准备（数据整理）
innobackupex --apply-log [option] 备份目录
xtrabackup --prepare --target-dir=/data/
选项：
--prepare 备份前整理数据，单独使用时对未完成的事务进行回滚；配合--apply-log-only用，就只是整理数据
--apply-log-only 用于保存未提交事务，对他们不进行回滚。用于增量备份恢复时使用。
--use-memory 和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
--export 表示开启可导出单独的表之后再导入其他Mysql中
--redo-only 此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并
--slave-info 备份从库, 备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO ...
还原
innobackupex --copy-back [选项] 备份目录
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] 备份目录
选项：
--copy-back 做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back 这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
--force-non-empty-directories cp或mv时，datadir的目录中同名的目录不会覆盖，但会报错已存在
xtrabackup工具做完全备份及还原：
1）安装xtrabackup2.4
yum -y install percona-xtrabackup-xxx.rpm
2）做完全备份，备份目录会自动创建
xtrabackup -uroot -p123456 --backup --target-dir=/backup/
scp -a /backup 1.1.1.25:/backup
3）做恢复预准备，未提交事务回滚
#在此目录下，把事务日志中的回滚
xtrabackup --prepare --target-dir=/backup/
4）模拟原数据库损坏
rm -rf /data/mysql
killall mysqld
5）恢复原数据库（被恢复的数据目录必须为空，服务必须停止）
#自动读取my.cnf配置文件，所以知道源数据目录位置
xtrabackup --copy-back --target-dir=/backup/
chown -R mysql:mysql /data/mysql
service mysqld start
xtrabackup增量备份及还原：
1）完全备份数据库
xtrabackup -uroot -p123456 --backup --target-dir=/data/base/
2）修改数据
mysql -uroot -p123456 hellodb
->insert teachers(name,age,gender) values('hj',10,'F'), ('zzz',20,'F');
3）增量备份一，基于base文件做增量，并再次修改数据
xtrabackup -uroot -p123456 --backup --incremental-basedir=/data/base/ --target-dir=/data/inc1
mysql -uroot -p123456 hellodb
->create table ts1(id int);
insert ts1 values(1),(2),(3);
4）增量备份二，基于inc1文件做增量
xtrabackup -uroot -p123456 --backup --incremental-basedir=/data/inc1 --target-dir=/data/inc2
5）整理数据，使用--apply-log-only参数
增量恢复时，除了最后一次需要事务回滚，其他的几次包括完全备份，都不需要回滚，因为前面是已经完成的事物，最后一个只做了一半
#整理完全备份的数据
xtrabackup --prepare --apply-log-only --target-dir=/data/base
#整理增量一的数据
xtrabackup --prepare --apply-log-only --target-dir=/data/base --incremental-dir=/data/inc1
#回滚增量二的数据
xtrabackup --prepare --target-dir=/data/base --incremental-dir=/data/inc2
6）还原数据
xtrabackup --copy-back --target-dir=/data/base --datadir /data/mysql
chown -R mysql:mysql /data/mysql
service mysqld start
