MySQL备份详解 - Breeze Breeze MySQL备份详解文章 -

简介：

数据库备份，做为工作中非常重要的环节和习惯。是必不可少的条件之一。

主要源： databases Binlog my.cnf

所有备份的数据都应该放在非本地数据库，而且建议有多份副本。
测试环境中做日常恢复演练，恢复备份更为重要。

备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其他地方。

冗余：数据有多份冗余，但不等于备份，冗余只能防止机械故障带来的数据丢失，例如主备模式，数据库集群。

备份过程中所必须要考虑的因素：
1 ：数据的一致性
2 ：服务的可用性

备份可以分为两种：
逻辑备份： 备份的是建表，库，插入等操作所执行的SQL语句(DDL,DML,DCL),适用于中小型数据库，效率相对较低
命令通常为 mysqldump 和 mydumper
物理备份： 直接复制数据库文件，从文件系统来备份，适用于大型数据库环节，不受存储引擎的限制，但不能恢复到不同的M有SQL版本中去。
命令通常为 tar,cp,xtrabackup,inbackup,lvm snapshot等

备份的模式：
完全备份：直接全部将数据备份
增量备份：在基准点(完全备份)的基础上，在上一份备份的基础上备份增量，一份一份的备份，顺序不能乱。下一份增量备份，基于上一次的增量备份

差异备份：在基准点(完全备份)的基础上按差异进行备份，可以灵活选择备份哪一次差异，每一次差异备份都包含上一次差异的备份。

一物理备份之 tar 备份数据库

备份的过程【完全物理备份】
1 停止数据库
2 tar 备份数据库
3 启动数据库

[root@Breeze /]# systemctl stop mysqld
[root@Breeze /]# mkdir /backup
[root@Breeze /]# tar -cf /backup/`date +%F`-mysql-all.tar /var/lib/mysql
[root@Breeze /]# systemctl start mysqld

注：备份文件应该复制到其他服务器或存储上

还原的过程：

1 停止数据库
2 清理环境 (哪怕是新数据库也有数据初始化的信息，所以要清理)
3 导入备份数据
4 启动数据库 (启动不了，考虑下权限的问题)
5 binlog恢复

[root@Breeze /]# systemctl stop mysqld
[root@Breeze /]# rm -rf /var/lib/mysql/* （我这里删除下数据库模拟还原）
[root@Breeze /]# tar -xf /backup/`之前备份的tar文件 / (这里为什么是根目录，因为之前备份了目录，所以解压直接到根目录，就相当于直接解压到了 /var/lib/mysql/ 下面)
[root@Breeze /]# chown -R mysql.mysql /var/lib/mysql 更换主机可能造成的权限问题，还原回来
[root@Breeze /]# systemctl start mysqld

这个时候还没完，这只是恢复了完全备份

如上图，比如我们两点钟开始备份数据(停掉了数据库) 两点半备份完毕的，然后数据库在9点钟崩的，所以我们在2点到9点钟这个时间段，是没有备份数据的，这是时候就需要找到我们的binlog日志，找到两点钟到9点钟这个时间段的数据操作，恢复就可以了。

二 LVM快照实现物理备份 +binlog

好处：数据一致，服务可用
注意：MySQL数据 lv 和将要创建的快照 snapshot 必须在同一个VG ,因此VG必须要有一定的剩余空间

优点：

几乎是热备(创建快照前把表上锁，创建完之后立即释放)
几乎支持所有存储引擎
备份速度快
无需使用昂贵的商业软件(这是操作系统级别的）

缺点：

可能需要跨部门协调(使用操作系统界别的命令，DBA 一般没权限)
无法预估服务停止的时间
数据如果分步在多个卷上面，比较麻烦(针对存储级别而言)

操作流程：
1 flush table with read lock 加锁做快照使数据定格，保证数据的一致性
2 create snapshot
3 show master status ;show slave status; 【可选】显示二进制日志文件位置
4 unlock tables
5 Copy files from the snapshot
6 Unmuont the snapshot
7 remove snapshot

但是，我们一般情况下 Mysql是不会装在逻辑卷上面的，如果我们没有安装mysql ，正常我们MySQL的安装流程是：

安装系统
准备LVM ，例如 /dev/vg_breeze/lv-mysql , mount /var/lib/mysql
安装mysql ，默认 datadir=/var/lib/mysql

但是现在的情况是我们的 mysql运行了一段时间，数据并没有存储LVM，所以就要将现在的数据迁移到LVM上

1 准备LVM 以及文件系统

先新加一块硬盘 vdb

[root@Breeze /]# vgcreate datavg /dev/vdb
[root@Breeze /]# lvcreate -n lv-myslq -L 2G datavg
[root@Breeze /]# mkfs,xfs /dev/datavg/lv-mysql

2 将数据迁移到LVM

[root@Breeze /]# systemctl stop mysqld
[root@Breeze /]# mount /dev/datavg/lv-mysql /mnt/ 临时挂载点
[root@Breeze /]# cp -a /var/lib/mysql/* /mnt 将M有SQL原数据镜像到临时挂载点
[root@Breeze /]# unmount /mnt/
[root@Breeze /]# vim /etc/fstab 加入fstab开机挂载
/dev/datavg/lv-mysql /var/lib/mysql xfs defatult 0 0
[root@Breeze /]# mount -a
[root@Breeze /]# chown -R mysql.mysql /var/lib/mysql
[root@Breeze /]# systemctl start mysql

LV快照备份流程

1 全局加读锁

mysql > flush tables with read lock ;

注意，这里的全局锁，是基于会话级别的，如果退出了这个mysql终端，全局锁就自动释放，全局锁就没有意义了，这种功能类似于个按钮，你必须得一直按着他才行。一松手就释放了。

2 创建快照

[root@Breeze /]# lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql
[root@Breeze /]# mysql -p ‘密码’ -e ‘show master status ‘> /backup/ `data +%F`_positon.txt
表示备份的二进制位置，并且保存到 txt文本恢复的时候就从这个位置开始。

3 释放锁

mysql > unlock tables ;

1 – 3 步必须在同一会话中完成所以上面的步骤都是错误的，应该像下面这样用

[root@Breeze /]# echo “FLUSH TABLES WITH READ LOCK ; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql ; UNLOCK TABLES ; ” | mysqo -p ‘密码’

[root@Breeze /]# echo “FLUSH TABLES WITH READ LOCK ; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql ; ” | mysqo -p ‘密码’ 解锁其实没有必要性，执行完之后锁自动就解锁了。只要保证快照时候有锁就行了。

4 从快照中备份

[root@Breeze /]# mount -o ro /dev/datavg/lv-mysql-snap /mnt/ //如果采用的nfs 的快照，挂载的时候要加 xfs -o ro, nouuid 否则挂载不上
[root@Breeze /]# cd /mnt
[root@Breeze /]# tar -cf /backup/ `data +%F`-mysql-all.tar.gz ./*

5 移除快照

[root@Breeze /]# cd ; unmount /mnt/
[root@Breeze /]# lvremove -f /dev/vg-newvg/lv-mysql-snap

LVM 快照恢复流程：

1 停止数据库
2 清理环境
3 导入数据
4 修改权限
5 启动数据库
6 binlog恢复

[root@Breeze /]# tar -xf /backup/xxxx-mysql-all.tar,gz -C /var/lib/mysql/
[root@Breeze /]# systemctl start msyqld

Tar 解压的注意事项

tar -tf xxxxx-mysql-all,tar.gz |less
解压的时候看带不带路径，如果不带路径就可以直接解压，带路径就还需要自己处理

脚本 + Cron

#!/bin/bash
#LVM backmysql
back_dir=/backup`date +%F`
[-d $back_dir ] || mkdir -p $back_dir
echo “FLUSH TABLES WITH READ LOCK ; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql ;” | mysql -p ‘admin’ -e ‘show master status’ > /backup/ `data +%F`_positon.txt

mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt

rsync -a /mnt/ $back_dir

if [$? -eq 0 ];then
unmount /mnt/
lvremove -f /dev/datavg/lv-mysql-snap
fi

三 percona-xtrabbackup 物理备份

percona 是一款开源免费的支持mysql 数据库热备份的软件，能对InnoDB 和XtraDB存储引擎的数据库非阻塞的备份，不暂停服务创建Innodb热备份；
类似于MariaDB 对Mysql支持一样。

为mysql做增量备份，在mysql服务器之间做在线表迁移，使创建replication更加容易；备份mysql而不增加服务器的负载

percona 官网下载界面可以选择yum的安装方式，官网有提供官方的yum源

安装Xtrabackup

官方源
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

启用存储库： percona-release enable-only tools release

如果打算将Percona XtraBackup与上游MySQL Server结合使用，则只需启用tools 存储库：。percona-release enable-only tools

通过运行以下命令安装Percona XtraBackup： yum install percona-xtrabackup-80

注意：

在CentOS 6上libev安装Percona XtraBackup之前，请确保已安装了软件包。对于此操作系统，libev可从EPEL存储库中获得该软件包。

rpm包的方式：

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
手动安装软件包时，需要确保解决所有依赖性并自行安装缺少的软件包。
要完全卸载Percona XtraBackup，需要删除所有已安装的软件包：yum remove percona-xtrabackup

展示套件 可以看到有哪些命令
rpm -ql percona-xtrabackup-80-8.0.4-1.el7.x86_64 
我们基本上使用
/usr/bin/innobackupex
/use/bin/xtrabbackup

XtraBackup 完全备份流程

[root@CentOS ~]# mkdir /xtracbackup/full -p 
[root@CentOS ~]# innobackupex --user=root --password='admin' 
 /xtracbackup/full 
备份的时候是需要连库的否则有的表无法锁上，恢复的时候不需要(恢复的时候数据库已经宕了)
自动完成热备份的完全备份，会在/xtracbackup/full 下生成一个带时间戳的文件夹，里面包含我们的所有备份文件，包括我们的my.cnf， 以及binlog_info信息

XtraBackup 完全备份恢复流程

停止数据库
清理环境
重演回滚—>恢复数据
修改权限
启动数据库

[root@CentOS ~]# systemctl stop mysqld
[root@CentOS ~]# rm -rf /var/lib/mysql/*
[root@CentOS ~]# rm -rf /var/log/mysqld.log
[root@CentOS ~]# rm -rf /var/log/mysql-slow/slow.log
[root@CentOS ~]# innobackupex --apply-log /xtracbackup/full/2020-02-27_10_50-11-18/   重演回滚，生成回滚日志
[root@CentOS ~]#innobackupex --copy-back /xtracbackup/full/2020-02-27_10_50-11-18/     这里没有指定拷贝到哪里去，为啥没有指定呢，因为程序会读取之前备份的 my.cnf文件 里面的 datadir=/var/lib/mysql 路径，之前是在哪里，就会拷贝还原到哪里。 很多备份软件都会读取这个文件的datadir目录。
[root@CentOS ~]# chown -R mysql.mysql /var/lib/mysql 
[root@CentOS ~]# systemctl start msyqld    
[root@CentOS ~]#  mysql -p 'amdin'  / 这里的密码就是我们备份数据库时候的密码账号可以不写 默认就是root

XtraBackup 既可以保证服务的可用性，也可以保证数据的一致性，原理就和我们做lvm snap备份是一个道理

增量备份流程

1，首先进行完全备份（即我们的基准备份）比如我们在周一进行的
innobackupex –user=root –password=’admin’ /xtracbackup/full
比如生成的备份文件夹为 2020-02-27_10_50-11-18/

2 ，然后进行增量备份（周二—周六）指定上一次备份的路径这里对在周一的基础上进行备份
innobackupex –user=root –password=’admin’ –incremental /xtracbackup/full — incremental-basedir=/xtracbackup/full/2020-02-27_10_50-11-18/
执行以后会在 /xtracbackup/full 生成一个增备份文件夹(以当前备份时间命名的) 假设为 /2020-03-05_10_50-11-18/

3，第二次增量备份(具体想备份几次增量看自己计划)比如我这次在周二–周六的基础上进行再次增量备份
innobackupex –user=root –password=’admin’ –incremental /xtracbackup/full — incremental-basedir=/xtracbackup/full/2020-03-05_10_50-11-18/
注意我最后的这个增量备份依靠的文件是上一次的增量文件会再生成一个当前时间戳的文件夹，假如为 2020-03-06_10_33-02-18/

现在我有三分备份文件
一份完全备份 2020-02-27_10_50-11-18/
一次增量 2020-03-05_10_50-11-18/
二次增量 2020-03-06_10_33-02-18/

增量恢复流程

停止数据库
清理环境
依次重演回滚 redo log —> 恢复数据
修改权限
启动数据库
binlog恢复

[root@CentOS ~]# systemctl stop mysqld 
[root@CentOS ~]# rm -rf /var/lib/mysql/*
依次重演回滚
第一次，在全量的基础上恢复
[root@CentOS ~]# innobackupex --apply-log --redo-only
 /xtracbackup/full/2020-02-27_10_50-11-18/
第二次，在一次增量的基础上恢复
[root@CentOS ~]# innobackupex --apply-log --redo-only 
 /xtracbackup/full/2020-02-27_10_50-11-18/  --incremental-dir=/xtracbackup/full/2020-03-05_10_50-11-18
第三次，在二次增量的基础上恢复
[root@CentOS ~]# innobackupex --apply-log --redo-only 
 /xtracbackup/full/2020-02-27_10_50-11-18/  --incremental-dir=/xtracbackup/full/2020-03-06_10_33-02-18

此时，三次增量回滚完了，准备写入，将回滚的数据拷贝到我们的数据库目录下 /var/lib/mysql/

注意：我们每一次恢复增量，都会把相应的增量数据写入到第一次的全量备份里面，所以我们上面恢复的/xtracbackup/full 都是2020-02-27_10_50-11-18，它是迭代写入的，当我们恢复到指定的增量之后，就只需要吧原始的全量数据拷贝到mysql 的数据目录 /var/lib/mysql/ 就可以了

恢复数据：

cp
rsync
innobackupex copy-back (datadir)
注意如果我们采用 innobackupex copy-back 进行恢复的话，恢复的目录会读取备份的 my.cnf 中的 datadir 目录，如果我们是用自己安装，编译安装的方式安装的数据库的话，就会找不到datadir目录。

所以我这里采用 cp 来拷贝数据

[root@CentOS ~]# cp -rf  /xtracbackup/full/2020-02-27_10_50-11-18/*  /var/lib/mysql/
[root@CentOS ~]# chown -R mysql.mysql /var/lib/mysql
[root@CentOS ~]# systemctl start mysql
[root@CentOS ~]# mysql -p 'admin'

最后再恢复binlog的二进制日志数据；

差异备份

流程和之前的都差不多

1 完全备份
innobackupex –user=root –password=’admin’ /xtracbackup/full
生成文件 2020-02-27-14-40

2 差异备份

第一次差异:
[root@CentOS ~]#  innobackupex --user=root --password='admin' --incremental  /xtracbackup/full   --incremental-basedir=/xtracbackup/full/2020-02-27-14-40(完全备份目录) 
生成文件2020-02-28-14-40

第二次差异：
[root@CentOS ~]#  innobackupex --user=root --password='admin' --incremental  /xtracbackup/full   --incremental-basedir=/xtracbackup/full/2020-02-27-14-40(完全备份目录) 
生成文件2020-02-29-14-40

第三次差异：
[root@CentOS ~]#  innobackupex --user=root --password='admin' --incremental  /xtracbackup/full   --incremental-basedir=/xtracbackup/full/2020-02-27-14-40(完全备份目录) 
生成文件2020-02-30-14-40

可以发现我们每次差异备份的命令都是一样的，其实差异备份的原理都也是增量备份，只不过每一次增量的目标都是在完全备份的基础上去做的增量。

差异备份恢复

恢复全量的redo log
[root@CentOS ~]# innobackupex –apply-log –redo-only /xtracbackup/full/ 2020-02-27-14-40(完全备份目录)
恢复差异的redo log
[root@CentOS ~]# innobackupex –apply-log –redo-only /xtracbackup/full/ 2020-02-27-14-40(完全备份目录) –incremental-dir= /xtracbackup/full/2020-02-30-14-40 (某个差异备份)
复制数据文件（cp,rsync）同上
修改权限
启动mysql
通过binlog增量恢复最近的二进制文件数据

四 mysqldump 实现逻辑完全备份 + binlog

数据一致，服务可用 mysqldump 是基于mysql客户端的工具，所以我们可以在远程进行备份，前提是有权限

语法：

# mydump -h 服务器主机(最好是域名，不要是IP) -u用户名 -p密码  数据库名 >备份文件.sql 
数据库名： 
   -A , --all-databases   所有库
   school                 数据库名
   school student class   school 库的student表,class表
   -B , --databases b1 b2 b3  多个数据库 b1库，b2库，b3库

其他参数：
  --single-transaction    #InnoDB 一致性，服务可用性
  -x , --lock-all-tables  #MyISAM 一致性，服务可用性(MyISAM基本已淘汰)
  -E , --events           #备份事件调度器代码
  --opt                   #同时启动各种高级选项
  -R , --routines         #备份存储过程和存储函数
  -F , --flush-logs       #备份之前刷新日志
  --triggers              #备份触发器
  --master-data=1|2       #此选项将会记录binlog的日志位置与文件名并追加到文件中
更多选项
[root@CentOS ~]# mysqldump  --help

开始逻辑全备份： 
[root@CentOS ~]# mysqldump -p'admin'  \
> --all-databases \
> --singel-transaction \
> --master-data=1 \
> --flush-logs \
> >/backup/`data+%F-%H`-mysql-all.sql

binlog的恢复

在知道恢复点的时候，使用mysqlbinlog 读取binlog日志来恢复

截断开始的时候恢复
[root@CentOS ~]# mysqlbinlog breeze-bin.000003  --start-position=154 | 
 mysql -uroot -p'admin'
后面的binlog日志 全部写入
[root@CentOS ~]# mysqlbinlog breeze-bin.000004, breeze-bin.000005,breeze-bin.000006 | mysql -uroot -p'admin'

所以 binlog日志对我们来说是非常重要的，不能够随着机器的崩溃而消失。如果binlog日志消失了，那么这一段时间的数据将无法恢复。所以我们要对binlog的日志做好保存,所以备份的不仅仅是数据库更是binlog

在逻辑备份恢复的时候，暂定binlog ，因为在恢复的时候写入也会产生二进制日志，所以也会记录到binlog日志当中

所以在我们重新安装好mysql之后，初始化进入数据库修改完密码，然后
mysql > set sql_log_bin=0;
mysql > source /back/2020-02-27-mysql-all.sql
这样恢复就不会大量写入我们恢复这个过程的binlog日志。

也可以这样做，在需要写入的binlog 日志中开头起点后面写入 set sql_log_bin=0;
比如 vim breeze-bin.000003 然后再保存退出，执行恢复过程

mysqlbinlog breeze-bin.000003 –start-position=154 |
mysql -uroot -p’admin’ 这样就不会大量写入我们恢复的binlog的日志
如果在恢复的时候写入binlog日志，尤其是在大量写几百万上千万的数据的时候，再去大量写入binlog日志，会产生大量的I/O操作，严重影响性能。

流程：

备份之前：
db,table….
备份方法：
mysqldump
模拟操作：
DDL,DCL,DML,DQL
库，表，数据
恢复流程：
恢复完全备份
恢复增量备份
恢复配置文件
创建所需的目录及权限

into outfile 表的导入导出

这种模式是对mysql的表中的数据，仅仅是对数据做备份，备份速度很快，借助mysql工具可以备份成多种文件格式

例如： SELECT ……INTO OUTFILE 导出文本文件

mysql >  SELECT *FROM schoo.student1  INTO OUTFILE '/backup/student1.txt'  \
mysql >  FIELDS TERMINATED BY '----'  /定义字段分隔符

注意：在使用这种模式的时候，5.7 以后的mysql受到安全限制，不会让你写入到文件，需要添加一个允许配置，在配置文件my.cnf 中添加
secure-file-priv =/backup
并且，给mysql这个文件 /backup 相应的权限

mysql 命令导出其他文本文件

[root@CentOS ~]# mysql -uroot -p'admin' -e 'select *From school.sdudent' >/backup/student.txt
[root@CentOS ~]# mysql -uroot -p'admin' --xml -e 'select *From school.sdudent' >/backup/student.xml
[root@CentOS ~]# mysql -uroot -p'admin' --html -e 'select *From school.sdudent' >/backup/student.html

LOAD DATA INFILE 导入文本文件

mysql > DELETE FROM student1;
mysql > LOAD DATA INFILE '/backup/student.txt'  INTO TABLE school.student FIELDS TERMINATED BY '---'

表的导入和导出只导入备份表记录，不会备份表结构，因此需要用过mysqldump备份表结构，恢复时先恢复表结构，再导入表数据

简介：

一 物理备份之 tar 备份数据库