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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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  lt  migration sql   This threw an error regarding creating mysql db  but everything works great now

User · Answer

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   gt      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   gt      mysql --batch --skip-column-names --execute  ALTER TABLE     dbname         item     ENGINE   InnoDB     with items      converttables stdout lines

User · Answer

You could write a script to do it in your favourite scripting language  The script would do the following   Issue SHOW FULL TABLES For each row returned  check that the second column says  BASE TABLE  and not  VIEW  If it is not  VIEW   issue the appropriate ALTER TABLE command

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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  gt  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  lt  convert sql   Replace  name-of-database  in the above statement and command line

User · Answer

use this line to alter the database engine for single table     ALTER TABLE table name ENGINE   INNODB

User · Answer

For converting MySql tables storage engine there is a number way   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    Write a script that loop 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

User · Answer

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  gt  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

User · Answer

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 · Answer

cd  var lib mysql DBNAME  ls   grep   frm    cut -d    -f1   xargs -I   -n1 mysql -D DBNAME -e  alter table    ENGINE INNODB   -uroot -pXXXXX

User · Answer

for mysqli connect    lt  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- gt query  sql    while  row    rs- gt fetch array           tbl    row 0        sql    ALTER TABLE   tbl  ENGINE INNODB        connect- gt query  sql       gt

User · Answer

lt  php       connect your database here first    mysql connect  host    user    pass        databases   mysql query  SHOW databases       while  db   mysql fetch array  databases         echo  quot database   gt    db 0   n quot       mysql select db  db 0          tables   mysql query  SHOW tables         while  tbl   mysql fetch array  tables           echo  quot table   gt    tbl 0   n quot         mysql query  quot ALTER TABLE   tbl 0   ENGINE InnoDB quot

User · Answer

In the scripts below  replace  lt username gt    lt password gt  and  lt schema gt  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  lt username gt  --password  lt password gt  -D  lt schema gt       awk     Tables in    print  ALTER TABLE    0   ENGINE   InnoDB          column -t     To simply execute the change  use this   echo  SHOW TABLES        mysql -u  lt username gt  --password  lt password gt  -D  lt schema gt       awk     Tables in    print  ALTER TABLE    0   ENGINE   InnoDB          column -t      mysql -u  lt username gt  --password  lt password gt  -D  lt schema gt    CREDIT  This is a variation of what was outlined in this article

User · Answer

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

User · Answer

This is a simple php script    lt  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   lt hr  gt                            if   count     ok               echo   lt div style  color  green  gt  lt b gt ALL OK lt  b gt  lt  div gt         else              echo   lt div style  color  red  gt  lt b gt ERRORS lt  b gt Total tables     count    updated tables    ok   lt  div gt

User · Answer

One line    mysql -u root -p dbName -e    show table status where Engine  MyISAM      awk    NR gt 1  print  ALTER TABLE   1  ENGINE   InnoDB           mysql -u root -p dbName

User · Answer

lt  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  lt  4      exit  Usage    argv 0    lt host gt   lt username gt   lt password gt  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       gt

User · Answer

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

User · Answer

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    gt  my schema sql  mysqldump --no-create-info DBNAME  gt  my data sql    Then load it again   mysql DBNAME  lt  my schema sql  amp  amp  mysql DBNAME  lt  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

User · Answer

lt  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           gt

User · Answer

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

User · Answer

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

[mysql] How to convert all tables from MyISAM into InnoDB?

Examples related to mysql

Examples related to sql

Examples related to innodb