#/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 ; 調用方法如下代碼:
