#/bin/bash DB=test USER=root PASSWD=test HOST=192.168.0.11 MYSQL_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 ] --phpfensi.com 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; --phpfensi.com 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 ; 调用方法如下代码: