ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Effective MySQL之备份与恢复

Effective MySQL之备份与恢复

原创 Linux操作系统 作者:qinghuawenkang 时间:2013-10-11 10:58:44 0 删除 编辑

五分钟成为一名DBA

  如果我们已经有了一个MySQL生产级系统,而该产品却没有MySQL备份策略,那么我们至少应该做些什么呢?在采取任何备份策略之前,有许多有关数据库 大小和存储策略引擎的用法的预备知识需要了解,在执行任何备份方案期间,上述二者对于系统的可用性都会产生直接影响。

  本章将讨论在确定一个最小功能性备份时所需的方法,包括:

●      确定数据库的大小

●      确定存储策略引擎的使用

●      锁和宕机带来的影响

1.1 My SQL备份

  备份MySQL环境的策略不止一种,它们都取决于MySQL拓扑中服务器的数量。有大量的开源或商业工具软件可用于执行备份。第2章中将对这些策略进行详细讨论。

  现在我们要讨论的情形是:环境中只有一台服务器,且需要创建一个一致的备份策略。我们有两个适用于所有MySQL环境的选择方案。第一个就是把MySQL 实例停下来,然后对整个文件系统进行冷备份。这样做会导致系统在一段不确定的时间内不可用,还要保证对所有正确的信息都进行了拷贝,这些正确的信息包 括:MySQL数据、可用的事务和二进制日志数据,以及当前的MySQL配置。

  第二个选择方案是利用标准MySQL安装中所包含的一个客户端工具。使用mysqldump命令可不停止MySQL实例就能够产生一个一致的备份。但在使用mysqldump命令时,需要做出几个重要的决定,以便选定最佳方案。这些决定包括:

●      需要备份的数据库有多大?

●      要生成一个一致性备份,什么锁策略是必需的?

●      备份需要占用多长时间?

.1.1  确定数据库的大小

  执行一次MySQL备份时,需要考虑一个重要问题,那就是将MySQL备份到本地磁盘上时,这个备份有多大。需要确保有足够的磁盘空间来存储备份文件。

  通过下面的SQL语句,可以得到当前的数据和索引的总大小(以MB为单位):

mysql>SELECT ROUND(SUM(data_length+index_length)/1024/1024)

   ->          AS total_mb,

   ->          ROUND(SUM(data_length)/1024/1024)ASdata_mb,

   ->          ROUND(SUM(index_length)/1024/1024)ASindex_mb

    ->FROM    INFORMATION_SCHEMA.tables;

+----------+---------+----------+

| total_mb | data_mb | index_mb |

+----------+---------+----------+

|      927 |      847 |       80 |

+----------+---------+----------+

  执行mysqldump所 得的备份的大小大致与数据的大小相同,但为了安全起见,有10%到15%的冗余。这种计算是不精确的,然而,这一备份会产生一个数据的基于文本的输出。例 如,一个在数据库中4字节的整数,在mysqldump备份文件中就可能长达10字符字节。在执行备份的同时将备份压缩或传输到另一个不同的网络设备上是 可能的,在第2章和第8章中将对它们及相关的限制进行讨论。

  执行上述SQL语句得到的数据库中的数据的大小是847MB,后面我们会看到,用通常的默认选项执行mysqldump所得的备份文件的大小是818MB。第8章中的示例数据库的大小是4.5GB,而所产生的备份文件的大小却只有2.9GB。

1.1.2  选择锁策略

  所选择的锁策略将决定在执行备份期间,应用程序是否可以对数据库执行写操作。默认情况下,mysqldump利用LOCK TABLES命令进行表级加锁,以便确保所有数据有一个一致的版本。这取决于--lock tables命令行选项,而这一选项在默认状态下是disabled的,它是--opt选项的一部分,而--opt选项在默认状态下是enabled的。 我们可以不锁表,但这样一来,就不能保证备份的一致性了。当使用MyISAM存储引擎时,--lock-tables对于确保备份的一致性而言是非常必要 的。

  反过来,mysqldump提 供了--single-transaction选项,它可以为一个单独的事务中所有的表创建一个版本一致的快照。这一选项只有在使用某种支持多版本的存储 引擎时才可用。InnoDB是MySQL默认安装时唯一包含的存储引擎。使用这一选项时,它自动关闭--lock-tables。

下面的SQL语句将确认当前MySQL实例所使用的存储引擎:

mysql> SELECT table_schema, engine, COUNT(*) AS tables

    ->FROM    information_schema.tables

    -> WHERE   table_schema NOT IN

   ->          ('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA')

    ->GROUP BY table_schema, engine

    ->ORDER BY 3 DESC;

+--------------------+--------+--------+

|table_schema       |engine | tables |

+--------------------+--------+--------+

|shopping_cart      |MyISAM |    109 |

|cust_db            |InnoDB |     48 |

|mysql              |MyISAM |     21 |

|analytics          |InnoDB |     20 |

|phpmyadmin         |MyISAM |      8 |

|newsletter         |MyISAM |      8 |

|cust_db            |MyISAM |      3 |

|mysql              |CSV    |      2 |

+--------------------+--------+--------+

在本例中,此MySQL实例包含数个不同的支持不同功能的模式(schedule),包括一个购物车、时事新闻和管理工具。一个完整的InnoDB应用具有如下形式:

+--------------------+-------------+-------------+

| table_schema       |  engine     |      tables |

+--------------------+-------------+-------------+

| prod_db            |  InnoDB     |         122 |

| mysql              |  MyISAM     |          21 |

| mysql              |  CSV        |           2 |

+--------------------+-------------+-------------+

如本例所示,mysql元模式使用MyISAM,这是无法改变的。如果数据库使用了完整的InnoDB,则将会有两种处理MyISAM的mysql表的选择,本章后续部分将对此进行讨论。

1.1.3  运行时间

确定备份需要消耗多长时间是最重要的需求。没有什么计算方法可以给出精确答案。数据库的大小、系统的RAM的容量、所使用的存储引擎、MySQL的 配置、硬盘的速度以及当前的工作负载等都会影响到计算结果。在执行备份时收集这种类型信息的重要意义在于将来要用得到它们。运行时间是重要的,这是因为它 是维护数据库的有效窗口。在数据库备份期间,可能会存在应用程序的功能限制、性能开销等,而且备份还可能限制了其他操作,如批处理或软件维护等。

1.1.4  组合信息

下面是一条推荐的SQL语句,它会把所有信息组合起来,以便对数据库大小进行审核:

$cat  storage_engines.sql

SELECT table_schema, engine,

         ROUND(SUM(data_length+index_length)/1024/1024) AStotal_mb,

         ROUND(SUM(data_length)/1024/1024) AS data_mb,

         ROUND(SUM(index_length)/1024/1024) AS index_mb,

        COUNT(*)  AS tables

FROM  information_schema.tables

GROUP BY  table_schema,  engine

ORDER BY  3  DESC;

 

mysql> source  storage_engines.sql

+--------------------+--------+-------+-------+-----+-----+

| table_schema |engine | total_mb | data_mb | index_mb | tables |

+--------------------+--------+-------+-------+-----+-----+

| analytics          | InnoDB | 10930 | 10525 | 378 |  20 |

| cust_db            | InnoDB |  1155 |   962 | 194 |  48 |

| newsletter         | InnoDB |   514 |   278 | 237 |   7 |

| shopping_cart      | MyISAM |    27 |    19 |   8 | 109 |

| cust_db            | MyISAM |     9 |     3 |   7 |   3 |

| mysql              | MyISAM |     1 |     0 |   0 |  21 |

| information_schema | MyISAM |     0 |     0 |   0 |   8 |

| information_schema | MEMORY |     0 |     0 |   0 |  20 |

| mysql              | CSV    |     0 |     0 |   0 |   2 |

+--------------------+--------+-------+-------+-----+-----+


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26421423/viewspace-774134/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
分享计算机前沿技术和国外计算机先进技术书籍。

注册时间:2011-11-08

  • 博文量
    54
  • 访问量
    104905