32. MySQL备份与恢复

和很多数据库类似，MySQL 的备份也主要分为逻辑备份和物理备份。

1. 备份/恢复策略⚓

对于一个 DBA 来说，定制合理的备份策略无疑是很重要的，以下是我们在进行备份或恢复操作时需要考虑的一些因素。

确定要备份的表的存储引擎是事务型还是非事务性，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。
确定使用全备份还是增量备份。全备份的优点是备份保持最新备份，恢复的时候可以花费更少的时间；缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。增量备份则恰恰相反，只需要备份每天的增量日志，备份时间少，对负载压力也小；缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间会长些。
可以考虑采取复制的方法来做异地备份，但是记住，复制不能代替备份，它对数据库的误操作也无能为力。
要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行。
确保 MySQL 打开 log-bin 选项，有了 BINLOG，MySQL 才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。
要经常做备份恢复测试，确保备份是有效的，并且是可以恢复的。

2. 逻辑备份和恢复⚓

在 MySQL 里面，逻辑备份的最大优点是对于各种存储引擎，都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法。

2.1 备份⚓

MySQL 中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在 MySQL 中，使用 mysqldump 工具来完成逻辑备份。

-- 没有创建数据库语句
mysqldump [OPTIONS] database [tables]
-- 包含创建数据库语句
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
-- 会将数据库的权限表等也复制出来，不适用不同版本数据库直接的复制
mysqldump [OPTIONS] --all-databases [OPTIONS]

例：

mysqldump -uroot -p --databases blog > blog-dump.sql

2.2 完全恢复⚓

将备份作为输入执行即可。

mysql –uroot –p blog < blog-dump.sql

注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做，语法如下：

mysqlbinlog binlog_file | mysql -u root –p

2.3 不完全恢复⚓

由于误操作，比如误删除了一张表，这时使用完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作语句，再恢复后面执行的语句，完成我们的恢复。这种恢复叫不完全恢复，在 MySQL 中，不完全恢复分为基于时间点的恢复和基于位置的恢复。

2.3.1 基于时间点恢复⚓

使用mysqlbinlog选项的--start-datetime=name和--stop-datetime=name恢复数据。

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/binlog.123456 | mysql -u
root –p
-- 跳过故障时的时间点，继续执行后面的 BINLOG，完成恢复。
mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/binlog.123456 | mysql -u
root -p

2.3.2 基于位置恢复⚓

和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条 SQL 语句同时执行。

-- 该命令将在/tmp 目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号
mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"
/var/log/mysql/binlog.123456 > /tmp/mysql_restore.sql
-- 跳过 368312~368315 之间的故障语句
mysqlbinlog --stop-position="368312" /var/log/mysql/binlog.123456 | mysql -u root -p
mysqlbinlog --start-position="368315" /var/log/mysql/binlog.123456 | mysql -u root -p

3. 物理备份和恢复⚓

物理备份又分为冷备份和热备份两种，和逻辑备份相比，它的最大优点是备份和恢复的速度更快，因为物理备份的原理都是基于文件的 cp。

3.1 冷备份⚓

停掉数据库服务，cp 数据文件的方法。种方法对 MyISAM 和 InnoDB 存储引擎都适合。

备份的操作：停掉 MySQL 服务，在操作系统级别备份 MySQL 的数据文件和日志文件到备份目录。
恢复的操作：首先停掉 MySQL 服务，在操作系统级别恢复 MySQL 的数据文件；然后重启 MySQL 服务，使用 mysqlbinlog 工具恢复自备份以来的所有 BINLOG。

3.2 热备份⚓

3.2.1 MyISAM⚓

MyISAM 存储引擎的热备份有很多方法，本质其实就是将要备份的表加读锁，然后再 cp 数据文件到备份目录。

3.2.1.1 mysqlhotcopy 工具⚓

3.2.1.2 手工锁表 copy⚓

首先数据库中所有表加读锁：

flush tables for read;

然后 cp 数据文件到备份目录即可。

3.2.2 InnoDB⚓

3.2.2.1 ibbackup 热备份工具⚓

收费

3.2.2.2 XtraBackup 热备份工具⚓

4. 表的导入导出⚓

4.1 导出⚓

在某些情况下，为了一些特定的目的，经常需要将表里的数据导出为某些符号分割的纯数据文本，而不是 SQL 语句。这些应用可能有以下一些：

用来作为 EXCEL 显示；
单纯为了节省备份空间；
为了快速的加载数据，LOAD DATA 的加载速度比普通的 SQL 加载要快 20 倍以上。

为了满足这些应用，可以使用以下两种办法来实现

使用SELECT INTO FILE语句

SELECT ...... [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]

export_options和LOAD DATA语句的FIELDS and LINES语法一样：

[{FIELDS | COLUMNS}
 -- 字段分隔符，默认为制表符’\t’
    [TERMINATED BY 'string']
 -- 字段引用符，如果加 OPTIONALLY 选项则只用在 char、varchar、enum 和 text 等字符型字段上。默认不使用引用符
    [[OPTIONALLY] ENCLOSED BY 'char']
 -- 转义字符，默认为’\’
    [ESCAPED BY 'char']
]
[LINES
 -- 每行前都加此字符串，默认为空字符串''
    [STARTING BY 'string']
 -- 行结束符，默认为’\n’
    [TERMINATED BY 'string']
]

但如果指定了两者，则FIELDS必须在LINES之前。

其中 char 表示此符号只能是单个字符，string 表示可以是字符串。

mysql> SELECT * FROM t1 INTO OUTFILE '/tmp/t1_data' FIELDS OPTIONALLY ENCLOSED BY '"' ;

-- 如果出现下面的错误，可在my.cnf中添加“secure-file-priv=”，然后重启服务即可
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

当导出命令中包含字段引用符时，数据中含有转义字符本身和字段引用符的字符需要被转义；
当导出命令中不包含字段引用符时，数据中含有转义字符本身和字段分隔符的字符需要被转义。
使用mysqldump命令

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

OPTIONS参数有好多，下面是和分隔符有关的：

--fields-terminated-by=name：字段分隔符
--fields-enclosed-by=name：字段引用符
--fields-optionally-enclosed-by=name：字段引用符，只用在 char、varchar、enum 和 text 等字符型字段上
--fields-escaped-by=name：转义字符
--lines-terminated-by=name：记录结束符

$ mysqldump -uroot -p -T /tmp blog t1 --fields-terminated-by ","

$ ll /tmp
-rw-r--r--. 1 root  root  1384 Jul 23 03:39 t1.sql
-rw-rw-rw-. 1 mysql mysql  279 Jul 23 03:39 t1.txt

其实，mysqldump 实际调用的就是SELECT INTO提供的接口，并在其上面添加了一些新的功能而已。

4.2 导入⚓

本节只讨论用 SELECT… INTO OUTFILE 或者 mysqldump 导出的纯数据文本的导入方法。

导入也有两种不同的方法，分别是 LOAD DATA INFILE…和 mysqlimport，它们的本质是一样的，区别只是在于一个在 MySQL 内部执行，另一个在 MySQL 外部执行。

使用LOAD DAT命令

LOAD DATA
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    -- 忽略前N行数据
    [IGNORE number {LINES | ROWS}]
    -- 加载的列及列的顺序
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    -- 将列作一定的数值转换后再加载
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

在载入之前，表一定要存在。

LOAD DATA INFILE '/tmp/t1_data' INTO TABLE t1 (id) SET id=id+2;

使用mysqlimport命令

mysqlimport [OPTIONS] database textfile...

OPTIONS参数有好多，下面是和分隔符有关的：

--fields-terminated-by=name：字段分隔符
--fields-enclosed-by=name：字段引用符
--fields-optionally-enclosed-by=name：字段引用符，只用在 char、varchar、enum 和 text 等字符型字段上
--fields-escaped-by=name：转义字符
--lines-terminated-by=name：记录结束符
--ignore-lines=#：忽略前N行数据

mysqlimport -uroot -p --fields-terminated-by=',' blog /tmp/t1.txt

Note

如果导入和导出是跨平台操作的（Windows 和 Linux），那么要注意设置参数 line-terminated-by ， Windows 上设置为 line-terminated-by=’\r\n’ ， Linux 上设置为 line-terminated-by=’\n’。

数据的导入导出在数据库的管理维护中使用非常频繁，而 LOAD DATA INFILE 是加载数据最快的方法，因此应重点掌握。