56,679
社区成员
发帖
与我相关
我的任务
分享mysql 做数据备份分离问题----100分
有一个这样的需求：
由于数据库中表的数据量很大，现基本都在 100W+， 随着时间的推移线性增长，为了降低数据库中的数据量，减轻压力，需要按日期定时将过期数据分离备份，就是建历史库的那种，周期是一个月到两个月不等。主要是为了保证线上数据库中数据量 维持在一个范围内，过期备份的数据可从线上拖下来在本地分析查询。
需求如上所述， 我的思路是 查出指定日期之前的所有数据，把数据导出到文本， 然后从库中删除这部分数据，每月导出一个文本文件，然后把文本下载到本地 ，需要分析哪一个月的就导入哪一个月的数据。
不知道这样做是否可行？ 还未实践这个思路， 我感觉是有问题的 在我导出数据到文本的时候还要删除数据， 而这个时候系统可能在往数据库中写数据，这样会不会出问题？ 如果我锁了表再操作，这样会不会丢失掉本来要写入库的数据？
不知道我的思路是否正确，求大神指点啊 。。。
由于数据库中表的数据量很大，现基本都在 100W+， 随着时间的推移线性增长，为了降低数据库中的数据量，减轻压力，需要按日期定时将过期数据分离备份，就是建历史库的那种，周期是一个月到两个月不等。主要是为了保证线上数据库中数据量 维持在一个范围内，过期备份的数据可从线上拖下来在本地分析查询。
需求如上所述， 我的思路是 查出指定日期之前的所有数据，把数据导出到文本， 然后从库中删除这部分数据，每月导出一个文本文件，然后把文本下载到本地 ，需要分析哪一个月的就导入哪一个月的数据。
不知道这样做是否可行？ 还未实践这个思路， 我感觉是有问题的 在我导出数据到文本的时候还要删除数据， 而这个时候系统可能在往数据库中写数据，这样会不会出问题？ 如果我锁了表再操作，这样会不会丢失掉本来要写入库的数据？
不知道我的思路是否正确，求大神指点啊 。。。
...全文
请发表友善的回复…
发表回复
小青年 2013-06-07
- 打赏
- 举报
直接进行分区文件操作, 是咋操作呢，不太明白
小青年 2013-06-06
- 打赏
- 举报
OK ，结贴。。谢谢两位
wwwwb 2013-06-06
- 打赏
- 举报
会不会阻塞系统线程？？
会
多增加几块硬盘（阵列），在硬盘上建立分区表（按月 OR 年）更合理一些
小青年 2013-06-06
- 打赏
- 举报
会不会阻塞系统线程？？？要等待呀
小青年 2013-06-06
- 打赏
- 举报
恩，表分区貌似比较合理点， 正在看
wwwwb 2013-06-06
- 打赏
- 举报
MYSQL来控制，等待写入
小青年 2013-06-06
- 打赏
- 举报
那么 谁来控制等待锁释放再写入呢？系统写库的时候表被锁，会等待写入么？ 还是直接不写了。
ACMAIN_CHM 2013-06-06
- 打赏
- 举报
18.1. MySQL中的分区概述
本节提供了关于MySQL 5.1.分区在概念上的概述。
SQL标准在数据存储的物理方面没有提供太多的指南。SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是，大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中，InnoDB存储引擎长期支持表空间的概念，并且MySQL服务器甚至在分区引入之前，就能配置为存储不同的数据库使用不同的物理路径(关于如何配置的解释，请参见7.6.1节，“使用符号链接”)。
分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。分区函数的例子可以在本章后面关于分区类型的讨论中找到 (请参见18.2节，“分区类型” )，也可在13.1.5节，“CREATE TABLE语法”的分区语法描述中找到。
当二进制码变成可用时（也就是说，5.1 -max 二进制码将通过--with-partition 建立），分区支持就将包含在MySQL 5.1的-max 版本中。如果MySQL二进制码是使用分区支持建立的，那么激活它不需要任何其他的东西 (例如，在my.cnf 文件中，不需要特殊的条目)。可以通过使用SHOW VARIABLES命令来确定MySQL是否支持分区，例如：
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
1 row in set (0.00 sec)
在如上列出的一个正确的SHOW VARIABLES 命令所产生的输出中，如果没有看到变量have_partition_engine的值为YES，那么MySQL的版本就不支持分区。（注意：在显示任何有关分区支持信息的命令SHOW ENGINES的输出中，不会给出任何信息；必须使用SHOW VARIABLES命令来做出这个判断）。
对于创建了分区的表，可以使用你的MySQL 服务器所支持的任何存储引擎；MySQL 分区引擎在一个单独的层中运行，并且可以和任何这样的层进行相互作用。在MySQL 5.1版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用MyISAM，而对另一个使用InnoDB。但是，这并不妨碍在同一个 MySQL 服务器中，甚至在同一个数据库中，对于不同的分区表使用不同的存储引擎。
要为某个分区表配置一个专门的存储引擎，必须且只能使用[STORAGE] ENGINE 选项，这如同为非分区表配置存储引擎一样。但是，必须记住[STORAGE] ENGINE（和其他的表选项）必须列在用在CREATE TABLE语句中的其他任何分区选项之前。下面的例子给出了怎样创建一个通过HASH分成6个分区、使用InnoDB存储引擎的表：
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH(MONTH(tr_date))
PARTITIONS 6;
（注释：每个PARTITION 子句可以包含一个 [STORAGE] ENGINE 选项，但是在MySQL 5.1版本中，这没有作用）。
创建分区的临时表也是可能的；但是，这种表的生命周期只有当前MySQL 的会话的时间那么长。对于非分区的临时表，这也是一样的。
注释：分区适用于一个表的所有数据和索引；不能只对数据分区而不对索引分区，反之亦然，同时也不能只对表的一部分进行分区。
可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。此外，MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。关于这些选项的更多信息，请参见18.3节，“分区管理”。注释：这个特殊的功能由于Bug #13250的原因，目前还不能实用。在第一个5.1二进制版本投入使用时，我们应该已经把这个问题解决了。
分区的一些优点包括：
· 与单个磁盘或文件系统分区相比，可以存储更多的数据。
· 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。
· 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
· 涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
· 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
要经常检查本页和本章，因为它将随MySQL 5.1后续的分区进展而更新。
18.2. 分区类型
18.2.1. RANGE分区
18.2.2. LIST分区
18.2.3. HASH分区
18.2.4. KEY分区
18.2.5. 子分区
18.2.6. MySQL分区处理NULL值的方式
本节讨论在MySQL 5.1中可用的分区类型。这些类型包括：
· RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。参见18.2.1节，“RANGE分区”。
· LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。参见18.2.2节，“LIST分区”。
· HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。参见18.2.3节，“HASH分区”。
· KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。参见18.2.4节，“KEY分区”。
无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。
分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。但是应当注意，分区的名字是不区分大小写的。例如，下面的CREATE TABLE语句将会产生如下的错误：
mysql> CREATE TABLE t2 (val INT)
-> PARTITION BY LIST(val)(
-> PARTITION mypart VALUES IN (1,3,5),
-> PARTITION MyPart VALUES IN (2,4,6)
-> );
错误1488 (HY000): 表的所有分区必须有唯一的名字。
这是因为MySQL认为分区名字mypart和MyPart没有区别。
注释：在下面的章节中，我们没有必要提供可以用来创建每种分区类型语法的所有可能形式，这些信息可以在13.1.5节，“CREATE TABLE语法” 中找到。
18.2.1. RANGE分区
按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创
ACMAIN_CHM 2013-06-06
- 打赏
- 举报
其实这个操作不叫备份，更常见的数据库说法是 归档 archiving
将明细数据从在线表、库中移到历史表、库中去。
如果单从MYSQL本身角度来说，更通用的做法是直接使用分区表，不同月份的表中在不同的表分区中。如果服务器容量或者其它原因想把历史数据从在线表中分离，则可以直接进行分区的文件操作。
wwwwb 2013-06-06
- 打赏
- 举报
思路可行，不过多增加几块硬盘（阵列）更好
在导出、删除时应该选择使用少的时间段进行
在我导出数据到文本的时候还要删除数据， 而这个时候系统可能在往数据库中写数据，这样会不会出问题？
会
如果我锁了表再操作，这样会不会丢失掉本来要写入库的数据？
不会，等待锁释放再写入
