• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

互联网 diligentman 2周前 (10-16) 12次浏览

目录

  • 一、完全备份、差异备份与增量备份概述
    • 三者的特点
    • 三者的区别举例
  • 二、完全备份实例
    • 2.1 冷备份与恢复
    • 2.2 mysqldump备份与恢复
      • 2.2.1单库备份与恢复
      • 2.2.2多库备份
      • 2.2.3所有库备份
      • 2.2.4对数据库表数据备份
  • 三、增量备份实例
    • 3.1增量备份
    • 3.2基于一般恢复
    • 3.3基于位置恢复
    • 3.4基于时间点恢复

一、完全备份、差异备份与增量备份概述

三者的特点

完全备份:每次对数据库进行完整的备份(包括表的结构和数据)
特点:备份与恢复操作简单,但占用大量备份空间,数据重复率高,冗余数据多,备份与恢复时间长

差异备份:以完全备份为基准,备份自从上次完全备份之后被修改过的文件
特点:占用空间小,但是安全性变差

增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
特点:无冗余数据,依靠二进制日志文件进行逐次增量备份,单个文件丢失则数据不完整,安全性低

三者的区别举例

备份类型 第一次备份:原有数据a 第二次备份:a,b 第三次备份:a,b,c
完全备份 a a,b a,b,c
差异备份 a b b,c
增量备份 a b c

二、完全备份实例

2.1 冷备份与恢复

数据库所有的数据在这个目录里,直接整个目录打包(需要关闭数据库,基本不用)
数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

[root@host3 data]# service mysqld stop //先关闭数据库
Redirecting to /bin/systemctl stop mysqld.service
[root@host3 data]# mkdir /opt/backup //创建备份目录,
[root@host3 data]# tar zcvf /opt/backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/
//把整个数据目录打包
[root@host3 data]# cd /opt/backup
[root@host3 backup]# ll
total 1384
-rw-r--r-- 1 root root 1413851 Oct 13 20:03 mysql_all_2020-10-13.tar.gz

数据库故障时,直接把压缩包解压,数据挪回data目录下

2.2 mysqldump备份与恢复

2.2.1单库备份与恢复

mysqldump -u 用户 -p[密码,不写则进行交互] 库名 > 保存的位置(文件以sql格式结尾)

[root@host3 mysql]# mysqldump -uroot -p school > /opt/school.sql
Enter password: 
[root@host3 mysql]# cd /opt
[root@host3 opt]# ll
total 47700
drwxr-xr-x  38 7161 31415     4096 Oct 14 10:22 mysql-5.7.20
-rw-r--r--   1 root root  48833145 Oct 23  2017 mysql-boost-5.7.20.tar.gz
drwxr-xr-x.  2 root root         6 Oct 31  2018 rh
-rw-r--r--   1 root root      2144 Oct 14 14:05 school.sql


查看sql文件可知,没有备份创建数据库的语句,所以恢复的时候需要先创建数据库,再恢复
数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。
制造故障:

mysql> drop database school;
Query OK, 1 row affected (0.01 sec)
直接恢复
mysql> source /opt/school.sql
ERROR 1046 (3D000): No database selected   报错,没有数据库可选
Query OK, 0 rows affected (0.00 sec)

单库正确恢复方式

mysql> create database school;   数据库名可根据需求取
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed

方法一:在数据库内用source语句恢复

mysql> source /opt/school.sql
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

查看,已恢复
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
9 rows in set (0.00 sec)

方法二:用linux命令mysql进行恢复
mysql> drop table info;  //把表删了
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> exit;
Bye
[root@host3 opt]# mysql -uroot -p school < /opt/school.sql  //反向导入恢复
Enter password: 

[root@host3 opt]# mysql -uroot -p -e 'show tables from school'  //查看
Enter password: 
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

2.2.2多库备份

mysqldump [选项] –databases 库名1 [库名2] … > /备份路径/备份文件名

[root@host3 opt]# mysqldump -uroot -p --databases mysql school > /opt/mysql-school.sql
Enter password: 
[root@host3 opt]# cd /opt
[root@host3 opt]# ll
total 49684
drwxr-xr-x  38 7161 31415     4096 Oct 14 10:22 mysql-5.7.20
-rw-r--r--   1 root root  48833145 Oct 23  2017 mysql-boost-5.7.20.tar.gz
-rw-r--r--   1 root root   1098302 Oct 14 14:26 mysql-school.sql
drwxr-xr-x.  2 root root         6 Oct 31  2018 rh
-rw-r--r--   1 root root      2144 Oct 14 14:05 school.sql

进去文件内查看,可以看到,在多库备份的时候,备份了创建数据库的语句,所以恢复的时候可以直接恢复
数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

2.2.3所有库备份

mysqldump [选项] –all-databases > /备份路径/备份文件名

[root@host3 opt]# mysqldump -uroot -p --all-databases > /opt/all-data.sql
Enter password: 
[root@host3 opt]# ls
all-data.sql  mysql-boost-5.7.20.tar.gz  rh
mysql-5.7.20  mysql-school.sql           school.sql

2.2.4对数据库表数据备份

mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名

[root@host3 opt]# mysqldump -uroot -p school info > /opt/school-info.sql
Enter password: 

三、增量备份实例

3.1增量备份

增量备份基于二进制日志文件备份,二进制日志在启动mysql服务器后开始记录,并在文件达到二进制日志所设置的最大值或者接受到flush logs命令后重新创建新的日志文件,生成二进制的文件序列,并及时把这些日志文件保存到安全的存储位置,即可完成一个时间段的增量备份。
先进入my.cnf文件
数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。
重启数据库刷新
数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。
接下来关于所有的数据库操作,都被记录在000001文件里。
进行一系列正确错误操作,目的是加入第十条

ysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
9 rows in set (0.00 sec)

mysql> delete from info where name='lisi';
Query OK, 1 row affected (0.00 sec)

mysql> insert into info (name,hobby,score) values ('zhaosi',2,66);
Query OK, 1 row affected (0.00 sec)

mysql> delete from info where hobby=1;
Query OK, 6 rows affected (0.00 sec)

mysql> select * from info;
+----+--------+-------+-------+------+
| id | name   | hobby | score | addr |
+----+--------+-------+-------+------+
|  3 | wangwu |     2 |    77 | NULL |
|  5 | tianqi |     3 |    50 | NULL |
| 10 | zhaosi |     2 |    66 | NULL |
+----+--------+-------+-------+------+
3 rows in set (0.00 sec)
误删了很多数据

想要通过增量备份恢复正确的数据
先刷新二进制日志文件

mysqladmin -uroot -p flush-logs
Enter password: 

数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
使用mysqlbinlog工具查看日志文件

每一次命令,都是一次事务的提交
数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

3.2基于一般恢复

mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p
一般恢复是直接把整个二进制文件的内容进行恢复。

mysql> select * from info;			当前内容
+----+--------+-------+-------+------+
| id | name   | hobby | score | addr |
+----+--------+-------+-------+------+
|  3 | wangwu |     2 |    77 | NULL |
|  5 | tianqi |     3 |    50 | NULL |
| 10 | zhaosi |     2 |    66 | NULL |
+----+--------+-------+-------+------+

mysql> drop table info;
Query OK, 0 rows affected (0.02 sec)
先把表删了,模拟故障
先进行完全备份的恢复
mysql> source /opt/school-info.sql
mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
再进行一般恢复
[root@host3 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -p
Enter password: 
查看,所有操作都恢复了
[root@host3 data]# mysql -uroot -p -e 'select * from school.info'
Enter password: 
+----+--------+-------+-------+------+
| id | name   | hobby | score | addr |
+----+--------+-------+-------+------+
|  3 | wangwu |     2 |    77 | NULL |
|  5 | tianqi |     3 |    50 | NULL |
| 10 | zhaosi |     2 |    66 | NULL |
+----+--------+-------+-------+------+

3.3基于位置恢复

通过查看文件,可以得出

操作 第一次操作 第二次操作 第三次操作
操作id 开始219 结束434 开始499 结束716 开始781 结束1095
时间点 开始2020-10-14 16:30:19结束2020-10-14 16:31:50 开始2020-10-14 16:31:50结束2020-10-14 16:33:38 开始2020-10-14 16:33:38结束2020-10-14 16:52:38

只有第二次是正确操作,第一次和第三次都误删数据
1、恢复数据到指定位置
mysqlbinlog –stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

和一般恢复一样,先进行完全备份,变成这样
mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
只进行第一次的操作恢复
mysqlbinlog --no-defaults --stop-position='434' mysql-bin.000001 |mysql -uroot -p
Enter password: 

数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

2、从指定的位置开始恢复数据
mysqlbinlog –start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
从第二次操作开始

[root@host3 data]# mysqlbinlog --no-defaults --start-position='499' mysql-bin.000001 |mysql -uroot -p
Enter password: 

数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

3.4基于时间点恢复

删除表,恢复完全备份后

mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+

1、从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] –stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

只进行第一次操作
[root@host3 data]# mysqlbinlog --no-defaults --stop-datetime='2020-10-14 16:31:50' mysql-bin.000001 |mysql -uroot -p
Enter password: 

数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

2、从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
进行后面的操作
Enter password:

[root@host3 data]# mysqlbinlog --no-defaults --start-datetime='2020-10-14 16:31:50' mysql-bin.000001 |mysql -uroot -p

数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。

3、从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] –start-datetime=’年-月-日 小时:分钟:秒’ –stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

恢复所有正确操作,不回复误删的操作
完全备份恢复之后
直接进行第二步操作

[root@host3 data]# mysqlbinlog --no-defaults --start-datetime='2020-10-14 16:31:50' --stop-datetime='2020-10-14 16:33:38' mysql-bin.000001 |mysql -uroot -p
Enter password: 

数据库数据损坏怎么办?——完全备份、差异备份与增量备份的合理使用。
恢复正确数据


喜欢 (0)