MySQL批量修改存储引擎之shell脚本篇

再看MySQL手册，看到有关修改存储引擎的部分，隧想到能否用shell脚本实现批量修改，于是便有了下面的脚本，以把MyISAM转换为InnoDB为例。

实验环境如下：

OS: CentOS 5.8 Final

MySQL Version:5.5.19

脚本内容如下：

#/bin/bash
#FileName:Convert_Storage_Engine.sh
#Desc:Conversion of a MySQL tables to other storage engines
#Create By:fedoracle
#Date:2012/06/27

DB=new
USER=test
PASSWD=test
HOST=192.168.25.121
MYSQL_BIN=/usr/local/mysql/bin
S_ENGINE=MyISAM
D_ENGINE=InnoDB

#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

测试过程如下：

[root@dbmaster ~]# mysql -h192.168.25.121 -utest -ptest
(test@192.168.25.121) [(none)] create database new;
Query OK, 1 row affected (0.01 sec)

(test@192.168.25.121) [(none)] show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 361                |
| mysql              |
| new                |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

[root@dbmaster ~]# mysql -h192.168.25.121 -utest -ptest new < 361.sql
(test@192.168.25.121) [(none)] use new;
Database changed
(test@192.168.25.121) [new] show tables;
+---------------------------+
| Tables_in_new             |
+---------------------------+
| ad_magazine_content       |
| ad_news_letter            |
| conf_app                  |
| ip_province               |
| ip_records                |
| order_action              |
| order_delivery            |
| order_goods               |
................................

(test@192.168.25.121) [information_schema] select TABLE_NAME,ENGINE from TABLES where TABLE_SCHEMA='new' and ENGINE='MyISAM';
+--------------------------+--------+
| TABLE_NAME               | ENGINE |
+--------------------------+--------+
| ad_news_letter           | MyISAM |
| conf_app                 | MyISAM |
| product_lib_attr_group   | MyISAM |
| product_lib_brand        | MyISAM |
| product_lib_ccard        | MyISAM |
| product_lib_color        | MyISAM |
| product_lib_fashion      | MyISAM |
| product_lib_material     | MyISAM |
| product_lib_season       | MyISAM |
| product_lib_series       | MyISAM |
| product_lib_size         | MyISAM |
| product_lib_size_compare | MyISAM |
| product_lib_temperature  | MyISAM |
| product_lib_type         | MyISAM |
| product_lib_virtual_cat  | MyISAM |
| req_conf_app             | MyISAM |
| shop_keywords_details    | MyISAM |
| system_api_user          | MyISAM |
| system_payment           | MyISAM |
| system_region            | MyISAM |
| system_shop_dist         | MyISAM |
| user_show_order          | MyISAM |
+--------------------------+--------+
22 rows in set (0.02 sec)

[root@dbmaster scripts]# bash ChangeStorageEngine.sh
Starting convert table ad_news_letter......
Starting convert table conf_app......
Starting convert table product_lib_attr_group......
Starting convert table product_lib_brand......
Starting convert table product_lib_ccard......
Starting convert table product_lib_color......
Starting convert table product_lib_fashion......
Starting convert table product_lib_material......
Starting convert table product_lib_season......
Starting convert table product_lib_series......
Starting convert table product_lib_size......
Starting convert table product_lib_size_compare......
Starting convert table product_lib_temperature......
Starting convert table product_lib_type......
...............................

(test@192.168.25.121) [information_schema] select TABLE_NAME,ENGINE from TABLES where TABLE_SCHEMA='new' and ENGINE='MyISAM';
Empty set (0.01 sec)

[root@dbmaster scripts]# cat con_table.log
Convert table ad_news_letter ended.
Convert table conf_app ended.
Convert table product_lib_attr_group ended.
Convert table product_lib_brand ended.
Convert table product_lib_ccard ended.
Convert table product_lib_color ended.
Convert table product_lib_fashion ended.
Convert table product_lib_material ended.
Convert table product_lib_season ended.
Convert table product_lib_series ended.
Convert table product_lib_size ended.
Convert table product_lib_size_compare ended.
Convert table product_lib_temperature ended.
Convert table product_lib_type ended.
Convert table product_lib_virtual_cat ended.
Convert table req_conf_app ended.
Convert table shop_keywords_details ended.
Convert table system_api_user ended.
Convert table system_payment ended.
Convert table system_region ended.
Convert table system_shop_dist ended.
Convert table user_show_order ended.

#############################################################################################

有些表在转换的时候由于字符集，字段长度，外键约束等原因会出现一些问题，如下

ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails
ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 1000 bytes