0%

数据备份和恢复

数据备份和恢复

什么情况下会用到数据备份呢

数据丢失的场景

  • 人为误操作造成的某些数据被误操作
  • 软件BUG造成数据部分或者全部丢失
  • 硬件故障造成数据库部分或全部丢失
  • 安全漏洞被入侵数据恶意破坏

非数据丢失场景

  • 基于某个时间点的数据恢复
  • 开发测试环境数据库搭建
  • 相同数据库的新环境搭建
  • 数据库或者数据迁移

既然数据备份如此重要,那么如何进行数据备份呢?

主要有逻辑备份和物理备份两个方向

逻辑备份与对应的恢复

逻辑备份是比较简单的,也是中小型系统中最常用的备份方式,主要有两种方式,一种是将数据生成可以完全重现当前数据库中数据的insert语句;另一种就是将数据通过逻辑备份软件,将数据库表数据以特定分隔符进行分隔记录在文本文件中

生成insert语句备份

使用mysqldump可以来实现生成insert语句的逻辑备份文件

1
2
3
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

不过为了保证数据库中的数据一致,就需要进行一下特殊处理,两种方式都可

第一种方式:保证在同一事务里备份

使用--single-transaction选项,可以保证备份过程在同一个事务中,而且可以不影响数据库的任何正常服务

第二种方式:数据库不在有其他新的操作

这种方式就是将所需要备份的表锁住,只允许读不允许写,可以使用--lock-tables--lock-all-tables来进行锁表操作,执行结束后会自动释放锁定

对于--lock-tables选项并不会一次性将所有需要备份的表都锁定,而是每次仅仅锁定一个数据库的表,所以如果需要备份多个数据库的表,要使用--lock-all-tables来进行锁表,才能确保数据的完全一致性

恢复方式

可以使用source命令来进行数据恢复

1
source /tmp/backup.sql

生成特定格式的纯文本备份

这种备份方式比insert命令文件相比,占用的存储空间更小,但是在同一个备份文件中不能存在多个表的备份数据

mysql中提供两种方式来实现这种备份文件

第一种方式

使用select ... to outfile from ...来实现

示例

1
2
3
select * from user into outfile '/tmp/dump.txt' 
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n';

第二种方式

依然使用mysqldump来实现

1
mysqldump -uroot -T/temp/dump test user --fields-enclosed-by=\" --fields-terminated-by=,
恢复方式

由于该方式是按照每个表来进行备份的,所以恢复起来也需要每张表来进行恢复

有两种方式来进行恢复

第一种方式

使用load data命令

示例

1
load data infile '/tmp/user.txt' into table user fields terminated by ',' enclosed by '"'

第二种方式

使用mysql提供的mysqlimport来进行恢复

示例

1
mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" fields-terminated-by=, /tmp/user.txt

物理备份与对应的恢复

物理备份就是对数据库的物理对象所做的备份,数据库的物理对象主要由数据库的物理数据文件、日志文件以及配置文件等组成,由于不同的存储引擎存储的文件也不同,所以对于不同的存储引擎备份的文件也有所不同

MyISAM存储引擎

存储表结构元数据的.frm文件,存储表数据的.MYD文件,存储索引数据的.MYI文件

mysql提供了mysqlhotcopy来备份MyISAM存储引擎的数据

1
mysqlhotcopy db_name [./table_regex/] [new_db_name | directory]

该命令会先lock住表,然后执行flush tables动作,该正常关闭的表正常关闭,该fsync的数据都fsync,然后通过执行OS级别的复制命令,将需要备份的表或数据库的所有物理文件都复制到指定的备份集位置

Innodb存储引擎

Innodb中存放数据位置的配置有innodb_data-home-dirinnodb_data_file_pathinnodb_log_group_home_dirinnodb_file_per_table