有效的MySQL备份与恢复

【TechTarget中国原创】如果您接手了一个 MySQL生产系统，但不确定它是否运行了MySQL备份策略，这时需要做哪些保障措施呢？在实施备份策略之前，一定要明确数据规模和存储引擎使用等先决条件。这会对系统在备份过程中的可用性产生直接影响。

在本文中，我们将介绍用于确定最小备份功能所需要的方法，其中包括：

• 确定数据库规模
• 确定存储引擎使用率
• 锁定和停机时间影响

MySQL备份方法

备份MySQL环境的策略有很多，这些策略与MySQL拓扑的服务器数量相关，有许多开源和商业工具可以执行备份操作。

假设目前您有一个单服务器环境，希望创建一个统一备份，那么您现在可以在所有MySQL环境中采用两种备份方法。第一种方法是停止MySQL实例，然后对整个文件系统执行冷备份。这样会让系统在特定时间段变成不可用状态，而且您必须确保复制了所有信息，其中包括MySQL数据、事务与二进制日志文件（如果有的话）和MySQL的当前配置。

第二种方法是使用MySQL标准安装所包含的客户端工具。使用mysqldump命令，可以在不停止MySQL实例的前提下创建一个统一的MySQL备份。然而，在运行mysqldump之前，必须考虑几个重要问题，才能够选择最佳的备份方法。

• 所备份数据库的大小是多少？
• 生成统一备份需要使用哪一种锁策略？
• 备份用时多久？

确定数据库大小

执行MySQL备份需要考虑的一个重要问题是备份到本地磁盘的备份文件大小。这要求您准备足够存储备份文件的磁盘空间。

运行下面的 SQL语句，可以获得当前数据与索引的总大小（单位为MB）：

SELECT round(sum(data_length+index_length)/1024/1024)
AS total_mb,
round(sum(data_length)/1024/1024) AS data_mb,
round(sum(index_length)/1024/1024) AS index_mb
FROM INFORMATION_SCHEMA.tables;

　　mysqldump备份大小大约为总数量加上10%~15%的预留量。这里并不存在精确计算大小的方法；然而，备份会生成以文本方式保存的数据。例如，数据库中4字节的整数可能会在mysqldump备份文件中占10个字符大小。在备份过程中，也可以同时压缩备份文件，或者将它传输到其他网络设备上。

运行这个SQL语句，得到的数据库数据大小为847MB。作为将来的参考，这里使用常用默认设置运行mysqldump，得到的备份文件大小为818MB。

选择锁策略

所选择的锁策略将确定应用程序是否可以在备份过程中执行数据库写操作。默认情况下，mysqldump会使用LOCK TABLES命令执行表一级的锁，以保证所有数据的统一性。这个命令由命令行选项--lock-tables指定，但是它默认是未启用的。这个选项属于默认启用的选项--opt。您可以选择不锁定表；然而，它还不足以保证实现统一的备份。在使用MyISAM存储引擎时，必须使用--lock-tables，才能保证实现统一的备份。

此外，mysqldump还提供了--single-transaction选项，它可以在一个事务中创建所有表的统一快照。这个选项只适用于支持多版本存储的存储引擎。InnoDB是唯一默认包含的存储引擎。

SELECT table_schema, engine, COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema NOT IN
('INFORMATION_SCHEMA', 'PERFORMANGE_SCHEMA')
GROUP BY table_schema, engine
ORDER BY 3 DESC;

　　MySQL安装环境也同样适用，指定这个选项会自动关闭--lock-tables。

执行面的SQL语句，可以确定MySQL实例所使用的存储引擎：

在这个例子中，MySQL实例拥有多个不同的模式，它们支持各种不同的功能，其中包括购物车、新闻邮件和管理工具。一个全InnoDB应用程序的模式可能像下面这样：

　　从上面可以看出，mysql元模式使用MyISAM。这个实例的存储引擎不能修改。如果数据库采用全InnoDB引擎，那么您有两个与备份MyISAM mysql表相关的方法。

执行时间

最重要的一点是确定备份所需要的时间。没有任何方法可以精确计算出备份时间。数据库大小、系统RAM数量、所使用的存储引擎、MySQL配置、硬盘速度和当前负载都会影响计算结果。在执行备份时，一定要收集这些信息，以备将来使用。执行时间很重要，因为这是数据库的有效维护时间窗口。在数据库备份过程中，应用程序的功能可能会受到影响，备份过程可能会产生性能过载，而且备份可能会影响其他的操作，其中包括批处理或软件维护。

汇总信息

SELECT table_schema, engine,
ROUND(SUM(data_length)/1024/1024) AS total_mb,
ROUND(SUM(data_length)/1024/1024) AS index_mb,
COUNT(*) AS tables
FROM information_schema.tables
GROUP BY table_schema,engine
ORDER BY 3 DESC;

　　上面是一条推荐使用的SQL语句，它汇总了所有用于确定数据库大小的信息。

转自： http://www.searchdatabase.com.cn/showcontent_66648.htm