批量修改MySQL存储引擎类型

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

一、shell脚本实现法

#/bin/bashDB=test
USER=root
PASSWD=test
HOST=192.168.0.11MYSQL_BIN=/usr/local/mysql/bin
S_ENGINE=MyISAM
D_ENGINE=DBDcluster
#echo "Enter MySQL bin path:"
#read MYSQL_BIN
#echo "Enter Host:"
#read HOST
#echo "Enter Uesr:"
#read USER
#echo "Enter Password:"
#read PASSWD
#echo "Enter DB name :"
#read DB
#echo "Enter the original engine:"
#read S_ENGINE
#echo "Enter the new engine:"
#read D_ENGINE
$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
for t_name in `cat tables.txt`
do
    echo "Starting convert table $t_name......"
    sleep 1
    $MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "alter table $t_name engine='"$D_ENGINE"'"
    if [ $? -eq 0 ]
    then
        echo "Convert table $t_name ended." >>con_table.log
        sleep 1
    else
        echo "Convert failed!" >> con_table.log    
    fi
 done

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

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

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

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

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

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

DELIMITER $$
DROP PROCEDURE IF EXISTS `t_girl`.`sp_alter_db_engine`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alter_db_engine`(
 IN f_db_name varchar(255), IN f_engine_name varchar(255))
 BEGIN
  -- Get the total number of tables.
  declare cnt1 int default 0;
  declare i int;
  set i = 0;
  select count(1) from information_schema.tables where table_schema = f_db_name into cnt1;
  while i < cnt1    
    do
      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');
      prepare s1 from @stmt;
      execute s1;
      deallocate prepare s1;
      set @stmt = '';
      set @tbname = concat(f_db_name,'.',@tbname);
      call sp_alter_table_engine(@tbname,f_engine_name);
      set i = i + 1;
  end while;
END$$
DELIMITER ;

调用方法：

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

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