How to convert all tables from MyISAM into InnoDB?

user_243782 0
Viewed 291930

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?

innodb mysql sql
31 Answers

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

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 !

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

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

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

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

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
user_35682050

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

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.

user_181253380

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
user_22508430
Related