MySQL学习整理之数据库备份

MySQL学习整理之数据库备份

[整理人:[e-mail] 整理时间:2009-12-25]

一、 简介

PS：flush tables 语句的作用确保将所有激活的索引页写入硬盘

Mysql表保存为文件方式（数据库的底层还是为文件系统！）

如果想要进行SQL级别的表备份，可以使用select into … outfile或backup table

另一种办法就是通过使用mysqldump或mysqlhotcopy

PS: InnoDB不将表的内容保存到数据库目录中；mysqlhotcopy 只适合于MyISAM表！

对于InnoDB表，可以进行在线备份，不需要对表进行锁定。而且备份的时候添加如下的两个选项：同时使用--master-data和--single-transaction提供了一个很方便的方式来进行适合点对点恢复的联机备份。

 

如果现在搭建了一个主从复制架构。当备份从机数据时，还应备份master.info和relay-log.info为文件。

为什么需要备份这两个为文件呢。因为我们在备份从机数据的时候对其做了一个锁表的操作不能有新记录进来，即不能再去同步新数据了。需要在同步完数据之后再去M机那抓数据回来同步。所以需要对这两个为文件作一次备份处理！

 

二、 备份策略

按计划定期备份。InnoDB Hot Backup为InnoDB数据文件提供在线非数据块物理备份，mysqldump提供在线逻辑备份。

示例：我们写一个计划任务定在每周的周日下午1点进行备份。因为此时的负荷较低。通过下面的命令就可以完全备份所有数据库中的所有innoDB表.

Mysqldump –single-transaction –all-databases > back.sql

这是在线非块备份，不会干扰对表的读写。我们以前假定我们的表为InnoDB（只有这种类型的表才支持–single-transaction 选项）表，因此--single-transaction使用一致性地读，并且保证mysqldump所看见的数据不会更改。

PS：一致性地读：假如有两个客户端A、B同时过来操作一个数据库。对于A的操作B是看不到的。即mysqldump只会备份所看到的。

PS：完全备份：每次完全备份可能会包括所有数据，甚至是从上一次完全备份以后没有更改的部分。所以这样的话是非常耗时间。一般是我们完成了初始完全备份后，以后的备份都是走增量备份。这样的备份为文件要小得多。备份时间也较短。

不利之处：恢复时不能只重载完全备份来恢复数据。还必须要用增量来恢复增量更改！

 

PS：如果想进行增量备份，我们需要保存增量更改。应使用-log-bin 选项启动MYSQL服务器。以便更新数据时将这些更改保存到文件中。通过加上这个选项启用二进制日志。

通过添加这个选项，就可以在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

好熟悉的为文件～

 

PS：这些都是我们所做的更改信息，被保存到了为文件中去。

 

 

每次重启，服务器用序列中的下一个编号创建一个新的二进制日志文件。当服务器运行时你还可以通过执行FLUSH LOGS SQL语句或mysqladmin flush-logs 命令，可以关闭当前的二进制日志并创建一个新为文件。

数据目录中的.index 为文件包含包含该目录下所有MYSQL二进制日志的清单。这个为文件用于复制

 

PS：记得上次每次重启MYSQL服务器就会自动产生出来一个新的bin. 为文件出来。这些为文件就是记录MYSQL每次增量操作的二进制为文件。通过这些文件能够实现增量还原数据操作的！

而index文件的作用就是一个索引文件。记录全部包含的二进制文件

 

恢复这样的增量备份。如果在完全备份时将日志记录清空了。即我们的/data/mysql/data

里面的全部二进制为文件清空了。那我们后面创建的二进制日志为文件就包含了备份后的所有数据更改。

 

shell> mysqldump --single-transaction --flush-logs --master-data=2

           --all-databases > backup_sunday_1_PM.sql

导过来的SQL文件内容为：

-- Position to start replication or point-in-time 恢复时y from

-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

（这个导出命令的含义原来如此 指定好了当前的还原点。然后从指定的还原点开始进行还原操作！）

之前在S机升级的时候还需要将当前的M值位置取出来。其实是不需要的可以直接获取其位置然后从这个点开始还原！

 

Mysql二进制日志占据硬盘空间。要想释放空间应当及时清空。操作方法是删除掉不再使用的二进制日志。

例如：

shell> mysqldump --single-transaction --flush-logs --master-data=2

           --all-databases --delete-master-logs > backup_sunday_1_PM.sql

 

完全备份还原恢复：

shell> mysql < backup_sunday_1_PM.sql

这种是非常简单的！

 

示例：假设周三上午8点出现了灾难事故。而我们最近上次完全备份的是在周日下午1点。

那我们可以先做一次完全备份恢复。那 还有几天的数据怎么办？

通过使用二进制日志文件能够恢复数据。

使用方法：shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

指定具体的二进制文件。对其进行相应的备份处理！

 

备份策略摘要

出现操作系统崩溃或电源故障时，InnoDB自己可以完成所有数据恢复工作。但为了确保你可以睡好觉，应遵从下面的指导：

·         一定用--log-bin或甚至--log-bin=log_name选项运行MySQL服务器，其中日志文件名位于某个安全媒介上，不同于数据目录所在驱动器。如果你有这样的安全媒介，最好进行硬盘负载均衡(这样能够提高性能)。

·         定期进行完全备份，使用mysqldump命令进行在线非块备份。

·         用FLUSH LOGS或mysqladmin flush-logs清空日志进行定期增量备份。

三、 指定恢复时间

通过使用mysqlbinlog 添加 –start-date 和 –stop-date  选项指定DATETIME格式的起止时间。