Mysql-数据备份与恢复-4

2023-03-14

概述

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

备份的意义

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

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

备份过程中必须考虑的因素：

1、数据的一致性

2、服务的可用性

mysql备份类型

物理备份

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同版本的mysql。

1、热备（hot backup）

在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件。

-对应用基本无影响（但是性能还是会有所下降，所以尽量不要在主库上做备份，在从库上做）

2、冷备（cold backup）

备份数据文件，需要停机，是在关闭数据库的时候进行。

– 备份 datadir 目录下的所有文件

3、温备（warm backup）

-针对mysam的备份（mysam不支持热备），备份时候实例只读不可写，数据库锁定表格（不可写入但可以读）的状态下进行。

-对应用影响很大

-通常加一个读锁

逻辑备份

备份的是建表、建库

插入等操作所执行的sql语句（DDL、DML、DCL），使用于中小型数据库，效率相对较低。

物理备份

完全备份-完整备份

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

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

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

增量备份

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

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

差异备份

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

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

 

总结

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

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

tar冷备份

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

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

备份过程

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

1、停止数据库服务

[root@localhost ~]# mysql -uroot -p'1' -e 'create database zlp'  #目的验证数据是否恢复
[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 wanbei' #创建验证数据

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 |
| wanbei             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

增量备份

增量备份流程

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

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

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

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 zengbei1'

增量备份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/zengbei
2022-03-25_12-03-45

创建新数据

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

增量备份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/zengbei
2022-03-25_12-03-45   2022-03-25_12-06-55

创建新数据

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

增量备份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/zengbei
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                |
| zengbei            |
| zengbei1           |
| zengbei2           |
| zengbei3           |
+--------------------+

差异备份

差异备份流程

原理：

1、创建备份目录

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

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/chabei --incremental-basedir=/xtrabackup/full/2022-03-25_12-30-02
[root@localhost ~]# ls /xtrabackup/chabei
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/chabei --incremental-basedir=/xtrabackup/full/2022-03-25_12-30-02
[root@localhost ~]# ls /xtrabackup/chabei
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 zlp;
mysql> create table zlp.ceshi(id int,name varchar(50));
mysql> insert into zlp.ceshi values(1,'zcg');
mysql> drop database zlp;          #模拟数据丢失

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 |
| zlp                |
| sys                |
+--------------------+

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

# mysqlbinlog --start-position='372' --stop-position='743' /var/lib/mysql/mybinlog.000003 |mysql -uroot -p'1'
mysql> select * from zlp.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日志

相关推荐

随机推荐