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

  1. 通过运行以下命令安装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 完全备份恢复流程

  1. 停止数据库
  2. 清理环境
  3. 重演回滚—>恢复数据
  4. 修改权限
  5. 启动数据库
[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/

增量恢复流程

  1. 停止数据库
  2. 清理环境
  3. 依次重演回滚 redo log —> 恢复数据
  4. 修改权限
  5. 启动数据库
  6. 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备份表结构,恢复时先恢复表结构,再导入表数据

发表评论

邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据