56,677
社区成员
发帖
与我相关
我的任务
分享关于MYSQL备份的问题。
我现在使用MYSQLDUMP备份数据库：
"d:\mysql\mysql server 5.0\bin\mysqldump.exe" -hlocalhost -uroot -pxxx --opt test_db> "D:\BAKUPDB\test_db_bak.sql"
发现备份出来的test_db_bak.sql因为数据量而增大，而且这个脚本完全是删除数据库再建表插入数据，
如果我做数据的差异备份，或说按时间段导出，恢复该怎么做？
"d:\mysql\mysql server 5.0\bin\mysqldump.exe" -hlocalhost -uroot -pxxx --opt test_db> "D:\BAKUPDB\test_db_bak.sql"
发现备份出来的test_db_bak.sql因为数据量而增大，而且这个脚本完全是删除数据库再建表插入数据，
如果我做数据的差异备份，或说按时间段导出，恢复该怎么做？
...全文
请发表友善的回复…
发表回复
回南山种豆 2011-04-12
- 打赏
- 举报
学习。。。。。。。。。。。。。。。。。。。
ACMAIN_CHM 2011-04-12
- 打赏
- 举报
[Quote]5.11.3. 二进制日志
二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。
二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。
注释：二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用。
二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句（例如，为了识别有问题的查询），你应使用一般查询日志。参见5.11.2节，“通用查询日志”。
二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。
二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。参见第6章：MySQL中的复制。
运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处，即用于恢复并允许设置复制超过了这个小小的性能损失。
当用--log-bin[=file_name]选项启动时，mysqld写入包含所有更新数据的SQL命令的日志文件。如果未给出file_name值， 默认名为-bin后面所跟的主机名。如果给出了文件名，但没有包含路径，则文件被写入数据目录。建议指定一个文件名，原因参见A.8.1节，“MySQL中的打开事宜”。
如果你在日志名中提供了扩展名(例如，--log-bin=file_name.extension)，则扩展名被悄悄除掉并忽略。
mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。如果你正使用大的事务，二进制日志还会超过max_binlog_size：事务全写入一个二进制日志中，绝对不要写入不同的二进制日志中。
为了能够知道还使用了哪个不同的二进制日志文件，mysqld还创建一个二进制日志索引文件，包含所有使用的二进制日志文件的文件名。默认情况下与二进制日志文件的文件名相同，扩展名为'.index'。你可以用--log-bin-index[=file_name]选项更改二进制日志索引文件的文件名。当mysqld在运行时，不应手动编辑该文件；如果这样做将会使mysqld变得混乱。
可以用RESET MASTER语句删除所有二进制日志文件，或用PURGE MASTER LOGS只删除部分二进制文件。参见13.5.5.5节，“RESET语法”和13.6.1节，“用于控制主服务器的SQL语句”。
二进制日志格式有一些已知限制，会影响从备份恢复。参见6.7节，“复制特性和已知问题”。
保存程序和触发器的二进制日志的描述参见20.4节，“存储子程序和触发程序的二进制日志功能”。
可以使用下面的mysqld选项来影响记录到二进制日志知的内容。又见选项后面的讨论。
· --binlog-do-db=db_name
告诉主服务器，如果当前的数据库(即USE选定的数
[/Quote]
[Quote]
5.9.2.1. 备份策略
我们都知道必须按计划定期进行备份。可以用几个工具完全备份(在某个时间点的数据快照)MySQL。例如，InnoDB Hot Backup为InnoDB数据文件提供在线非数据块物理备份，mysqldump提供在线逻辑备份。这里使用mysqldump。
假定我们在星期日下午1点进行了备份，此时负荷较低。下面的命令可以完全备份所有数据库中的所有InnoDB表：
shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
这是在线非块备份，不会干扰对表的读写。我们以前假定我们的表为InnoDB表，因此--single-transaction使用一致性地读，并且保证mysqldump所看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见）。如果我们还有其它类型的表，我们必须假定在备份过程中它们不会更改。例如，对于mysql数据库中的MyISAM表，我们必须假定在备份过程中没有对MySQL账户进行管理更改。
mysqldump命令产生的.sql文件包含一系列SQL INSERT语句，可以用来重载转储的表。
需要进行完全备份，但有时不方便。会产生大的备份文件并需要花时间来生成。从某个角度，完全备份并不理想，因为每个成功的完全备份包括所有数据，甚至自从上一次完全备份以来没有更改的部分。完成了初使完全备份后，进行增量备份会更有效。这样备份文件要小得多，备份时间也较短。不利之处是，恢复时不能只重载完全备份来恢复数据。还必须要用增量备份来恢复增量更改。
要想进行增量备份，我们需要保存增量更改。应使用--log-bin选项启动MySQL服务器，以便更新数据时将这些更改保存到文件中。该选项启用二进制日志，因此服务器写将每个更新数据的SQL语句写入MySQL二进制日志。让我们看看用--log-bin选项启动的已经运行多日的MySQL服务器的数据目录。我们找到以下MySQL二进制日志文件：
-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index
每次重启，MySQL服务器用序列中的下一个编号创建一个新的二进制日志文件。当服务器运行时，你还可以通过执行FLUSH LOGS SQL语句或mysqladmin flush-logs命令，告诉服务器关闭当前的二进制日志文件并创建一个新文件。mysqldump也有一个选项来清空日志。数据目录中的.index文件包含该目录下所有MySQL二进制日志的清单。该文件用于复制。
恢复时MySQL二进制日志很重要，因为它们是增量备份。如果进行完全备份时确保清空了日志，则后面创建的二进制日志文件包含了备份后的所有数据更改。让我们稍稍修改前面的mysqldump命令，让它在完全备份时能够清空 MySQL二进制日志，以便转储文件包含包含新的当前的二进制日志：
shell> mysqldump --single-transaction --flush-logs --master-data=2
--all-databases > backup_sunday_1_PM.sql
执行该命令后，数据目录则包含新的二进制日志文件，gbichot2-bin.000007。结果.sql文件包含下列行：
-- Position to start replication or point-in-time 恢复时y from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;
因为mysqldump命令可以执行完全备份，这些行表示两件事情：
· .sql文件包含所有写入gbichot2-bin.000007二进制日志文件或最新的文件之前的更改。
· 备份后所记录的所有数据更改不出现在.sql中，但出现在gbichot2-bin.000007二进制日志文件或最新的文件中。
在星期一下午1点，我们可以清空日志开始新的二进制日志文件来创建增量备份。例如，执行mysqladmin flush-logs命令创建gbichot2-bin.000008。星期日下午1点的完全备份和星期一下午1点之间的所有更改为文件gbichot2-bin.000007。该增量备份很重要，因此最好将它复制到安全的地方。(例如，备份到磁带或DVD上，或复制到另一台机器上）。在星期二下午1点，执行另一个mysqladmin flush-logs命令。星期一下午1点和星期二下午1点之间的所有所有更改为文件gbichot2-bin.000008(也应复制到某个安全的地方)。
MySQL二进制日志占据硬盘空间。要想释放空间，应随时清空。操作方法是删掉不再使用的二进制日志，例如进行完全备份时：
shell> mysqldump --single-transaction --flush-logs --master-data=2
--all-databases --delete-master-logs > backup_sunday_1_PM.sql
注释：如果你的服务器为复制主服务器，用mysqldump --delete-master-logs删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。
PURGE MASTER LOGS语句的描述中解释了为什么在删掉MySQL二进制日志之前应进行确认。参见13.6.1.1节，“PURGE MASTER LOGS语法”。
5.9.2.2. 为恢复进行备份
现在假设在星期三上午8点出现了灾难性崩溃，需要使用备份文件进行恢复。恢复时，我们首先恢复最后的完全备份(从星期日下午1点开始)。完全备份文件是一系列SQL语句，因此恢复它很容易：
shell> mysql < backup_sunday_1_PM.sql
在该点，数据恢复到星期日下午1点的状态。要想恢复从那时起的更改，我们必须使用增量备份，也就是，gbichot2-bin.000007和gbichot2-bin.000008二进制日志文件。根据需要从备份处取过这些文件，然后按下述方式处理：
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
我们现在将数据恢复到星期二下午1点的状态，但是从该时刻到崩溃之间的数据仍然有丢失。要想恢复，我们需要MySQL服务器将MySQL二进制日志保存到安全的位置(RAID disks, SAN, ...)，应为与数据文件的保存位置不同的地方，保证这些日志不在毁坏的硬盘上。(也就是，我们可以用--log-bin选项启动服务器，指定一个其它物理设备上的与数据目录不同的位置。这样，即使包含该目录的设备丢失，日志也不会丢失）。如果我们执行了这些操作，我们手头上会有gbichot2-bin.000009文件，我们可以用它来恢复大部分最新的数据更改，而不会丢失到崩溃时的数据。
5.9.2.3. 备份策略摘要
出现操作系统崩溃或电源故障时，InnoDB自己可以完成所有数据恢复工作。但为了确保你可以睡好觉，应遵从下面的指导：
· 一定用--log-bin或甚至--log-bin=log_name选项运行MySQL服务器，其中日志文件名位于某个安全媒介上，不同于数据目录所在驱动器。如果你有这样的安全媒介，最好进行硬盘负载均衡(这样能够提高性能)。
· 定期进行完全备份，使用mysqldump命令进行在线非块备份。
· 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。
5.9.3. 自动恢复
5.9.3.1. 指定恢复时间
5.9.3.2. 指定恢复位置
如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。关于启用二进制日志的信息，参见5.11.3节，“二进制日志”。对于mysqlbinlog的详细信息，参见8.6节，“mysqlbinlog：用于处理二进制日志文件的实用工具”。
要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式
[/Quote]
二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。
二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。
注释：二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用。
二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句（例如，为了识别有问题的查询），你应使用一般查询日志。参见5.11.2节，“通用查询日志”。
二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。
二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。参见第6章：MySQL中的复制。
运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处，即用于恢复并允许设置复制超过了这个小小的性能损失。
当用--log-bin[=file_name]选项启动时，mysqld写入包含所有更新数据的SQL命令的日志文件。如果未给出file_name值， 默认名为-bin后面所跟的主机名。如果给出了文件名，但没有包含路径，则文件被写入数据目录。建议指定一个文件名，原因参见A.8.1节，“MySQL中的打开事宜”。
如果你在日志名中提供了扩展名(例如，--log-bin=file_name.extension)，则扩展名被悄悄除掉并忽略。
mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。如果你正使用大的事务，二进制日志还会超过max_binlog_size：事务全写入一个二进制日志中，绝对不要写入不同的二进制日志中。
为了能够知道还使用了哪个不同的二进制日志文件，mysqld还创建一个二进制日志索引文件，包含所有使用的二进制日志文件的文件名。默认情况下与二进制日志文件的文件名相同，扩展名为'.index'。你可以用--log-bin-index[=file_name]选项更改二进制日志索引文件的文件名。当mysqld在运行时，不应手动编辑该文件；如果这样做将会使mysqld变得混乱。
可以用RESET MASTER语句删除所有二进制日志文件，或用PURGE MASTER LOGS只删除部分二进制文件。参见13.5.5.5节，“RESET语法”和13.6.1节，“用于控制主服务器的SQL语句”。
二进制日志格式有一些已知限制，会影响从备份恢复。参见6.7节，“复制特性和已知问题”。
保存程序和触发器的二进制日志的描述参见20.4节，“存储子程序和触发程序的二进制日志功能”。
可以使用下面的mysqld选项来影响记录到二进制日志知的内容。又见选项后面的讨论。
· --binlog-do-db=db_name
告诉主服务器，如果当前的数据库(即USE选定的数
[/Quote]
[Quote]
5.9.2.1. 备份策略
我们都知道必须按计划定期进行备份。可以用几个工具完全备份(在某个时间点的数据快照)MySQL。例如，InnoDB Hot Backup为InnoDB数据文件提供在线非数据块物理备份，mysqldump提供在线逻辑备份。这里使用mysqldump。
假定我们在星期日下午1点进行了备份，此时负荷较低。下面的命令可以完全备份所有数据库中的所有InnoDB表：
shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
这是在线非块备份，不会干扰对表的读写。我们以前假定我们的表为InnoDB表，因此--single-transaction使用一致性地读，并且保证mysqldump所看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见）。如果我们还有其它类型的表，我们必须假定在备份过程中它们不会更改。例如，对于mysql数据库中的MyISAM表，我们必须假定在备份过程中没有对MySQL账户进行管理更改。
mysqldump命令产生的.sql文件包含一系列SQL INSERT语句，可以用来重载转储的表。
需要进行完全备份，但有时不方便。会产生大的备份文件并需要花时间来生成。从某个角度，完全备份并不理想，因为每个成功的完全备份包括所有数据，甚至自从上一次完全备份以来没有更改的部分。完成了初使完全备份后，进行增量备份会更有效。这样备份文件要小得多，备份时间也较短。不利之处是，恢复时不能只重载完全备份来恢复数据。还必须要用增量备份来恢复增量更改。
要想进行增量备份，我们需要保存增量更改。应使用--log-bin选项启动MySQL服务器，以便更新数据时将这些更改保存到文件中。该选项启用二进制日志，因此服务器写将每个更新数据的SQL语句写入MySQL二进制日志。让我们看看用--log-bin选项启动的已经运行多日的MySQL服务器的数据目录。我们找到以下MySQL二进制日志文件：
-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index
每次重启，MySQL服务器用序列中的下一个编号创建一个新的二进制日志文件。当服务器运行时，你还可以通过执行FLUSH LOGS SQL语句或mysqladmin flush-logs命令，告诉服务器关闭当前的二进制日志文件并创建一个新文件。mysqldump也有一个选项来清空日志。数据目录中的.index文件包含该目录下所有MySQL二进制日志的清单。该文件用于复制。
恢复时MySQL二进制日志很重要，因为它们是增量备份。如果进行完全备份时确保清空了日志，则后面创建的二进制日志文件包含了备份后的所有数据更改。让我们稍稍修改前面的mysqldump命令，让它在完全备份时能够清空 MySQL二进制日志，以便转储文件包含包含新的当前的二进制日志：
shell> mysqldump --single-transaction --flush-logs --master-data=2
--all-databases > backup_sunday_1_PM.sql
执行该命令后，数据目录则包含新的二进制日志文件，gbichot2-bin.000007。结果.sql文件包含下列行：
-- Position to start replication or point-in-time 恢复时y from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;
因为mysqldump命令可以执行完全备份，这些行表示两件事情：
· .sql文件包含所有写入gbichot2-bin.000007二进制日志文件或最新的文件之前的更改。
· 备份后所记录的所有数据更改不出现在.sql中，但出现在gbichot2-bin.000007二进制日志文件或最新的文件中。
在星期一下午1点，我们可以清空日志开始新的二进制日志文件来创建增量备份。例如，执行mysqladmin flush-logs命令创建gbichot2-bin.000008。星期日下午1点的完全备份和星期一下午1点之间的所有更改为文件gbichot2-bin.000007。该增量备份很重要，因此最好将它复制到安全的地方。(例如，备份到磁带或DVD上，或复制到另一台机器上）。在星期二下午1点，执行另一个mysqladmin flush-logs命令。星期一下午1点和星期二下午1点之间的所有所有更改为文件gbichot2-bin.000008(也应复制到某个安全的地方)。
MySQL二进制日志占据硬盘空间。要想释放空间，应随时清空。操作方法是删掉不再使用的二进制日志，例如进行完全备份时：
shell> mysqldump --single-transaction --flush-logs --master-data=2
--all-databases --delete-master-logs > backup_sunday_1_PM.sql
注释：如果你的服务器为复制主服务器，用mysqldump --delete-master-logs删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。
PURGE MASTER LOGS语句的描述中解释了为什么在删掉MySQL二进制日志之前应进行确认。参见13.6.1.1节，“PURGE MASTER LOGS语法”。
5.9.2.2. 为恢复进行备份
现在假设在星期三上午8点出现了灾难性崩溃，需要使用备份文件进行恢复。恢复时，我们首先恢复最后的完全备份(从星期日下午1点开始)。完全备份文件是一系列SQL语句，因此恢复它很容易：
shell> mysql < backup_sunday_1_PM.sql
在该点，数据恢复到星期日下午1点的状态。要想恢复从那时起的更改，我们必须使用增量备份，也就是，gbichot2-bin.000007和gbichot2-bin.000008二进制日志文件。根据需要从备份处取过这些文件，然后按下述方式处理：
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
我们现在将数据恢复到星期二下午1点的状态，但是从该时刻到崩溃之间的数据仍然有丢失。要想恢复，我们需要MySQL服务器将MySQL二进制日志保存到安全的位置(RAID disks, SAN, ...)，应为与数据文件的保存位置不同的地方，保证这些日志不在毁坏的硬盘上。(也就是，我们可以用--log-bin选项启动服务器，指定一个其它物理设备上的与数据目录不同的位置。这样，即使包含该目录的设备丢失，日志也不会丢失）。如果我们执行了这些操作，我们手头上会有gbichot2-bin.000009文件，我们可以用它来恢复大部分最新的数据更改，而不会丢失到崩溃时的数据。
5.9.2.3. 备份策略摘要
出现操作系统崩溃或电源故障时，InnoDB自己可以完成所有数据恢复工作。但为了确保你可以睡好觉，应遵从下面的指导：
· 一定用--log-bin或甚至--log-bin=log_name选项运行MySQL服务器，其中日志文件名位于某个安全媒介上，不同于数据目录所在驱动器。如果你有这样的安全媒介，最好进行硬盘负载均衡(这样能够提高性能)。
· 定期进行完全备份，使用mysqldump命令进行在线非块备份。
· 用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。
5.9.3. 自动恢复
5.9.3.1. 指定恢复时间
5.9.3.2. 指定恢复位置
如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。关于启用二进制日志的信息，参见5.11.3节，“二进制日志”。对于mysqlbinlog的详细信息，参见8.6节，“mysqlbinlog：用于处理二进制日志文件的实用工具”。
要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式
[/Quote]
ACMAIN_CHM 2011-04-12
- 打赏
- 举报
mysqldump.exe 是全备工具，无法实现整个数据库的差备。
MYSQL中如果要实现差备，则近似的方法是使用BINLOG来实现。
MYSQL中如果要实现差备，则近似的方法是使用BINLOG来实现。
rucypli 2011-04-12
- 打赏
- 举报
全备肯定要定期做的 ，否则的话恢复起来需要很长时间
WWWWA 2011-04-12
- 打赏
- 举报
一般用二进制日志来恢复，你是怎样进行差异备份的
