备份恢复与数据迁移
DBA在数据库备份恢复方面的职责
全备
增量
定期恢复演练
故障恢复
迁移（停机时间、回退方案）
备份类型
- 热备：在数据库正常业务时，备份数据，并且能够一致性恢复（只能是innodb）
- 稳备：锁表备份，只能查询修改，不能修改（myisam）
- 冷备：关闭数据库业务，数据库没有任何变更的情况下，进行备份数据。
备份方式及工具
-
逻辑备份：基于SQL语句进行备份
mysqldump mysqlbinlog -
物理备份：基于磁盘数据文件备份
xtrabackup(XBK):percona 第三方(推荐使用) Mysql Enterprise Backup (MEB)
逻辑备份VS物理备份
- mysqldump(MDP)
# 优点 1.不需要下载安装 2.备份出来的是SQL，文本格式，可读性高，便于备份处理 3.压缩比较高，介绍备份的磁盘空间 # 缺点 1.依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行存储，比较耗费资源，数据量大的话效率较低 2.100G以内的数据量级，可以使用mysqldump，也可以使用xtrabackup 3.超过TB以上，也可以选择mysqldump,配合分布式的系统 - xtrabackup(XBK)
# 优点 1.类似于直接cp数据文件，不需要管理逻辑结构，相对来说性能较高 # 缺点 1.可读性差 2.压缩比低，需要更多磁盘空间 3.建议大于100G且小于1TB的时候使用
备份策略
全备：全库备份，备份所有数据。
增量：备份变化数据。
逻辑备份 = mysqldump(全备) + mysqlbinlog(增量)
物理备份 = xtrabackup_full + xtrabackup_incr + mysqlbinlog 或者 xtrabackup_full + mysqlbinlog
根据数据量设计备份周期，比如：周日全备，周1-周6增量。
逻辑备份工具-mysqldump
连接方式
# 本地备份的连接方式
mysqldump -uroot -pxxx -S /tmp/mysql.sock
# 远程备份的连接方式
mysqldump -uroot -pxxx -h 192.168.31.210 -P 3306
基本备份参数
-A # 全库备份
-B # 备份单个库或多个库
库名 表名
# 备份全库
mkdir -p /data/backup
mysqldump -uroot -p123 -A -S /tmp/mysql.sock > /data/backup/full.sql
# 备份oldboy和world库
mysqldump -uroot -p123 -B world oldboy -S /tmp/mysql.sock > /data/backup/db.sql
# 备份某个库下的一张或多张表：别分world数据库下的city和country表(恢复数据的时候需要提前建库并且use database，只会备份表)
mysqldump -uroot -p123 world city country -S /tmp/mysql.sock > /data/backup/tab.sql
备份必加参数
-R # 在备份时，同时备份存储过程和函数，如果没有会自动忽略
-E # 在备份时，同时备份event，如果没有会自动忽略
--triggers # 在备份时，同时备份触发器，如果没有会自动忽略
--master-data=2 #记录备份时候记录当前position号，作为将来日志截取的起点，自动锁表，配合 --single-transaction，减少锁表（innodb引擎）
--single-transaction # 对于innodb的表，实现快照备份，不锁表
--set-gtid-purged=auto # 默认是auto,on
使用场景：
1. --set-gtid-purged=OFF,可以使用在日常备份参数中
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF > /data/backup/full.sql
2.auto , on :在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON > /data/backup/full.sql
--max-allowed-packet=128M #避免由于备份大表产生的数据包报错问题
恢复思路
1.全备的时候自动记录日志当前的position号
2.全部的时候自动滚动生成新的bin_log日志
mysql> source /data/backup/full.sql
备份恢复案例（mysqldump+binlog）
数据量80G，每日数据增长5-6M
备份策略：每天mysqldump全备 + binlog备份
故障描述：周三下午2点，数据路径由于某原因数据损坏
处理思路：
1.挂出维护页。
2.评估数据损坏状态（全部丢失【直接生产恢复】 or 部分丢失【从备份中导出单表数据，或者测试库进行全部恢复，再把单表导回生产库中】）
3.恢复全部，将数据追溯到周二晚上23：00状态，
4.截取并恢复从备份时刻到周三下午2点误删除之前binlog
5.校验数据的一致性（测试人员会校验）
6.撤除维护页，恢复生产
处理结果：经过30~40分钟处理，业务恢复，评估此次故障的处理的合理性和实用性。
案例模拟
1、进行周二晚上23：00的全备
[root@localhost backup]# mysqldump -uroot -p123 -A -R --triggers -E --master-data=2 --single-transaction > /data/backup/full.sql
[root@localhost backup]# ll -h
total 49M
-rw-r--r--. 1 root root 49M May 9 09:22 full.sql
# 记录备份时候记录当前position号，作为将来日志截取的起点，起点就是MASTER_LOG_POS=22108059;
[root@localhost backup]# vim full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=22108059;
# 如果使用GTID来恢复，下面代表从285开始
[root@localhost backup]# vim full.sql
SET @@GLOBAL.GTID_PURGED='f98d1e0b-a7e4-11eb-8259-080027fb878b:1-284';
2、模拟周二晚上23：00之后到周三下午2点误删除之前的业务操作
mysql> create database mdp charset utf8mb4;
mysql> use mdp;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3.模拟数据损坏（真刺激）
[root@localhost data]# pwd
/data/mysql/data
[root@localhost data]# /etc/init.d/mysqld stop
Shutting down MySQL.... SUCCESS!
[root@localhost data]# rm -rf *
[root@localhost data]# ll
total 0
4.初始化数据库
[root@localhost data]# mysqld --initialize-insecure --user=mysql --basedir=/application/mysql/ --datadir=/data/mysql/data
[root@localhost data]# ll
total 110636
-rw-r-----. 1 mysql mysql 56 May 9 09:44 auto.cnf
-rw-r-----. 1 mysql mysql 419 May 9 09:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 May 9 09:44 ibdata1
-rw-r-----. 1 mysql mysql 50331648 May 9 09:44 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 May 9 09:44 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 May 9 09:44 mysql
-rw-r-----. 1 mysql mysql 975 May 9 09:44 mysql.log
drwxr-x---. 2 mysql mysql 8192 May 9 09:44 performance_schema
-rw-r-----. 1 mysql mysql 163 May 9 09:44 slow.log
drwxr-x---. 2 mysql mysql 8192 May 9 09:44 sys
# 启动数据库
[root@localhost data]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
5.进行全备恢复（没有mdp库）
mysql> set sql_log_bin=0;
mysql> source /data/backup/full.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gg |
| mysql |
| oldboy |
| oldboy2 |
| performance_schema |
| school |
| sys |
| test |
| world |
| zrblog |
+--------------------+
11 rows in set (0.00 sec)
mysql> flush privileges;
6.截取binlog日志，完成增量恢复，需要找起点和终点，终点不需要找，起点的话都可以position 活着 gtid都可以。
# 分析000007日志
mysql> show binlog events in 'mysql-bin.000007';
285~287
# 截取日志
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids="f98d1e0b-a7e4-11eb-8259-080027fb878b:285-287" /data/binlog/mysql-bin.000007 > /data/backup/bin.sql
或者这里没有写--stop-position 就代表一直截取到最后
[root@localhost ~]# mysqlbinlog --skip-gtids --start-position=22108059 /data/binlog/mysql-bin.000007 > /data/backup/bin.sql
# 恢复数据
mysql> set sql_log_bin=0;
mysql> source /data/backup/bin.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gg |
| mdp |
| mysql |
| oldboy |
| oldboy2 |
| performance_schema |
| school |
| sys |
| test |
| world |
| zrblog |
+--------------------+
mysql> use mdp;
Database changed
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
mysqldump备份命令
# 仿照如下命令编写备份脚本
mysql -uroot -p123 -A -R --triggers --master-data=2 max_allowed_packet=128M --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
XBK
# 安装依赖包
yum install -y perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
# 下载地址
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
# 安装软件包
[root@localhost software]# yum -y install percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
[root@localhost software]# innobackupex --version
xtrabackup: recognized server arguments: --datadir=/data/mysql/data --server-id=6 --log_bin=/data/binlog/mysql-bin --server-id=6
innobackupex version 2.4.22 Linux (x86_64) (revision id: c99a781)
备份命令介绍
xtrabackup
innobackupex ******
备份方式：物理备份
1.对于非Innodb表，锁表cp数据文件，数据一种温备份。
2.对于Innodb的表(支持事务的)，不锁表，拷贝数据页，最终于数据文件的方式保存下来，把一部分redo和undo一并备走，数据热备份方式。
XBK全备
# 新建备份目录
[root@localhost software]# mkdir -p /data/bak
# 修改配置文件，将[mysql]改为[client]
[root@localhost software]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/data/mysql.log
log_bin=/data/binlog/mysql-bin
server_id=6
secure-file-priv=/tmp
gtid-mode=on
enforce-gtid-consistency=true
slow_query_log=1 #开关
slow_query_log_file=/data/mysql/data/slow.log #位置
long_query_time=0.1 #设定慢查询时间
log_queries_not_using_indexes #没走索引的语句也记录
[client] # innobackupex需要使用socket
socket=/tmp/mysql.sock
# 重启服务
[root@localhost software]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
# 全备：备份工具依赖于/etc/my.cnf文件，会读取[mysqld][client]标签下的信息
[root@localhost software]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 /data/bak
或者自主定制备份路径名(目录名)
[root@localhost software]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp /data/bak/full_$(date +%F)
# 以目录的形式保存
[root@localhost software]# ls /data/bak/
2021-05-09_11-10-54
xtrabackup_binlog_info：记录position和GTID
[root@localhost 2021-05-09_11-10-54]# cat xtrabackup_binlog_info
mysql-bin.000010 234 1839d529-b068-11eb-8f64-080027fb878b:1,
f98d1e0b-a7e4-11eb-8259-080027fb878b:1-287
xtrabackup_checkpoints：lsn号
[root@localhost 2021-05-09_11-10-54]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 105394732
last_lsn = 105394741
compact = 0
recover_binlog_info = 0
flushed_lsn = 105394741
xtrabackup_info：备份的总体信息
xtrabackup_logfile：备份过程中产生的新的数据变化
恢复数据
# 模拟数据丢失
[root@localhost 2021-05-09_11-10-54]# cd /data/mysql/data/
[root@localhost data]# ls
auto.cnf ib_buffer_pool ib_logfile0 mdp mysql.log oldboy2 school sys world
gg ibdata1 ib_logfile1 mysql oldboy performance_schema slow.log test zrblog
# 回复备份的前提是数据目录必须是空的，恢复的数据实例是关闭的
[root@localhost data]# rm -rf *
[root@localhost 2021-05-09_11-10-54]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
# 准备恢复数据
[root@localhost data]# innobackupex --apply-log /data/bak/2021-05-09_11-10-54/
InnoDB: Shutdown completed; log sequence number 105395240
210509 11:36:49 completed OK!
# 恢复数据
[root@localhost data]# cd /data/bak/2021-05-09_11-10-54/
[root@localhost 2021-05-09_11-10-54]# cp -a /backup/bak/2021-05-09_11-10-54/* /data/mysql/data/
[root@localhost 2021-05-09_11-10-54]# cd /data/mysql/data/
[root@localhost data]# ll
total 196672
-rw-r-----. 1 root root 487 May 9 11:10 backup-my.cnf
drwxr-x---. 2 root root 48 May 9 11:10 gg
-rw-r-----. 1 root root 10254 May 9 11:10 ib_buffer_pool
-rw-r-----. 1 root root 79691776 May 9 11:36 ibdata1
-rw-r-----. 1 root root 50331648 May 9 11:36 ib_logfile0
-rw-r-----. 1 root root 50331648 May 9 11:36 ib_logfile1
-rw-r-----. 1 root root 12582912 May 9 11:36 ibtmp1
drwxr-x---. 2 root root 48 May 9 11:10 mdp
drwxr-x---. 2 root root 4096 May 9 11:10 mysql
drwxr-x---. 2 root root 54 May 9 11:10 oldboy
drwxr-x---. 2 root root 20 May 9 11:10 oldboy2
drwxr-x---. 2 root root 8192 May 9 11:10 performance_schema
drwxr-x---. 2 root root 160 May 9 11:10 school
drwxr-x---. 2 root root 8192 May 9 11:10 sys
drwxr-x---. 2 root root 48 May 9 11:10 test
drwxr-x---. 2 root root 174 May 9 11:10 world
-rw-r-----. 1 root root 104 May 9 11:10 xtrabackup_binlog_info
-rw-r-----. 1 root root 141 May 9 11:36 xtrabackup_checkpoints
-rw-r-----. 1 root root 607 May 9 11:10 xtrabackup_info
-rw-r-----. 1 root root 8388608 May 9 11:36 xtrabackup_logfile
-rw-r--r--. 1 root root 1 May 9 11:36 xtrabackup_master_key_id
drwxr-x---. 2 root root 4096 May 9 11:10 zrblog
[root@localhost data]# chown -R mysql.mysql *
[root@localhost data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/data/mysql.log'.
SUCCESS!
XBK增量备份
备份方式：基于上次的备份的增量
增量备份不能单独恢复，必须合并到全备中，一起恢复。
# 全备
[root@localhost bak]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp /data/bak/full_$(date +%F)
[root@localhost bak]# ll
total 4
drwxr-x---. 13 root root 4096 May 9 12:06 full_2021-05-09
# 模拟周一的数据变化
mysql> create database xbk charset utf8mb4;
mysql> use xbk;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
# 周一晚上的增量备份
[root@localhost bak]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/full_2021-05-09 /data/bak/inc1_$(date +%F)
# 模拟周二的数据变化
mysql> use xbk;
mysql> create table t2(id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
# 周二晚上的增量备份
[root@localhost bak]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/inc1_2021-05-09 /data/bak/inc2_$(date +%F)
XBK增量恢复
1、合并所有增量到全备
2、每个XB备份都需要恢复准备(prepare)
--apply-log --redo-only
1.整理全备
innobackupex --apply-log --redo-only /data/bak/full_2021-05-09
2.整理并合并周一增量到全备
innobackupex --apply-log --redo-only --incremental-dir=/data/bak/inc1_2021-05-09 /data/bak/full_2021-05-09
3.整理并合并周二的增量到全备
innobackupex --apply-log --redo-only --incremental-dir=/data/bak/inc2_2021-05-09 /data/bak/full_2021-05-09
4.再次整理全备
innobackupex --apply-log /data/bak/full_2021-05-09
5.停止Mysql实例，并且删除数据目录下的所有文件
[root@localhost bak]# /etc/init.d/mysqld stop
[root@localhost bak]# rm -rf /data/mysql/data/*
6.恢复数据
[root@localhost bak]# innobackupex --copy-back /data/bak/full_2021-05-09
[root@localhost data]# chown -R mysql.mysql /data/mysql/data/*
7.验证数据完整性
[root@localhost data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/data/mysql.log'.
SUCCESS!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gg |
| mdp |
| mysql |
| oldboy |
| oldboy2 |
| performance_schema |
| school |
| sys |
| test |
| world |
| xbk |
| zrblog |
+--------------------+
13 rows in set (0.01 sec)
mysql> use xbk;
mysql> show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| t1 |
| t2 |
+---------------+
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
XBK full+inc+binlog 案例
某中型互联网公司，数据量级600G，每日数据量15-50M
备份策略：周日XBK全备 + 周一到周六inc增量备份+binlog备份。每天23:00进行。
故障描述：周三下午2点，数据由于某原因数据损坏。
处理思路：
1.整理合并所有备份 full + inc1 + inc2
2.截取周二晚上到周三下午故障之间的binlog日志
3.恢复全备，恢复binlog
4.验证数据完整性
处理结果：
经过70-80分钟处理，业务恢复
模拟环境：数据+故障
# 周日全备
[root@localhost bak]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp /data/bak/full
# 模拟周一的数据变化
mysql> create database oss charset utf8mb4;
mysql> use oss;
mysql> create table t1(id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
# 模拟周一晚上的增量备份
[root@localhost bak]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/full /data/bak/inc1
# 模拟周二的数据变化
mysql> insert into t1 values(11),(22),(33);
mysql> commit;
# 模拟周二晚上的增量备份
[root@localhost bak]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/bak/inc1 /data/bak/inc2
# 模拟周三的数据变化
mysql> insert into t1 values(111),(222),(333);
mysql> commit;
# 有个员工把数据库data目录下的数据全部都给删掉了
[root@localhost bak]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost data]# rm -rf /data/mysql/data/*
数据恢复
# 整理全备
[root@localhost data]# innobackupex --apply-log --redo-only /data/bak/full
# 合并inc1到full
[root@localhost data]# innobackupex --apply-log --redo-only --incremental-dir=/data/bak/inc1 /data/bak/full
# 合并inc2到full
[root@localhost data]# innobackupex --apply-log --redo-only --incremental-dir=/data/bak/inc2 /data/bak/full
# 整体整理
[root@localhost data]# innobackupex --apply-log /data/bak/full
# 恢复整理好的全备数据到数据库
[root@localhost bak]# innobackupex --copy-back /data/bak/full
[root@localhost bak]# chown -R mysql.mysql /data/mysql/data/*
[root@localhost bak]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/data/mysql.log'.
SUCCESS!
# 查看inc2中的xtrabackup_binlog_info信息
[root@localhost bak]# cat /data/bak/inc2/xtrabackup_binlog_info
mysql-bin.000018 1288 1839d529-b068-11eb-8f64-080027fb878b:1,
42725169-b078-11eb-ba4c-080027fb878b:1-5,
49fa3c6a-b081-11eb-ab05-080027fb878b:1-5,
7a9f76ed-b08d-11eb-a5ac-080027fb878b:1-4, ******
a6927d77-b08a-11eb-b910-080027fb878b:1-5,
b9462caa-b08c-11eb-bbb4-080027fb878b:1-9,
d0b3b659-b088-11eb-9d15-080027fb878b:1-7,
f98d1e0b-a7e4-11eb-8259-080027fb878b:1-287
# 发现当前使用的是 7a9f76ed-b08d-11eb-a5ac-080027fb878b GTID
# 根据分析，7a9f76ed-b08d-11eb-a5ac-080027fb878b:1-4,已经包含 1-4，那么就从第五的事务开始截取到最后
mysql> show binlog events in 'mysql-bin.000018';
SET @@SESSION.GTID_NEXT= '7a9f76ed-b08d-11eb-a5ac-080027fb878b:5'
# 截取日志
[root@localhost bak]# mysqlbinlog --skip-gtids --include-gtids='7a9f76ed-b08d-11eb-a5ac-080027fb878b:5' /data/binlog/mysql-bin.000018 >/data/bak/bin.sql
# 恢复数据
mysql> set sql_log_bin=0;
mysql> source /data/bak/bin.sql
# 验证数据一致性
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
| 111 |
| 222 |
| 333 |
+------+
9 rows in set (0.00 sec)
远程推送备份
# 备份完并使用ssh推送到指定服务器的目录下命名为/data/full.tgz
innobackupex --user-root --password=123 --defaults-file=/etc/my.cnf --no-timestamp --stream=tar --use-memory=256M --parallel=8 /data/bak/full | gzip | ssh root@10.1.1.1" cat - > /data/full.tgz"
Mysql数据迁移
-
换主机（性能更强的硬件主机）：
1.在线 MDP XBK 备份出来，scp到目标主机恢复 2.追加所有备份后的日志 3.申请停机5分钟 4.剩余的binlog继续恢复（搭建主从的方式来代替） 5.校验数据 6.进行业务切换 7.数据量大用主从架构 -
换版本升级：
# 方法一 新机器安装5.7，5.6使用逻辑备份mysqldump按业务库做备份，排除掉information_schema,performance_schema,sys, 在5.7中恢复，之后升级数据字典。 # 方法二 主从进行过滤复制，排除掉information_schema,performance_schema,sys -
异构迁移（系统不一样）：
只能用逻辑备份 mysqldump -
异构迁移（数据库产品不同）：
Oracle -- OGG -- MYSQL MYSQL -- CSV(格式) -- MongoDB MYSQL8.0 -- JSON -- MongoDB