最近在做mysql-cluster 7.2.7的架构测试，不过因为目前现网所用的mysql引擎类型是MyISAM，而集群所用的类型是NDBCluster。而手动一张表一张表的去ALTER去修改显然是不现实的。因为公司现网上在用的表有好几百个。从网上找了两种批量修改的方法，如下：

一、shell脚本实现法

 1#/bin/bash
 2DB=test
 3USER=root
 4PASSWD=test
 5HOST=192.168.0.11
 6MYSQL_BIN=/usr/local/mysql/bin
 7S_ENGINE=MyISAM
 8D_ENGINE=DBDcluster
 9#echo "Enter MySQL bin path:"
10#read MYSQL_BIN
11#echo "Enter Host:"
12#read HOST
13#echo "Enter Uesr:"
14#read USER
15#echo "Enter Password:"
16#read PASSWD
17#echo "Enter DB name :"
18#read DB
19#echo "Enter the original engine:"
20#read S_ENGINE
21#echo "Enter the new engine:"
22#read D_ENGINE
23$MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='"$DB"' and ENGINE='"$S_ENGINE"';" | grep -v "TABLE_NAME" >tables.txt
24for t_name in `cat tables.txt`
25do
26    echo "Starting convert table $t_name......"
27    sleep 1
28    $MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "alter table $t_name engine='"$D_ENGINE"'"
29    if [ $? -eq 0 ]
30    then
31        echo "Convert table $t_name ended." >>con_table.log
32        sleep 1
33    else
34        echo "Convert failed!" >> con_table.log
35    fi
36done

喜欢交互式的就把echo 、read那段的注释去掉，可以根据提示进行更改。也可以根据自己的需要把DB、user、password、host等信息修改后直接运行。该方法的原理就是循环调用alter table 表名 engine=NDBcluster的语句。该方法还有一个变种：

首先利用mysql内部的系统表得出要执行的sql语句：

1SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE table_schema="db_name" AND ENGINE="myisam";

将以上结果输出到文件。然后执行该SQL语句的文件。执行完后，可以通过下面的语句确认下：

1SELECT CONCAT(table_name,'  ', engine) FROM information_schema.tables WHERE table_schema="db_name";

方法二、利用存储过程批量修改

 1DELIMITER $$
 2DROP PROCEDURE IF EXISTS `t_girl`.`sp_alter_db_engine`$$
 3CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alter_db_engine`(
 4 IN f_db_name varchar(255), IN f_engine_name varchar(255))
 5BEGIN
 6  -- Get the total number of tables.
 7  declare cnt1 int default 0;
 8  declare i int;
 9  set i = 0;
10  select count(1) from information_schema.tables where table_schema = f_db_name into cnt1;
11  while i < cnt1
12    do
13      set @stmt = concat('select @tbname:=table_name from information_schema.tables where table_schema=''',f_db_name,''' order by table_name desc limit ',i,',1 into @tbname');
14      prepare s1 from @stmt;
15      execute s1;
16      deallocate prepare s1;
17      set @stmt = '';
18      set @tbname = concat(f_db_name,'.',@tbname);
19      call sp_alter_table_engine(@tbname,f_engine_name);
20      set i = i + 1;
21  end while;
22END$$
23DELIMITER ;

调用方法：

1call sp_alter_db_engine('baigan_cs','innodb');

前表一个是库名，后面是要改成的引擎类型。