13-MySQL--数据备份与还原(数据表备份+单表数据备份+SQL备份+增量备份)

MySQL 专栏收录该内容
13 篇文章 0 订阅

一、数据备份与还原

1、备份:将当前已有的数据或者记录保留(在不影响原来数据的基础上保留)

2、还原:将已经保留的数据恢复到对应的表中

3、为什么要做备份还原

(1)防止数据丢失:被盗、误操作等

(2)保护数据记录

4、数据备份还原的方式有很多种:数据表备份、单表数据备份、SQL备份、增量备份

二、数据表备份

1、数据表备份:不需要通过SQL来备份,直接进入到数据库文件夹复制对应的表结构以及数据文件。还原的时候,直接将备份的内容放进去即可

2、数据表备份有前提条件:根据不同的存储引擎有不同的区别

(1)存储引擎:MySQL进行数据存储的方式。主要有两种:innodb和myisam

(2)对比innodb和myisam数据存储方式

a). innodb:只有表结构xxx.frm,所有表的数据全部存储到ibdata1文件中(data\ibdata1跨库不识别)

好处:集中管理,数据查询的效率相对较高

弊端:管的太多,算法比较复杂

b). myisam:表、数据和索引全部单独分开存储(不依赖任何数据库)。一个存储引擎为myisam的表创建成功后,在数据库对应的文件夹下,会产生三个文件:

表名.frm:结构文件

表名.MYD:数据data

表名.MYI:索引index

3、数据表还原

(1)数据表备份通常适用于myisam存储引擎。备份时,直接复制xxx.frm+xxx.MYD+xxx.MYI三个文件即可。还原时,将复制的备份文件(xxx.frm+xxx.MYD+xxx.MYI)放到对应的数据库下,即可使用

eg:在mydatabase数据库中创建了一个存储引擎是myisam的表my_myisam。备份时,直接将mydatabase文件夹下的对应文件(三个)复制即可。还原时,如果想要将my_myisam表还原到test数据库下,直接将复制的三个文件粘贴到test文件夹中,即可使用my_myisam表的数据

(2)如果数据表的存储引擎是innodb,不能使用数据表备份这种方式备份数据。因为存储引擎是innodb的数据表,表结构文件(表名.frm)在数据库对应的文件夹下,但表中的数据在data\ibdata1文件中。只备份表结构没用,要的是结构中的数据。而innodb规定这个数据表只能在这个数据库下,对应的数据才能提供输出。数据在data\ibdata1中,跨库不识别

eg:在mydatabase数据库中创建了一个存储引擎是innodb的表my_class,想将该表my_class移动到test数据库下。如果只将mydatabase文件夹下的表结构文件my_class.frm复制到test文件夹下,use test; show tables; 时可以看到my_class表,但select * from my_class; 查询数据时会报错,找不到my_class表

4、存储引擎是myisam的数据表非常好迁移,备份时也不需要断掉业务(服务器可以继续使用),直接备份文件。但是备份上占用磁盘空间,没有效率 -- 不推荐使用数据表备份

三、单表数据备份

1、单表数据备份:每次只能备份一张表,且只能备份表中数据,不能备份表结构

2、通常的使用:将表中的数据导出到文件

注:二维表不能放到.excel中进行统计,所以要将数据导出到.txt再进行打印等操作

3、单表数据备份:从表中选出一部分数据保存到外部的文件xxx\xxx\xxx.txt中(outfile)。前提是外部文件不存在

    -- 单表数据备份:从表中选出一部分数据保存到外部的文件中(outfile)
    -- 前提是外部文件不存在
    -- 备份文件所在路径要加单引号
    select * / 字段列表 into outfile 备份文件所在路径 from 数据源;
    -- 单表数据备份:从表中选出一部分数据保存到外部的文件中(outfile)
    -- 前提是外部文件不存在
    -- 备份文件所在路径要加单引号

    -- 选择要操作的数据库
    use mydatabase;

    -- 备份文件所在路径要加单引号
    select * into outfile 'C:/ProgramData/MySQL/MySQL Server 5.5/data/test/student.txt' from my_student;

(1)备份文件所在路径要加单引号,且指定的备份文件所在路径是:路径+文件名

(2)备份成功后,在指定的备份文件所在路径下会生成一个指定文件名的文件xxx.txt。不要用.txt打开该文件,因为用.txt打开该文件会永久性的改变备份文件的字符集编码格式

(3)生成的备份文件xxx.txt中,只有数据,没有表结构,且数据之间用tab键隔开

(4)单表数据备份的前提是外部文件不存在。如果指定的备份路径下存在文件xxx\xxx\xxx.txt,则执行数据备份的sql语句会报错,备份失败

4、单表数据高级备份:自己定制字段和行的处理方式(指定备份处理方式)

    -- 单表数据高级备份:自己定制字段和行的处理方式(指定备份处理方式)
    select * / 字段列表 into outfile 备份文件所在路径 fields 字段处理 lines 行处理 from 数据源;

(1)fields:字段处理

a). enclosed by:字段使用什么内容包裹。默认是'',空字符串(相当于没有被包裹)

b). terminated by:字段以什么结束。默认是"\t",tab键

c). escaped by:特殊符号用什么方式处理。默认是'\\',使用反斜杠转义

(2)lines:行处理

a). starting by:每行以什么开始。默认是'',空字符串(没有东西)

b). terminated by:每行以什么结束。默认是"\r\n",换行符

    -- 单表数据高级备份:自己定制字段和行的处理方式(指定备份处理方式)
    select * into outfile 'C:/ProgramData/MySQL/MySQL Server 5.5/data/test/class.txt'
    -- 字段处理
    fields
    enclosed by '"'    -- 数据使用双引号包裹
    terminated by '|'    -- 使用竖线分隔字段数据
    -- 行处理
    lines
    starting by 'START:'    -- 行以"STRAT:"开始
    from my_class;

5、单表数据还原:将一个在外部保存的数据重新恢复到表中。前提是表结构存在(如果表结构不存在,不能还原单表数据)

    -- 单表数据还原:将一个在外部保存的数据重新恢复到表中
    -- 前提是表结构存在
    -- 怎么备份的,怎么还原
    load data infile 备份文件所在路径 into table 表名 [(字段列表)] fields 字段处理 lines 行处理;
    -- 单表数据还原:将一个在外部保存的数据重新恢复到表中
    -- 前提是表结构存在
    
    -- 删除表中数据(表结构还在)
    delete from my_class;
    -- 查看删除后的表数据
    select * from my_class;

    -- 单表数据还原
    load data infile 'C:/ProgramData/MySQL/MySQL Server 5.5/data/test/class.txt' into table my_class 
    -- 字段处理
    fields
    enclosed by '"'    -- 数据使用双引号包裹
    terminated by '|'    -- 使用竖线分隔字段数据
    -- 行处理
    lines
    starting by 'START:';    -- 行以"STRAT:"开始

    -- 查看还原后的表数据
    select * from my_class;

6、单表数据备份,备份成功后,在指定的备份文件所在路径下会生成一个指定文件名的文件xxx.txt。不要用.txt打开该文件,因为用.txt打开该文件会永久性的改变备份文件的字符集编码格式。

      如果用.txt打开备份的单表数据文件,在单表数据还原时,会因为乱码而还原失败

四、SQL备份

1、SQL备份:备份的是sql语句。系统会对表结构以及数据进行处理,变成对应的sql语句,然后进行备份。还原的时候,只要执行sql指令即可(主要就是针对表结构)

注:SQL备份是一种较小访问量的数据备份

2、对于SQL备份,MySQL没有提供备份指令,需要利用MySQL提供的软件mysqldump.exe

3、mysqldump.exe也是一种客户端,需要操作服务器,必须连接认证(一个客户端要操作服务器,必须要经过连接认证)

    -- 连接认证mysqldump.exe
    -- 可以进行单表、多表(多表用空格分隔)和整库(不写表名代表整库备份)备份
    -- SQL备份,文件路径以.sql结尾,代表是一个sql文件。结尾没有分号(;)
    mysqldump/mysqldump.exe -hPup 数据库名字 [数据表名字1 数据表名字2 ... ] > 外部文件目录(建议使用xxx\xxx\xxx.sql)

注:mysql的bin目录下有三个软件:

(1)mysql.exe:客户端

(2)mysqld.exe:服务器

(3)mysqldump.exe:专门用来备份的

4、每次做SQL备份或还原时,都需要先退出MySQL,备份或还原之后再进入MySQL。因为SQL备份和还原用的是mysqldump.exe,而sql的执行用的是mysql.exe

5、SQL备份:单表备份

    -- SQL备份:单表备份
    -- SQL备份,文件路径以.sql结尾,代表是一个sql文件。结尾没有分号(;)
    mysqldump -uroot -proot123 mydatabase my_student > 'C:/ProgramData/MySQL/MySQL Server 5.5/data/test/student.sql'

6、SQL备份:整库备份

    -- SQL备份:整库备份
    -- 整库备份,时间会稍微长一点,因为数据量较大
    -- xxx\xxx\xxx.sql:备份的数据库mydatabase下的所有表(sql语句)都在该文件中
    mysqldump -uroot -proot123 mydatabase > 'C:/ProgramData/MySQL/MySQL Server 5.5/data/test/mydatabase.sql'

7、SQL还原:SQL还原数据有两种方式

(1)方式一:使用mysql.exe客户端还原

    -- 使用mysql.exe客户端还原
    -- 必须要指定数据库,因为可以还原到别的数据库
    -- 不用指定表名,因为备份的sql语句会先把表删除再重新创建(还原数据时,单表和多表没有区别)
    mysql.exe/mysql -hPup 数据库名字 < 备份文件目录

注:每次做SQL备份或还原时,都需要先退出MySQL,备份或还原之后再进入MySQL。因为SQL备份和还原用的是mysqldump.exe,而sql的执行用的是mysql.exe

(2)方式二:使用SQL指令还原(在mysql中执行即可)

    -- 使用SQL指令还原(在mysql中执行即可)
    source 备份文件所在路径;

8、SQL备份的优缺点

(1)优点:可以备份结构

(2)缺点:

a). 浪费空间。因为会额外的增加sql指令(不但有数据,还有很多sql指令)

b). 备份时间很长。因为都是整表备份(还原的时候,会先把原来的表删除。如果不是整表备份,没有备份的那部分数据不能被还原)

c). SQL备份-单表备份的缺点:单表备份依赖表的存储引擎,且单表数据过于单一

注:所有备份都存在的问题:新增数据也会被删除

五、增量备份

1、增量备份不是针对数据或者sql指令进行备份,是针对MySQL服务器的日志文件进行备份

注:日志中记录了所有的操作,增删改查全都在里面(SQL备份中间的过程是不知道的,它只记录最后的结果)

2、增量备份:指定时间段开始进行备份,备份数据不会重复,而且所有的操作都会备份(大项目都用增量备份)

eg:昨天备份从前面一直到昨天晚上十二点,那么今天备份就从昨天晚上十二点到今天晚上十二点,明天备份就从今天晚上十二点到明天晚上十二点

3、增量备份很麻烦,但一般大的项目都用增量备份。因为大的项目用SQL备份周期长,备份成本高。而用增量备份时间短,且备份的数据很少,还原会很精确,还原的效率也比较高

4、中小型项目可以用SQL备份。因为对数据的精确程度要求不是特别高,且备份的时间可以一两天或几天备份一次

注:增量备份(大项目)一小时备份一次。eg:银行系统

 

  • 3
    点赞
  • 0
    评论
  • 14
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

ruyu00

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值