Mysql数据库引擎mysiam转为innodb(笔记)

最近没有太多的事情可忙,就正好整理整理以前的笔记了。这是刚毕业时在老东家提的一个转换mysql引擎的建议,现在也都过去快2年了,再看看还是有点意义的,就整理下发到博客。记得当时还用邮件把它发送给了老大,私下也说过线上使用mysql时最好默认使用innodb引擎,很现实的问题mysiam的表级锁是个很蛋疼的问题,业务量稍微大点,show full processlist看下,一堆的锁表语句卡在那里…

一: 转引擎前的准备工作

1.继续分库,把一部分使用量小的数据库分到204上,把204作为一个主库,210上留下尽可能少的数据库
2.检查转为innodb后会不会对现在的业务产生影响,myisam和innodb引擎的主要差别有:
  a.InnoDB不支持FULLTEXT类型的索引。
  b.InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
  c.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
  d.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
  e.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用

参考资料:

http://jeck2046.blog.51cto.com/184478/90499

http://www.ha97.com/4197.html

对于innodb的select count(*) from table: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

优化方法有:

a.减少select count(*) from table;的执行次数,数值存入redis,过期时间设置长些

b.查看康盛的做法是读取information_schema这个表里的统计信息(模糊值,不精确)
use information_schema;
select table_rows as rows from information_schema.tables where table_schema='dbname' and table_name='tablename';

c.show table status like 'tablename'

参考资料:

http://dev.mysql.com/doc/refman/5.1/zh/information-schema.html

http://hi.baidu.com/mlxiangbala/item/ad0a01c6ba3a3d44a9ba94e4

http://blog.csdn.net/jiaxiaolei19871112/article/details/7161799

二: 开始转换mysiam为innodb

1.备份数据库所有数据,防止出错
innobackupex --user=root --password=password --defaults-file=/etc/my.cnf /data/data_bak/

2.转换表引擎
主要使用mysql_convert_table_format这个脚本批量转换
a.修改/usr/local/mysql/bin/mysql_convert_table_format,将$opt_engine="MYISAM"改为$opt_engine="INNODB";
b.转换数据库引擎(批量将dbname的所有表引擎转为INNODB):
/usr/local/mysql/bin/mysql_convert_table_format dbname --user=root --password=password --socket=/tmp/mysql.sock

3.主库转完引擎后,在主库备份,重新做主从
a.主库备份
innobackupex --user=root --password=password --defaults-file=/etc/my.cnf /data/data_bak/
innobackupex --defaults-file=/etc/my.cnf --user=root --password=password --apply-log   /data/data_bak/2011-12-11_18-50-55
b.重新做mysql主从
(1)复制主库/data/data_bak/2011-12-11_18-50-55文件到从库/data/mysql_db
   mv /data/data_bak/2011-12-11_18-50-55 /data/mysql_db
   chown -R mysql:mysql /data/mysql_db
(2)做同步
   cd /data/mysql_db
   cat xtrabackup_binlog_pos_innodb
   CHANGE MASTER TO MASTER_HOST='$masterip', MASTER_USER='repl', MASTER_PASSWORD='$slavepass', MASTER_LOG_FILE='TheMaster-bin.xxx', MASTER_LOG_POS=xxx;

完:

上文说的是当时环境下,我觉得最适合我们的方法,肯定还有其他方法的,比如mysqldump导出所有数据,然后改下文件里的建表语句为innodb,再导入数据库,等等。