一行:

 mysql -u root -p dbName -e 
 "show table status where Engine='MyISAM';" | awk 
 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}'  | 
  mysql -u root -p dbName

这是一个简单的php脚本。

<?php
    @error_reporting(E_ALL | E_STRICT);
    @ini_set('display_errors', '1');


    $con = mysql_connect('server', 'user', 'pass');
    $dbName = 'moodle2014';

    $sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '".$dbName."';";
    $rs = mysql_query($sql, $con);

    $count = 0;
    $ok = 0;
    while($row = mysql_fetch_array($rs)){
            $count ++;
            $tbl = $row[0];
            $sql = "ALTER TABLE ".$dbName.".".$tbl." ENGINE=INNODB;";
            $resultado = mysql_query($sql);
            if ($resultado){
                    $ok ++;
                    echo $sql."<hr/>";
            }
    }
    if ($count == $ok){
            echo '<div style="color: green"><b>ALL OK</b></div>';
    }else{
            echo '<div style="color: red"><b>ERRORS</b>Total tables: '.$count.', updated tables:'.$ok.'</div>';
    }

在我的例子中，我从一个默认MyISAM的MySQL实例迁移到一个默认InnoDB的MariaDB实例。

根据MariaDB迁移文件。

在旧服务器上运行:

mysqldump -u root -p --skip-create-options --all-databases > migration.sql

——skip-create-options确保数据库服务器在加载数据时使用默认存储引擎，而不是MyISAM。

mysql -u root -p < migration.sql

这抛出了一个关于创建mysql.db的错误，但现在一切都很好了:)

遵循步骤:

Use MySql commands as follows, for converting to InnoDB (ALTER TABLE t1 ENGINE = InnoDB) or (ALTER TABLE t1 ENGINE = MyISAM) for MyISAM (You should do this for each individual tables, t1 is for the table name.). Write a script that loops on all tables and run the alter command Use an already available script to handle that: https://github.com/rafihaidari/convert-mysql-tables-storage-engine Try this SQL to Get all info will get all the tables information then you can change all the table from isam to InnoDB SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'your_DB_Name';

运行此SQL语句(在MySQL客户端、phpMyAdmin或任何地方)检索数据库中的所有MyISAM表。

将name_of_your_db变量的值替换为您的数据库名称。

SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

然后，复制输出并作为一个新的SQL查询运行。

创建数据库的SQL转储文件(database_dump.sql)，并在记事本中打开它。 将所有“ENGINE=MyISAM”替换为“ENGINE=InnoDB”。 保存文件并将其导入数据库。