shell批量修改MySQL存储引擎类型2种方法

╰半夏微凉° 2022-05-05 03:29 220阅读 0赞

**MySQL存储引擎类型有哪些**

MyISAM管理非事务表。提供高速检索，以及全文搜索能力。  
Memory存储引擎提供”内存中”表，MERGE存储引擎允许集合将被树立统一的MyISAM表做为一个单表。非事务表。可把多个myisam表构建为一个虚拟表，使得对这些表的查询仿佛在一个表上进行，提高了查询速度和修复效率，并节省了磁盘空间。

**InnoDB,BDB存储引擎提供事务安全表。**

EXAMPLE存储引擎是一个”存根”引擎，它不做什么。可以用这个引擎创建表，但没有数据被存储其中或从其中检索，这个引擎的目的是服务。针对[开发][Link 1]人员。

NDB Cluster是被My[sql][] Cluster用来实现分割多台[计算][Link 2]机上的表的存储引擎。只被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语句：

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;代码如下</td> 
   <td>复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="vertical-align:top;"> <p>SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE table_schema="db_name" AND ENGINE="myisam";</p> </td> 
  </tr> 
 </tbody> 
</table>

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

SELECT CONCAT(table\_name,' ', engine) FROM information\_schema.tables WHERE table\_schema="db\_name";

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

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;代码如下</td> 
   <td>复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="vertical-align:top;"> <p>DELIMITER $$<br> DROP PROCEDURE IF EXISTS `t_girl`.`sp_alter_db_engine`$$<br> CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alter_db_engine`(<br> &nbsp;IN f_db_name varchar(255), IN f_engine_name varchar(255))<br> BEGIN<br> &nbsp; -- Get the total number of tables.<br> &nbsp; declare cnt1 int default 0;<br> &nbsp; declare i int;<br> &nbsp; set i = 0;<br> &nbsp; select count(1) from information_schema.tables where table_schema = f_db_name into cnt1;<br> &nbsp; while i &lt; cnt1<br> &nbsp;&nbsp;&nbsp; do<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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');<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; prepare s1 from @stmt;<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; execute s1;<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; deallocate prepare s1;<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set @stmt = '';<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set @tbname = concat(f_db_name,'.',@tbname);<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; call sp_alter_table_engine(@tbname,f_engine_name);<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set i = i + 1;<br> &nbsp; end while;<br> END$$<br> DELIMITER ;</p> </td> 
  </tr> 
 </tbody> 
</table>

调用方法：

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;代码如下</td> 
   <td>复制代码</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="vertical-align:top;"> <p>call sp_alter_db_engine('baigan_cs','innodb');</p> </td> 
  </tr> 
 </tbody> 
</table>

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

[Link 1]: https://developer.aliyun.com/index
[sql]: https://edu.aliyun.com/jiaocheng/1043
[Link 2]: https://www.aliyun.com/
[mysql]: https://edu.aliyun.com/jiaocheng/1107