CODEWITHSUNDEEP
mysqlsqlinnodb
Pentium10
Pentium10

Reputation: 208042

How to convert all tables from MyISAM into InnoDB?

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?

Upvotes: 317

Views: 323236

Answers (30)

RCode
RCode

Reputation: 379

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

Upvotes: 1

Sachin from Pune
Sachin from Pune

Reputation: 736

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;

Upvotes: 19

Steve
Steve

Reputation: 1775

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.

Upvotes: 0

Antony Gibbs
Antony Gibbs

Reputation: 1497

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

Upvotes: 0

Alex
Alex

Reputation: 1

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.

Upvotes: -1

Shubham
Shubham

Reputation: 11

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

Upvotes: 1

Mat Lipe
Mat Lipe

Reputation: 815

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

Upvotes: 0

Rodrigo Gregorio
Rodrigo Gregorio

Reputation: 91

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

Upvotes: 1

Gajendra Bang
Gajendra Bang

Reputation: 3583

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

Upvotes: 185

Berdan
Berdan

Reputation: 57

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

Upvotes: 1

Malibou
Malibou

Reputation: 131

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 !

Upvotes: 7

Synchro
Synchro

Reputation: 37818

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

Upvotes: 0

Lavi Avigdor
Lavi Avigdor

Reputation: 4182

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.

Upvotes: 14

Will Jones
Will Jones

Reputation: 6541

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.

Upvotes: 654

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2831

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 :)

Upvotes: 3

api984
api984

Reputation: 1

cd /var/lib/mysql/DBNAME

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

Upvotes: -1

Russell G
Russell G

Reputation: 488

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

?>

Upvotes: 1

Omkar Kulkarni
Omkar Kulkarni

Reputation: 824

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

Upvotes: 79

Harald Leithner
Harald Leithner

Reputation: 396

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;

Upvotes: 8

user3035727
user3035727

Reputation: 21

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;

Upvotes: 2

Quinn Comendant
Quinn Comendant

Reputation: 10586

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

Upvotes: 11

user3035649
user3035649

Reputation: 502

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.

Upvotes: 3

kuzea
kuzea

Reputation: 401

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

Upvotes: 30

touzas
touzas

Reputation: 89

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

Upvotes: 1

Muhammad Reda
Muhammad Reda

Reputation: 27053

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

Upvotes: 2

Developer
Developer

Reputation: 3998

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

  ALTER TABLE table_name ENGINE = INNODB;

Upvotes: 6

leech
leech

Reputation: 8431

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

Upvotes: 8

Jay Brunet
Jay Brunet

Reputation: 3750

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

Upvotes: 5

Hendrik Brummermann
Hendrik Brummermann

Reputation: 8312

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.

Upvotes: 20

Zwarmapapa
Zwarmapapa

Reputation: 347

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

Upvotes: 22

Related Questions