mysql 批量更改引擎_生产环境批量修改mysql引擎

生产环境中如何批量修改MySQL引擎

一般来说这样的需求并不多见，但是偶尔也会有，在这里我们推荐使用sed对备份的内容进行引擎转换的方式，当然了，不要忘记修改my.cnf使之支持并能高效的使用对于的引用。

方法1

mysql命令语句修改

创建后引擎更改，5.0版本之后

alter table lvnian engine=innodb;

alter table lvnian engine=MyISAM;

其中lvnian是表名

更改实例

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

mysql> show create table test\G;

*************************** 1. row ***************************

Table: test

Create Table: CREATE TABLE `test` (

`id` int(4) NOT NULL AUTO_INCREMENT,

`name` char(20) 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, 300 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(20) 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>

更改成功

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

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

批量更改的方法；

可以用脚本，先把每个表过来处理，再用一个for循环更改

#######

下面创建模拟使用

1、先创建lvnian数据库，再创建这个库中创建100 testN表

a.创建lvnian库

mysql -uroot -plvnian -e "show databases ;"

mysql -uroot -plvnian -e "create database lvnian; ;"

mysql -uroot -plvnian -e "show databases ;"

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

[root@M_MYSQL /]#  mysql -uroot -plvnian -e "show databases ;"

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

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

[root@M_MYSQL /]# mysql -uroot -plvnian -e "create database lvnian; ;"

[root@M_MYSQL /]# mysql -uroot -plvnian -e "show databases ;"

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

| Database           |

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

| information_schema |

| lvnian             |

| mysql              |

| performance_schema |

| test               |

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

[root@M_MYSQL /]#

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

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

b、创建100个表

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) not null auto_increment,name char(20) not null,primary key(id));" ; done

mysql -uroot -plvnian -e "use lvnian;show tables ;"

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

[root@M_MYSQL /]# mysql -uroot -plvnian -e "use lvnian;show tables ;"

[root@M_MYSQL /]# for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;create table test$n(id int(4) not null auto_increment,name char(20) not null,primary key(id));" ; done

[root@M_MYSQL /]# mysql -uroot -plvnian -e "use lvnian;show tables ;"

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

| Tables_in_lvnian |

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

| test1            |

| test10           |

| test100          |

| test11           |

| test12           |

| test13           |

...

...

...

| test95           |

| test96           |

| test97           |

| test98           |

| test99           |

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

[root@M_MYSQL /]#

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

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

c、查询这100个表的引擎是什么

查看一个表的默认引擎是什么

mysql -uroot -plvnian -e "use lvnian;show create table test1\G;"|awk  '/CREATE/{print "------"$5"--------"}  /ENGINE=/{print $2}'

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

[root@M_MYSQL /]# mysql -uroot -plvnian -e "use lvnian;show create table test1\G;"|awk  '/CREATE/{print "------"$5"--------"}  /ENGINE=/{print $2}'

------`test1`--------

ENGINE=InnoDB

[root@M_MYSQL /]#

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

查看100个引擎的方法

for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;show create table test${n}\G;" |awk '/CREATE/{print "------"$5"--------"} /ENGINE=/{print $2}'  ; done

[root@M_MYSQL /]# for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;show create table test${n}\G;" |awk '/CREATE/{print "------"$5"--------"} /ENGINE=/{print $2}'  ; 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

[root@M_MYSQL /]#

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

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

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

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

2、批量修改引擎

a、修改一个引擎的方法

mysql -uroot -plvnian -e "use lvnian;show create table test1\G;" |awk '/CREATE/{print $5} /ENGINE=/{print $2}'

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 $5} /ENGINE=/{print $2}'

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

[root@M_MYSQL /]# mysql -uroot -plvnian -e "use lvnian;show create table test1\G;" |awk '/CREATE/{print $5} /ENGINE=/{print $2}'

`test1`

ENGINE=InnoDB

[root@M_MYSQL /]#

[root@M_MYSQL /]#

[root@M_MYSQL /]# mysql -uroot -plvnian -e "use lvnian;use lvnian;alter table test1 engine=MyISAM;"

[root@M_MYSQL /]# mysql -uroot -plvnian -e "use lvnian;show create table test1\G;" |awk '/CREATE/{print $5} /ENGINE=/{print $2}'

`test1`

ENGINE=MyISAM

[root@M_MYSQL /]#

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

修改100个引擎的方法

for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;show create table test${n}\G;" |awk '/CREATE/{print "------"$5"--------"} /ENGINE=/{print $2}'  ; 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 "------"$5"--------"} /ENGINE=/{print $2}'  ; done

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

[root@M_MYSQL /]# for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;show create table test${n}\G;" |awk '/CREATE/{print "------"$5"--------"} /ENGINE=/{print $2}'  ; 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

[root@M_MYSQL /]#

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

[root@M_MYSQL /]# for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;use lvnian;alter table test${n} engine=MyISAM;"  ; done

[root@M_MYSQL /]#

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

[root@M_MYSQL /]# for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;show create table test${n}\G;" |awk '/CREATE/{print "------"$5"--------"} /ENGINE=/{print $2}'  ; 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

[root@M_MYSQL /]#

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

修改N个引擎的方法

for n in `seq 100` ; do mysql -uroot -plvnian -e "use lvnian;show create table test${n}\G;" |awk '/CREATE/{print "------"$5"--------"} /ENGINE=/{print $2}'  ; 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 "------"$5"--------"} /ENGINE=/{print $2}'  ; done