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

2024-07-24 12:39:40

字体：大中小

来源：转载

供稿：网友

存储引擎是每一个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 ]  --Vevb.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; --Vevb.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 ;