How to convert all tables from MyISAM into InnoDB?

Question

I know I can issue an alter table individually to change the table storage from MyISAM to InnoDB.

I am wondering if there is a way to quickly change all of them to InnoDB?

Corporative

Tips on the conversion. – Carlos

Answer 1

<?php
    // connect your database here first 
    // 

    // Actual code starts here 

    $sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'your_database_name' 
        AND ENGINE = 'MyISAM'";

    $rs = mysql_query($sql);

    while($row = mysql_fetch_array($rs))
    {
        $tbl = $row[0];
        $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
        mysql_query($sql);
    }
?>

Wright

It probably would be better to limit this to the database you're focusing on. Add a " AND TABLE_SCHEMA = 'dbname', otherwise this can/will change all the internet MySQL tables to innodb as well (when some of them should be memory) – Wyn

PHP's mysql_* interface is deprecated and removed from ver 7. Don't use this code as is. – Carlos

@RickJames The question was answered two years ago :) – Wright

@GajendraBang - Yes, the answer as valid when presented. But for newcomers, it is no longer valid. My intent was to warn against using it as is. – Carlos

The question does not mention PHP whatsoever – Afroasian

How is the most recent edit not flagged? The MySQL portion is a direct copy of Will Jones' answer. Look at each edit history to find that Will's answer appeared in 2013 while this answer appeared in 2019. As a result, the integrity of this question is compromised. – Crawl

As per @Crawl - the SQL portion of this answer is copied from Will Jones. Without attribution. Anyone who uses that portion should upvote Will Jones' answer, not this one. – Complaisant

Answer 2

Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database.

Replace value of the name_of_your_db variable with your database name.

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;

Then, copy the output and run as a new SQL query.

Alda

That worked nicely! I've put it into an example shell script here: shrubbery.mynetgear.net/c/display/W/… – Teal

if you want to use this sql rename the alias from "SQL" to anything else, like "SQLaltermytables" – Aspirin

"#1267 illegal mix of collations..." I'm getting this error, it doesn't work – Beene

Just out of curiosity, what's the point of the explicit descending ordering? (ORDER BY table_name DESC) – Rice

For those migrating a large number of tables to InnoDB (I just migrated 50k tables :) ), the best way I found to execute the resulting SQL statements is to save them into a file and pass it to mysql. Example: mysql name_of_your_db < migration-statements.sql – Rice

If your dealing with multiple databases and don't want to change the database everytime, change CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') to CONCAT('ALTER TABLE ',@DATABASE_NAME,'.', table_name, ' ENGINE=InnoDB;') – Cellophane

One little tweak so you don't have to 'use database': SET @DATABASE_NAME = 'name_of_your_db'; SELECT CONCAT('ALTER TABLE ',@DATABASE_NAME,'.', 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; – Show

If you start your mysql session with the -s flag, it will avoid printing the | table characters | around the queries, making it possible to copy and paste them all in one block. – Areopagite

Note that if you are doing this across databases as some comments suggest, you need to exclude doing it for the mysql database. – Subsonic

If you want to get the the statements for all databases (except the MySQL system databases): SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS sql_statements FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql') AND engine = 'MyISAM' AND table_type = 'BASE TABLE' ORDER BY table_schema,table_name – Gramme

Sometimes, when converting from MyISAM to InnoDB, SQL throws an error about the table index being too big. This is because in many shared hosting configurations InnoDB is set to have Compact row format by default. To solve this problem modify the SELECT line to add Dynamic row format like so: SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB, ROW_FORMAT=Dynamic;') AS sql_statements – Avie

Answer 3

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase';

Works like a charm.

This will give you list of all tables with the alter queries that you can run in a batch

Escamilla

After running this you first need to execute the following query: USE databasename; Then you can use the queries that the above script gives. – Sumter

How do you run a batch? – Dyewood

The above query will give you alter table queries. just select them all and execute them together. or divide them in groups of 50 queries and run them if there are too many tables in the resultset – Escamilla

It's working even on 2018 and on Percona Cluster. If using it from PHPMyAdmin, you'll only get 20 or so names, then "..." or a pagination >> symbol. This means you have to click and keep copying all the next pages so you won't miss any table. If you do forget that, you can safely re-apply the above query and it'll give you the next MyISAM tables to convert. – Twopiece

Answer 4

One line:

 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

Serenity

The BEST and the most INTELLIJENT answer! – Hardden

When I run this in a bash script it interprets the $1 as a bash script variable overwriting the NR definition. Any way around this? – Thorncombe

@WorksforaLiving enclose the "$1" in backticks like this: `"$1"` similar to what's in my answer. – Consign

Answer 5

In the scripts below, replace <username>, <password> and <schema> with your specific data.

To show the statements that you can copy-paste into a mysql client session type the following:

echo 'SHOW TABLES;' \
 | mysql -u <username> --password=<password> -D <schema> \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \

To simply execute the change, use this:

echo 'SHOW TABLES;' \
 | mysql -u <username> --password=<password> -D <schema> \
 | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
 | column -t \
 | mysql -u <username> --password=<password> -D <schema>

CREDIT: This is a variation of what was outlined in this article.

Consign

Answer 6

Use this as a sql query in your phpMyAdmin

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;') 
FROM information_schema.tables 
WHERE engine = 'MyISAM';

Phillida

This doesn't seem to actually convert the tables to InnoDB. – Moonstruck

This outputs a script that you then run to convert the tables - it's two steps. It tries to convert INFORMATION_SCHEMA tables, though - that's a bad thing. Need to limit it to the right database. – Charest

You will have to filter our the internal mysql tables - according to the docs "Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type." link – Margit

Without editing to incorporate @eug's comment into this answer, I think it's deserving of a down-vote, though it's otherwise as elegant as any of the variants on this page. – Subsonic

Hmm. @charlie-s is also correct, and this doesn't produce working SQL. A down-vote seems to me to be justified. – Subsonic

This works good and it is safe. The person can copy and paste the Alter Table. – Romeu

Super script. Not at all needed to be downvoted. Instead of PHP scripts that require a database, this shows me all tables needing such alter help without actually altering them. Thank you so much @Zwarmapapa! Helped me more than any other answer on this page. – Irreligious

Very Good. I also escaped the table names and ignored the system tables with the following query: SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' and table_schema not in ('information_schema','mysql','performance_schema','sys'); – Lampion

Answer 7

You can execute this statement in the mysql command line tool:

echo "SELECT concat('ALTER TABLE `',TABLE_NAME,'` ENGINE=InnoDB;')
FROM Information_schema.TABLES 
WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE' 
AND TABLE_SCHEMA='name-of-database'" | mysql > convert.sql

You may need to specify username and password using: mysql -u username -p The result is an sql script that you can pipe back into mysql:

mysql name-of-database < convert.sql

Replace "name-of-database" in the above statement and command line.

Kirstiekirstin

@itsraja, "echo" is a command supported by both sh on linux/unix and cmd on Microsoft systems, the result is piped as input to the mysql tool. – Kirstiekirstin

that's right. But u've mentioned as "mysql command line tool" – Vanden

Also, echo "SELECT concat(concat('ALTER TRABLE ', TABLE_NAME), ' ENGINE=InnoDB;') FROM TABLES WHERE ENGINE != 'InnoDB' AND TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='testinno'" | mysql -u root --sock=/opt/lampp/var/mysql/mysql.sock --database=testinno > convert.sql ERROR 1146 (42S02) at line 1: Table 'testinno.TABLES' doesn't exist – Vanden

I've put this into an example shell script here: shrubbery.mynetgear.net/c/display/W/… – Teal

How can we properly escape the sql statement as a string? As it is now, I get -bash: ,TABLE_NAME,: command not found – Pahang

Btw, you don't need to output to file, you can pipe it back echo "..." | mysql | mysql – Bundle

pur SQL worked great with MySQL Workbench. Copy all rows "unquoted" and run the lines in a new editor. So you can fist see all lines and check it first, then run. – Alkalosis

Answer 8

It’s very simple. There are only two steps.

1. Copy, paste and run this:

   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;
   

(copy and paste all result in in sql tab)

2. Copy all result into the SQL tab and paste below in the line.

   START TRANSACTION;
   COMMIT;
   

For example:

START TRANSACTION;
ALTER TABLE `admin_files` ENGINE=InnoDB;
COMMIT;

Selfcentered

Answer 9

To generate ALTER statements for all tables in all the non-system schemas, ordered by those schemas/tables run the following:

SELECT  CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables
WHERE   TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'innodb', 'sys', 'tmp')
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, table_name DESC;

After that, run those queries via a client to perform the alteration.

• Answer is based on above answers, but improves schema handling.

Drabeck

Answer 10

It hasn't been mentioned yet, so I'll write it for posterity:

If you're migrating between DB servers (or have another reason you'd dump and reload your dta), you can just modify the output from mysqldump:

mysqldump --no-data DBNAME | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' > my_schema.sql;
mysqldump --no-create-info DBNAME > my_data.sql;

Then load it again:

mysql DBNAME < my_schema.sql && mysql DBNAME < my_data.sql

(Also, in my limited experience, this can be a much faster process than altering the tables ‘live’. It probably depends on the type of data and indexes.)

Frater

ty! exactly what I was looking for. Will test it in a few days. – Horrific

Answer 11

Here is a way to do it for Django users:

from django.core.management.base import BaseCommand
from django.db import connections


class Command(BaseCommand):

    def handle(self, database="default", *args, **options):

        cursor = connections[database].cursor()

        cursor.execute("SHOW TABLE STATUS");

        for row in cursor.fetchall():
            if row[1] != "InnoDB":
                print "Converting %s" % row[0],
                result = cursor.execute("ALTER TABLE %s ENGINE=INNODB" % row[0])
                print result

Add that to your app under the folders management/commands/ Then you can convert all your tables with a manage.py command:

python manage.py convert_to_innodb

Hebephrenia

Answer 12

A plain MySQL Version.

You can simply start mysql executable, use database and copy-paste the query.

This will convert all MyISAM tables in the current Database into INNODB tables.

DROP PROCEDURE IF EXISTS convertToInnodb;
DELIMITER //
CREATE PROCEDURE convertToInnodb()
BEGIN
mainloop: LOOP
  SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
  WHERE `TABLE_SCHEMA` LIKE DATABASE()
  AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
  IF @convertTable IS NULL THEN 
    LEAVE mainloop;
  END IF;
  SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
  PREPARE convertTables FROM @sqltext;
  EXECUTE convertTables;
  DEALLOCATE PREPARE convertTables;
  SET @convertTable = NULL;
END LOOP mainloop;

END//
DELIMITER ;

CALL convertToInnodb();
DROP PROCEDURE IF EXISTS convertToInnodb;

Shill

I'm surprised that there weren't more stored procedure-based solutions! – Prohibit

Because writing and testing procedures in MySQL is pain ;-) the comment has nothing todo with the question. – Shill

Answer 13

Just tested another (simple ?) way, and worked for me.

Just export your DB as .sql file, edit-it with gedit or notepad;

Replace ENGINE=MyISAM with ENGINE=INNODB and Save the file edited

Number or replacement done should be the number of your tables

Import it to MySQL (phpMyAdmin or command line)

And Voila !

Samellasameness

Answer 14

use this line to alter the database engine for single table.

  ALTER TABLE table_name ENGINE = INNODB;

Maledict

Answer 15

From inside mysql, you could use search/replace using a text editor:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

Note: You should probably ignore information_schema and mysql because "The mysql and information_schema databases, that implement some of the MySQL internals, still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB." ( http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html )

In any case, note the tables to ignore and run:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

Now just copy/paste that list into your text editor and search/replace "|" with "ALTER TABLE" etc.

You'll then have a list like this you can simply paste into your mysql terminal:

ALTER TABLE arth_commentmeta           ENGINE=Innodb;
ALTER TABLE arth_comments              ENGINE=Innodb;
ALTER TABLE arth_links                 ENGINE=Innodb;
ALTER TABLE arth_options               ENGINE=Innodb;
ALTER TABLE arth_postmeta              ENGINE=Innodb;
ALTER TABLE arth_posts                 ENGINE=Innodb;
ALTER TABLE arth_term_relationships    ENGINE=Innodb;
ALTER TABLE arth_term_taxonomy         ENGINE=Innodb;
ALTER TABLE arth_terms                 ENGINE=Innodb;
ALTER TABLE arth_usermeta              ENGINE=Innodb;

If your text editor can't do this easily, here's another solution for getting a similar list (that you can paste into mysql) for just one prefix of your database, from linux terminal:

mysql -u [username] -p[password] -B -N -e 'show tables like "arth_%"' [database name] | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;"

Spumescent

Answer 16

I'm a newbie and had to find my own solution because mysql commands on the web are usually riddled with misspellings create a real life nightmare for people just starting out. Here is my solution....

Instead of in 1 command per table, I prepared dozens of commands (ready to copy and paste) at once using excel.

How? expand your putty window and enter mysql and then run the command "SHOW TABLE STATUS;" and the copy/paste the output to microsoft excel. Go to the Data tab and use the "text to columns" feature an delimit the columns by a space key. Then Sort the columns by whichever column shows your table types and delete all rows which the tables are already in InnoDb format (because we don't need to run commands against them, they are already done). Then add 2 columns to the left of the tables column, and 2 columns to the right. Then paste in the first part of the command in column-1 (see below). Column 2 should contain only a space. Column 3 is your tables column. Column 4 should contain only a space. Column 5 is the last part of your command. It should look like this:

column-1        column-2            column-3         column-4     column-5
ALTER TABLE     t_lade_tr           ENGINE=InnoDB;
ALTER TABLE     t_foro_detail_ms    ENGINE=InnoDB;
ALTER TABLE     t_ljk_ms            ENGINE=InnoDB;

Then copy and paste about 5 rows at a time into mysql. This will convert about 5 at once. I noticed if I did more than that at once then the commands would fail.

Stepup

Answer 17

In my case, I was migrating from a MySQL instance with a default of MyISAM, to a MariaDB instance with a DEFAULT of InnoDB.

Per MariaDB Migration Doc's.

On old Server Run :

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

The --skip-create-options ensures that the database server uses the default storage engine when loading the data, instead of MyISAM.

mysql -u root -p < migration.sql

This threw an error regarding creating mysql.db, but everything works great now :)

Hopefully

Answer 18

Try this shell script

DBENGINE='InnoDB' ;
DBUSER='your_db_user' ;
DBNAME='your_db_name' ;
DBHOST='your_db_host'
DBPASS='your_db_pass' ;
mysqldump --add-drop-table -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME > mtest.sql; mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "SHOW TABLES;" | while read TABLE ; do mysql -h$DBHOST -u$DBUSER -p$DBPASS $DBNAME -Nse "ALTER TABLE $TABLE ENGINE=$DBENGINE;" ; done

Fibroin

Answer 19

Some fixes to this util script

SET @DATABASE_NAME = 'Integradb';

SELECT  CONCAT('ALTER TABLE ', table_schema, '.', 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;

Vassaux

Answer 20

You could write a script to do it in your favourite scripting language. The script would do the following:

1. Issue SHOW FULL TABLES.
2. For each row returned, check that the second column says 'BASE TABLE' and not 'VIEW'.
3. If it is not 'VIEW', issue the appropriate ALTER TABLE command.

Brinkley

Answer 21

<?php

  // connect your database here first

  mysql_connect('host', 'user', 'pass');

  $databases = mysql_query('SHOW databases');

  while($db = mysql_fetch_array($databases)) {
    echo "database => {$db[0]}\n";
    mysql_select_db($db[0]);

    $tables = mysql_query('SHOW tables');

    while($tbl = mysql_fetch_array($tables)) {
      echo "table => {$tbl[0]}\n";
      mysql_query("ALTER TABLE {$tbl[0]} ENGINE=InnoDB");
    }
  }

Madame

Answer 22

This is a simple php script.

<?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>';
    }

Leonorleonora

Answer 23

<?php

// Convert all MyISAM tables to INNODB tables in all non-special databases.
// Note: With MySQL less than 5.6, tables with a fulltext search index cannot be converted to INNODB and will be skipped.

if($argc < 4)
    exit("Usage: {$argv[0]} <host> <username> <password>\n");
$host = $argv[1];
$username = $argv[2];
$password = $argv[3];

// Connect to the database.
if(!mysql_connect($host, $username, $password))
    exit("Error opening database. " . mysql_error() . "\n");

// Get all databases except special ones that shouldn't be converted.
$databases = mysql_query("SHOW databases WHERE `Database` NOT IN ('mysql', 'information_schema', 'performance_schema')");
if($databases === false)
    exit("Error showing databases. " . mysql_error() . "\n");

while($db = mysql_fetch_array($databases))
{
    // Select the database.
    if(!mysql_select_db($db[0]))
        exit("Error selecting database: {$db[0]}. " . mysql_error() . "\n");
    printf("Database: %s\n", $db[0]);

    // Get all MyISAM tables in the database.
    $tables = mysql_query("SHOW table status WHERE Engine = 'MyISAM'");
    if($tables === false)
        exit("Error showing tables. " . mysql_error() . "\n");

    while($tbl = mysql_fetch_array($tables))
    {
        // Convert the table to INNODB.
        printf("--- Converting %s\n", $tbl[0]);
        if(mysql_query("ALTER TABLE `{$tbl[0]}` ENGINE = INNODB") === false)
            printf("--- --- Error altering table: {$tbl[0]}. " . mysql_error() . "\n");
    }
}

mysql_close();

?>

Marsden

Answer 24

for mysqli connect;

<?php

$host       = "host";
$user       = "user";
$pass       = "pss";
$database   = "db_name";


$connect = new mysqli($host, $user, $pass, $database);  

// Actual code starts here Dont forget to change db_name !!
$sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'db_name' 
    AND ENGINE = 'MyISAM'";

$rs = $connect->query($sql);

while($row = $rs->fetch_array())
{
    $tbl = $row[0];
    $sql = "ALTER TABLE `$tbl` ENGINE=INNODB";
    $connect->query($sql);
} ?>

Salley

Answer 25

For converting MySql tables storage engine there is a number way:

1. Use MySql commands as follow, 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 table name.).
2. Write a script that loop on all tables and run the alter command
3. Use an already available script to handle that: https://github.com/rafihaidari/convert-mysql-tables-storage-engine

Finochio

@AdrianMole and SilverNak, thank you for your inputs I have edited my answer I hope this help. – Finochio

Answer 26

Follow steps:

1. 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.).

2. Write a script that loops on all tables and run the alter command

3. Use an already available script to handle that: https://github.com/rafihaidari/convert-mysql-tables-storage-engine

4. 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';
   

Floeter

Answer 27

Yet another option... Here's how to do it in ansible. It assumes that the name of your database is in dbname and that you have already configured access.

- name: Get list of DB tables that need converting to InnoDB
  command: >
    mysql --batch --skip-column-names --execute="SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = '{{ dbname }}' AND ENGINE = 'MyISAM';"
  register: converttables
  check_mode: no
  changed_when: False

- name: Convert any unconverted tables
  command: >
    mysql --batch --skip-column-names --execute="ALTER TABLE `{{ dbname }}`.`{{ item }}` ENGINE = InnoDB;"
  with_items: "{{ converttables.stdout_lines }}"

Sibelle

Answer 28

If you are using Windows you can accomplish this inside a batch file with the following loop.

set database=YOURDATABASENAME
for /F "tokens=1 skip=1 usebackq" %%a in (`mysql %%database%% -e "show table status where Engine != 'InnoDB';"`) do (
    mysql %database% -e "ALTER TABLE %%a ENGINE = 'InnoDB';"
)

Simply change the YOURDATABASENAME to the name of the database you are targeting or use %~1 to pass the database name via the command line.

Every table which is not currently InooDB will be converted to InnoDB. If you want to specifically target MyISAM as the question suggested, the following code has an updated MySQL conditional for only MyISAM.

set database=YOURDATABASENAME
for /F "tokens=1 skip=1 usebackq" %%a in (`mysql %%database%% -e "show table status where Engine = 'MyISAM';"`) do (
    mysql %database% -e "ALTER TABLE %%a ENGINE = 'InnoDB';"
)

Pantelleria

Answer 29

When tables are big, better doing it from a console

convert-to-innodb.sh

#!/usr/bin/env bash

# Usage: ./convert-to-innodb.sh 'db' 'user' 'password' | mysql 'db' -u  user -p password


set -eu

db="$1"
user="$2"
pass="$3"


sql="SET @DATABASE_NAME = '${db}';"

sql+="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;"

echo $sql | mysql -u${user} -p${pass} | tail -n +2 

Choroid

Answer 30

If you're using WordPress you can use the plugin Index WP MySQL For Speed, it has a bulk conversion feature, from MyISAM to InnoDB.

Hako

Answer 31

cd /var/lib/mysql/DBNAME

ls | grep ".frm" | cut -d"." -f1 | xargs -I{} -n1 mysql -D DBNAME -e "alter table {} ENGINE=INNODB;" -uroot -pXXXXX

Cinquain

Answer 32

Create a SQL dump file of your database (database_dump.sql) and open it in notepad. Find and Replace all "ENGINE=MyISAM" with "ENGINE=InnoDB". Save the file and import it back into your database.

Austin