shell批量修改MySQL存儲引擎類型2種方法

存儲引擎是每一個mysql數據庫必須有的一個類型，不同的類型是不一樣的的，特別是對不同數據存儲有一些不同了，下面我們來看看shell批量修改MySQL存儲引擎類型2種方法

MySQL存儲引擎類型有哪些

MyISAM管理非事務表。提供高速檢索，以及全文搜索能力。
Memory存儲引擎提供”內存中”表，MERGE存儲引擎允許集合將被樹立統一的MyISAM表做為一個單表。非事務表。可把多個myisam表構建為一個虛擬表，使得對這些表的查詢仿佛在一個表上進行，提高了查詢速度和修復效率，並節省了磁盤空間。

InnoDB,BDB存儲引擎提供事務安全表。

EXAMPLE存儲引擎是一個”存根”引擎，它不做什麼。可以用這個引擎創建表，但沒有數據被存儲其中或從其中檢索，這個引擎的目的是服務。針對開發人員。

NDB Cluster是被Mysql Cluster用來實現分割多台計算機上的表的存儲引擎。只被LINUX,SOLARIS,Mac os支持。
ARCHIVE存儲引起被用來無索引地，非常小的覆蓋存儲的大量數據。
CSV存儲引擎把數據以逗號的格式存儲在文本文件中。

BLACKHOLE存儲引擎把數據存在遠程數據庫中。在5.1中他只和Mysql一起工作，使用Mysql C client API。在未來的分發版中，我們想要讓它使用其他驅動器或客戶端連接方法裡連接到另外的數據源。
       
 MyISAM類型的表在磁盤上存儲成三個文件

*.frm文件存儲表定義
*.MYD(mydata)文件存儲表中數據
*.MYI(myindex)文件存儲表上建立的索引。
       
InnoDB類型的表提供提交，回滾，崩潰恢復能力的存儲引擎。行級鎖。可以與其他Mysql表混合起來，甚至在同一個查詢中也可以混用。

為處理巨大數據量時的最大性能設計。

批量修改MySQL存儲引擎類型方法

一、shell腳本實現法

 代碼如下 復制代碼

#/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 ]
    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');

前表一個是庫名，後面是要改成的引擎類型。