数据库基础
Source
Edit
History

undefined

一、备份概念

1. 根据备份的方法（是否需要数据库离线），将备份分为：

  • 热备：对正在运行的数据库没有任何影响，数据库读写可以正常执行
  • 冷备：必须在数据库停止的情况下进行。一般只需要复制相关的数据库物理文件即可
  • 温备：数据库运行时操作，数据库此时仅支持读操作，不支持写操作

2. 按照备份后的文件内容，热备份分为

  • 逻辑备份：备份出来的文件是可读的，一般是文本内容。一般是由一条条SQL语句，或者是表内实际数据组成。如 mysqldump、select * into outfile 方法。好处是可以观察导出文件内容，一般适用于数据库的升级、迁移等工作。但缺点是恢复时间较长。
  • 裸文件备份：指复制数据库的物理文件，既可以在数据库运行中进行复制（如 ibbackup、xtrabackup工具），也可以在数据库停止运行时直接复制数据文件。这类备份的恢复时间往往比逻辑备份短很多

3. 按照备份数据库的内容，备份分为

  • 完全备份：指对数据库进行一个完整的备份，即备份整个数据库，如果数据较多会占用较大的时间和空间
  • 部分备份：备份部分数据库（例如：备份一个表）
    • 增量备份：需要使用专业的备份工具，指在上次完全备份的基础上，对更改的数据进行备份。也就是说每次备份只会备份自上次备份之后到备份时间之内产生的数据。因为每次备份都比差异备份节约空间，但恢复数据麻烦
    • 差异备份：指自上一次完全备份以来变化的数据。和增量备份相比，浪费空间，但恢复数据比增量备份简单

注：MyISAM 不支持热备，支持温备和冷备。InnoDB支持热备、温备和冷备

一般情况，需要备份的数据有：

  • 表数据
  • 二进制日志、InnoDB事务日志
  • 代码（存储过程、存储函数、触发器、时间调度器）
  • 服务器配置文件

常用的备份工具：

  • mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
  • cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份
  • Lvm2 snapshot：借助文件系统管理工具进行备份
  • xtrabackup：一款由percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份

二、冷备份

需要先停止 Mysql服务器。保证了数据的完整性，备份过程简单且恢复速度相对对一些，但是关闭数据库会影响现有业务的进行。

冷备的备份和恢复过程：

  1. 为保证所备份数据的完整性，在停止 Mysql 数据库服务器之前，需要先执行 FLUSH TABLES 语句将所有数据写入数据文件的文本里
  2. 停掉 Mysql 服务。mysqladmin -uroot -proot shutdown
  3. 备份过程就是复制整个数据目录到远程备份机或者本地磁盘，datadir 所设置的目录。 scp -r /data/mysql root@ip:/新目录
  4. 恢复：仅仅需要把已备份的数据目录替换原有的目录就可以了，最后重启mysql 服务

注意：通过复制数据文件这种方式实现数据恢复时，必须保证两个mysql 数据库的主版本号(第一个数字表示主版本号)一致，保证他们的数据文件类型是相同的

三、热备份

逻辑备份

  1. mysqldump：将需备份的数据查询出来，然后将查询的数据转换成对应的 insert 语句。恢复时执行这些insert 语句即可。

    恢复数据： mysql -uroot -p < backup.sql 或登录数据使用 source /path/backup.sql

  2. select into … outfile：把表数据导出到一个文本文件中，且能将数据库中表数据以特定分隔符进行分割后记录在文本文件中。但是这种方式只能导出或导入数据的内容，，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表结构；而且这种方法不能在同一份文件中存在多个表的备份数据，增加了文件维护和恢复的成本。

    恢复：1.通过mysql 的 load data infile 命令来恢复数据。2.通过mysql提供的使用工具 mysqlimport 来进行恢复

  3. mydumper：轻量级第三方的开源工具，支持多线程，备份速度高。

    恢复：myloader

裸文件备份

主要在底层复制数据文件，所以备份速度比逻辑备份要快

Percona 公司发布的一个 XtraBackup 热备份工具来完成裸文件备份。优点是备份与恢复过程的速度很快，安全可靠。而且在备份过程中不会缩表，不影响现有业务。但目前还是不能对表结构文件和其他非事务类型的表进行备份。

四、导出/恢复表数据

导出数据到文本文件，目标文件不能是一个已经存在的文件

1
select 列名 from table [where 语句] into outfile '目标文件' [options]

[options] 为可选参数选项，options 部分的语法包括 fields 和 lines 子句，常用的值有：

  • FIELDS TERMINATED BY ‘字符串’：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况为制表符
  • FIELDS [OPTIONALLY] ENCLOSED BY ‘字符’：设置字符来括上 char、varchar 和 text 等字符型字段。如果使用了 OPTIONALLY 则只能括上 char 和 varchar 等字符型字段
  • FIELDS ESCAPED BY ‘字符’：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
  • LINES STARTING BY ‘字符串’：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
  • LINES TERMINATED BY ‘字符串’：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。

注意：FIELDS 和 LINES 两个子句都是自选的，但如果两个都被指定了，FIELDS 必须位于 LINES 的前面

1
2
select * from test.person into outfile '/data/person.txt';
select * from test.persion into outfile '/data/person.txt' fields terminated by '\、' optionally enclosed by '\"' lines starting by '\_' terminated by '\r\n';
  • FIELDS TERMINATED BY ‘、’：表示字段之间用、分隔；
  • ENCLOSED BY ‘"‘：表示每个字段都用双引号括起来；
  • LINES STARTING BY ‘-‘：表示每行以-开头；
  • TERMINATED BY ‘\r\n’ 表示每行以回车换行符结尾，保证每一条记录占一行。

导出 person.txt 文件：每条记录都以 - 开头，每个数据之间以 、 隔开，所有字符串字段被双引号包括

1
2
3
4
5
-1、"Java"、12
-2、"MySQL"、13
-3、"C"、15
-4、"C++"、22
-5、"Python"、18

恢复备份文件的数据到数据库

1
2
3
4
# 1. 先建表
create table tb_students_copy like tb_students_info;
# 2. 导入数据
load data infile '/data/person.txt' into table test_db.tb_students_copy fields terminated by '\、' optionally enclosed by '\"' lines starting by '\_' terminated by '\r\n';