Production environment batch modify MySQL engine

How to batch modify MySQL engine in production environment

In general, this demand is not much, but occasionally, here we recommend the use of SED to the backup of the content of the engine conversion method, of course, do not forget to modify the MY.CNF to support and efficient use of the reference.

Method 1

MySQL Command statement modification

After the engine changes are created, after version 5.0

ALTER TABLE Lvnian ENGINE=INNODB;

ALTER TABLE Lvnian Engine=myisam;

Where Lvnian is the table name

Change instance

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

Mysql> Show CREATE TABLE test\g;

1. Row ***************************

Table:test

Create table:create Table ' test ' (

' ID ' int (4) not NULL auto_increment,

' Name ' char (a) is not NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=301 DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

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

mysql> use lvnian;alter table Test Engine=myisam;

Database changed

Query OK, rows affected (0.18 sec)

records:300 duplicates:0 warnings:0

Mysql> Show CREATE TABLE test\g;

1. Row ***************************

Table:test

Create table:create Table ' test ' (

' ID ' int (4) not NULL auto_increment,

' Name ' char (a) is not NULL,

PRIMARY KEY (' id ')

) Engine=myisam auto_increment=301 DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>

Change succeeded

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

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

method of batch change;

You can use the script to process each table first, and then change it with a for loop.

#######

The following creates a simulation using

1. Create the Lvnian database first and create the TESTN table in this library

A. Creating a Lvnian library

Mysql-uroot-plvnian-e "Show databases;"

MYSQL-UROOT-PLVNIAN-E "CREATE database Lvnian;;"

Mysql-uroot-plvnian-e "Show databases;"

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

[Email protected]_mysql/]# mysql-uroot-plvnian-e "show databases;"

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

[Email protected]_mysql/]# mysql-uroot-plvnian-e "CREATE database Lvnian;;"

[Email protected]_mysql/]# mysql-uroot-plvnian-e "show databases;"

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Lvnian |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

[[Email Protected]_mysql/]#

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

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

B. Create 100 tables

MYSQL-UROOT-PLVNIAN-E "use lvnian;show tables;"

For n in ' seq 100 '; Do mysql-uroot-plvnian-e ' use Lvnian;create table test$n (id int (4) is not null Auto_increment,name char (a) not Null,prima RY key (ID)); "; Done

MYSQL-UROOT-PLVNIAN-E "use lvnian;show tables;"

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

[Email protected]_mysql/]# mysql-uroot-plvnian-e "use lvnian;show tables;"

[[Email Protected]_mysql/]# for n ' seq 100 '; Do mysql-uroot-plvnian-e ' use Lvnian;create table test$n (id int (4) is not null Auto_increment,name char (a) not Null,prima RY key (ID)); "; Done

[Email protected]_mysql/]# mysql-uroot-plvnian-e "use lvnian;show tables;"

+------------------+

| Tables_in_lvnian |

+------------------+

| Test1 |

| test10 |

| test100 |

| test11 |

| test12 |

| test13 |

...

...

...

| Test95 |

| test96 |

| Test97 |

| test98 |

| test99 |

+------------------+

[[Email Protected]_mysql/]#

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

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

C, what is the engine that queries these 100 tables

See what the default engine for a table is

MYSQL-UROOT-PLVNIAN-E "Use lvnian;show create TABLE test1\g;" | awk '/create/{print '------"$--------"}/engine=/{print

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

[Email protected]_mysql/]# mysql-uroot-plvnian-e "Use lvnian;show create TABLE test1\g;" | awk '/create/{print '------"$--------"}/engine=/{print

------' test1 '--------

Engine=innodb

[[Email Protected]_mysql/]#

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

See the 100 engine methods

For n in ' seq 100 '; Do mysql-uroot-plvnian-e "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine= /{print} '; Done

[[Email Protected]_mysql/]# for n ' seq 100 '; Do mysql-uroot-plvnian-e "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine= /{print} '; Done

------' test1 '--------

Engine=innodb

------' Test2 '--------

Engine=innodb

------' Test3 '--------

Engine=innodb

------' test4 '--------

Engine=innodb

...

...

------' test96 '--------

Engine=innodb

------' Test97 '--------

Engine=innodb

------' test98 '--------

Engine=innodb

------' test99 '--------

Engine=innodb

------' test100 '--------

Engine=innodb

[[Email Protected]_mysql/]#

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

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

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

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

2. Batch modification engine

A, the method of modifying an engine

MYSQL-UROOT-PLVNIAN-E "Use lvnian;show create TABLE test1\g;" |awk '/create/{print $/engine=/{print} '

Mysql-uroot-plvnian-e "Use lvnian;use lvnian;alter table test1 Engine=myisam;"

MYSQL-UROOT-PLVNIAN-E "Use lvnian;show create TABLE test1\g;" |awk '/create/{print $/engine=/{print} '

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

[Email protected]_mysql/]# mysql-uroot-plvnian-e "Use lvnian;show create TABLE test1\g;" |awk '/create/{print $ $}/EN Gine=/{print} '

' Test1 '

Engine=innodb

[[Email Protected]_mysql/]#

[[Email Protected]_mysql/]#

[Email protected]_mysql/]# mysql-uroot-plvnian-e "Use lvnian;use lvnian;alter table test1 Engine=myisam;"

[Email protected]_mysql/]# mysql-uroot-plvnian-e "Use lvnian;show create TABLE test1\g;" |awk '/create/{print $ $}/EN Gine=/{print} '

' Test1 '

Engine=myisam

[[Email Protected]_mysql/]#

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

Ways to modify 100 engines

For n in ' seq 100 '; Do mysql-uroot-plvnian-e "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine= /{print} '; Done

For n in ' seq 100 ';  Do mysql-uroot-plvnian-e "Use Lvnian;use lvnian;alter table test${n} engine=myisam;" ; Done

For n in ' seq 100 '

Do

MYSQL-UROOT-PLVNIAN-E "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine=/{p Rint} '; Done

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

[[Email Protected]_mysql/]# for n ' seq 100 '; Do mysql-uroot-plvnian-e "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine= /{print} '; Done

------' test1 '--------

Engine=myisam

------' Test2 '--------

Engine=innodb

------' Test3 '--------

Engine=innodb

------' test4 '--------

Engine=innodb

...

...

------' test96 '--------

Engine=innodb

------' Test97 '--------

Engine=innodb

------' test98 '--------

Engine=innodb

------' test99 '--------

Engine=innodb

------' test100 '--------

Engine=innodb

[[Email Protected]_mysql/]#

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

[[Email Protected]_mysql/]# for n ' seq 100 ';  Do mysql-uroot-plvnian-e "Use Lvnian;use lvnian;alter table test${n} engine=myisam;" ; Done

[[Email Protected]_mysql/]#

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

[[Email Protected]_mysql/]# for n ' seq 100 '; Do mysql-uroot-plvnian-e "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine= /{print} '; Done

------' test1 '--------

Engine=myisam

------' Test2 '--------

Engine=myisam

------' Test3 '--------

Engine=myisam

------' test4 '--------

Engine=myisam

...

...

------' Test95 '--------

Engine=myisam

------' test96 '--------

Engine=myisam

------' Test97 '--------

Engine=myisam

------' test98 '--------

Engine=myisam

------' test99 '--------

Engine=myisam

------' test100 '--------

Engine=myisam

[[Email Protected]_mysql/]#

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

Methods for modifying N engines

For n in ' seq 100 '; Do mysql-uroot-plvnian-e "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine= /{print} '; Done

For n in ' seq 100 ';  Do mysql-uroot-plvnian-e "Use Lvnian;use lvnian;alter table test${n} engine=myisam;" ; Done

For n in ' seq 100 '

Do

MYSQL-UROOT-PLVNIAN-E "Use lvnian;show create TABLE test${n}\g;" |awk '/create/{print "------" $--------"}/engine=/{p Rint} '; Done

This article is from the "Struggle Bar" blog, please be sure to keep this source http://lvnian.blog.51cto.com/7155281/1699847

Production environment batch modify MySQL engine