数据库 - MySQL 备份和导入 - 《博客专栏》

备份工具
备份方式
备份类型
逻辑备份 MySQL dump
物理备份 XBK（xtrabackup）
恢复案例

msyqldump -u用户名 -p密码 数据库名字 表名字 -A -R -E --triggers --master-data=2 --single-transaction > /backup/full.sql
mysql -u用户名 -p密码
use 数据库
source /backup/full.sql

备份周期：根据数据量

备份工具

mysqldump
XBK(percona Xtrabackup)
MEB（mysql enterprise backup meb）

备份方式

全量：mysqldump
增量：binlog (flush logs , cp)
物理备份：XBK（全量，增量但增量一般自己弄）

备份类型

热备：对于业务影响最小        innodb
温备：长时间锁表备份        myisam
冷备：业务关闭情况下备份

逻辑备份 MySQL dump

连接数据库

-u -p -S -h -P

基础备份参数

-A    全库备份
    [root@localhost ~]# mysqldump -uroot -p123 -A > /backup/full.sql
-B    指定库
    [root@localhost backup]# mysqldump -uroot -p123 -B mysql sys > /backup/db.sql
库和表
    [root@localhost backup]# mysqldump -uroot -p123 mysql user plugin > /backup/tab.sql

特殊备份参数

-R    存储过程和函数
-E    事件
--triggers    触发器
--master-data=2    记录二进制日志截取的起点，自动锁表备份
--single-transaction    锁表
    不加这个参数就是温备份
    加了的话，对于innodb表不锁表备份（快照备份）

扩展参数

控制备份时传输数据包的大小
--max_allowed_packet=128M

从mysqldump 全备中获取库和表的备份

1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql
2、获得INSERT INTO 语句，用于数据的恢复
# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &
3.获取单库的备份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

物理备份 XBK（xtrabackup）

1、对于非Innodb表，锁表cp数据文件，属于一种温备份。
2、对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。

3、在恢复时，模拟innodb自动故障恢复（CSR）过程，将redo（前滚）和undo（后滚）进行应用，将数据和 redo的 lsn追平，然后进行一致性恢复

下载安装

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
手动地址：
https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

备份产生的文件介绍

记录备份时刻的二进制日志信息
xtrabackup_binlog_info
xtrabackup_checkpoints
from    备份中包含的lsn号的起点，全备时0，增量时上次备份的结束位置
to        ckpt 时的lsn
last-9    备份结束时的lsn，下次增量备份的起始位置

备份的过程

1、非innodb表，进行短暂锁表，然后copy数据文件
2、innodb表，立即发checkpoint，会立即记录一个lsn，copy数据文件
3、备份过程中产生的redo进行截取和保存，并记录此时最新的lsn

恢复的过程

模拟了CSR的全过程，在恢复之前，将数据的lsn和redo lsn号追平
恢复的方法就是直接cp回去

XBK 全备和恢复

全备：
innobackupex --user=root --password=123 --no-timestamp /backup/full
恢复：
innobackupex --apply-log /backup/full
cp -a /backup/full/* /data/mysql/data/
chown -R mysql.mysql /data/
/etc/init.d/mysqld start

XBK 增量备份

背景：mysql数据库，数据量500G，每日更新20-30M（cp时间2个小时）
备份策略：XBK备份，每周日23：00全备，周一到周六23：00增量备份
故障场景：周三下午2点出现数据库崩溃损坏

#清空备份路径
rm -rf /backup/*
#模拟数据
create database full charset utf8mb4;
use full;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#进行周日全备
innobackupex --user=root --password=123 --no-timestamp /backup/full
#模拟周一数据变化
create database date1 charset utf8mb4;
use date1;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#进行周一的增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/date1
说明：
--incremental 开关
--incremental-basedir=/backup/full 基于哪个备份进行增量
/backup/full2 增量备份的位置
#检查增量备份的lsn
增量的from_lsn 等于全量的last_lsn值-9
#模拟周二的数据变化
create database date2 charset utf8mb4;
use date2;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#进行周二的增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/date1 /backup/date2
#模拟周三的数据变化
create database date3 charset utf8mb4;
use date3;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#模拟上午10点数据库崩溃
pkill mysqld
rm -rf /data/mysql/data/*
======================================================================================
#恢复思路
1、停业务，挂维护页
2、查找可用备份，找最近的全备+增量（full+date1+date2）
3、binlog：date2 到故障时间点的binlog
4、恢复全备+增量+binlog
5、验证数据
6、起业务，撤维护页
#恢复过程
1、备份文件处理（合并date1 到full，合并date2 到full，整理full）
innobackupex --apply-log --redo-only /backup/full
    --redo-only在整理全备和所有增量合并时用，除了最后一次增量
innobackupex --apply-log --redo-only --incremental-dir=backup/date1 /backup/full
innobackupex --apply-log --incremental-dir=/backup/date2 /backup/full
innobackupex --apply-log /backup/full
2、截取binlog二进制日志
#找到binlog起点
cat /backup/date2/xtrabackup_binlog_info
#找到binlog终点（mysql-bin的文件名在起点的文件里）
mysqlbinlog /data/binlog/mysql-bin.000031 | grep "SET"
#截取（其中gtid的起点是上面查看binlog起点中gitd的结束点+1）
mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-ae9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031 > /backup/binlog.sql
3、恢复备份
cp -a /backup/full/* /data/mysql/data/
chown -R mysql.mysql /data/
/etc/init.d/mysqld start
mysql -uroot -p123
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql;
4、验证数据
select * from date2.t1;

恢复案例

1、MySQL dump+binlog恢复

背景：mysql-5.7.26数据库，数据量50G，日增长量1-5M
备份策略：每天23:00，计划任务全量备份
故障时间：周三下午3:10误删数据库
思路：
    1、停业务，挂维护页，避免二次伤害
    2、找一个临时库，恢复到周二的全量备份
    3、截取全量备份的binlog起点到周三下午3:10的binlog终点，恢复到临时库
    4、测试可用性和完整性
    5、将误删除的表导出恢复到生产，或者直接临时库顶替生产库
    6、开启业务
结果：经过30分钟处理，最终业务恢复正常

--准备数据
    create database backup;
    use backup
    create table t1 (id int);
    insert into t1 values (1),(2),(3);
    commit;
--清空备份文件夹
    rm -rf /backup/*
--模拟全备
    mysqldump -uroot -p123 -A -R -E --triggers --master-data=2 --single-transaction | gzip > /backup/full_$(data +%F).sql.gz
--模拟备份后数据发生了变动
    use backup
    insert into t1 values (11),(22),(33);
    commit;
    create table t2 (id int);
    insert into t2 values (22),(33),(44);
    commit;
--模拟删库（生产勿用）
    drop database backup;
============================================================================
--准备新的临时库
--截取二进制日志
    找到备份sql文件中binlog起点（一般在前几行）
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=2134;
    主库查询binlog文件的结束位置
    show binlog events in 'mysql-bin.000002';
    截取二进制日志（日志中写了gtid的话，截取的话一定要跟上--skip-gtids）
    mysqlbinlog --skip-gtids --start-position=2134 --stop-position=2856 mysql-bin.000002 > /backup/bin.sql
--数据恢复到临时库
    mysql -S /data/3307/mysql.sock
    set sql_log_bin=0;
    source /backup/full.sql
    source /backup/bin.sql
============================================================================
--将故障表导出并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup > /backup/bak.sql
mysql -uroot -p 123
set sql_log_bin=0;
use backup;
source /backup/bak.sql

2、MySQL XBK 单表恢复

背景：mysql数据库，数据量500G，每日更新20-30M
备份策略：XBK备份，每周日23：00全备，周一到周六23：00增量备份
故障场景：周三上午10点出现误删一张10M的表

#清空备份路径
rm -rf /backup/*
#模拟数据
create database full charset utf8mb4;
use full;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#进行周日全备
innobackupex --user=root --password=123 --no-timestamp /backup/full
#模拟周一数据变化
create database date1 charset utf8mb4;
use date1;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#进行周一的增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/date1
说明：
--incremental 开关
--incremental-basedir=/backup/full 基于哪个备份进行增量
/backup/full2 增量备份的位置
#检查增量备份的lsn
增量的from_lsn 等于全量的last_lsn值-9
#模拟周二的数据变化
create database date2 charset utf8mb4;
use date2;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#进行周二的增量备份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/date1 /backup/date2
#模拟周三的数据变化
create database date3 charset utf8mb4;
use date3;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
#模拟上午10点删除数据库表
drop table city;
======================================================================================
#恢复过程
1、备份文件处理（合并date1 到full，合并date2 到full，整理full）
innobackupex --apply-log --redo-only /backup/full
    --redo-only在整理全备和所有增量合并时用，除了最后一次增量
innobackupex --apply-log --redo-only --incremental-dir=backup/date1 /backup/full
innobackupex --apply-log --incremental-dir=/backup/date2 /backup/full
innobackupex --apply-log /backup/full
2、截取binlog二进制日志
#找到binlog起点
cat /backup/date2/xtrabackup_binlog_info
#找到binlog终点（mysql-bin的文件名在起点的文件里）
mysqlbinlog /data/binlog/mysql-bin.000031 | grep "SET"
#截取（其中gtid的起点是上面查看binlog起点中gitd的结束点+1）
mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-ae9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031 > /backup/binlog.sql
3、恢复备份
create table city like city_bak;
alter table city discard tablespace;
cp /backup/full/world/city.ibd  /application/mysql/data/world/
chown -R mysql.mysql  /application/mysql/data/world/*
alter table city import tablespace;
4、验证数据
select * from table.city;

3、MySQL 5.6 迁移到 5.7

5.6操作：
1、搭建5.6环境，插入数据
2、全备
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full.sql
3、移动全备文件到5.7
scp /tmp/full.sql 192.168.0.57:/backup

5.7操作：
1、登录mysql重置sql_bin索引
mysql> set sql_log_bin=0;
2、还原（数据过来了，版本不兼容）
mysql> source /backup/full.sql;
mysql> flush privileges;
3、设置数据兼容
mysql_upgrade -uroot -p123 -S /data/3306/mysql.sock
4、刷新二进制binlog使其产生一份新的文件
恢复全备到flush之间的二进制binlog
5、停业物，恢复剩余的binlog
6、业务割接