MySQL的备份恢复

MySQL的备份恢复

通过现有备份,能够将数据库恢复到故障之前的时间点.

备份工具

mysqldump(MDP)
percona Xtrabackup(XBK)
MySQL Enterprise BACKUP(MEB)
mysqlbinlog

备份方式

#逻辑备份
全备 : mysqldump 
增量 : binlog
#物理备份
全备 : XBK 
增量 : XBK

备份类型

#热备
在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）
对业务影响非常小

#温备
锁表备份,只能查询不能修改（myisam）
影响到写入操作

#冷备
关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止

备份工具使用

逻辑备份-mysqldump

#基础备份参数
-A : 全备参数
-B : 备份库或多个库
库 表进行备份

#全备
[root@mysql ~]# mysqldump -uroot -p123456 -A >/backup/full.sql

#分库备份
[root@mysql ~]# mysql -uroot -p123456 -B world test opesn >/backup/db.sql

#库 表备份
[root@mysql ~]# mysqldump -uroot -p123456 world city country > /backup/tab.sql

#特殊备份参数
-R : 备份存储过程和函数
-E : 备份事件
--triggers : 备份触发器
--master-data=2
1. 自动记录备份时刻的binlog的文件名+位置点
2. 自动进行锁表和解锁
3. 如果配合--single-transaction,会有不一样的结果。
	不加--single-transaction ,温备份
	加了--single-transaction,对于InnoDB表不锁表备份(快照备份)

##扩展参数
#在构建主从时,使用AUTO
--set-gtid-purged=AUTO

#仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF  

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

[root@mysql ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=128M >/backup/full_$(date +%F).sql

物理备份-XBK

#安装依赖包
[root@mysql ~]# wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
[root@mysql ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
#下载软件并安装
[root@mysql ~]# 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
[root@mysql ~]# yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

innobackupex备份核心理念

1. 备份的过程
(1) innobackupex工具进行备份.会自动区分不同的存储引擎类型
(2)	非InnoDB引擎表,自动锁表备份,FTWRL(Flush table with read lock),备份完自动解锁.
(3) InnoDB引擎表,立即触发CKPT(将内存中已经提交的脏页,刷写到磁盘上),会立即记录LSN号,开始备份磁盘数据页
(4) 在备份过程中产生的新的数据变化,XBK会自动备份过程中产生REDO日志.
(5) 系统表空间也会被一次性备份走
(6) 所有备份工作完成之后,再次记录最新的LSN号码.

2. 恢复过程
模拟了ACSR的全过程,在恢复之前,将数据的LSN号和redo LSN号追平
恢复方法就是直接cp回去即可

全量和增量备份

#全量
[root@mysql backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/tmp/mysql.sock --no-timestamp /backup/full

#增量
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1
说明:
--incremental    开关 
--incremental-basedir=/backup/full 基于哪个备份进行增量
/backup/inc1     增量备份的位置点

#备份产生的文件介绍
(1) xtrabackup_binlog_info
记录备份时刻的二进制日志信息. 可以作为binlog截取的起点.
(2) xtrabackup_checkpoints
from : 备份中包含的LSN号的起点,全备:0,增量:上次备份的结束位置
to 	 : ckpt 时的LSN
last-9 : 备份结束时的LSN.下次增量备份的起始位置.

模拟数据备份恢复

#模拟数据
mysql> create database full charset utf8mb4;
mysql> use full;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

#进行每周全备
[root@mysql backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --socket=/tmp/mysql.sock --no-timestamp /backup/full

#模拟第一次的数据变化
mysql> create database inc1 charset utf8mb4;
mysql> use inc1
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

#模拟第一次增量备份
[root@mysql ~]# innobackupex   --user=root --password=123456 --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1

#模拟第二次的数据变化
mysql> create database inc2 charset utf8mb4;
mysql> use inc2;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

#模拟第二次增量备份
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --socket=/tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2

#模拟第三次的数据变化
mysql> create database inc3 charset utf8mb4;
mysql> use inc3;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

#模拟数据库崩坏
[root@mysql ~]# pkill mysqld
[root@mysql ~]# rm -rf /data/mysql/data/*

#进行恢复准备
(1) 整理full
[root@mysql ~]# innobackupex --apply-log --redo-only /backup/full
(2) 合并inc1到full,并整理备份
[root@mysql ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full
(3) 合并inc2到full,并整理备份
[root@mysql ~]# innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full
(4) 最后一次整理full
[root@mysql ~]# innobackupex --apply-log /backup/full

#截取二进制日志
[root@mysql ~]# cat /backup/full/xtrabackup_binlog_info 
mysql-bin.000004	5277	d980595d-c934-11e9-bbfc-000c29d70b6d:1-29
[root@mysql ~]# mysqlbinlog --skip-gtids --include-gtids='d980595d-c934-11e9-bbfc-000c29d70b6d:30-32' /data/binlog/mysql-bin.000004>/backup/binlog.sql

#恢复数据
[root@mysql ~]# cp -a /backup/full/* /data/mysql/data/
[root@mysql ~]# chown -R mysql.mysql /data/mysql/data/*
[root@mysql ~]# /etc/init.d/mysqld start
[root@mysql ~]# mysql -uroot -p123456
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql
mysql> set sql_log_bin=1;

mysql5.6迁移至mysql5.7

#数据库备份
[root@mysql5 ~]# mysqldump -uroot -p123456 -A --master-data=2 --single-transaction -R -E --triggers > /mnt/full.sql
#数据迁移
[root@mysql5 ~]# scp /mnt/full.sql 10.0.1.110:/data/3308/

#数据恢复
[root@mysql ~]# systemctl start mysqld3308
[root@mysql ~]# mysql -S /data/3308/mysql.sock
mysql> source /data/3308/full.sql;
mysql> flush privileges;
[root@mysql ~]# mysql_upgrade -uroot -p123456 -S /data/3308/mysql.sock