对数据库来说,最重要也最容易被忽视的就是备份。由于不可预测性,偶然的事件可能会导致非常惨重的损失。
数据越是重要,数据的变化越频繁,备份越发需要经常进行。
备份周期根据不同业务的需要可以调整,但是不能忽视备份。
备份时最好也备份 my.cnf 或 my.ini,这样可以保存你以前的配置参数。
因为 MySQL 表保存为文件方式,很容易备份。要想保持备份的一致性,对相关表执行 LOCK TABLES 操作,然后对表执行 FLUSH TABLES。
你只需要读锁定;这样当你复制数据库目录中的文件时,允许其它客户继续查询表。需要 FLUSH TABLES 语句来确保开始备份前将所有激活的索引页写入硬盘。
标准流程:锁表 -》刷新表到磁盘 -》拷贝文件 -》解锁。
如果你想要进行 SQL 级别的表备份,你可以使用 SELECT INTO ...OUTFILE 或 BACKUP TABLE。对于 SELECT INTO ...OUTFILE, 输出的文件不能先存在。
对于 BACKUP TABLE 也如此,因为覆盖完整的文件会有安全风险。
这两种备份方法,如果输出文件有重名的话,最好把重名文件移除。
BACKUP TABLE 备份时注意输出目录的权限,改方法只是备份 MYD 和 frm 文件,不备份索引。
mysqlhotcopy 是一个 Perl 脚本,最初由 Tim Bunce 编写并提供。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库目录所在的机器上。mysqlhotcopy 只用于备份 MyISAM。它运行在 Unix 和 NetWare 中。
- shell > mysqlhotcopy db_name [/path/to / new_directory ]
- shell > mysqlhotcopy db_name_1 ... db_name_n / path / to / new_directory
备份给定数据库中的匹配正则表达式的表:
shell> mysqlhotcopy db_name./regex/
加上发音符 ('~') 前缀,表名的正则表达式可以被否定:
shell> mysqlhotcopy db_name./~regex/
mysqlhotcopy 支持下面的选项:
· ---help,-?
显示帮助消息并退出。
· --allowold
如果目标存在不放弃 (加上一个_old 后缀重新命名它)。
· --checkpoint=db_name.tbl_name
在指定的 db_name.tbl_name 插入检查点条目。
· ---debug
启用调试输出。
· --dryrun,-n
报告动作而不执行它们。
· --flushlog
所有表锁定后刷新日志。
· --keepold
完成后不删除以前 (重新命名的) 的目标。
· -- method=command
复制方法 (cp 或 scp)。
· --noindices
备份中不包括全部索引文件。这样使备份更小、更快。可以在以后用 myisamchk -rq 重新构建索引。
· --password=password,-p password
当连接服务器时使用的密码。请注意该选项的密码值是不可选的,不象其它 MySQL 程序。
· --port=port_num,-P port_num
当连接本地服务器时使用的 TCP/IP 端口号。
· --quiet,-q
除了出现错误时保持沉默。
· --regexp=expr
复制所有数据库名匹配给出的正则表达式的数据库。
· --socket=path,-S path
用于连接的 Unix 套接字文件。
· --suffix=str
所复制的数据库名的后缀。
· --tmpdir=path
临时目录 (代替 / tmp)。
· --user=user_name,-u user_name
当连接服务器时使用的 MySQL 用户名。
mysqlhotcopy 从选项文件读取 [client] 和[mysqlhotcopy]选项组。
因为 mysqlhotcopy 一般是用来做完全备份,所以推荐使用—flushlog 选项来产生增量更新日志。
可以备份表结构和数据,可以同时支持 MyISAM 和 InnoDB 引擎数据库。
mysqldump 可以备份单个表、单个库或所有库。
Mysqldump 还可以只导出表结构。
mysqldump 是逻辑备份,输出的是 sql 语句文件,还可以输出其他数据库兼容的格式。
有 3 种方式来调用 mysqldump:
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] ---database DB1 [DB2 DB3...]
shell> mysqldump [options] --all--database
如果没有指定任何表或使用了 ---database 或 --all--database 选项,则转储整个数据库。
要想获得你的版本的 mysqldump 支持的选项,执行 mysqldump ---help。
如果运行 mysqldump 没有 --quick 或 --opt 选项,mysqldump 在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用,但可以用 --skip-opt 禁用。
mysqldump 支持下面的选项:
· ---help,-?
显示帮助消息并退出。
· --add-drop--database
在每个 CREATE DATABASE 语句前添加 DROP DATABASE 语句。
· --add-drop-tables
在每个 CREATE TABLE 语句前添加 DROP TABLE 语句。
· --add-locking
用 LOCK TABLES 和 UNLOCK TABLES 语句引用每个表转储。重载转储文件时插入得更快。
· --all--database,-A
转储所有数据库中的所有表。与使用 ---database 选项相同,在命令行中命名所有数据库。
· --allow-keywords
允许创建关键字列名。应在每个列名前面加上表名前缀。
· ---comments[={0|1}]
如果设置为 0,禁止转储文件中的其它信息,例如程序版本、服务器版本和主机。--skip—comments 与 ---comments=0 的结果相同。 默认值为 1,即包括额外信息。
· --compact
产生少量输出。该选项禁用注释并启用 --skip-add-drop-tables、--no-set-names、--skip-disable-keys 和 --skip-add-locking 选项。
· --compatible=name
产生与其它数据库系统或旧的 MySQL 服务器更兼容的输出。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options 或者 no_field_options。要使用几个值,用逗号将它们隔开。这些值与设置服务器 SQL 模式的相应选项有相同的含义。
该选项不能保证同其它服务器之间的兼容性。它只启用那些目前能够使转储输出更兼容的 SQL 模式值。例如,--compatible=oracle 不映射 Oracle 类型或使用 Oracle 注释语法的数据类型。
· --complete-insert,-c
使用包括列名的完整的 INSERT 语句。
· --compress,-C
压缩在客户端和服务器之间发送的所有信息(如果二者均支持压缩)。
· --create-option
在 CREATE TABLE 语句中包括所有 MySQL 表选项。
· ---database,-B
转储几个数据库。通常情况,mysqldump 将命令行中的第 1 个名字参量看作数据库名,后面的名看作表名。使用该选项,它将所有名字参量看作数据库名。CREATE DATABASE IF NOT EXISTS db_name 和 USE db_name 语句包含在每个新数据库前的输出中。
· ---debug[=debug_options],-# [debug_options]
写调试日志。debug_options 字符串通常为'd:t:o,file_name'。
· --default-character-set=charset
使用 charsetas 默认字符集。如果没有指定,mysqldump 使用 utf8。
· --delayed-insert
使用 INSERT DELAYED 语句插入行。
· --delete-master-logs
在主复制服务器上,完成转储操作后删除二进制日志。该选项自动启用 --master-data。
· --disable-keys,-K
对于每个表,用; 和; 语句引用 INSERT 语句。这样可以更快地装载转储文件,因为在插入所有行后创建索引。该选项只适合 MyISAM 表。
· --extended-insert,-e
使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件更小,重载文件时可以加速插入。
· --fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=...,-- 行 - terminated-by=...
这些选项结合 - T 选项使用,与 LOAD DATA INFILE 的相应子句有相同的含义。
· --first-slave,-x
不赞成使用,现在重新命名为 --lock-all-tables。
· --flush-logs,-F
开始转储前刷新 MySQL 服务器日志文件。该选项要求 RELOAD 权限。请注意如果结合 --all--database(或 - A) 选项使用该选项,根据每个转储的数据库刷新日志。例外情况是当使用 --lock-all-tables 或 --master-data 的时候:在这种情况下,日志只刷新一次,在所有 表被锁定后刷新。如果你想要同时转储和刷新日志,应使用 --flush-logs 连同 --lock-all-tables 或 --master-data。
· --force,-f
在表转储过程中,即使出现 SQL 错误也继续。
· --host=host_name,-h host_name
从给定主机的 MySQL 服务器转储数据。默认主机是 localhost。
· --hex-blob
使用十六进制符号转储二进制字符串列 (例如,'abc'变为 0x616263)。影响到的列有 BINARY、VARBINARY、BLOB。
· --lock-all-tables,-x
所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭 --single-transaction 和 --lock-tables。
· --lock-tables,-l
开始转储前锁定所有表。用 READ LOCAL 锁定表以允许并行插入 MyISAM 表。对于事务表例如 InnoDB 和 BDB,--single-transaction 是一个更好的选项,因为它不根本需要锁定表。
请注意当转储多个数据库时,--lock-tables 分别为每个数据库锁定表。因此,该选项不能保证转储文件中的表在数据库之间的逻辑一致性。不同数据库表的转储状态可以完全不同。
· --master-data[=value]
该选项将二进制日志的位置和文件名写入到输出中。该选项要求有 RELOAD 权限,并且必须启用二进制日志。如果该选项值等于 1,位置和文件名被写入 CHANGE MASTER 语句形式的转储输出,如果你使用该 SQL 转储主服务器以设置从服务器,从服务器从主服务器二进制日志的正确位置开始。如果选项值等于 2,CHANGE MASTER 语句被写成 SQL 注释。如果 value 被省略,这是默认动作。
--master-data 选项启用 --lock-all-tables,除非还指定 --single-transaction(在这种情况下,只在刚开始转储时短时间获得全局读锁定。又见 --single-transaction。在任何一种情况下,日志相关动作发生在转储时。该选项自动关闭 --lock-tables。
· --no-create-db,-n
该选项禁用 CREATE DATABASE db_name 语句,如果给出 ---database 或 --all--database 选项,则包含到输出中。
· --no-create-info,-t
不写重新创建每个转储表的 CREATE TABLE 语句。
· --no-data,-d
不写表的任何行信息。如果你只想转储表的结构这很有用。
· --opt
该选项是速记;等同于指定 --add-drop-tables--add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset。它可以给出很快的转储操作并产生一个可以很快装入 MySQL 服务器的转储文件。该选项默认开启,但可以用 --skip-opt 禁用。要想只禁用确信用 - opt 启用的选项,使用 --skip 形式;例如,--skip-add-drop-tables 或 --skip-quick。
· --password[=password],-p[password]
连接服务器时使用的密码。如果你使用短选项形式 (-p),不能在选项和密码之间有一个空格。如果在命令行中,忽略了 --password 或 - p 选项后面的 密码值,将提示你输入一个。
· --port=port_num,-P port_num
用于连接的 TCP/IP 端口号。
· --protocol={TCP | SOCKET | PIPE | MEMORY}
使用的连接协议。
· --quick,-q
该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。
· --quote-names,-Q
用'`'字符引用数据库、表和列名。如果服务器 SQL 模式包括 ANSI_QUOTES 选项,用'"'字符引用名。默认启用该选项。可以用 --skip-quote-names 禁用,但该选项应跟在其它选项后面,例如可以启用 --quote-names 的 --compatible。
· --result-file=file,-r file
将输出转向给定的文件。该选项应用在 Windows 中,因为它禁止将新行'/n'字符转换为'/r/n'回车、返回 / 新行序列。
· --routines,-R
在转储的数据库中转储存储程序 (函数和程序)。使用 ---routines 产生的输出包含 CREATE PROCEDURE 和 CREATE FUNCTION 语句以重新创建子程序。但是,这些语句不包括属性,例如子程序定义者或创建和修改时间戳。这说明当重载子程序时,对它们进行创建时定义者应设置为重载用户,时间戳等于重载时间。
如果你需要创建的子程序使用原来的定义者和时间戳属性,不使用 --routines。相反,使用一个具有 mysql 数据库相应权限的 MySQL 账户直接转储和重载 mysql.proc 表的内容。
该选项在 MySQL 5.1.2 中添加进来。在此之前,存储程序不转储。
· --set-charset
将 SET NAMES default_character_set 加到输出中。该选项默认启用。要想禁用 SET NAMES 语句,使用 --skip-set-charset。
· --single-transaction
该选项从服务器转储数据之前发出一个 BEGIN SQL 语句。它只适用于事务表,例如 InnoDB 和 BDB,因为然后它将在发出 BEGIN 而没有阻塞任何应用程序时转储一致的数据库状态。
当使用该选项时,应记住只有 InnoDB 表能以一致的状态被转储。例如,使用该选项时任何转储的 MyISAM 或 HEAP 表仍然可以更改状态。
--single-transaction 选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。
要想转储大的表,应结合 --quick 使用该选项。
· --socket=path,-S path
当连接 localhost(为默认主机) 时使用的套接字文件。
· --skip--comments
参见 ---comments 选项的描述。
· --tab=path,-T path
产生 tab 分割的数据文件。对于每个转储的表,mysqldump 创建一个包含创建表的 CREATE TABLE 语句的 tbl_name.sql 文件,和一个包含其数据的 tbl_name.txt 文件。选项值为写入文件的目录。
默认情况,.txt 数据文件的格式是在列值和每行后面的新行之间使用 tab 字符。可以使用 --fields-xxx 和 -- 行 --xxx 选项明显指定格式。
注释:该选项只适用于 mysqldump 与 mysqld 服务器在同一台机器上运行时。你必须具有 FILE 权限,并且服务器必须有在你指定的目录中有写文件的许可。
· --tables
覆盖 ---database 或 - B 选项。选项后面的所有参量被看作表名。
· --triggers
为每个转储的表转储触发器。该选项默认启用;用 --skip-triggers 禁用它。
· --tz-utc
在转储文件中加入 SET TIME_ZONE='+00:00'以便 TIMESTAMP 列可以在具有不同时区的服务器之间转储和重载。(不使用该选项,TIMESTAMP 列在具有本地时区的源服务器和目的服务器之间转储和重载)。--tz-utc 也可以保护由于夏令时带来的更改。--tz-utc 默认启用。要想禁用它,使用 --skip-tz-utc。该选项在 MySQL 5.1.2 中加入。
· --user=user_name,-u user_name
连接服务器时使用的 MySQL 用户名。
· --verbose,-v
冗长模式。打印出程序操作的详细信息。
· --version,-V
显示版本信息并退出。
· --where='where-condition', -w 'where-condition'
只转储给定的 WHERE 条件选择的记录。请注意如果条件包含命令解释符专用空格或字符,一定要将条件引用起来。
当 mysql 服务器停止时,通过复制所有表文件 (*.frm、*.MYD 和 *.MYI 文件) 来备份 MyISAM 数据库。
1、 如果是通过 mysqldump 备份的,就执行:mysql –u root < 备份文件名。
2、 如果通过 mysqlhotcopy 或文件冷 / 热拷贝来备份的,停止 mysql 服务,使用备份文件来覆盖现有文件。
3、 如果是采用 BACKUP TABLE 备份的,使用 restore table 来恢复。因为 backup table 不会备份索引文件,恢复表注意重建索引。
4、 如果是采用 SELECT INTO ...OUTFILE 备份的,使用 load data 恢复数据,也可以使用 mysqlimport 命令来代替。
日志备份可以支持 MyISAM 和 InnoDB,这跟有些备份工具只支持 MyISAM 不一样。
另外,日志可以做增量备份,这是其他方法无法做到的。
启动日志备份:用 --log-bin[=file_name] 选项来启动 mysql 服务。
先执行 FLUSH LOGS 刷新日志,同步日志缓存到磁盘,关闭当前的日志并产生新的日志文件。
拷贝上一次完整备份或增量备份后的一个或一些日志文件到一个安全的地方。
这样就制作了一个增量备份。
日志恢复可以选择恢复某个数据库、某些操作点或时间范围,非常灵活。
通过 mysqlbinlog 工具可以恢复二进制日志,可以一次恢复多个日志文件,命令如下:
shell> mysqlbinlog [options] hostname-bin.[0-9]* | mysql
mysqlbinlog 命令选项
· ---help,-?
显示帮助消息并退出。
· ---database=db_name,-d db_name
只列出该数据库的条目 (只用本地日志)。
· --force-read,-f
使用该选项,如果 mysqlbinlog 读它不能识别的二进制日志事件,它会打印警告,忽略该事件并继续。没有该选项,如果 mysqlbinlog 读到此类事件则停止。
· --hexdump,-H
在注释中显示日志的十六进制转储。该输出可以帮助复制过程中的调试。在 MySQL 5.1.2 中添加了该选项。
· --host=host_name,-h host_name
获取给定主机上的 MySQL 服务器的二进制日志。
· --local-load=path,-l pat
为指定目录中的 LOAD DATA INFILE 预处理本地临时文件。
· --offset=N,-o N
跳过前 N 个条目。
· --password[=password],-p[password]
当连接服务器时使用的密码。如果使用短选项形式 (-p),选项和 密码之间不能有空格。如果在命令行中 --password 或 - p 选项后面没有 密码值,则提示输入一个密码。
· --port=port_num,-P port_num
用于连接远程服务器的 TCP/IP 端口号。
· --position=N,-j N
不赞成使用,应使用 --start-position。
· --protocol={TCP | SOCKET | PIPE | -position
使用的连接协议。
· --read-from-remote-server,-R
从 MySQL 服务器读二进制日志。如果未给出该选项,任何连接参数选项将被忽略。这些选项是 --host、--password、--port、--protocol、--socket 和 --user。
· --result-file=name, -r name
将输出指向给定的文件。
· --short-form,-s
只显示日志中包含的语句,不显示其它信息。
· --socket=path,-S path
用于连接的套接字文件。
· --start-datetime=datetime
从二进制日志中第 1 个日期时间等于或晚于 datetime 参量的事件开始读取。datetime 值相对于运行 mysqlbinlog 的机器上的本地时区。该值格式应符合 DATETIME 或 TIMESTAMP 数据类型。例如:
shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003
该选项可以帮助点对点恢复。
· --stop-datetime=datetime
从二进制日志中第 1 个日期时间等于或晚于 datetime 参量的事件起停止读。关于 datetime 值的描述参见 --start-datetime 选项。该选项可以帮助及时恢复。
· --start-position=N
从二进制日志中第 1 个位置等于 N 参量时的事件开始读。
· --stop-position=N
从二进制日志中第 1 个位置等于和大于 N 参量时的事件起停止读。
· --to-last-logs,-t
在 MySQL 服务器中请求的二进制日志的结尾处不停止,而是继续打印直到最后一个二进制日志的结尾。如果将输出发送给同一台 MySQL 服务器,会导致无限循环。该选项要求 --read-from-remote-server。
· --disable-logs-bin,-D
禁用二进制日志。如果使用 --to-last-logs 选项将输出发送给同一台 MySQL 服务器,可以避免无限循环。该选项在崩溃恢复时也很有用,可以避免复制已经记录的语句。注释:该选项要求有 SUPER 权限。
· --user=user_name,-u user_name
连接远程服务器时使用的 MySQL 用户名。
· --version,-V
显示版本信息并退出。
还可以使用 --var_name=value 选项设置下面的变量:
· open_files_limit
指定要保留的打开的文件描述符的数量。
InnoDB Hotbackup 是一个在线备份工具,你可以用它来在 InnoDB 数据库运行之时备份你的 InnoDB 数据库。InnoDB 热备份工具 不要求你关闭数据库,并且它不设置任何锁定或干扰你正常的数据库处理。InnoDB 热备份工具 是非免费(商业的)附加软件,它每年的证书费用是每台 MySQL 服务器运行的计算机 390 欧元。
具体使用方法可以参考相关文档,我也没有使用过这个工具。
Mysqldump 提供对 InnoDB 非物理的在线逻辑热备份。
使用方法与备份 MyISAM 时一样。
可以使用 select into 备份一个或多个表,用法与 MyISAM 表相同。
如果你可以关闭你的 MySQL 服务器,你可以生成一个包含 InnoDB 用来管理它的表的所有文件的二进制备份。使用如下步骤:
1. 关闭 MySQL 服务器,确信它是无错误关闭。
2. 复制你所有数据文件(ibdata 文件和. ibd 文件)到一个安全的地方。
3. 复制你所有 ib_logfile 文件到一个安全的地方。
4. 复制 my.cnf 配置文件或文件到一个安全的地方。
5. 为你 InnoDB 表复制. frm 文件到一个安全的地方。
1、 先尝试使用 InnoDB 的日志自动恢复功能,方法是重启 mysql 服务。
2、 在一些情况下,明显地数据库损坏是因为操作系统损坏它自己的文件缓存,磁盘上的数据可能完好,最好是首先重启计算机。它可以消除那些显得是数据库页损坏的错误。
如果不行,则采用下面的方法进行恢复。
3、 如果是 mysqldump 做的完全备份,先恢复完全备份,然后再恢复完全备份后的增量日志备份。
4、 如果是采用 select into 备份表的话,则采用 load data 或 mysqlimport 恢复。
5、 如果是采用二进制冷备份做的完全备份,则先停止 mysql 服务,覆盖备份的二进制文件,然后执行上次完全备份后的增量日志备份。
6、 注意:InnoDB 二进制文件没有 MyISAM 那么好,必须在相同的浮点数的 cpu 机器上移植。
因为在一个数据库中有可能要同时使用 MyISAM 和 InnoDB 两种引擎,因此统一考虑他们的备份 / 恢复策略。
1、 完整备份采用 mysqldump。
2、 增量备份采用 bin-log 日志。
3、 单表备份采用 select into。
4、
虽然 MySQL 提供了多种备份 / 恢复手段,但是定期维护表大大可以降低表毁坏的可能性,并且可以提高查询性能。
1、对于 InnoDB 引擎,你可以使用 innodb_tablespace_monitor 来检查表空间文件内文件空间管理的完整性。
2、对于 MyISAM 引擎,可以通过 sql 语句或 myisamchk 工具来维护表 。
在许多情况下,你会发现使用 SQL 语句实现 MyISAM 表的维护比执行 myisamchk 操作要容易地多:
· 要想检查或维护 MyISAM 表,使用 CHECK TABLE 或 REPAIR TABLE。
· 要想优化 MyISAM 表,使用 OPTIMIZE TABLE。
· 要想分析 MyISAM 表,使用 ANALYZE TABLE。
这些语句比 myisamchk 有利的地方是服务器可以做任何工作。使用 myisamchk,你必须确保服务器在同一时间不使用表。否则,myisamchk 和服务器之间会出现不期望的相互干涉。
一般建议在停止 mysql 服务时执行 myisamchk,如果是在线执行则最好先 flush tables(把所有更新写入磁盘)。
1、一定用 --log-bin 或甚至 --log-bin=log_name 选项运行 MySQL 服务器,其中日志文件名位于某个安全媒介上,不同于数据目录所在驱动器。如果你有这样的安全媒介,最好进行硬盘负载均衡 (这样能够提高性能)。
2、定期进行完全备份,使用 mysqldump 命令进行在线非块备份。
在负载比较低的时候进行,并且建议采用 --single-transaction 参数来保证事务数据的一致性,同时不影响其他用户的正常读写。
3、完整备份时采用 FLUSH LOGS,便于产生增量备份日志。
4、用 FLUSH LOGS 或 mysqladmin flush-logs 刷新日志进行定期增量备份。
5、定期维护表,这样既可以提高性能,并且可以减少数据丢失和出错的可能性。
6、关键表可以另外再做备份,根据需要。
来源: http://blog.csdn.net/nightelve/article/details/17397063