MySQL数据库-备份恢复

最新推荐文章于 2022-08-26 21:01:17 发布

乔大辉子

最新推荐文章于 2022-08-26 21:01:17 发布

阅读量238

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_26363645/article/details/106182280

版权

备份恢复

介绍

备份主要时用来恢复数据

工作职责

1 设计备份策略和恢复策略
备份策略： 
	1. 备份的时间
	   根据公司的实际业务情况，一般选择业务低谷。
	2. 备份的周期
	   每天 
	   每周
	3. 备份的方式
		全备、增量
		逻辑、物理
恢复策略： 
	1. 方案。
	2. 准确性。
    3. 需要多长时间。
2 检查备份
   1. 存在性。 
   2. 大小。
   3. 日志。
3 定期恢复演练

4 故障恢复。

5 迁移升级

备份工具介绍

逻辑备份工具

主流： mysqldump （MDP）

基于SQL（create database ,create table , insert into）语句的备份。
使用场景： 100G以内，比较常用的就是逻辑备份。
优点：自带工具，不需要单独安装；文本形式存储，便于查看处理；压缩比较高，节省空间。
缺点：备份时间较长。恢复时间更长（4-6倍）。

其他的.

mydumper
mysqlpump
into outfile/load data infile

物理备份工具

Percona - Xtrabackup(xbk,pbk,pxb)

备份数据文件。
优点：备份、恢复速度更快。
缺点：二进制方式，可读性差；压缩比较低，浪费空间。
使用场景： 100G-TB级别数据量，一般采用xbk。

mysqldump 应用

备份方式

基于SQL（create database ,create table , insert into）语句的备份。
针对InnoDB表可以实现非锁定备份。原理上是通过MVCC中的快照技术进行备份。
针对非InnoDB表，是启用了锁表备份，FTWRL（global read lock）。

参数应用

连接参数

-u
-p
-S
-h
-P

备份参数

-A   全备参数 ：
[root@db01 ~]# mysqldump -uroot -p123 -A >/opt/full.sql

-B   单库或多库备份：
[root@db01 ~]# mysqldump -uroot -p123 -B world test >/opt/db.sql

备份库下的单表或多表： 
[root@db01 opt]# mysqldump -uroot -p123 world city country >/opt/tab.sql
注意： 单表或多表备份，在恢复时需要提前创建库，use到库中再恢复。

--master-data=2
功能： 
1. 备份时自动生成，当前的binlog位置信息
2. 自动开启锁表备份功能，如果开了--single-transaction，可以减少global read lock.

--single-transaction
功能：InnoDB “热备”。 对于InnoDB表不锁表，开启一执行快照备份。


-R -E --triggers 特殊对象备份
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/opt/full.sql

--max-allowed-packet=128M
# 1153 - Got a packet bigger than 'max_allowed_packet' bytes 
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=128M >/opt/full.sql

mysqldump -uroot -p123 -A  -R  --triggers -E  --master-data=2  --single-transaction --max-allowed-packet=128M |gzip > /opt/full_$(date +%F).sql.gz

[root@db01 opt]# mysqldump -uroot -p123 -A  -R  --triggers -E  --master-data=2  --single-transaction --max-allowed-packet=128M |bzip2 -9   > /opt/full_$(date +%F).sql.bz2

MDP+binlog生产故障恢复模拟

模式环境

mysql> create database mdp charset utf8mb4;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

模拟全备

[root@db01 opt]# mysqldump -uroot -p123 -A  -R  --triggers -E  --master-data=2  --single-transaction --max-allowed-packet=128M |bzip2 -9   > /opt/full_$(date +%F).sql.bz2

模拟新的数据变化

mysql> create table t2 (id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;

搞破坏

mysql> drop database mdp;

恢复

检查备份

SET @@GLOBAL.GTID_PURGED='09a8c1af-6e45-11ea-a381-000c29248f69:1-10';
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=1387;

截取日志

起点： 
position ---》mysql-bin.000009', MASTER_LOG_POS=1387;
GTID     ---》09a8c1af-6e45-11ea-a381-000c29248f69:11
终点： 
GTID：    09a8c1af-6e45-11ea-a381-000c29248f69:12
position  mysql-bin.000009 | 1874

[root@db01 opt]# mysqlbinlog  --skip-gtids --include-gtids='09a8c1af-6e45-11ea-a381-000c29248f69:11-12' /data/3306/binlog/mysql-bin.000009 >/opt/bin.sql

恢复数据

mysql> set sql_log_bin=0;
mysql> source /opt/full_2020-03-27.sql 
mysql> source /opt/bin.sql
mysql> set sql_log_bin=1;

percona-xtrabackup 应用

介绍

物理备份工具。备份数据文件。

下载安装

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-*

配置

[client]
socket=/tmp/mysql.sock

xbk备份恢复原理

备份： 
InnoDB表：
  1、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
  2、备份InnoDB表时，拷贝 ibd 、ibdata、undo、ibtmp1，将备份期间产生的redo截取拷贝，记录LSN
非InnoDB表： 
  1. 触发 FTWRL 全局锁，关闭binlog记录
  2. 拷贝非InnoDB表数据。
 
数据完成后
  记录binlog位置，停止redo拷贝，记录last lsn，记录所有备份日志到指定日志文件中，解锁
  
恢复：  
	1. prepare 备份：利用了InnoDB CSR的功能，使用redo进行前滚，undo进行回滚
    2. 恢复备份： cp 文件至源路径。

应用

全备

innobackupex --user=root --password=123 --no-timestamp  /opt/full/xbkfull_`date +%F`

全备恢复演示

[root@db01 ~]# pkill mysqld 
[root@db01 ~]# rm -rf /data/3306/data/*

# prepare 全备 
[root@db01 ~]# innobackupex  --apply-log  /opt/full/xbkfull_2020-03-27/

# 恢复全备 
[root@db01 ~]# innobackupex --copy-back /opt/full/xbkfull_2020-03-27/
或者： 
[root@db01 data]# cp -r /opt/full/xbkfull_2020-03-27/* /data/3306/data/
[root@db01 ~]# chown -R mysql.mysql /data/
[root@db01 data]# /etc/init.d/mysqld start

Xtrabackup 全备+增量应用

增量备份原理

xrabackup自动检查LSN号码的变化，将每天LSN发生变化的数据页备份走。
增量备份，必须要依赖全备。恢复时，必须合并到全备。

查看备份集

xtrabackup_binlog_info  ：binlog位置点 
xtrabackup_checkpoints  ：LSN号码记录
xtrabackup_info         ：备份总览
xtrabackup_logfile      ：部分redo

增量备份应用

模拟数据

mysql> create database incremental charset utf8mb4;
mysql> use incremental
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

模拟周日全备

innobackupex --user=root --password=123 --no-timestamp /opt/full

模拟周一增量

innobackupex --user=root --password=123 --no-timestamp  --incremental-basedir=/opt/full  --incremental  /opt/inc1

模拟周二数据

 use incremental 
 create table t3 (id int);
 insert into t3 values(1),(2),(3);
 commit;

模拟周二增量

innobackupex --user=root --password=123 --no-timestamp  --incremental-basedir=/opt/inc1  --incremental  /opt/inc2

模拟周三数据

  use incremental 
 create table t4 (id int);
 insert into t4 values(1),(2),(3);
 commit;

搞破坏

[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*

故障恢复 full+inc1+inc2+binlog

# Prepare Base Full 处理基础全备
[root@db01 ~]# innobackupex --apply-log --redo-only /opt/full/

# merging  inc1 to BASE FULL ,合并处理inc1增量到全备

innobackupex --apply-log --redo-only  --incremental-dir=/opt/inc1 /opt/full 

# merging  inc2 to BASE FULL ,合并处理inc2增量到全备
innobackupex --apply-log  --incremental-dir=/opt/inc2  /opt/full 

# 最后一次全备整理
[root@db01 opt]# innobackupex --apply-log  /opt/full/

# 恢复数据
root@db01 full]# chown -R mysql.mysql /opt/*
vim /etc/my.cnf
#datadir=/data/3306/data
datadir=/opt/full

[root@db01 full]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS!

# 截取恢复binlog 
[root@db01 inc2]# cat /opt/inc2/xtrabackup_binlog_info 
mysql-bin.000009	1712	81c442f5-7011-11ea-b4da-000c29248f69:1-7

[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='81c442f5-7011-11ea-b4da-000c29248f69:8-9' /data/3306/binlog/mysql-bin.000009 >/opt/bin.sql


# 清空binlog日志，再发起全备。
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

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

获得表结构

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

xtrabackup 单独恢复表

单独起实例，备份数据拉起来
截取日志恢复此实例
故障表，表空间迁移恢复到生产