MySQL怎么批量修改存儲(chǔ)引擎

發(fā)布時(shí)間：2021-08-17 02:19:39 來(lái)源：億速云閱讀：121 作者：chen 欄目：數(shù)據(jù)庫(kù)

本篇內(nèi)容主要講解“MySQL怎么批量修改存儲(chǔ)引擎”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL怎么批量修改存儲(chǔ)引擎”吧!

再看MySQL手冊(cè)，看到有關(guān)修改存儲(chǔ)引擎的部分，隧想到能否用shell腳本實(shí)現(xiàn)批量修改，于是便有了下面的腳本，以把MyISAM轉(zhuǎn)換為InnoDB為例。

實(shí)驗(yàn)環(huán)境如下： OS: CentOS 5.8 Final MySQL Version:5.5.19 腳本內(nèi)容如下：點(diǎn)擊(此處)折疊或打開

#/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

測(cè)試過(guò)程如下：

點(diǎn)擊(此處)折疊或打開

[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.

################################### 有些表在轉(zhuǎn)換的時(shí)候由于字符集，字段長(zhǎng)度，外鍵約束等原因會(huì)出現(xiàn)一些問(wèn)題，如下點(diǎn)擊(此處)折疊或打開

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

到此，相信大家對(duì)“MySQL怎么批量修改存儲(chǔ)引擎”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

向AI問(wèn)一下細(xì)節(jié)

MySQL怎么批量修改存儲(chǔ)引擎

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽