MySQL数据库备份

 

数据库预防维护工作的基本原则

为了预防可能发生的数据库故障，应该采取以下的措施：

1.激活MySQL服务器的自动恢复能力

2.有计划的安排一些维护性工作，定期对数据表进行检查。

3.制定一会数据库备份计划。还应该启用二进制。

可以使用check table和reqair table 等SQL语句让MySQL服务器根据需要执行几种数据表维护操作。

在使用myisamcheck程序时，一定要保证MySQL服务器不会在此期间也去访问它。

 

在服务器运行时维护数据库

如果不想关停MySQL服务器，又不想让运行中的服务器和你正在使用的外部程序相互干扰，就必须使用某种锁定机制来与服务器协调。MySQL提供了两种锁定机制

内部锁定机制

外部锁定机制

 

内部锁定机制只适用于那些把每个数据表分别表示为一组文件的存储引擎。

不用于吧多个表信息保存在同一个相关文件的存储引擎（如innodb falcon等）

 

充分利用MySQL服务器的自动恢复能力

MySQL服务器的崩溃恢复能力是数据库完整性维护工作的第一道防线。其中某些存储引擎在MySQL服务器每次启动的时都会自动发生：另一种（MyISAM）是可选的，需要明确启用。

如果启用了Innodb存储引擎，它能自动查出和纠正很多问题。它能在数据库启动后在上次崩溃是没有提交和回滚的事务进行redo.以保证完整性。

Falcon也具备这种能力。

对于MyISAM MySQL提供可一种可选的数据表自动恢复能力。这个值适用--myisam-recover=level来开启  level的可选值有

    backup：如果自动恢复一个数据表的时候，先为它创建一个备份。

   force:强行恢复，哪怕会因此而丢失一个以上的数据行

   quick:

   default：这与把这个选项设为空一样。

比如想在发现问题时先创建一个备份在进行强行恢复：

   myisam-recover=backup,force

MyISAM的自动恢复能力很有用，应该启用它。作为MySQL数据库日常维护的一个重要手段。如果启用了键缓存，就更应该启用该能力了。

 

定期进行预防性维护

除了启用自动恢复能力，还应该制定一会预防性的维护计划，这样能减少对于备份的可能性。

看一个unix上使用一个cron 作业：

45 3 * * 0/user/local/mysql/bin/mysqlcheck

    --all databases --check-only-change --silent 

在每个星期日的3.45运行mysqlcheck程序。--silent是阻止输出，除非在数据表里发现错误。

注：自动的维护不一定适用于那些需要频繁更新的大数据表，因为当一个数据表正在被检查的时候，它将不能被更新。

 

制作数据库的备份

数据库的备份按照他们的格式可以分为两大类

文本格式的备份：这类备份是用过使用mysqldmp程序吧数据表内容写到转储文件里而得到的，其内容主要有create table和insert两种SQL语句构成。

二进制备份 ：这类备份是通过直接复制那些包含着数据表内容的文件而得到的。

每种备份方法都有它自己的优点和缺点。在挑选备份工具的时候考虑的包括：是否要关停服务器，制作备份要花的时间，备份的可移植性等等。

 

mysqldump程序必须与MySQL服务器配合使用，所以在服务运行的时候去使用它。

二进制备份有些需要关停服务器的时候去备份它，或者有些需要在复制数据表文件的时候不能去修改数据表。

 

mysqldump程序比二进制备份要慢，这是因为转储操作需要通过网络在mysqlfdump和mysql服务器之间传输信息。二进制备份则是直接复制。

 

mysqldump程序生成的是SQL语句的文本文件。这些文件很容易移植到其他的机器上，甚至可以移植到不同硬件结构的机器上，所以特别适合用来把数据库从一个服务器复制到另一个服务器。

二进制备份则不一定能移植到其他的机器上了。MyISAM和innodb数据表通常与具体的机器无关。Falcon存储引擎的日志文件盒表空间都以一种鱼机器相关的格式存储。

 

mysqldump程序的输出只包含数据库的内容（数据表、视图、存储历程、等等），不包含没被保存在数据库里的信息，如配合文件、日志文件或复制状态文件。

二进制备份的选择余地就大了，你可以选择复制任何一个文件。

无论选择哪一种备份的方法，必须遵守几个原则：

1.定期对数据库进行备份。制定一个计划，并严格按照这个计划行事

2.启用二进制日志。

3.给备份文件起的名字既要有规律，又要有意义。

4.不要把备份文件盒数据库放在同一个文件系统上。

5.定期使用文件系统的备份功能备份你的数据备份文件。

6.定期对备份文件进行过期处理。

 

用mysqldump程序制作文件备份

C:\Users\Administrator>mysqldump -u sampadm1 -p sampdb1 student >E:\student.sql
Enter password: ********

　　将所有数据库的数据表备份到同一个文件里：

mysqldump --all-database >E:\dump_all.2013-06-8

减少转储文件长度的另一个办法是对它进行压缩。

如果你打算把转储文件加载到另一个服务器上去，请慎重使用--all-databases选项:这样会替换mysql数据库里的各个权限表。

在默认的情况下，mysqldump程序将同时备份数据表的结构和数据插入语句。如果你只想要其中的一部分，则使用

--no-create-info或--no-data选项。

--opt默认是启用的，能对转储程序进行优化。使用-opt选项时最常见的做法了，因为它可以加快备份速度。但其中也有一个坏处，就是--opt选项会吧你正在转储的数据表全部锁上，不让任何人对他们

进行修改。这要的问题是，一个访问量持续很大的数据库，在忙的时候制作备份，不一会就有很多人投诉。

如果制作备份是为了定期去更新另一个数据库，这时候--opt选项就会有很有，它会自动启用--add-drop-table选项：即会在备份文件里的每条create table语句之前加上一条drop table if exsits语句。

在转储InnoDB 或者Falcon数据表的时候，最好使用--single-transaction选项把转储操作放在一个事务里执行，这可以获得一个稳定的备份。

 

制作二进制数据库备份

制作二进制备份文件时需要两个要点：

1.一定要确保没有人在使用那张表

2.在直接复制这个表是，一定要把恢复这个数据表锁需要的文件全部复制下来。