MySQL数据库的备份¶
备份类型¶
物理备份与逻辑备份¶
物理备份直接备份MySQL Server的数据文件，适用于数据量大，比较重要并且需要快速恢复的数据。其特点有:[1]
- 比逻辑备份快（为什么？）
- 进行备份操作时需要MySQL Server停止服务或者禁止改变数据库内容的操作
- 备份粒度
- 可以方便备份与数据库相关的配置，日志信息
逻辑备份通过向MySQL Server发生请求以获取数据库的结构和内容信息并进行保存。其特点有：
- 比物理备份慢（为什么？）
- 备份文件相对较大（为什么？）
- 备份、恢复的粒度更小
- 备份后的数据与机器无关，便于恢复
- 需要MySQL Server处于运行状态
在线备份和离线备份¶
本地备份和远程备份¶
执行SQL语句SELECT ... INTO OUTFILE可以将数据输出到MySQL Server上的某个文件中（无论在本地还是远程执行此语句）。
快照备份¶
利用文件系统（LVM, ZFS）的快照功能（snapshot）可以对数据库所在的文件分区进行快照备份。（需要对文件系统进行合理的设计）
全备份和增量备份，以及PIT(Point-in-Time)恢复¶
表维护¶
备份方法¶
直接备份数据库文件¶
对于使用MyISAM引擎的数据表，可能通过直接复制其数据文件（*.frm, *.MYD, *.MYI）来进行备份。为了保证备份时数据不会发生改变，应该停止服务器运行或对相关的数据表进行锁表操作：[2]
FLUSH TABLES tbl_list WITH READ LOCK;
另外也可以使用工具mysqlhotcopy完成相同的任务。由于存储引擎InnoDB的特性（并不一定并数据存放在相应的数据库目录下，另外即使服务器没有更新硬盘数据，也可能在修改缓存于内存中的数据而没有将其回写到硬盘）,mysqlhotcopy并不适用于使用InnoDB引擎的表进行备份。
将数据备份到分隔符文件¶
SELECT * INTO OUTFILE 'file_name' FROM tbl_name可以将数据导出到MySQL Server上，注意保存文件file_name不能存在（安全方面的考量），另外此方法只能保存数据，不会导出表结构。
使用命令mysqldump和--tab选项可以完成相同的操作，同时可以导出表结构（包含CREATE TABLE语句）。
使用LOAD DATA INFILE或命令mysqlimport可以将导出的数据恢复至数据库。
增量备份¶
开启binlog功能后可以对数据库进行增量备份。进行增量备份时，必须首先刷新binlog：FLUSH LOGS。另外也可以使用命令：mysqldump --flush-logs ``\ 或\ ``mysqlimport --flush-logs。
使用从库¶
恢复受损表¶
对于使用MyISAM引擎的表，如果发生损坏，可以使用REPAIR TABLE或者myisamchk -r来进行恢复，有99.9%的几率修复数据。
使用文件系统快照¶
一般可以执行下面步骤：
- 从客户端登陆并执行：FLUSH TABLES WITH READ LOCK
- 从另外一个Shell执行文件系统快照操作
- 从客户端执行解锁：UNLOCK TABLES
- 将快照文件拷贝备份
备份策略¶
一般备份策略是，建立一个完整备份，然后定期进行增量备份。[3]
对于使用InnoDB引擎的数据表，使用mysqldump进行备份时加上选项--single-transaction可以保证备份时数不会发生变化。对于全部使用INFILE 的数据库，可以使用下面命令进行全备份：
mysqldump --single-transaction --all-databases > backup_20140308.sql
对非事务型引擎，如MyISAM备份时必须加上读锁以保证备份时数据不会发生变更：
FLUSH TABLES WITH READ LOCK;
注：执行``FLUSH``语句时，如果系统正在执行一个耗时的操作，会短暂阻塞直到完成相关操作
执行增量备份需要MySQL Server开启二进制日志(binlog)功能。开启binlog后，服务器每次重启都会新建一个binlog，并将所有的数据变更写入到binlog中。也可能手动执行FLUSH LOGS语句刷新binlog，或者执行命令mysqladmin flush-logs刷新binlog。数据目录下的*.index中包含了当前目录下的所有binlog列表。
mysqldump也有刷新binlog的选项，所有可以使用下面方法来建立一个完整备份：
mysqldump --single-transaction --flush-logs --master-data=2 --all-databases
> backup_20140308
全备份+增量备份的一个实践是：
- 利用上面的mysqldump命令（带--flush-logs选项）建立一个完整备份;
- 定期运行命令mysqladmin flush-logs以刷新binlog，然后备份相应的binlog即可
对于数据量较大的网站，binlog将占相当大的空间，所以需要定期进行清理。运行SQL语句PURGE BINARY LOG或者运行命令mysqldump时加上选项--delete-master-logs也可以删除二进制日志。如果建立了主从同步，在删除主库上的binlog时需要小心，因为binlog的内容可能还没有同步至从库。PURGE BINARY LOG删除binlog前会执行相关检查。
从备份恢复数据¶
从上面通过全备份+增量备份的备份数据恢复：
先从全备份恢复
mysql < backup_20140308
然后依时间序从增量备份的binlog恢复数据
mysqlbinlog mysqld-binlog.00001 mysqld-binlog.00002 | mysql对于从最后一个增量备份时间点到MySQL Server崩溃点间的数据，依不同备份策略可能会丢失
mysqldump¶
Dumping Data in SQL Format¶
默认情况下导出数据存储为SQL语句
--all-databases | -A 导出所有数据库
mysqldump --all-databases > dump.sql
--databases | -B db1 db2 导出指定的数据库
mysqldump --databases db1 db2 db3 > dump.sql
--add-drop-database 添加DROP DATABASE语句
mysqldump --add-drop-database --all-databases > dump.sql
mysqldump使用选项--all-databases, --databases时，在导出数据时会主动添加CREATE DATABASE和USE语句，即主动创建相应的数据库。如果需要清除数据库中的原数据，可以增加选项--add-drop-database。
注意导出单个数据库时下面的差异：
- mysqldump --databases db1 > dump_db1.sql
- mysqldump db1 > dump_db1.sql
方法一会在导出数据中添加CREATE DATABASE语句；而方法二则不会。方法二导出的数据可以方便的导入到与原数据库不同名的数据库。
恢复由mysqldump导出的数据可以直接运行mysql < dump.sql或者mysql> source dump.sql来导入数据。导入数据时需要注意的是是否包含CREATE DATABASE语句，需要根据需要进行一些额外的操作。
Dumping Data in Delimited-Text Format¶
命令mysqldump使用选项--tab=dir_name可以将指定的数据库导出为分隔符格式的数据文件，存放于目录dir_name中的两个文件：tbl_name.txt, tbl_name.sql。txt文件中存放的是表中的数据；sql文件中表结构信息（CREATE TABLE语句等）。如：
shell> mysqldump --tab=/tmp db1
上面的命令将数据库db1中的所有表导出到/tmp目录下，每一张表对应两个文件（tbl_name.txt, tbl_name.sql）。txt文件实际上是由MySQL Server执行SELECT ... INTO OUTFILE所生成，用户必须有FILE权限，另外，如果存在相应的txt文件，会提示出错。MySQL Server将CREATE等定义发送给mysqldump并写入到sql文件，所以sql文件的所有者（及其它权限）属于执行mysqldump命令的用户。
最好在本地执行mysqldump --tab=dir_name命令，目录dir_name必须同时存在于本地和远程服务器（MySQL Server）。txt文件将被Server写在服务器上，而sql文件将被保存在本地（执行mysqldump命令的主机）。
如果需要定制分隔符文件的格式还有一些其它选项用于配置。[4]
由于mysqldump --tab备份的数据是由两个文件组成，所以恢复时与SQL格式备份略有不同：[5]
导入tbl_name.sql文件，建立相应的数据表mysql db1 < tbl_name.sql
导入tbl_name.txt中的数据，可以有以下不同的方法：
直接使用命令：mysqlimport [options] db1 tbl_name.txt。如果在导出数据时，使用了自定义格式，在import时也需要加上相应选项。
在mysql shell下：
mysql> USE db1; mysql> LOAD DATA INFILE 'tbl_name.txt' INOT TABLE t1;
Dumping Stored Programs¶
对于数据库中的stored procedures, functions, triggers, events的备份，有一些额外的选项：
- --events：导出events
- --routines：导出stored procedures, functions
- --triggers：导入triggers（默认）
如果想显式的禁止导出，相应的选项有：
- --skip-events
- --skip-routines
- --skip-triggers
Dumping Table Definitions and Content Separately¶
有时候可能只想导出表结构或者表数据，mysqldump同样提供了相应的功能选项：
- --no-data：仅导出表结构，即CREATE语句
- --no-create-info：仅导出表数据，即INSERT INTO语句
例如：
shell> mysqldump --no-data test > dump-defs.sql
shell> mysqldump --no-create-info test > dump-data.sql
在准备数据库版本时，最好分别寻出表结构和数据，然后分别在新版本的服务器上进行恢复。由于表结果部分不包括数据，可以很快的导入，发生错误时也便于检查修复。确认结构恢复正常后再导入数据，并确认正常。
Point-in-Time Recovery - mysqlbinlog¶
通过全备份/增量备份，我们可以通过工具mysqlbinlog从二进制日志中逐步恢复数据。执行SQL语句SHOW BINARY LOGS可以查看二进制日志列表；SHOW MASTER STATUS可以查看当前正在使用日志文件。
可以通过命令mysqlbinlog binlog_file | mysq -u root -p来恢复数据，也可以通过命令mysqlbinlog binlog_file | less来查看binlog中的内容。
另外需要注意的是，从binlog恢复数据时，如果是恢复多个文件，应该在单个连接中完成多个文件的恢复，即：
mysqlbinlog binlog.00001 binlog.00002 binlog.00003 | mysql -u root -p
# 或者
mysqlbinlog binlog.00001 > log.sql
mysqlbinlog binlog.00002 >> log.sql
mysqlbinlog binlog.00003 >> log.sql
mysql -u root -p -e 'source log.sql'
依据时间恢复¶
mysqlbinlog有两个选项--start-datetime和--stop-datetime可以设定从某个时间点开始恢复，或者恢复至某个时间点。如：
# 恢复至2005年4月20号上午10点
shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" /var/lib/mysql/binlog.123456 | mysql -u root -p
# 从2005年4月20号上午10点开始恢复
shell> mysqlbinlog --start-datetime="2005-04-20 9:59:59" /var/lib/mysql/binlog.123456 | mysql -u root -p
依据事件点来恢复¶
为了能够准确的恢复到某个日志位置，需要确定日志中期望事件的log_pos。
# 释放出binlog中的内容
shell> mysqlbinlog /var/lib/mysql/binlog.00001 > log.sql
查看log.sql中查看\ `log_pos`\ 然后找到合适位置
shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 | mysql -u root -p
需要注意的是恢复后的数据，相关的时间均为日志中的时间。
MyISAM表的维护与恢复¶
由MyISAM引擎的特点决定对表数据进行更新整理可以减少使用空间，提高访问性能，而且可以减少表数据出现故障的几率。所以使用MyISAM引擎的表需要制定周期性计划对表进行优化整理。
使用MyISAM引擎的表在对应的数据库目录下存在三个文件：
| File | Purpose |
| tbl_name.frm | Definition (format) file |
| tbl_name.MYD | Data file |
| tbl_name.MYI | Index file |
myisamchk命令¶
- 外部锁：如果MySQL Server禁用了外部锁（默认设定），使用myisamchk命令是不安全的。如果mysqld与myisamchk在同一张表上工作，会引起此表的数据损坏。此时最安全的做法是停止MySQL Server服务再执行myisamchk命令；如果可以确定mysqld没有在某张表上工作，可以先运行命令mysqladmin flush-tables，再运行myisamchk命令。
- 如果外部锁（external lock）被mysqld开启，可以在任何时候运行myisamchk以检查数据表。（为什么？锁）
- 通常情况下myisamchk命令通过逐行复制数据文件.MYD来进行检查和修复工作，当修复结束时，会删除原来的MYD文件，将新的文件命名为原文件。
检查错误¶
下列选项可用于检查MyISAM表的错误
- myisamchk tbl_name 可以发现99.99%的问题，如果仅仅是数据文件的错误则无法发现。-s (slient)不输入正常信息
- myisamchk -m tbl_name 首先会检查索引条目；然后会计算并核对每行中 的KEY的校验和
- myisamchk -e tbl_name (-e “extened check”)会彻底的检查所有数据。对于数据比较多的表会消耗较长时间。当发现第一错误后将会终止。选项-v将会显示更加详细的信息，且会显示最多20个错误才终止检查。
- myisamchk -e -i tbl_name 与上面的命令类似，选项-i会显示一些额外的统计信息。
修复MyISAM表¶
Warning
进行任何修复操作前必须进行备份! 如果使用了主从复制，请先停止！
常见的错误包括： * tbl_name.frm被锁无法修改 * 找不到文件tbl_name.MYI * 无法预期的文件结尾 * 记录文件损坏 * 处理表时发生错误nnn
通过命令perror nnn可以获取详细的错误描述：
perror 132 134 135 136
# OS error code 132: Operation not possible due to RF-kill
# MySQL error code 132: Old database file
# MySQL error code 134: Record was already deleted (or record file crashed)
# MySQL error code 135: No more room in record file
# MySQL error code 136: No more room in index file
错误135(No more room in record file)和(No more room in index file)可以使用ALTER TABLE来增大表选项中的MAX_ROWS和AVG_ROW_LENGTH的值来修复：
ALTER TABLE tbl_name MAX_ROWS=100000 AVG_ROW_LENGTH=10000;
通过SHOW CREATE TABLE可以查看表的相关选项值。
对于其它一些错误（非135/136）需要使用myisamchk进一步修复。修复一般分为四步：（在进行修复前必须先将MySQL Server关闭。）
检查表
运行命令myisamchk [-e | others options] *.MYI选项-s可以减少不必要的输出。使用 –update-state选项，myisamchk会对检查过的表进行标记。
对于一般错误进入第二步进行修复；对于无法预知的错误（如内存溢出，myisamchk崩溃）跳至第三步进行修复。
简单安全的修复
- 首先通过命令myisamchk -r -q tbl_name尝试快速修复。这种方法将尝试修复索引文件而不处理数据文件。如果修复失败，则进行下一步；
- 备份数据文件
- 使用myisamchk -r tbl_name进行修复，此操作将从数据文件中删除不正的数据，并重建索引文件。
- 如果上一步失败，使用myisamchk --safe-recover tbl_name进行修复。安全修复模式会使用一种比较老的方法来修复。
如果操作过程中出现内存溢出，myisamchk崩溃请执行第三步进行恢复。
困难的修复
如果走到这一步通常是：
索引文件的最初16KB损坏或包含不正确的信息
如果索引文件丢失，则必须新建一个索引文件： * 将数据文件备份至安全位置 * 使用表描述文件创建新的数据文件和索引文件
USE db_name; SET autocommit=1; TRUNCATE TABLE tbl_name; quit;
- 将数据文件复制回来一份，覆盖新的数据文件
回到第二步执行myisamchk -r -q。另外也可用REPAIR TABLE tbl_name USE_FRM来进行修复。
如果需要执行此步进行修复，说明表描述文件(.FRM)已经损坏，这种情况几乎不会发生，因为表描述文件在数据表建立好后就不会再改变。修复方法如下：
- 从备份中恢复表描述文件（.FRM）返回执行第三步进行修复；如果索引文件也可从备份中恢复，返回第二步进行修复；
- 如果没有备份但是知道相应的表是如果创建的，可以在其它库中建立一个相同的表，然后将其.frm, .MYI文件复制一份与数据文件.MYD组合成完整的表，然后回至第二步进行修复操作，尝试重建索引文件。
- 如果啥也没有，那就废了。
myisamchk还有一些选项可以加速修复操作：sort-buffer-size, key-buffer-size。
MyISAM表的优化¶
使用OPTIMIZE TABLE也可以进行MyISAM的优化操作，通常包含表修复, KEY分析，索引树排序等操作，经过优化可以提高KEY的查找速度。
myisamchk命令用于优化的常用选项有：
- -r | --recover 修复MyISAM表中几乎所有的问题（除KEY不唯一外），回收被浪费的空间；
- -a | --analyze 通过分布分析改善JOIN性能。使用join优化器选择最佳的表连接和索引顺序
- -S | --sort-index 对索引块排序，优化查找性能，提高用索引搜索表的速度
- -R | --sort-records=index_num 应用指定的索引对数据行进行排序，使得数据聚集的更好，提高基于范围（range-based）的SELECT和ORDER BY操作的速度
MyISAM的周期维护¶
优化MyISAM既可以使用SQL语句CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLE, ANALYZE TABLE也可以通过myisamchk命令。
需要注意：执行myisamchk命令时，相应的数据表不可以操作。
正常情况下，MySQL的表只需进行很少维护，如果MyISAM表更新的大量可变大量的内容（VARCHAR, BLOB, TEXT类型的字段），或者删除的大量的数据，这时可以通过OPTIMIZE TABLE进行优化。
MySQL Server自动维护¶
如果MySQL Server的配置文件中开启了选项--myisam-recover-options=[options,...]，此选项有五个值可选（并可以组合使用）：OFF, DEFAULT, BACKUP, FORCE, QUICK。选项值为空等同于DEFAULT，而选项值为：”“等同于OFF。开启此功能后，mysqld每次打开MyISAM表时，都会检查表是否被标记为损坏或者非正常关闭，如果是，mysqld将会检查并修复表。从文档表述来看，开启此功能对MySQL Server的性能有一定影响。
| Option | Descripton |
| OFF | 关闭mysqld的自动维护MyISAM表的功能 |
| DEFAULT | Recovery without backup, forcing or quick checking |
| BACKUP | 如果数据在恢复时发生变化，备份tbl_name.MYD为tbl_name-datetime.BAK |
| FORCE | 即使会丢失.MYD中的数据，也执行恢复 |
| QUICK | 如果没有删除块的操作，Do not check the rows in the table. |
crontab计划任务¶
在crontab中添加一条计划任务定期检查是个不错的选择：
35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI
也可以通过执行SQL语句OPTIMIZE TABLE来优化。
小结¶
- 对MyISAM进行定期维护以降低故障发生的可能性。
- 修复前关停MySQL Server，备份数据。
- 可以使用SQL语句OPTIMIZE, CHECK, REPAIR, ANALYZE TABLE或者myisamchk进行维护和故障修复。
参考资料¶
| [1] | http://dev.mysql.com/doc/mysql-backup-excerpt/5.5/en/backup-types.html |
| [2] | http://dev.mysql.com/doc/mysql-backup-excerpt/5.5/en/backup-methods.html |
| [3] | http://dev.mysql.com/doc/refman/5.5/en/backup-policy.html |
| [4] | http://dev.mysql.com/doc/refman/5.5/en/mysqldump-delimited-text.html |
| [5] | http://dev.mysql.com/doc/refman/5.5/en/reloading-delimited-text-dumps.html |