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脚本实现法,代码如下:

1. #/bin/bash
2. DB=test
3. USER=root
4. PASSWD=test
5. HOST=192.168.0.11
6. MYSQL_BIN=/usr/local/mysql/bin
7. S_ENGINE=MyISAM
8. D_ENGINE=DBDcluster
9. #echo"EnterMySQLbinpath:"
10. #readMYSQL_BIN
11. #echo"EnterHost:"
12. #readHOST
13. #echo"EnterUesr:"
14. #readUSER
15. #echo"EnterPassword:"
16. #readPASSWD
17. #echo"EnterDBname:"
18. #readDB
19. #echo"Entertheoriginalengine:"
20. #readS_ENGINE
21. #echo"Enterthenewengine:"
22. #readD_ENGINE
23. $MYSQL_BIN/mysql-h$HOST-u$USER-p$PASSWD$DB-e"selectTABLE_NAMEfrominformation_schema.TABLESwhereTABLE_SCHEMA='"$DB"'andENGINE='"$S_ENGINE"';"|grep-v"TABLE_NAME">tables.txt
24. fort_namein`cattables.txt`
25. do
26. echo"Startingconverttable$t_name......"
27. sleep1
28. $MYSQL_BIN/mysql-h$HOST-u$USER-p$PASSWD$DB-e"altertable$t_nameengine='"$D_ENGINE"'"
29. if[$?-eq0]--phpfensi.com
30. then
31. echo"Converttable$t_nameended.">>con_table.log
32. sleep1
33. else
34. echo"Convertfailed!">>con_table.log
35. fi
36. 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";

方法二、利用存储过程批量修改,代码如下:

1. DELIMITER$$
2. DROPPROCEDUREIFEXISTS`t_girl`.`sp_alter_db_engine`$$
3. CREATEDEFINER=`root`@`localhost`PROCEDURE`sp_alter_db_engine`(
4. INf_db_namevarchar(255),INf_engine_namevarchar(255))
5. BEGIN
6. --Getthetotalnumberoftables.
7. declarecnt1intdefault0;
8. declareiint;
9. seti=0;
10. selectcount(1)frominformation_schema.tableswheretable_schema=f_db_nameintocnt1;
11. whilei<cnt1
12. do
13. set@stmt=concat('select@tbname:=table_namefrominformation_schema.tableswheretable_schema=''',f_db_name,'''orderbytable_namedesclimit',i,',1into@tbname');
14. prepares1from@stmt;--phpfensi.com
15. executes1;
16. deallocateprepares1;
17. set@stmt='';
18. set@tbname=concat(f_db_name,'.',@tbname);
19. callsp_alter_table_engine(@tbname,f_engine_name);
20. seti=i+1;
21. endwhile;
22. END$$
23. DELIMITER;

调用方法如下代码:

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

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