MySQL-数据备份与恢复第五天

物理和逻辑备份的区别
	逻辑备份	物理备份
备份方式	备份数据库建库、建表、插入sql语句	备份数据库物理文件
优点	备份文件相对较小，只备份表中的数据与结构	恢复速度比较快
缺点	恢复速度慢（需要重建索引，存储过程等）	备份文件相对较大（备份表空间，包含数据与索引）
对业务影响	I/O 负载加大	I/O负载加大
代表工具	MySQLdump	tar、cp、ibbackup、xtrabackup、lvm snapshot、mysqlbackup

物理备份

完全备份-完整备份

每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份和增量备份的基础。

优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快。

缺点：占用空间大，备份速度慢。

增量备份

每次备份上一次备份到现在产生的新数据。只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或者上次增量备份的时间为时间点，仅备份这之间的数据变化。

特点：因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。

差异备份

只备份跟完整备份不一样的。备份那些自第一次完整备份之后被修改过的所有文件，备份的时间起点是从第一次的完整备份起，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，而不是上一次），备份自第一次完整备份以来所欲的修改过的文件。备份数据量会越来越大。

特点：占用空间比增量备份大，比完整备份小，恢复时只需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

总结

增量备份节约磁盘资源，恢复比较复杂。

差异备份：占用磁盘资源比较多，可以任意恢复某一时间节点的数据。

tar冷备份

注意：正常情况下，做备份时不会将数据保留在本地服务器，都会远程拷贝或者nfs共享或者采用ftp的方式保存到其他服务器。备份期间服务器不可用（一般夜间执行）

可以用tar+scp+cron（计划任务）+脚本的方式定期备份

备份过程

做实验时保证数据库中有数据，以便验证是否恢复数据

1、停止数据库服务

[root@localhost ~]# mysql -uroot -p'1' -e 'create database qf'  #目的验证数据是否恢复
[root@localhost ~]# systemctl stop mysqld

2、tar命令对数据库物理文件进行打包压缩

[root@localhost ~]# cd /var/lib/mysql
[root@localhost mysql]# tar zcf /opt/msyql-`date +%F`.all.tar.gz ./*

[root@localhost ~]# rm -rf /var/lib/mysql/*    #模拟数据丢失

3、解压缩数据库

[root@localhost ~]# tar zxf /opt/mysql-`date +%F`.all.tar.gz -C /var/lib/mysql
[root@localhost ~]# chown mysql.mysql /var/lib/mysql -R

4、启动mysql，验证数据是否恢复。

注意：如果在模拟数据丢失时，删除的是/var/lib/mysql整个目录，在数据恢复之前需要创建对应目录，并修改属主属组为mysql。

xtrabackup备份

Xtrabackup是开源免费的支持MySQL 数据库热备份的软件，在 Xtrabackup 包中主要有 Xtrabackup 和 innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能。它可以不暂停服务创建Innodb热备份。

部署xtrabackup

1、安装percona-xtrabackup的软件仓库

[root@localhost ~]# rpm -ivh https://downloads.percona.com/downloads/percona-release/percona-release-0.1-4/redhat/percona-release-0.1-4.noarch.rpm

2、关掉签名机制

vim /etc/yum.repos.d/percona-release.repo

扩展：安装一个rpm包时，会生成他的仓库文件，如果不小心删了他的仓库文件，此时再重新安装软件包也不会生成仓库文件，此时需要卸载掉这个软件包（rpm -e xxxx，或者yum -y remove xxxx.rpm）而不是卸载这个软件，（yum -y remove xxxx）因为还没安装这个软件。

3、下载软件

[root@localhost ~]# yum -y install percona-xtrabackup-24.x86_64

注意：如果依赖包perl-DBD-MySQL安装不上，需先把percona源拿掉用centos的源单独安装，然后再安装percona-xtrabackup-24.x86_64

[root@localhost yum.repos.d]# mv percona-release.repo percona-release.repo.bak
[root@localhost yum.repos.d]# yum -y install perl-DBD-MySQL
[root@localhost yum.repos.d]# mv percona-release.repo.bak percona-release.repo 
[root@localhost yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64

报错解决

报错：安装xtrabackup
Transaction check error:
  file /etc/my.cnf from install of Percona-Server-shared-56-5.6.49-rel89.0.1.el7.x86_64 conflicts with file from package mysql-community-server-5.7.31-1.el7.x86_64

Error Summary

解决方案：安装数据库时一些关于数据库的安装包没有安装
yum -y install mysql-community-libs-compat
然后在去安装xtrabackup

完整备份

完全备份流程

1、创建备份目录

[root@localhost ~]# mkdir /xtrabackup/full -p
[root@localhost ~]# mysql -uroot -p'1' -e 'create database wanzheng' #创建验证数据

2、备份

[root@localhost ~]# innobackupex --user=root --password='1' /xtrabackup/full

完整备份恢复流程

1、停止数据库，模拟数据丢失（做热备时是不需要停止服务器的，我们做实验停止服务器是为了模拟数据丢失，如果不关闭数据库，数据库的物理文件会一直生成新文件，可能会混淆实验效果）

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# rm -rf /var/log/mysql-slow/slowlog  #有的话删除，没有开启则不需操作
[root@localhost ~]# rm -rf /var/log/mysqld.log

2、恢复之前的验证恢复

[root@localhost ~]# innobackupex --apply-log /xtrabackup/full/2022-03-25_11-30-47

恢复之前需要确认配置文件内有数据库目录制定，不然xtrabackup不知道恢复到哪里

# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql

3、恢复数据

[root@localhost ~]# innobackupex --copy-back /xtrabackup/full/2022-03-25_11-30-47

4、修改权限

[root@localhost ~]# chown mysql.mysql /var/lib/mysql -R

5、启动数据库验证数据是否恢复

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p'1' -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wanzheng           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

增量备份

增量备份流程

原理：每次备份上一次备份到现在产生的新数据

1、创建备份目录(记得删除上个实验的备份目录/xtrabackup/full)

[root@localhost ~]# mkdir -p /xtrabackup/{full,zeng}
mysql -uroot -p'1' -e 'create database zeng'

2、完整备份

[root@localhost ~]# innobackupex --user=root --password='1' /xtrabackup/full
[root@localhost ~]# ls /xtrabackup/full
2022-03-25_12-00-00

3、增量备份

创建新数据

[root@localhost ~]# mysql -uroot -p'1' -e 'create database zeng1'

增量备份1

[root@localhost ~]# innnobackupex --user=root --password='1' --incremental /xtrabackup/zeng --incremental-basedir=/xtrabackup/full/2022-03-25_12-00-00
[root@localhost ~]# ls /xtrabackup/zeng
2022-03-25_12-03-45

创建新数据

[root@localhost ~]# mysql -uroot -p'1' -e 'create database zeng2'

增量备份2

[root@localhost ~]# innobackupex --user=root --password='1' --incremental /xtrabackup/zeng --incremental-basedir=/xtrabackup/zeng/2022-03-25_12-03-45
[root@localhost ~]# ls /xtrabackup/zeng
2022-03-25_12-03-45   2022-03-25_12-06-55

创建新数据

[root@localhost ~]# mysql -uroot -p'1' -e 'create database zeng3'

增量备份3

[root@localhost ~]# innobackupex --user=root --password='1' --incremental /xtrabackup/zeng --incremental-basedir=/xtrabackup/zeng/2022-03-25_12-06-55
[root@localhost ~]# ls /xtrabackup/zeng
2022-03-25_12-03-45   2022-03-25_12-06-55   2022-03-25_12-09-12

增量备份恢复流程

1、停止数据库，模拟数据丢失

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# rm -rf /var/log/mysql-slow/slowlog  
[root@localhost ~]# rm -rf /var/log/mysqld.log

2、依次重演回滚 redo log --> 恢复数据（想要恢复到某一刻的数据，需要依次回滚到该时刻的增量备份，比如我要恢复到第三次增量备份的数据，就要依次回滚到第三次增量备份的数据）

[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-03-25_12-00-00     #回滚到完整备份
[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-03-25_12-00-00 --incremental-dir=/xtrabackup/zeng/2022-03-25_12-03-45  #回滚到增量备份1
[root@localhost ~]# innobackupex --apply-log --redo-noly /xtrabackup/full/2022-03-25_12-00-00 --incremental-dir=/xtrabackup/zeng/2022-03-25_12-06-55  #回滚到增量备份2
[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-03-25_12-00-00 --incremental-dir=/xtrabackup/zeng/2022-03-25_12-09-12  #回滚到增量备份3

3、数据恢复，只需要恢复全备,修改/var/libmysql目录下所有文件的属主属组

[root@localhost ~]# innobackupex --copy-back /xtrabackup/full/2022-03-25_12-00-00
[root@localhost ~]# chown mysql.mysql /var/lib/mysql -R

4、启动数据库，验证数据是否恢复

[root@localhost ~]# systemctl start msyqld
[root@localhost ~]# mysql -uroot -p'1' -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zeng               |
| zeng1              |
| zeng2              |
| zeng3              |
+--------------------+

差异备份

差异备份流程

原理：

1、创建备份目录

[root@localhost ~]# mkdir -p /xtrabackup/{full,cha}
[root@localhost ~]# mysql -uroot -p'1' -e 'create database cha'

2、完整备份

[root@localhost ~]# innobackupex --user=root --password='1' /xtrabackup/full
[root@localhost ~]# ls /xtrabackup/full
2022-03-25_12-30-02

3、差异备份

创建新数据

[root@localhost ~]# mysql -uroot -p'1' -e 'create database chayi1'

差异备份1

[root@localhost ~]# innobackupex --user=root --password='1' /xtrabackup/cha --incremental-basedir=/xtrabackup/full/2022-03-25_12-30-02
[root@localhost ~]# ls /xtrabackup/cha
2022-03-25_12-35-12

创建新数据

[root@localhost ~]# mysql -uroot -p'1' -e 'create database chayi2'

差异备份2

[root@localhost ~]# innobackupex --user==root --password='1' --incremental /xtrabackup/cha --incremental-basedir=/xtrabackup/full/2022-03-25_12-30-02
[root@localhost ~]# ls /xtrabackup/cha
2022-03-25_12-35-12  2022-03-25_12-38-12

创建新数据

[root@localhost ~]# mysql -uroot -p'1' -e 'create database chayi3'

差异备份3

[root@localhost ~]# innobackupex --user=root --password='1' --incremental /xtrabackup/cha --incremental-basedir=/xtrabackup/full/2022-03-25_12-30-02
[root@localhost ~]# ls /xtrabackup/cha
2022-03-25_12-35-12  2022-03-25_12-38-12  2022-03-25_12-42-00

差异备份恢复流程

1、关闭数据库，模拟数据丢失

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# rm -rf /var/log/mysql-slow/slowlog  
[root@localhost ~]# rm -rf /var/log/mysqld.log

2、数据回滚

想恢复第几次备份的，直接将那一次的差异数据回滚到全备，比如我想恢复第二次差异备份的数据，就将第二次差异备份的数据回滚到全备

完整备份回滚

[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-03-25_12-30-02

差异备份回滚

[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2022-03-25_12-30-02 --incremental-dir=/xtrabackup/cha/2022-03-25_12-38-12

3、数据恢复，只需要恢复到全备

[root@localhost ~]# innobackupex --copy-back /xtrabackup/full/2022-03-25_12-30-02
[root@localhost ~]# chown mysql.mysql /var/lib/mysql -R

4、启动mysql，查看数据是否恢复

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p'1' -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| cha                |
| chayi1             |
| chayi2             |
+--------------------+   
#可以看到只看到了chayi2，因为没有恢复到差异备份3的数据，所以没有chayi3

通过bin-log日志恢复数据

开启binlog日志

vim /etc/my.cnf 在最下方插入log-bin=/var/lib/mysql/mybinlog server-id=1

/var/lib/mysql的属主属组是mysql，如果指定了其他目录，需要创建对应的目录，并修改属主属组为mysq

mysql> flush logs; #刷新binlog日志会截断产生新的日志文件

重启mysqld也会截断binlog日志

查看binlog日志的方法

1、# mysqlbinlog /var/lib/mysql/mybinlog.000001 -v --base64-output=decode-rows

（不加后边的参数的话无法看到插入的数据的部分，会显示乱码）

2、mysql> show binlog events in 'mybinlog.000001'; （必须加引号）

通过binlog日志内容恢复数据的方法

1、通过时间点恢复数据

# mysqlbinlog --start-datetime='开始时间点' --stop-datetime='结束时间点' /var/lib/mysql/mybinlog.000001 |mysql -uroot -p'1'

2、通过位置点恢复数据

# mysqlbinlog --start-position='开始位置点' --stop-position='结束位置点' /var/lib/mysql/mybinlog.000001 |mysql -uroot -p'1'

实验：

1、创建测试数据

mysql> create database qf;
mysql> create table qf.ceshi(id int,name varchar(50));
mysql> insert into qf.ceshi values(1,'zcg');
mysql> drop database qf;          #模拟数据丢失

2、查看binlog日志

# mysqlbinlog /var/lib/mysql/mybinlog.000001 -v --base64-output=decode-rows

（在进行该实验时，失败了两次，每次都是位置点没找好，拿上图为例，在用位置点恢复表以及表中数据时，起止位置点选择的是372-712，结果每次都是只恢复了表，没有恢复表中的数据，结果第三次是把起止位置点改为372-743，回复成功，可能插入数据时，在712这个点这个命令结束，但是mysql并没有将数据更新到表中，可能跟事务的提交方式有关，默认自动提交，只到743这个点，整个数据的插入才算完成。只是本小白的猜测）

3、通过时间点恢复库（不恢复表和表中的数据）

# mysqlbinlog --start-datetime='2022-03-25 21:22:29' --stop-datetime='2022-03-25 21:23:02' /var/lib/mysql/mybinlog.000003 |mysql -uroot -p'1'
mysql> show databases
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qf                 |
| sys                |
+--------------------+

4、通过位置点恢复表，以及表中的数据

# mysqlbinlog --start-position='372' --stop-position='743' /var/lib/mysql/mybinlog.000003 |mysql -uroot -p'1'
mysql> select * from qf.ceshi;
+------+------+
| id   | name |
+------+------+
|    1 | zcg  |
+------+------+
# 恢复成功

注意：如果在恢复的过程中，第一次恢复没有达到效果，只恢复表，没有恢复数据，有两种解决方案：

第一种：把恢复的删除，然后重新选择时间点，进行恢复

第二种：重新选择时间点/位置点，把恢复过了的剔除掉

逻辑备份

mysqldump实现逻辑完全备份+binlog

环境：数据一致，服务可用

备份表

备份：    # mysqldump -u root -p1 db1 t1  > /db1.t1.sql
恢复：    # mysql -u root -p1 db1  < /db1.t1.sql

备份一个库

   # mysqldump -u root -p1 db1 > /db1.sql

备份多个库

    #mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.sql

备份所有库

  #mysqldump  -u root -p1 -A > /alldb.sql

恢复数据库
    为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志
    因为binlog使用binlog日志恢复数据时也会产生binlog日志
    mysql> set sql_log_bin=0  临时关闭

    mysql> source  db1.t1.sql
    或者
    #mysql -u root -p1 -D  db1 < db1.t1.sql

常用备份选项：
-A, --all-databases
备份所有库

-B, --databases bbs test mysql
备份多个数据库

-F, --flush-logs
备份之前刷新binlog日志

fajixianshouhu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL-数据备份与恢复第五天

目录概述备份的意义mysql备份类型物理备份逻辑备份物理备份tar冷备份备份过程xtrabackup备份部署xtrabackup完整备份完全备份流程完整备份恢复流程增量备份增量备份流程增量备份恢复流程差异备份差异备份流程差异备份恢复流程通过bin-log日志恢复数据开启binlog日志查看binlog日志的方法通过binlog日志内容恢复数据的方法概述所有备份数据都应放在非数据库本地，而且建议有多份副本。
复制链接

扫一扫