Shulou(Shulou.com)05/31 Report--

This article mainly explains how MySQL modifies the storage engine in batches. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "MySQL how to modify the storage engine in batches"!

Looking back at the MySQL manual, I saw the section about modifying the storage engine, and I wondered if I could use shell scripts to make batch changes, so I had the following script to convert MyISAM to InnoDB as an example.

The experimental environment is as follows: OS: CentOS 5.8 Final MySQL Version:5.5.19 script: click (here) to collapse or open

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

The test process is as follows:

Click (here) to collapse or open

[root@dbmaster] # mysql-h292.168.25.121-utest-ptest

(test@192.168.25.121) [(none)] create database new

Query OK, 1 row affected (0.01sec)

(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.01sec)

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

# # some tables may have some problems due to character set, field length, foreign key constraints and other reasons during conversion. Click (here) to collapse or open.

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

At this point, I believe you have a deeper understanding of "MySQL how to modify the storage engine in batches". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.