简单快速将MySQL MyISAMl转换成innodb存储引擎
1、用脚本列出目标库的所有表,并生成alter table语句
mysql -uuser -ppassword -e "show tables in database;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > tables.sql 另一种写法,不需要tail:
mysql -uuser -ppassword -e "show tables in database;" --silent --skip-column-names | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > tables.sql 2、查看生成的SQL
[root@localhost ~]# cat cy2009_table.sql |more 3、导入数据库,将MyISAM转成innodb [root@localhost ~]# mysql -uuser -ppassword database < tables.sql 4、查看是否修改成功 mysql> show create table tablename; 5、用SQL语句进行转换
select "alter table ",a.table_name," engine = 'Innodb'; " from (select table_name from information_schema.tables where table_schema = 'baseball')as a;
|
|