MyISAM表转移至InnoDB

mysql> select version();

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

| version()  |

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

| 5.5.16-log |

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

1 row in set (0.00 sec)

mysql> show engines;

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

| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |

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

| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |

| FEDERATED          | NO      | Federated MySQL storage engine                             | NULL         | NULL | NULL       |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |

| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |

| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |

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

7 rows in set (0.00 sec)

 ::export

 mysqldump  --opt -R -uroot -pxxxxxx info_account > info_account.sql

 

 //-R, --routines      Dump stored routines (functions and procedures).

 

 ::importing

 mysql -uroot -pxxxxxx info_account < info_account.sql

 

 修改表引擎语句

ALTER TABLE tablename ENGINE = INNODB;

设置本地密码xxxxxx

[root@bogon mysql]# mysqladmin -uroot -p password xxxxxx

Enter password: 

批量修改表引擎

[root@bogon mysql]# vim change-engines.sh

#!/bin/bash
#write by H
declare -a database
database=(info_account info_game)
password='xxxxxx'
for db in ${database[@]}
        do
        #echo "DATABASE=$db"
        tables=`mysql  -uroot -p$password -e "use $db;show tables;"  | sed 1d`
        for table in $tables
                do
                echo "TABLE=$db.$table"
                mysql  -uroot -p$password -e "alter table $db.$table engine=INNODB;"
                sleep 0.2
        done
done