MySQL数据库的备份与恢复_数据库备份和恢复-CSDN博客

本文链接：https://blog.csdn.net/weixin_47152389/article/details/108109013

一、数据库备份的分类

1、数据备份的重要性

在生产环境中，数据的安全性至关重要，任何数据的丢失都可能产生严重的后果
造成数据丢失的原因有很多：程序错误，人为操作错误，运算错误，磁盘故障等等

2、数据库备份的分类

从物理与逻辑角度分：

物理备份：对数据库操作系统的物理文件（如数据文件，日志文件）备份
物理备份方法有：
冷备份（脱机备份）：关闭数据库时进行的
热备份（联机备份）：数据库处于运行状态，依赖于日志文件
温备份：数据库锁定表格（可读不可写）的状态下进行备份
逻辑备份：对数据库逻辑组件的备份（如表等数据库对象）

从数据库备份策略角度分：

完全备份：每次对数据库进行完整的备份
差异备份：备份自上次完全备份后被修改的文件
增量备份：只有在上次完全备份或差异备份后被修改的文件才会被备份

3、常见的备份方法

物理冷备：

备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也最简单

专用备份工具mydump或mysqlhotcopy

mysqldump常用逻辑备份工具
mysqlhotcopy仅拥有备份mylsam和archive表

启用二进制日志进行增量备份

需要刷新二进制日志

第三方工具

免费的MySQL热备份软件percona xtrabackup

二、mysql完全备份与恢复

1、优缺点

是对整个数据库进行备份，保存的是备份完成时刻的数据库，是差异备份和增量备份的基础
优点：备份与恢复操作简单方便
缺点：数据存在大量重复、占用大量备份空间、备份恢复时间长

2、完全备份分类

1）物理冷备份及恢复

关闭mysql数据库、使用tar命令直接打包数据库文件夹、直接替换现有MySQL目录即完成。
备份操作：

[root@localhost ~]# systemctl stop mysqld  #关闭mysql数据库
[root@localhost ~]# mkdir /backup
[root@localhost ~]# ls /
backup  boot  etc   lib    media  opt   root  sbin  sys  usr
bin     dev   home  lib64  mnt    proc  run   srv   tmp  var
[root@localhost /]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/  ##直接打包
[root@localhost /]# cd /backup/
[root@localhost backup]# ls
mysql_all-2020-08-21.tar.gz
[root@localhost backup]#

恢复：

[root@localhost backup]# mv /usr/local/mysql/data/ /bak
[root@localhost backup]# tar zxf /backup/mysql_all-2020-08-21.tar.gz
[root@localhost backup]# mv /backup/usr/lcoal/mysql/data /usr/local/mysql/
[root@localhost backup]# systemctl start mysqld

2）mysqldump备份与恢复

备份

mysqldump命令对单个库进行完全备份
mysqldump命令对多个库进行完全备份
mysqldump命令对所有库进行完全备份
mysqldump命令对表进行完全备份

恢复

source：用在mysql模式下
在这里插入图片描述

mysql：Linux命令模式下使用
在这里插入图片描述

三、mysql增量备份与恢复

1、增量备份

完全备份中存在数据重复与时间过长等问题。增量备份特点：没有重复的数据，时间短，恢复时可以精确到每个步骤。
mysql没有直接的备份方法，可通过mysql提供的二进制日志间接备份
mysql二进制日志对备份的意义：
二进日志保存了所有更新或者可能更新数据库的操作
二进制日志在启动mysql服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接受到flush logs命令后重新创建新的日志文件
只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

增量恢复方法：
一般恢复：将所有二进制日志内容全部恢复
基于位置恢复：在某一错误操作段进行恢复，可跳过错误的操作进行恢复
基于时间点恢复：跳过存在错误操作的时间点

2、备份与恢复

[root@localhost backup]# mysqldump -uroot -p123456 info info_bak > /backup/info_bak.mysql   ##完全备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost backup]# ls
info_bak.mysql  mysql_all-2020-08-21.tar.gz
[root@localhost data]# vim /etc/my.cnf
[mysqld]
......
log-bin=mysql-bin   ##开启日志功能
[root@localhost data]# systemctl restart mysqld  ##重启

在这里插入图片描述
完全备份

[root@localhost data]# mysqldump -uroot -p123456 info info_bak > /opt/info_bak.mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ls /opt
aa.txt  info_bak.mysql  mysql-5.7.20  mysql-boost-5.7.20.tar.gz  rh

三次操作

mysql> insert into info_bak values(3,'wangwu',88,'shanghai');   ##添加数据
Query OK, 1 row affected (0.00 sec)

mysql> delete from info_bak where id=1;   ##删除数据
Query OK, 1 row affected (0.00 sec)

mysql> insert into info_bak values(4,'xiaobai',99,'wuhu');   ##添加数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from info_bak;
+----+---------+-------+----------+
| id | name    | score | address  |
+----+---------+-------+----------+
|  2 | lisi    | 22.00 | nanjing  |
|  3 | wangwu  | 88.00 | shanghai |
|  4 | xiaobai | 99.00 | wuhu     |
+----+---------+-------+----------+
3 rows in set (0.00 sec)

增量备份
在这里插入图片描述
此时增量备份的操作在000001文件中

接下来进行一般恢复

删除表
mysql> drop table info_bak;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
恢复操作
[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p123456

mysql> select * from info_bak;    ##这是三次操作完的数据库内容
+----+---------+-------+---------+
| id | name    | score | adress  |
+----+---------+-------+---------+
|  2 | lisi    | 56.00 | nanjing |
|  3 | wangwu  | 77.00 | beijing |
|  4 | xiaobai | 88.00 | wuhu    |
+----+---------+-------+---------+
3 rows in set (0.01 sec)

基于位置的恢复（把三次操作中错误的操作略过）
在这里插入图片描述

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/aa.txt    ##解码查看日志文件
[root@localhost data]# vim /opt/aa.txt
```handlebars
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/aa.txt    ##此命令查看文件中的三次操作文件解码内容
[root@localhost data]# vim /opt/aa.txt 
......
# at 577
 #200823 10:10:14 server id 1  end_log_pos 637 CRC32 0x0c60e876  Table_map: `in    fo`.`info_bak` mapped to number 108
 # at 637
 #200823 10:10:14 server id 1  end_log_pos 698 CRC32 0x6b0a69ff  Delete_rows: t    able id 108 flags: STMT_END_F
 ### DELETE FROM `info`.`info_bak`
 ### WHERE
 ###   @1=1
 ###   @2='zhangsan'
 ###   @3=99.00
 ###   @4='shanghai'
 # at 698
......

在这里插入图片描述

在这里插入图片描述
基于时间的恢复

[root@localhost data]# vim /opt/aa.txt
# at 577
#200823 10:10:14 server id 1  end_log_pos 637 CRC32 0x0c60e876  Table_map: `info`.`info_bak` mapped to number 108
# at 637
#200823 10:10:14 server id 1  end_log_pos 698 CRC32 0x6b0a69ff  Delete_rows: table id 108 flags: STMT_END_F
### DELETE FROM `info`.`info_bak`
### WHERE
###   @1=1
###   @2='zhangsan'
###   @3=99.00
###   @4='shanghai'
......
# at 926
#200823 10:10:45 server id 1  end_log_pos 982 CRC32 0x2fb4f7b3  Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `info`.`info_bak`
### SET
###   @1=4
###   @2='xiaobai'
###   @3=88.00
###   @4='wuhu'

在这里插入图片描述