MySQL线上600W纪录的MyISAM表,要把存储引擎改为innoDB,一alter表就立马锁死的问题探讨...

行业内部朋友企鹅上问:

Sun Shine:

我线上有个数据库大约600百万数据我想把存储引擎改为innoDB, 现在表平均每分钟有50个插入,已修改表就立马锁死,有什么好的办法吗?

黄杉 11:27:31
那肯定会锁表啊，你全天业务低峰期是在哪个时间短啊? ltertabletbnameengine=innodb;就是全锁表的

Sun shine11:28:33
很平均的现在相对数据少点

黄杉()11:28:57
如果这样的话，你的模式是ms 还是mm呢？ 果是这样子的话，可以先在一台从上面做，然后主从切换

黄杉()11:30:05
线上生产库竟然没有主从机制？

Sun shine11:30:11
如果整表改的话 需要多久
对的，下一步准备做主从复制。

黄杉()11:31:32
这样子，你在测试库 test库里面建一个临时表，把现在600W的纪录倒入到临时表里面，先做一下alter table tbname engine=innodb; 试下看需要多久

Sun shine11:32:15
我试试下，其他还有什么好的办法没？

黄杉()11:32:30
有啊，用临时表啊

Sun shine11:33:02
好的 我试试。谢谢

黄杉()11:33:14
1 ，在test库上面建立一个临时表，表结构跟600W的纪录的表结构一样，只是engine=innodb
2， 将600W纪录导入到临时表里面
3，在原表上建立触发器，同步数据到临时表里面
4， 观察一段时间，看原表与临时表数据是否一致
5 ，观察完毕，稳定后，执行rename table xxx.tbname to test.tbname_old_myisam;rename table test.tbname to xxx.tbname; 底切换过来，当然这个过程会有2秒的down time

Sun shine11:36:33
恩好的 2秒的话 没问题。

黄杉()11:36:39
你才 1tps，估计这最多2秒的down time 对你几乎没有影响的

Sun shine11:36:50
对的。

黄杉()11:37:14
你可以先在测试环境试试我这个方案

Sun shine11:37:35
恩ok, 怎么对单个表的 不锁表备份 以及导入

黄杉()11:55:17
你的是myisam吧，直接copy文件啊，然后数据库重起一下，copy数据文件到test库的目录下，然后重起mysql数据库，test库里面就有数据了

黄杉()11:56:18
这是最野蛮的做法

Sun shine11:56:33
好强悍。

黄杉()11:56:35
还有就是用mysqldump以及mysql导入
mysqldump -uadmin -p -P3307 -h127.0.0.1 --default-character-set=utf8 --extended-insert=false os ulc> /home/check/ulc.sql 类似这样的导出
我以前在生产库就是如此操作的，很快就导出来了，记住加-d参数，只导出数据，不导出表结构

黄杉()12:00:43
还有一种方案，就是不用导入导出了，在原表上写一个触发器，数据同时更新到临时表里面

Sun shine12:01:39
看的不是太懂，就是我有个表库是 ota表示 otalog 单独到处这个表然后再导入到另一库 test、

黄杉()12:01:58
是的,test库的表engine=innodb

Sun shine12:03:03
mysqldump -uadmin -p -P3307 -h127.0.0.1 --default-character-set=utf8 --extended-insert=false os ulc> /home/check/ulc.sql 这个怎么改?

黄杉()12:03:49
mysqldump -uadmin -p -P3307 -h127.0.0.1 --default-character-set=utf8 --extended-insert=false -d ota otalog> /home/check/ulc.sql

黄杉()12:04:59
-u后面跟的是用户名，-p后面跟的是密码

Sun shine12:05:11
恩好的。

Share出来，感觉有些地方回答不是太好，欢迎大家一起交流探讨。

总结：

1 线上要主从或者主主，不能单机

2 600W的记录，如果一分钟insert 50条的话，一天增长 SELECT 24*60*50 = 72000，7W条纪录，一年增长SELECT 24*60*50*365=26280000，2千万纪录。个人觉得可以考虑分表或者分区策略了。

3 看表的命名是log结尾的，估计是日志系列的业务，可以考虑存到mongodb这种nosql里面，减轻MySQL的压力。