MySQL 第21天 备份与恢复

MySQL 的备份也主要分为逻辑备份和物理备份

备份/恢复策略

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

确保 MySQL 打开 log-bin 选项,有了 BINLOG,MySQL 才可以在必要的时候做完
整恢复,或基于时间点的恢复,或基于位置的恢复。
要经常做备份恢复测试,确保备份是有效的,并且是可以恢复的。

逻辑备份和恢复

在 MySQL 里面,逻辑备份的最大优点是对于各种存储引擎,都可以用同样的方法来备份;
而物理备份则不同,不同的存储引擎有着不同的备份方法。因此,对于不同存储引擎混合的
数据库,用逻辑备份会更简单一些

备份

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

调用mysqldump

 备份指定的数据库,或者此数据库中某些表。
shell> mysqldump [options] db_name [tables]
 备份指定的一个或多个数据库。
shell> mysqldump [options] ---database DB1 [DB2 DB3...]
 备份所有数据库。
shell> mysqldump [options] --all--database

其中 mysqldump 的选项很多,具体可以使用“--help”参数查看帮助:
mysqldump --help

需要强调的是,为了保证数据备份的一致性,MyISAM 存储引擎在备份的时候需要加上-l 参
数,表示将所有表加上读锁,在备份期间,所有表将只能读而不能进行数据更新。但是对于
事务存储引擎(InnoDB 和 BDB)来说,可以采用更好的选项--single-transaction,此选项将使
得 InnoDB 存储引擎得到一个快照(Snapshot),使得备份的数据能够保证一致性。

完全恢复

mysqldump 的恢复也很简单,将备份作为输入执行即可,具体语法如下:
mysql –uroot –p dbname < bakfile

注意,将备份恢复后数据并不完整,还需要将备份后执行的日志进行重做,语法如下:
mysqlbinlog binlog-file | mysql -u root –p***

P333页 有例子

基于时间点恢复

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

(1)如果上午 10 点发生了误操作,可以用以下语句用备份和 BINLOG 将数据恢复到故
障前:
shell>mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u
root –pmypwd
(2)跳过故障时的时间点,继续执行后面的 BINLOG,完成恢复。
shell>mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456| mysql -u
root -pmypwd \

基于位置恢复

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

1)在 shell 下执行如下命令:
shell>mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp 目录创建小的文本文件,编辑此文件,找到出错语句前后的位置号,

例如前后位置号分别是 368312 和 368315。
(2)恢复了以前的备份文件后,应从命令行输入下面内容:
shell>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd
shell>mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进
制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 SQL 语句记录之前的 SET
TIMESTAMP 语句,因此恢复的数据和相关 MySQL 日志将反应事务执行的原时间。

物理备份和恢复

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

冷备份

冷备份其实就是停掉数据库服务,cp 数据文件的方法。这种方法对 MyISAM 和 InnoDB 存储
引擎都适合,但是一般很少使用,因为很多应用是不允许长时间停机的。

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

热备份

1.MyISAM 存储引擎

本质其实就是将要备份的表加读锁,然后再 cp 数据文件到备份目录

方法 1:使用 mysqlhotcopy 工具
mysqlhotcopy 是 MySQL 自带的一个热备份工具,使用方法很简单:
shell> mysqlhotcopy db_name [/path/to/new_directory]
mysqlhotcopy 有很多选项,具体可以使用“--help”查看帮助:
mysqlhotcopy --help

方法 2:手工锁表 copy

在 mysqlhotcopy 使用不正常的情况下,可以手工来做热备份,操作步骤如下

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

mysql>flush tables for read ;

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

2.InnoDB 存储引擎

ibbackup 是 Innobase 公司(www.innodb.com)的一个热备份工具,专门对 InnoDB 存
储引擎进行物理热备份,此工具是收费的,但可以免费使用 1 个月

使 用 方 法 读 者 可 以 用 “ ibbackup --help ” 命 令 进 行 查 看 , 或 者 参 阅 官 方 帮 助 文 档
(http://www.innodb.com/support/documentation/innodb-hot-backup-manual/)。对于 InnoDB
和 MyISAM 混合的数据库,Innobase 公司还提供了一个开源的 Perl 脚本 innobackup,它可
以将两种存储引擎的表一起进行备份,具体使用方法读者可以参阅上述链接中的
文档

表的导入导出

在数据库的日常维护中,表的导入导出是很频繁的一类操作

导出

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

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

方法 1:使用 SELECT ...INTO OUTFILE ...命令来导出数据,具体语法如下。

mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];

MySQL 导出的数据中需要转义的字符主要包括以下 3 类:

 转义字符本身;
 字段分隔符;
 记录分隔符。

注意:在 MySQL 客户端连接成功后,如果要执行操作系统的命令,可以用“system+操作系统命令”来进行执行。

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

注意:SELECT...INTO OUTFILE...产生的输出文件如果在目标目录下有重名文件,将不会创建
成功,源文件不能被自动覆盖。

方法 2:用 mysqldump 导出数据为文本

mysqldump –u username –T target_dir dbname tablename [option]

其中 option 参数可以是以下选项:
 --fields-terminated-by=name(字段分隔符);
 --fields-enclosed-by=name(字段引用符);
 --fields-optionally-enclosed-by=name(字段引用符,只用在 char、varchar 和 text 等字符
型字段上);
 --fields-escaped-by=name(转义字符);

 --lines-terminated-by=name(记录结束符)。

导入

只讨论用 SELECT... INTO OUTFILE 或者 mysqldump 导出的纯数据文本的导入方
法。和导出类似,导入也有两种不同的方法,分别是 LOAD DATA INFILE...和 mysqlimport,
它们的本质是一样的,

区别只是在于一个在 MySQL 内部执行,另一个在 MySQL 外部执行。

方法 1:使用“LOAD DATA INFILE...”命令。

mysql > LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option]

option 可以是以下选项:
 FIELDS TERMINATED BY 'string'(字段分隔符,默认为制表符'\t');
 FIELDS [OPTIONALLY] ENCLOSED BY 'char'(字段引用符,如果加 OPTIONALLY 选项则只用
在 char、varchar 和 text 等字符型字段上。默认不使用引用符);
 FIELDS ESCAPED BY 'char'(转义字符,默认为'\')
;
 LINES STARTING BY 'string'(每行前都加此字符串,默认'');
 LINES TERMINATED BY 'string'(行结束符,默认为'\n');
 IGNORE number LINES(忽略输入文件中的前 n 行数据);
 (col_name_or_user_var,...) (按照列出的字段顺序和字段数量加载数据)
;
 SET col_name = expr,... 将列做一定的数值转换后再加载。
其中 char 表示此符号只能是单个字符,string 表示可以是字符串。

在使用时可以加上ignore   set  函数

方法 2:用 mysqlimport 来实现,具体命令如下。

shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option]

其中 option 参数可以是以下选项:
 --fields-terminated-by=name(字段分隔符);
 --fields-enclosed-by=name(字段引用符);
 --fields-optionally-enclosed-by=name(字段引用符,只用在 char、varchar 和 text 等字符
型字段上);
 --fields-escaped-by=name(转义字符);
 --lines-terminated-by=name(记录结束符);
 -- ignore-lines=number(或略前几行)。

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

小结

本章主要介绍了 MySQL 的备份和恢复方法。和其他数据库类似,MySQL 也分为逻辑备份和
物理备份。两种备份方法各有优缺点,逻辑备份保存的是 SQL 文本,可以在各种条件下恢
复,但是对于大数据量的系统,备份和恢复的时间都比较长;物理备份恰恰相反,由于是文
件的物理 cp,备份和恢复时间都比较短,但是备份的文件在不同的平台上不一定兼容。
其中,mysqldump 是最常用的逻辑备份工具,适合各种存储引擎,希望大家重点掌握。物理
备份对于不同的存储引擎,备份方式有所不同,大家主要掌握 mysqlhotcopy 和手工热备份
的方法;对于 InnoDB 的热备份工具,读者可以了解一下 ibbackup 的使用方法,由于此工具
是收费的,因此普及率不太高,而 MySQL 官方目前没有提供免费的 InnoDB 热备份工具。
对于数据表的导入导出方法,应重点掌握 SELECT ...INTO OUTFILE 和 LOAD DATA INFILE 的使用,
mysqldump 和 mysqlimport 实际上是调用了前两种方法接口,只不过是在 mysql 外部执行罢
了。数据的导入导出在数据库的管理维护中使用非常频繁,而 LOAD DATA INFILE 是加载数据
最快的方法,因此读者应重点掌握。