mysql数据库备份和还原方法集锦推荐

mysql数据库备份和还原方法集锦推荐日期：、006/10/01本文讨论y的备份和恢复机制，以及如何维护数据表，包括最主要的两种表类型：y和，文中设计的MyS版本为.、目前y支持的免费备份工具有:mysqldump、mysqlhc还可以用语法进行备份：或者又或者备份二进制日志（）o还可以是直接拷贝数据文件和相关的配置文件。y表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。所有的表都保存在同一个数据文件d中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份,1或者用mysqldump1、mysqldump1.1备份mysqldump是釆用级别的备份机制，它将数据表导成脚本文件，在不同的y版本之间升级时相对比较合适，这也是最常用的备份方法。现在来讲一下mysqldump的些主要参数：* mplm它告诉mysqldump导出的数据将和哪种数据库或哪个旧版本的y服务器相兼容。值可以为smysql、mysql、ps、sql、mssq、d、mdy、Psls_psld等,p要s使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。* mplts导出的数据釆用包含字段名的完整方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到mlld参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。* dulss指定导出数据时釆用何种字符集，如果数据表不是釆用默认的l字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。dsleys告诉mysqldump在语句的开头和结尾增加l和引的。该选项只适合ly表。语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索-extended-suls默认情况下，mysqldump开启mpl模式，因此不想用它的的话，就使用本选项，设定它的值为l即可。使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有、。l,llls在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭single-t-lock-和sl选项。lsls它和l表，如果是类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于y表可以用sl选项。s只导出数据，而不添加语句。不导出任何数据，只导出数据库表结构。这只是一个快捷选项，等同于同时添加选项。本选项能让默认开启，但可以用禁用。注意，如果运行在内存中。如果导出大数据库的话可能会出现问题。很快的导出数据，并且导出的数据能很快导回。该选项没有指定或选项，则会将整个结果集放该选项在导出大表时很有用，它强制从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。导出存储过程以及自定义函数。该选项在导出数据之前提交一个语句，不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如和B本选项和选项是互斥的，因为会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用选项。同时导出触发器。该选项默认启用，用禁用它。其他参数详情请参考手册，我通常使用以下来备份表:使用以下来备份表:另外，如果想要实现在线备份，还可以使用参数来实现，如下:-:efault-character-set=utf8-opt-master-:ata=1它只是在一开始的瞬间请求锁表，然后就刷新了，而后在导出的文件中加入语句来指定当前备份的位置，如果要把这个文件恢复到里去，就可以釆用这种方法来做。1.还原用备份出来的文件是一个可以直接倒入的脚本，有两种方法可以将数据导入。*直接用客户端例如:*用语法其实这不是标准的语法，而是客户端提供的功能，例如:这里需要指定文件的绝对路径，并且必须是mysql运行用户例如oo有又限读取的文件。2、mysqlhotcopy2.1备份mysqlhotcop是一个程序，最初由m编写。它使用LLS_SI和ECp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上。mysqlhotcop只能用于备份yS,并且只能运行在类和t系统上。mysqlhotcop应持一次性拷贝多个数据库，同时还支持正则表达。以下是几个例子：oot#/usr/locam把数据库/mysql/b目录db_nin/mysqlhotcopy-拷贝到tm下h=localhost-uu=yejr-p=yoot#/usr/loca/mysql/bmysqlhotcopyh=localhost-u=yejr-p=ym1.b_nametmpoot#/usr/loca/mysql/bmysqlhotcopyh=localhost-u=yejr-p=ym.tmp更详细的使用方法请查看手册，或者调用下面的命令来查看mysqlhotcop的帮助：plocsloclmysqlmysqlhotcopy注意，想要使用mysqlhotcopy必须要有SLL要执行LSLES）并且还必须要能够有读取t自录的权限。2.2还原mysqlhotcop备份出来的是整个数据库目录，使用时可以直接拷贝到mysql指定的t在这里是sloclmysql目录下即可，同时要注意权限的问题，如下例：ootcpmsloclmysqltootchoooyooys将loclm目录的属主改成mysql运行用户3、SQL语法备份3.1备份语法其实和mysqlhotcop的工作原理差不多，都是锁表，然后拷贝数据文件。它能实现在线备份，但是效果不理想，因此不推荐使用。它只拷贝表结构文件和数据文件，不同时拷贝索引文件，因此恢复时比较慢。例子：BACKTABLEtbl_nameTO/tmp/db_name/;注意，必须要有L又限才能执行本SQL，并且目录tmp必须能被mysql用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。SL则是把数据导出来成为普通的文本文件，可以自定义字段间隔的方式，方便处理这些数据。例子：SLLtmpmtlm.tttlm注意，必须要有L权限才能执行本SQL，并且文件tmpmt必须能被mysql用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。3.2恢复用方法备份出来的文件，可以运行S语句来恢复数据表。例子：SLtmpm权限要求类似上面所述。用SL例子：方法备份出来的文件，可以运行L语句来恢复数据表。bl_n关键字来权限要求类似上面所述。倒入数据之前，数据表要已经存在才行。如果担心数据会发生重复，可以增加替换已有记录或者用关键字来忽略他们。4、启用二进制日志(binlog)釆用binlog的方法相对来说更灵活，省心省力，而且还可以支持增量备份。启用binlog时必须要重启。l首先，关闭，l打开,加入以下几行：server-id=1log-bin=binloglog-bin-index=binlog.index然后启动就可以了。运行过程中会产生binlog以及binlogin,前面的文件是记录所有对数据的更新操作，后面的文件则是所有binlog的索引，都不能轻易删除。关于binlog的信息请查看手册。终止对当前binlog的写入，就可以把文件直接备份，这样的话就能达到需要备份时，可以先执行一下语句，让增量备份的目的了：如果是备份复制系统中的从服务器，还应该备份ma备份出来的binlog文件可以用提供的工具/usr/local/mys，l/bin/mys，lbinlog/tmp该工具允许你显示指定的数据库下的所有语句,恢复时，可以釆用类似以下语句来做到：/usr/local/mys，l/bin/mys，lbinlog/tmp和inlolc文件nolbin来查看，如：binlog.000001并且还可以限定时间范围，相当的方便，详细的请查看手册binlog.000001|mys，l-uyejr-pyejrdb_n把lbin输出的语句直接作为输入来执行它。如果你有空闲的机器，不妨釆用这种方式来备份。由于作为l的机器性能要求相对不是那么高，因此成本低，用低成本就能实现增量备份而且还能分担一部分数据查询压力，何乐而不为呢？5、直接备份数据文件相较前几种方法，备份数据文件最为直接、快速、方便，缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在靠背文件前，执行以下语句：也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。注意，对于nno类型表来说，还需要备份其日志文件，即iblogi文件。因为当nno表损坏时，就可以依靠这些日志文件来恢复。6、备份策略对于中等级别业务量的系统来说，备份策略可以这么定：第一次全量备份，每天一次增量备份，每周再做一次全量备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是釆用主从复制机制：lia在on)机器上做备份。7、数据维护和灾难恢复作为一名我目前还不是，呵呵)，最重要的工作内容之一是保证数据表能安全、稳定、高速使用。因此，需要定期维护你的数据表。以下语句就很有用：，表或，检查或维护，优化表,分析表表则可以通过执行以下语句来整理碎片，提高索引速度:这其实是一个操作，表面上看什么也不做，实际上重新整理碎片了。通常使用的表可以用上面提到的恢复方法来完成。如果是索引坏了，可以用工具来重建索引。而对于表来说，就没这么直接了，因为它把所有的表都保存在一个表空间了。不过有一个检查机制叫模糊检查点，只要保存了日志文件，就能根据日志文件来修复错误。可以在文件中，增加以下参数，让在启动时自动检查日志文件：关于该参数的信息请查看手册。8、总结做好数据备份，定只好合适的备份策略，这是一个所做事情的一小部分，万事开头难，就从现在开始吧!