手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表Tag:innodb

InnoDB数据表空间文件平滑迁移

为了保持作者原有的格式,和他的URL版本的邮箱,我就不做更改了。其实世界上很多事情都是那么巧的,php focus群里的朋友还在问我数据库是否能够直接拷了就能使用。我的解释是4.0以下的版本都可以拷的,从4.1开始尽量使用mysqldump,但是sundj说,对于MYISAM类型的数据库和表,是可以直接COPY的。对于INNODB则不行。然后打开日常订阅的一些RSS,就发现了这篇文章 。。。套用范伟老师的话:缘份呐

原文如下:

作/译者:叶金荣(Email: ),来源:http://imysql.cn,转载请注明作/译者和出处,并且不能用于商业用途,违者必究。

前言

InnoDB存储引擎满足了MVCC和ACID特性,在需要支持事务的环境下必不可少。有些环境下,采用InnoDB可能效果比MyISAM还要来 的好。不过,在很多人眼中看来,InnoDB表空间文件由于无法实现跨服务器平滑迁移,因此不愿意使用。实际情况真是这样吗?本文就来探讨一下 InnoDB表空间文件的平滑迁移可能性。

如何迁移?

从MySQL文档中我们了解到,InnoDB的表空间可以是共享的或独立的。如果是共享表空间,则所有的表空间都放在一个文件 里:ibdata1,ibdata2..ibdataN,这种情况下,目前应该还没办法实现表空间的迁移,除非完全迁移,因此不在本次讨论之列;我们只讨 论独立表空间的情况。

不管是共享还是独立表空间,InnoDB每个数据表的元数据(metadata)总是保存在 ibdata1 这个共享表空间里,因此该文件必不可少,它还可以用来保存各种数据字典等信息。数据字典中,会保存每个数据表的ID号,每次发生数据表空间新增时,都会使 得该ID自增一个值(++1),例如:CREATE TABLE xx ENGINE = InnoDB / ALTER TABLE xx ENGINE = InnoDB 都会使得ID值增加。
有了上面的理解,想要实现InnoDB表空间文件的平滑迁移就很容易了,呵呵。下面是一些例子:
假定我们有2台DB主机,一个是A,一个B;现在想把A上的某个InnoDB表空间文件迁移到B上直接用。

一、迁移失败的例子

直接从A上把表空间文件 yejr.ibd 拷贝到 B 上后,导入表空间,报错,无法使用。这是由于A,B上创建该表时的顺序不一致,导致表的ID不一样,无法导入。
注意:,在这里,表空间文件直接拷贝的前提是该表空间处于"干净"状态下,也就是所有的数据均已经刷新到磁盘中,否则可能导致无法使用或部分数据丢失。
1. 在B上将旧的表空间废弃

([email protected]/17:52:47)[yejr]>ALTER TABLE yejr DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

2. 拷贝到目标机器

scp yejr.ibd B:/home/mysql/yejr/yejr.ibd
....

3. 启用该表空间

([email protected]/17:52:47)[yejr]>ALTER TABLE yejr IMPORT TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine

4. 查看错误

InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './test/b.ibd'!
InnoDB: Error: cannot reset lsn's in table `test/b`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

5. 很明显,是权限的问题,修正过来,然后重新导入

([email protected]/17:52:47)[yejr]>ALTER TABLE yejr DISCARD TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine

6. 怎么还是错误?继续看日志

InnoDB: Error: tablespace id in file './yejr/yejr.ibd' is 15, but in the InnoDB
InnoDB: data dictionary it is 13.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `yejr/yejr`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

从上面的日志得知,由于在A服务器上,yejr表的ID是15,而在B服务器上,yejr表的ID却是13,二者不一致,因此迁移失败。
既然只是因为ID不一样,而且有了上面的理论基础,我们完全可以人为的让它们的ID一致嘛,请看下面的第2次尝试。

二、人工干预下的成功迁移

1. 上面的例子中,B上面的yejr表ID为13,而A上面为15;因此只需要让B上的yejr表ID增加2就可以了。

([email protected]/17:52:47)[yejr]>ALTER TABLE yejr RENAME TO yejr1;
Query OK, 0 rows affected (0.00 sec)
#这个时候,yejr的ID变为14
([email protected]/17:52:47)[yejr]>ALTER TABLE yejr1 RENAME TO yejr;
Query OK, 0 rows affected (0.00 sec)
#这个时候,yejr的ID变为15

2. 然后,我们再导入

([email protected]/17:52:47)[yejr]>ALTER TABLE yejr IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
([email protected]/17:52:47)[yejr]>select count(*) from yejr;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)

看到了吧,成功了,呵呵。想要让他ID增加的方式也可以重复创建表,根据实际情况或者个人喜好而定了。
以上测试均在mysql 5.0.67版本下通过,只不过显示数据稍作处理了。

原文URL:http://imysql.cn/2008_12_17_migrate_innodb_tablespace_smoothly

Tags: innodb, mysql, database, 数据迁移, 叶金荣

MySQL主从服务器的一些技巧

问题:主从服务器表类型的选择

一般的共识是主服务器使用innodb,从服务器使用myisam,以便各尽其能。

问题:主从服务器字段类型的选择

字段类型对于分页等操作有很大影响。主服务器一般是innodb,因为不涉及查询,所以可以使用varchar等来存储字符串来节省空间,从服务器一般是 myisam,因为涉及查询,所以必须在char和varchar之间仔细权衡,没有varchar, text, blob字段的表是静态表,反之是动态表,静态表的检索效率要比动态表好若干倍,一般来说,所有涉及大结果集的查询都应该尽可能保证在静态表上完成,这里 说一个例子:比如说常见的articles表有title(varchar), body(text)等字段,在做文章列表的时候,因为不是静态表,所以查询不会很快,下面开始重构解决方案:把原来的articles表拆分成 subjects表和contents表,title字段设置为一个足够的char类型放在subjects表里,body字段还保持是text类型放到 contents表里,subjects和contents表之间的关系是一对多,这样,顺带着也方便的实现了多页文章的功能,而且更重要的是在查询文章 列表的时候,操作都是在subjects静态表里完成,效率肯定会比前一种方案提升很多。

问题:主从服务器NOW()函数造成数据不一致

假设在主服务器上执行一条INSERT .... VALUES ( ..., NOW()),那么在从服务器上也会同样执行一条的SQL语句,但是一来主从服务器各自的时间设置可能就不一致,二来主从服务器间的SQL同步也可能存在 时间上的的延迟,这样,NOW()在两台服务器上的结果就可能不一致。解决方法是显而易见的,就是不要使用NOW(),时间的计算在应用程序里完成。这里 介绍一个额外的小技巧:在PHP里如果想获得当前时间的时间戳,不要用time(),而应该使用$_SERVER[‘REQUEST_TIME’] (PHP版本大于5.1有效),这样少做了一次系统调用,更有效率。

问题:主从服务器读写分离时读操作失败

先重现一下问题:比如说添加一条新数据,添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是在主服务 器上完成的,浏览新数据的操作实在从服务器上完成的,不过由于主从服务器间SQL同步存在延迟,所以当使用last_insert_id在从服务器上查询 的时候,从服务器很可能还没有还没来得及同步到此记录,所以读操作失败。解决思路也不复杂,在代码里加入一个缓存层(可以使用memcached),新添 加的数据都顺手放到缓存层里一份,新数据的读操作也先查询缓存层,这样就不会再有读操作失败的问题了,当然删除或者更新数据的时候也要顺带着处理好缓存数 据,可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录,对于读取列表的记录的情况,则是无效的。

问题:主从服务器索引是否有必要保持一致

一般都是利用主从服务器完成读写分离,从服务器上进行读操作,主服务器进行写操作,这样的话,主服务器上仅保留主键,外键,唯一索引等必要的索引即可,以 便保持数据合法性,而对于那些原本用于优化SELECT操作的索引,可以全部删除,如此的话主服务器的写操作效率会提升很多。

作者:老王
原文:http://hi.baidu.com/thinkinginlamp/blog/item/5d72dd5469b1885fd0090633.html

Tags: database, mysql, 主从数据库, myisam, innodb

精通MYSQL数据库——连载三

 前两天我们介绍了myisam和innodb两种类型的表结构,可是:to be or not to be ,that's a question。

在我的实际应用中,我到底应该选择哪个呢?用myisam还是innodb,确实是让人伤脑筋的问题。所幸,这两种表结构可以存在于同一个数据库中,DBA们也就可以根据实际应用来设计数据表是使用哪种类型的了。
虽然一般情况下认为myisam的存取速度超过innodb,但这也不是绝对的。确实innodb所占用的空间要比myisam大的多,但是相对的,innodb在存储数据时,是行锁定,而并非myisam的表锁定,所以,在这一点上,又不能说谁快谁慢了。
相对于安全性方面,innodb毫无疑问是首选,至少不用再担心多人同时操作表时会发生什么意外的状况了。可是从节约时间和空间上来选择,myisam又是远超innodb。
究竟应该选用哪种呢?实在是一个非常头疼的问题。所以说,一般情况下只能根据系统的需求来决定到底使用哪种类型的表结构。如果你为了追求速度,认为数据丢失一条两条无所谓,错误一下问题也不大,那么毋庸置疑,myisam是首选。如果需要用到事务,那innodb就是不得不选的。如果你实在没法确定需要哪种类型的表结构,那么,创建两个不同类型的表,逐步增加数据量的大小,多测试几个重要的SQL(以后系统中可能会使用的SQL),看看哪个效率更高就选择哪个。
虽然这些都是根据特性来选择的,但我们也不能忽略服务器自身的配置。如果服务器是8核、12G内存,却只用来处理一个小小的留言板,你还会在乎是使用哪种类型的表结构吗?

官方网站上也有关于这两种数据表在细节方面的优劣对比:http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html
 
随便说说而己。。。明天继续介绍heap表和一些不常用的表结构类型。
 

Tags: mysql, innodb, myisam, 选择, 连载

精通MYSQL数据库——连载二

(今天晚上要读书,所以先发上来)

 昨天我们讲的是myisam表的特性,今天我们来讲讲innodb的一些特性。

之所以要采用innodb,是因为innodb有一些新的特性,而这些特性又是myisam所不具备的,它们是:事务、数据行级锁定、外键约束、崩溃恢复
看到上面这些特性,你是不是基本了解了innodb所应用的范围了?是的,innodb所具备的这些特性,使得mysql即使在高端应用也有了用武之地,特别是在处理类似银行这些对事务非常关心,也非常重要的场合(存钱取钱以及转帐等),下面就对这些特性进行简单介绍。
在介绍特性之间,先说明一下,innodb虽然一直是MSYQL的集成组件之一,但事实上,innodb的驱动的研发和收费技术支持都是来自innodb公司:http://www.innodb.com,这可是一家独立的公司。
事务:在innodb里所有的操作都可以被看成是在执行一个事务,它允许把几条有内在逻辑关系的SQL当成一个整体来执行。所以执行期间发生错误,则所有的命令会全部失效,就好象从未执行过任何SQL一样,而不仅仅是导致错误的那一条命令。正因为这样,所以事务功能相对增强了数据库的安全性。mysql支持全部的4种事务级别(READ UNCOMMITTED,READ COMMITED,REPEATABLE READ , SERIALIZABLE)
数据行级锁定:在执行事务时,innodb表的驱动程序会使用内置的行级锁定机制,而不是采用mysql自身的行级锁定机制。也就是说即使在执行事务时,其他进程仍然可以访问它,因为被锁定的仅仅是那些正在接收事务处理的记录。这与mysql的lock table不一样,lock table执行完后,整个表都被锁定了。这两种方式的对比可以让你看出,如果有一大堆人在处理一个很大的数据表,孰优孰劣一看便知。并且,innodb的这个驱动相对比较先进的就是,它能够自动识别“死锁”(所谓死锁,就是两个进程各战胜一项对方需要的资源,同时又在等待对方先释放所占用的资源,结果导致两个进程都不能执行),并自动终止两个进程中的一个。这才是innodb的强项。
外键约束:如果在数据表间定义了关联,innodb驱动将自动保证数据表的引用一致性,即便在执行过delete后也能同样保持。也就是说,不可能出现在A表的记录引用B表一条并不存在的记录,这就是所谓的:外键约束
崩溃恢复:理论上,在数据库发生崩溃后,只要计算机的文件系统没有被破坏,innodb数据表能够迅速地自动恢复到一个稳定可用的状态。说明一下,这只是理论上,具体怎么样,我没有测试过,也没有搜索到相关文章,如果有人搜索到相关的资料,请告知,以便我更新一下,谢谢。
 
说了这么多的特性,下面也要谈谈它的缺点,毕竟凡事都有双面性,有优点自然也会存在缺点,否则,谁还会用myisam?
使用innodb有以下几个缺点:
1、表空间的管理。与myisam分成三个文件的存储方式不一样,innodb把所有的数据、索引、结构都存在了一个表空间里。当然,表空间可以是一个或多个文件组成,也就是说这些文件组成了一个虚拟的文件系统。然而问题就出在这里,这些文件被创建后只能增长,不能缩小。如果想复制innodb表,只有使用mysqldump来进行处理,而myisam在停掉数据库后,直接复制就可以了(好象到了4.1或5.0后,这样的直接复制好象不行了?我是指两个表的设置如果不一样的话)
2、数据记录的长度。innodb的单条记录最多占用8000个字节。这个限制不包括text和blob等类型字段,事实上,这此字段只有前512个字节和其他数据存在数据库里,超过这个长度的内容都是被存储在上面所说的“虚拟文件系统——表空间”的其他文件里面。在这里,有一个回复,也可以参考一下,虽然不一定完全正确:http://imysql.cn/node/548,呵呵。
3、存储空间。在内容相同的情况下,一般来说innodb所占用的空间要比myisam所占用的大的多,最多时,可能会有两倍。
4、全文索引。这恐怕是它最大的缺憾了,innodb不支持full-text index。
5、GIS数据。存储和读取这个,还是myisam来的更方便一点,所以innodb也不支持它。
6、COUNT相关。innodb在处理SELECT COUNT(*) FROM table的时候,比myisam慢得多,这主要是因为它支持事务。可是,我在用sql server的时候也没有慢很多嘛。不知道它何时能够被解决,否则在内容较大的时候,处理分页就烦了。
7、表锁定。因为innodb有自己的表锁定算法,所以想使用表锁定功能时,用innodb所支持的select .. for update或select .. IN share mode,而不要使用mysql 自带的lock table。
8、虽然有这么多好处,可惜mysql这张表,还是得用myisam格式。而不能使用innodb
9、费用。。。。对于我们个人来说,我们只需要使用免费的mysql,而不是使用商业版的mysql,那么收费就与我们无缘了。至于商业应用嘛,那,钱是少不了的,听说,如果在mysql许可证里加上innodb支持,将收取双倍费用。好恐怖。。。
 
上面的这些也是我在参考书本后,进行的整理,否则,凭我这些小小的经验,怎么可能了解这么多?

Tags: mysql, database, innodb, myisam, 连载

Records:912