紧急修改表存储引擎:MyISAM 为 InnoDB步骤详解

发布时间:2020-05-15编辑:脚本学堂
本文介绍下,在生产环境中,紧急修改mysql数据表的存储引擎:MyISAM 为 InnoDB引擎的详细步骤,有需要的朋友参考下。

环境描述:
进行一次数据库迁移,由于设置问题导致所有的数据库表储存引擎都设置为:myisam,按照业务需求,部分数据库表必须为:innodb,否则会导致事务执行失败及不起效,为此把需要的数据库表存储引擎从MyISAM 改为 InnoDB。

而其数据库中大概有300多张表,其中将近三分之一表需要修改为:InnoDB,剩下的继续使用MyISAM引擎。

备注:暂时没有一张innodb引擎的表,另外my.cnf配置也不太妥当,相关参数没有给其相应合适的值

硬件环境:
机型:R710  CPU:2颗4core E5620  MEM:24G  RAID:1+0  DISK:6*15K*146G  Network:2*1000M

自动生成修改动作及检查动作的sql语句
用于生成变更的SQL语句:
 

复制代码 代码示例:
SELECT CONCAT(‘ALTER TABLE ‘,table_name,’ ENGINE=InnoDB;’) FROM information_schema.tables WHERE table_schema=’db_name’ AND `ENGINE`=’myisam’;

用于生成检查表的SQL语句:
 

复制代码 代码示例:
SELECT CONCAT(‘CHECK TABLE ‘,table_name) FROM information_schema.tables WHERE table_schema=’db_name’;

由于其表名称没有规律,无法再进步通过SQL语句条件缩小范围,只好再人工进行后续编辑,当变更及检查的SQL语句准备妥当,即将开始其修改征程:
1.停掉mysqld服务
 

复制代码 代码示例:
mysqladmin -uroot -p*** –socket=/data/mysqldata/sock/mysql.sock shutdown

2.修改my.cnf文件配置
 

复制代码 代码示例:
innodb_file_per_table
innodb_data_file_path=ibdata1:256M:autoextend
innodb_buffer_pool_size = 10G
innodb_log_buffer_size  = 8M
key_buffer_size         = 2G
innodb_log_file_size      = 256M
innodb_log_files_in_group = 8

3.把已经生成好的ibdata、ib_logfile文件先移动到其他目录
备注:当前没有使用innodb引擎的表,要是有需要再另外处理,若还是共享表空间,那就不能这么蛮干

4.启动mysqld服务
 

复制代码 代码示例:
mysqld_safe &

5.执行修改参数
 

复制代码 代码示例:
SET GLOBAL sort_buffer_size=128*1024*1024;
SET GLOBAL tmp_table_size=128*1024*1024;
SET GLOBAL read_buffer_size=32*1024*1024;
SET GLOBAL read_rnd_buffer_size=32*1024*1024;

6.执行ALTER TABLE语句

7.执行CHECK语句

8.修改my.cnf配置
 

复制代码 代码示例:
sort_buffer_size = 2M
tmp_table_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 1M

9.设置全局变量
 

复制代码 代码示例:
SET GLOBAL sort_buffer_size=2*1024*1024;
SET GLOBAL tmp_table_size=8*1024*1024;
SET GLOBAL read_buffer_size=2*1024*1024;
SET GLOBAL read_rnd_buffer_size=1*1024*1024;

10.开放服务、并且做测试、监控mysql日志及程序日志信息

如此,便将mysql数据表的存储引擎由MyISAM 改为 InnoDB了。