Submitted by gouki on 2008, December 17, 10:56 PM
为了保持作者原有的格式，和他的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上将旧的表空间废弃
(root@imysql.cn/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. 启用该表空间
(root@imysql.cn/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. 很明显，是权限的问题，修正过来，然后重新导入
(root@imysql.cn/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就可以了。
(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr RENAME TO yejr1;
Query OK, 0 rows affected (0.00 sec)
#这个时候,yejr的ID变为14
(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr1 RENAME TO yejr;
Query OK, 0 rows affected (0.00 sec)
#这个时候,yejr的ID变为15
2. 然后，我们再导入
(root@imysql.cn/17:52:47)[yejr]>ALTER TABLE yejr IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
(root@imysql.cn/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, 数据迁移, 叶金荣
DataBase | 评论:0
| 阅读:23019
Submitted by gouki on 2008, December 16, 3:32 PM
上午莫莫找我，问我有没有办法处理UTC时间，我很得意的将前两天COPY过来的，淘宝DBA的文章发给他看。说道，很简单呀，str_to_date就行了。
然而实现下来，却不正常，莫莫的时间是：05/Dec/2008:12:10:59 +0800,处理了半天都没有处理下来。因为我用的是：
SELECT STR_TO_DATE('05/Dec/2008:12:10:59 +0800' , '%d/%m/%Y');在我想来，date,month,year就行了，结果，返回给我的是NULL，死活不知道是什么问题。。。。
后来莫莫和我说，陌陌解决了这个问题，他用的是：SELECT STR_TO_DATE('05/Dec/2008:12:10:59 +0800' , '%d/%b/%Y');整个参数里就一个b和我的不一样。
才知道，也才明白，用STR_TO_DATE的时候，后面的参数和格式要和前面的参数一模一样，包括位置，包括参数的格式；虽然返回的值是：2008-12-05,但格式不对或者顺序不对，返回的肯定是NULL
记录下来，提醒一下自己，这个东西和php的strtotime不一样啊。。。
Tags: mysql, database, utc, str_to_date
DataBase | 评论:0
| 阅读:30004
Submitted by gouki on 2008, December 15, 9:36 AM
说实话，在我的应用当中，很少会用到这些MYSQL自带的时间函数，除非是在使用PHPMYADMIN的时候，于是，经常在有人问我，时间上加一天怎么办，减一天怎么办，当前月的最后一天怎么处理，上个月的今天到这个月的今天怎么搞，我都是临时翻手册来加以解释。毕竟大多数的情况下，我们都是会把这些处理交给PHP计算好再使用SQL查询，而真的很少利用自带函数。
在看到Taobao的DBA们把它们整理出来时，不禁暗暗 的YING笑了一番，以后方便了，不用找手册了。。。。。
原文：http://rdc.taobao.com/blog/dba/html/234_mysql_date_func.html
作者：丁原
内容：
通常我们会有一些时间的转换需求，比如要统计某个时间段的收入，比如要截取某个时间的年份，比如要根据某个日期推算出是星期几等个，这些都可以通过Mysql自带的时间函数很容易实现。因为我对Mysql的函数还不熟，而通常又会调用这些时间函数，这边稍加总结以便查询。
–返回当前时间
mysql> select now(),date(now()),sysdate();
+---------------------+-------------+---------------------+
| now() | date(now()) | sysdate() |
+---------------------+-------------+---------------------+
| 2008-12-02 10:11:36 | 2008-12-02 | 2008-12-02 10:11:36 |
+---------------------+-------------+---------------------+
1 row in set (0.00 sec)
mysql> select curdate(),curdate()+0,curtime(),curtime()+0;
+---------------------+-------------+---------------------+
| curdate() | curdate()+0 | curtime() | curtime()+0 |
+---------------------+-------------+---------------------+
| 2008-12-02 | 20081202 | 10:00:33 | 100033.000000 |
+---------------------+-------------+---------------------+
–返回日期当月最后一天
mysql> select last_day('2008-12-02');
+------------------------+
| last_day('2008-12-02') |
+------------------------+
| 2008-12-31 |
+------------------------+
1 row in set (0.00 sec)
–返回日期的星期几
mysql> select dayname('2008-12-02'),dayofweek('2008-12-02');
+-----------------------+-------------------------+
| dayname('2008-12-02') | dayofweek('2008-12-02') |
+-----------------------+-------------------------+
| tuesday | 3 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)
–返回日期的年，月，日
mysql> select month('2008-12-02'),year('2008-12-02'),day('2008-12-02');
+---------------------+--------------------+-------------------+
| month('2008-12-02') | year('2008-12-02') | day('2008-12-02') |
+---------------------+--------------------+-------------------+
| 12 | 2008 | 2 |
+---------------------+--------------------+-------------------+
1 row in set (0.00 sec)
–返回日期的小时，分，秒
mysql> select hour('10:05:03'),minute('10:05:03'),second('10:05:03');
+------------------+--------------------+--------------------+
| hour('10:05:03') | minute('10:05:03') | second('10:05:03') |
+------------------+--------------------+--------------------+
| 10 | 5 | 3 |
+------------------+--------------------+--------------------+
1 row in set (0.00 sec)
1.subdate(d,t)：起始时间加上一段时间（year,month,day…）
mysql> select date_add('1998-01-02', interval 31 day),adddate('1998-01-02', 31);
+-----------------------------------------+---------------------------+
| date_add('1998-01-02', interval 31 day) | adddate('1998-01-02', 31) |
+-----------------------------------------+---------------------------+
| 1998-02-02 | 1998-02-02 |
+-----------------------------------------+---------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1998-01-02',interval 2 year);
+-----------------------------------------------------+
| date_add('1998-01-02', interval 2 year)
+-----------------------------------------------------+
| 2000-01-02
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('1998-01-02', interval 2 hour);
+-----------------------------------------------------+
| date_add('1998-01-02', interval 2 hour)
+-----------------------------------------------------+
| 1998-01-02 02:00:00
+-----------------------------------------------------+
1 row in set (0.00 sec)
2.subdate(d,t)：起始时间减去一段时间
mysql> select subdate('1998-01-02', interval 31 day),subdate('1998-01-02', 31);
+----------------------------------------+---------------------------+
| subdate('1998-01-02', interval 31 day) | subdate('1998-01-02', 31) |
+----------------------------------------+---------------------------+
| 1997-12-02 | 1997-12-02 |
+----------------------------------------+---------------------------+
1 row in set (0.00 sec)
3.addtime(d,t):起始时间d加入时间t
mysql> select addtime('1997-12-31 23:59:50','00:00:05'), addtime('23:59:50','00:00:05') ;
+-------------------------------------------+--------------------------------+
| addtime('1997-12-31 23:59:50','00:00:05') | addtime('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:55 | 23:59:55 |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
4.subtime(d,t):起始时间d减去时间t
mysql> select subtime('1997-12-31 23:59:50','00:00:05'), subtime('23:59:50','00:00:05');
+-------------------------------------------+--------------------------------+
| subtime('1997-12-31 23:59:50','00:00:05') | subtime('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:45 | 23:59:45 |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
5.datediff(d1,d2):返回起始时间d1和结束时间d2之间的天数
mysql> select datediff('1997-12-31 23:59:59','1997-12-30');
+----------------------------------------------+
| datediff('1997-12-31 23:59:59','1997-12-30') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in set (0.00 sec)
6.date_format(date,format):根据format字符串显示date值的格式
mysql> select date_format('2008-12-02 22:23:00', '%y %m %m %h:%i:%s');
+---------------------------------------------------------+
| date_format('2008-12-02 22:23:00', '%y %m %m %h:%i:%s') |
+---------------------------------------------------------+
| 2008 12 12 22:23:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
7.str_to_date(str,format) 字符串转化为时间
mysql> select str_to_date('04/31/2004', '%m/%d/%y %h:%i:s');
+-----------------------------------------------+
| str_to_date('04/31/2004', '%m/%d/%y %h:%i:s') |
+-----------------------------------------------+
| 2004-04-31 00:00:00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
8.timestamp(expr) , timestamp(expr,expr2) :
对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2添加到日期或日期时间表达式 expr 中，将theresult作为日期时间值返回
mysql> select timestamp('2003-12-31'), timestamp('2003-12-31 12:00:00','12:00:00');
+-------------------------+---------------------------------------------+
| timestamp('2003-12-31') | timestamp('2003-12-31 12:00:00','12:00:00') |
+-------------------------+---------------------------------------------+
| 2003-12-31 00:00:00 | 2004-01-01 00:00:00 |
+-------------------------+---------------------------------------------+
1 row in set (0.00 sec)
9.取当天0点0分，下一天0点0分
mysql> select timestamp(date(sysdate())),timestamp(adddate(date(sysdate()),1));
+----------------------------+---------------------------------------+
| timestamp(date(sysdate())) | timestamp(adddate(date(sysdate()),1)) |
+----------------------------+---------------------------------------+
| 2008-12-02 00:00:00 | 2008-12-03 00:00:00 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)
--EOF--
Tags: mysql, database, 时间函数
DataBase | 评论:0
| 阅读:23790
Submitted by gouki on 2008, November 25, 6:13 PM
回忆未来的张宴推荐了这本书，并且在他的博客上提供了下载的链接，链接是新浪的爱问：点此下载《High Performance MySQL Second Edition》PDF电子版，并且博客上还有详细介绍：
XML/HTML代码
- High Performance MySQL Second Edition
- 作者: Baron Schwartz / Peter Zaitsev / Vadim Tkachenko / Jeremy Zawodny / Arjen Lentz / Derek Balling
-
- 副标题: Optimization, Backups, Replication, and More
- ISBN: 9780596101718
- 页数: 708
- 定价: USD 49.99
- 出版社: O'Reilly Media, Inc.
- 装帧: Paperback
- 出版年: 2008-06-18
-
- High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity.
-
- High Performance MySQL teaches you advanced techniques in depth so you can bring out MySQL's full power. Learn how to design schemas, indexes, queries and advanced MySQL features for maximum performance, and get detailed guidance for tuning your MySQL server, operating system, and hardware to their fullest potential. You'll also learn practical, safe, high-performance ways to scale your applications with replication, load balancing, high availability, and failover.
-
- This second edition is completely revised and greatly expanded, with deeper coverage in all areas. Major additions include:
- * Emphasis throughout on both performance and reliability
- * Thorough coverage of storage engines, including in-depth tuning and optimizations for the InnoDB storage engine
- * Effects of new features in MySQL 5.0 and 5.1, including stored procedures, partitioned databases, triggers, and views
- * A detailed discussion on how to build very large, highly scalable systems with MySQL
- * New options for backups and replication
- * Optimization of advanced querying features, such as full-text searches
- * Four new appendices
- The book also includes chapters on benchmarking, profiling, backups, security, and tools and techniques to help you measure, monitor, and manage your MySQL installations.
为此，张宴还提供了一张图片：
整篇博客我作了一下整理和排序，想看原文的，请到：http://blog.s135.com/read.php?381，因为在这个页面上张宴采用了划词翻译，如果有看不懂英文的话，选中一下就会自动翻译了。。我没有提供。也不想再插入一些代码。呵呵
为了方便大家，我也上传了这个文件，大家可以点击下载：high.performance.mysql_second.edition.zip
Tags: mysql, database, pdf, download
DataBase | 评论:0
| 阅读:29427
Submitted by gouki on 2008, November 25, 6:03 PM
Author:丹臣 posted on Taobao.com
mysql数据库里，对一个已创建的表进行DDL操作，比如说添加一个字段。在做测试时，发现ddl操作的时间特别的长。oracle里，通常情况下只是 修改数据字典就可以了，操作时间非常的短，阻塞DML的时间也比较短。mysql数据库对表进行ddl操作跟oracle数据库有很大的不同，它先要把原 表拷贝一份到临时表，这期间不阻塞select,阻塞所有的更改操作(update,delete,insert),对临时表ddl操作完成，删除原表， 重命名临时表。
如果一张比较大的表进行ddl变更，比如说40G，那拷贝的时间让人无法忍受，并且阻塞所有的DML操作,让业务无法继续。
以下是测试过程：
mysql> desc t1;
+-------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| nick | varchar(32) | YES | | NULL | |
| email | varchar(32) | YES | | NULL | |
| gmt_create | datetime | YES | | NULL | |
| gmt_modified | datetime | YES | | NULL | |
+-------------------+------------------+------+-----+---------+-------+
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 2228017 |
+----------+
1 row in set (1.78 sec)
现在对它进行表结构变更，增加一列：
mysql> alter table t1 add(tel varchar(20));
Query OK, 2304923 rows affected (41.03 sec)
Records: 2304923 Duplicates: 0 Warnings: 0
在上述表结构变更过程中，启动另外一个会话，进行select查询操作和一个更新操作：
mysql> select count(*) from t1;
+---------------+
| count(*) |
+---------------+
| 2304923 |
+---------------+
1 row in set (2.10 sec)
mysql> select * from t1 limit 10;
+------+-------+------------------------+----------------------------+----------------------------+
| id | nick | email | gmt_create | gmt_modified |
+------+-------+------------------------+----------------------------+----------------------------+
| 0 | nick0 | nick0@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 1 | nick1 | nick1@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 2 | nick2 | nick2@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 3 | nick3 | nick3@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 4 | nick4 | nick4@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 5 | nick5 | nick5@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 6 | nick6 | nick6@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 7 | nick7 | nick7@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 8 | nick8 | nick8@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
| 9 | nick9 | nick9@taobao.com | 2008-03-14 00:00:00 | 2008-03-14 00:00:00 |
+------+-------+------------------------+----------------------------+----------------------------+
10 rows in set (0.00 sec)
mysql> update t1 set nick='test_nick' where id=1;
Query OK, 4 rows affected (43.89 sec) --这里是阻塞的时间
Rows matched: 4 Changed: 4 Warnings: 0
通过以上实验可以看出，对表进行ddl操作时，mysql并不阻塞select查询，但会严重阻塞dml操作。另外，如果你要对表进行ddl操作，由于有一个拷贝操作，你要计算好你的可用空间够不够？如果你的系统经常要进行表结构变更，那么你将不得不要考虑此问题！
--EOF--
膘叔：说实话，一般对于表的操作往往都是在夜深人静的时候，虽然select并不影响，但为避免在更新表结构的时候更新数据，这还是必须的。。。以防万一啊
Tags: mysql, database, 修改, 表结构, taobao dba
DataBase | 评论:0
| 阅读:24064
