mysql – How to convert all tables from MyISAM into InnoDB?

The Question :

270 people think this question is useful

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?

The Question Comments :
  • Tips on the conversion.
  • don’t use InnoDB, innodb occurs unrecoverable corruption

The Answer 1

176 people think this answer is useful
<?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);
    }
?>

The Answer 2

570 people think this answer is useful

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.

The Answer 3

63 people think this answer is useful
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

The Answer 4

23 people think this answer is useful

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.

The Answer 5

23 people think this answer is useful

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

The Answer 6

20 people think this answer is useful

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

The Answer 7

20 people think this answer is useful

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.

The Answer 8

12 people think this answer is useful

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.

The Answer 9

11 people think this answer is useful

It’s Very simple there are only TWO step just copy and paste :

step 1.

  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)

step 2: (copy all result in in sql tab) and paste below in the line

START TRANSACTION;

COMMIT;

eg. START TRANSACTION;

ALTER TABLE admin_files ENGINE=InnoDB;

COMMIT;

The Answer 10

8 people think this answer is useful

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 &amp;&amp; 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.)

The Answer 11

7 people think this answer is useful

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

The Answer 12

7 people think this answer is useful

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;

The Answer 13

5 people think this answer is useful

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;"

The Answer 14

4 people think this answer is useful

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 !

The Answer 15

3 people think this answer is useful

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

  ALTER TABLE table_name ENGINE = INNODB;

The Answer 16

3 people think this answer is useful

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.

The Answer 17

3 people think this answer is useful

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 🙂

The Answer 18

2 people think this answer is useful

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.

The Answer 19

2 people think this answer is useful

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

The Answer 20

2 people think this answer is useful

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;

The Answer 21

1 people think this answer is useful

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

The Answer 22

1 people think this answer is useful
<?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();

?>

The Answer 23

1 people think this answer is useful

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

The Answer 24

1 people think this answer is useful
<?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");
    }
  }

The Answer 25

0 people think this answer is useful

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 }}"

The Answer 26

-1 people think this answer is useful

cd /var/lib/mysql/DBNAME

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

Add a Comment