MySQL怎么批量修改存储引擎

发布时间：2021-08-17 02:19:39 来源：亿速云阅读：127 作者：chen 栏目：数据库

本篇内容主要讲解“MySQL怎么批量修改存储引擎”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL怎么批量修改存储引擎”吧!

再看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 -h292.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 -h292.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

到此，相信大家对“MySQL怎么批量修改存储引擎”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

向AI问一下细节

MySQL怎么批量修改存储引擎

猜你喜欢

最新资讯

相关推荐

相关标签